mysqldump of information_schema creates huge file

Bug #1228172 reported by Hubertus Krogmann

This bug report was converted into a question: question #236166: mysqldump of information_schema creates huge file.

6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Invalid
Undecided
Unassigned

Bug Description

Hello

Percona-Server-server-55-5.5.33-rel31.1.566.rhel6.x86_64
RedHat Enterprise Linux Server release 6.4 (Santiago)

nearly no data in the DB, just mysql_installdb and a monitoring table.

tmpdir configured to:
tmpfs /mysqltmp tmpfs rw,gid=27,uid=27,size=10G,nr_inodes=10k,mode=0700 0 0

mysqldump --socket=/var/lib/mysql/mysql.sock --max_allowed_packet=1G --quick --single-transaction -u root -p$(cat /ruut/.mysql) information_schema > ttt.dmp

creates a
-rw-rw---- 1 mysql mysql 10094927872 Sep 20 15:52 #sql_2a5e_0.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 20 15:52 #sql_2a5e_0.MYI

and crashes with:
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `INNODB_BUFFER_PAGE`': Incorrect key file for table '/mysqltmp/#sql_2a5e_0.MYI'; try to repair it (126)

tmpdir on disk runs, because of more space:
-rw-rw---- 1 mysql mysql 17541113148 Sep 20 16:01 #sql_2e10_0.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 20 16:01 #sql_2e10_0.MYI

but why is such a huge temptable created here (about 16GB) for dumping informationschema
the dump is 278Mb (other databases (versions) generate 900kb)

Is this "normal"?
What paramter may influence the size of the dump?
All scripts that loop over all databases could generate a problem here if tmpdir is not big enough.

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

Please, send your my.cnf file content. I wonder how big is your innodb_buffer_pool_size.

As a side note: why are you trying to dump information_schema at all? All it's content is dynamically generated from other sources.

Changed in percona-server:
status: New → Incomplete
Revision history for this message
Hubertus Krogmann (hubertus-krogmann) wrote :

Hi
the script used loops over "show databases" :-) I changed that to exclude information_schema.

innodb_buffer_pool_size is 40GB

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

So, that SELECT generated by mysqldump explicitly called to dump information_schema tried to dump summary of content of 40G buffer pool to disk. No wonder you've got the problem.

Read http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-page-table.html about the table. I think this is more an upstream mysqldump bug/feature request (to skip this table's content by default) if a bug at all. You've got what you asked for...

Changed in percona-server:
status: Incomplete → New
Revision history for this message
Hubertus Krogmann (hubertus-krogmann) wrote :

As Valerii Kravchuk said, more a question:
"I think this is more an upstream mysqldump bug/feature request (to skip this table's content by default) if a bug
at all."

Changed in percona-server:
status: New → Invalid
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3029

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Related questions

Remote bug watches

Bug watches keep track of this bug in other bug trackers.