After upgrading to 5.7 mysqldump much slower

[Nikita Beletskii]

1. Get latest 5.7 version (I use official Docker container, but if i build a package from source bug also reproduced)
root@4d9cbcb420f4:/# mysql --version
mysql Ver 14.14 Distrib 5.7.11-4, for debian-linux-gnu (x86_64) using 6.3

2. Create 300k innodb table with simple python/php script. The greater the number of tables => slower dump.

3. Create sakila database:
wget http://downloads.mysql.com/docs/sakila-db.tar.gz

4. Create dump sakila database per table.
root@4d9cbcb420f4:/# time mysql sakila -BNe 'show tables' | while read line; do mysqldump --max_allowed_packet=1024M --force --compress --hex-blob --skip-extended-insert --opt --quote-names --routines --single-transaction --default-character-set=utf8 sakila $line > /tmp/$line.sql; done

real 0m33.789s

Or just database:

root@4d9cbcb420f4:/# time mysqldump --max_allowed_packet=1024M --force --compress --hex-blob --skip-extended-insert --opt --quote-names --routines --single-transaction --default-character-set=utf8 sakila > /tmp/sakila.sql

real 0m2.023s
user 0m0.104s
sys 0m0.028s

5. Percona 5.6 (Per table):

root@daeaf38383ef:/# time mysql sakila -BNe 'show tables' | while read line; do mysqldump --max_allowed_packet=1024M --force --compress --hex-blob --skip-extended-insert --opt --quote-names --routines --single-transaction --default-character-set=utf8 sakila $line > /tmp/$line.sql; done

real 0m0.606s

Just:

root@daeaf38383ef:/# time mysqldump --max_allowed_packet=1024M --force --compress --hex-blob --skip-extended-insert --opt --quote-names --routines --single-transaction --default-character-set=utf8 sakila > /tmp/sakila.sql

real 0m0.281s

6. Most of time is spend to query INFORMATION_SCHEMA.FILES:

Percona 5.7:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILES;
+----------+
| COUNT(*) |
+----------+
| 327317 |
+----------+
1 row in set (1.98 sec)

Percona 5.6:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILES;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

[Georgi Georgiev (melonella)]

Hello,

We have the same problem - after upgrading from 5.6 to 5.7, mysqldump for a single empty table on a server with many InnoDB databases/tables became much slower. We found that the reason for this behaviour are these two queries which are executed by mysqldump:

SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test_db' AND TABLE_NAME IN ('test_table'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE, EXTRA ORDER BY LOGFILE_GROUP_NAME;

SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test_db' AND TABLE_NAME IN ('test_table')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME;

In 5.6 the table INFORMATION_SCHEMA.FILES is empty and the above queries are fast. In 5.7, if you use the innodb_file_per_table option, the INFORMATION_SCHEMA.FILES has an entry for each innodb table on the server. So for a sever with thousands of innodb tables the above queries are slow and the mysqldump takes longer. Our solution was to use mysqldump's option "--no-tablespaces" as we are not using general tablespaces for our tables, but in the case where general tablespaces are used I guess this is not an option.

[Shahriyar Rzayev (rzayev-sehriyar)]

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