Percona Server moved to https://jira.percona.com/projects/PS

mysqldump of information_schema creates huge file

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

Question information

Language:
English Edit question
Status:
Answered
For:
Percona Server moved to https://jira.percona.com/projects/PS Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:

This question was originally filed as bug #1228172.

Revision history for this message
Hubertus Krogmann (hubertus-krogmann) said : 		#1

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

Revision history for this message
Alexey Kopytov (akopytov) said : 		#2

That's how INFORMATION_SCHEMA works: even though it contains dynamically generated data, it creates a temporary MyISAM to materialize that data. And that's why it also consumes disk space. But even if the temporary table was created only in memory, the problem would most likely move to mysqld running out of memory.

So dumping INNODB_BUFFER_PAGE is a bad idea all around. All backup solutions involving mysqldump should be careful to exclude INFORMATION_SCHEMA tables.

Can you help with this problem?

Provide an answer of your own, or ask Hubertus Krogmann for more information if necessary.

To post a message you must log in.