Comment 2 for bug 1589334

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.