mysqldump of information_schema creates huge file

[Hubertus Krogmann]

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.

[Valerii Kravchuk (valerii-kravchuk)]

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.

[Hubertus Krogmann (hubertus-krogmann)]

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

innodb_buffer_pool_size is 40GB

[Valerii Kravchuk (valerii-kravchuk)]

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...

[Hubertus Krogmann (hubertus-krogmann)]

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."

[Shahriyar Rzayev (rzayev-sehriyar)]

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