After upgrading to 5.7 mysqldump much slower
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
New
|
Undecided
|
Unassigned |
Bug Description
1. Get latest 5.7 version (I use official Docker container, but if i build a package from source bug also reproduced)
root@4d9cbcb420
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://
4. Create dump sakila database per table.
root@4d9cbcb420
real 0m33.789s
Or just database:
root@4d9cbcb420
real 0m2.023s
user 0m0.104s
sys 0m0.028s
5. Percona 5.6 (Per table):
root@daeaf38383
real 0m0.606s
Just:
root@daeaf38383
real 0m0.281s
6. Most of time is spend to query INFORMATION_
Percona 5.7:
SELECT COUNT(*) FROM INFORMATION_
+----------+
| COUNT(*) |
+----------+
| 327317 |
+----------+
1 row in set (1.98 sec)
Percona 5.6:
SELECT COUNT(*) FROM INFORMATION_
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
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.