Bug #93875 mysqldump per-table dump is slow since 5.7 on instances with many tables
Submitted: 10 Jan 2019 13:25 Modified: 25 Nov 2021 16:39
Reporter: Nikolai Ikhalainen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.7.24, 8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[10 Jan 2019 13:25] Nikolai Ikhalainen
Description:
If we are making a backup of sakila test database with mysqldump, one table per mysqldump execution, the total execution time jumps from 6 seconds up to 59 seconds when database has 300k tables in unrelated schema.

In 8.0.13 the situation is even worse: 2m52.321s (compare to 7 seconds if server has just sakila database)

There is no such issue with mysql 5.6 branch.

During execution there are queries generated by mysqldump running for a long time:
*************************** 2. row ***************************
     Id: 77
   User: root
   Host: 127.0.0.1:47106
     db: NULL
Command: Query
   Time: 19
  State: checking permissions
   Info: 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 IN ('sakila'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

     Id: 93
   User: root
   Host: 127.0.0.1:47186
     db: NULL
Command: Query
   Time: 2
  State: Sending data
   Info: 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 IN ('sakila'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

How to repeat:
create several docker images: mysqld started under eatmydata,
it's possible to repeat the issue even without this library, but whole test preparation consuming a huge time. I'm running docker on the host with HDD.

# Dockerfile, do not forget to change FROM to 5.7 and 8.0
FROM mysql:5.6

# Install eat my data
RUN apt-get update && apt-get install -y eatmydata && apt-get clean

# Wrap the entrypoint with eat my data
ENTRYPOINT ["eatmydata", "/entrypoint.sh"]

CMD ["mysqld"]

build 5.6, 5.7 and 8.0 images:
docker pull mysql:8.0;docker build -t eatmydata-mysql-8.0 .

test.sh:
#!/bin/bash
P="--user=root --password=secret --protocol=tcp"
IMG=$1
docker run -d -e MYSQL_ROOT_PASSWORD=secret --name ps3460 $IMG
wget -c https://downloads.mysql.com/docs/sakila-db.tar.gz 2>/dev/null
tar xzf sakila-db.tar.gz
docker exec -i ps3460 bash -c "while ! (mysqladmin $P --silent -c 2 -i 1 --wait=300 ping &>/dev/null) ;do echo -n . ; sleep 1 ; done"
docker exec -i ps3460 bash -c "mysql --silent $P -e 'create database test;create database sakila;select version()' 2>/dev/null"
cat sakila-db/sakila-schema.sql sakila-db/sakila-data.sql | docker exec -i ps3460 bash -c "mysql sakila --silent $P 2>/dev/null"
echo -n "$IMG only sakila, per-table"
docker exec -i ps3460 bash -c "time mysql $P sakila -BNe 'show tables' 2>/dev/null | while read line; do mysqldump $P --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 2>/dev/null ; done"
echo -n "$IMG only sakila, whole database"
docker exec -i ps3460 bash -c "time mysqldump $P --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 2>/dev/null"
docker exec -i ps3460 bash -c "for i in \$(seq 300000) ; do echo CREATE TABLE t\$i \\(id int\\) engine=innodb\\; ; done|mysql $P test 2>/dev/null"
#docker exec -i ps3460 bash -c "time mysql $P test -BNe 'show tables' 2>/dev/null"
sync;sync;sync
echo -n "$IMG many tables in test, sakila, per-table"
docker exec -i ps3460 bash -c "time mysql $P sakila -BNe 'show tables' 2>/dev/null | while read line; do mysqldump $P --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 2>/dev/null ; done"
echo -n "$IMG many tables in test, sakila, whole database"
docker exec -i ps3460 bash -c "time mysqldump $P --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 2>/dev/null"
docker exec -it ps3460 rm -rf /var/lib/mysql/*
docker rm -f ps3460 &>/dev/null

Execute test sh with different versions:
rm bug.log;for i in eatmydata-mysql-5.6 eatmydata-mysql-5.7 eatmydata-mysql-8.0 ; do ./test.sh $i 2>&1 ; done |tee -a bug.log

5.6.42
eatmydata-mysql-5.6 only sakila, per-table
real	0m6.867s
eatmydata-mysql-5.6 only sakila, whole database
real	0m0.267s
eatmydata-mysql-5.6 many tables in test, sakila, per-table
real	0m8.658s
eatmydata-mysql-5.6 many tables in test, sakila, whole database
real	0m0.266s
5.7.24
eatmydata-mysql-5.7 only sakila, per-table
real	0m7.851s
eatmydata-mysql-5.7 only sakila, whole database
real	0m0.300s
eatmydata-mysql-5.7 many tables in test, sakila, per-table
real	0m59.358s
eatmydata-mysql-5.7 many tables in test, sakila, whole database
real	0m2.437s
8.0.13
eatmydata-mysql-8.0 only sakila, per-table
real	0m7.782s
eatmydata-mysql-8.0 only sakila, whole database
real	0m0.325s
eatmydata-mysql-8.0 many tables in test, sakila, per-table
real	2m52.321s
eatmydata-mysql-8.0 many tables in test, sakila, whole database
real	0m6.518s
[14 Jan 2019 14:08] MySQL Verification Team
Hello Nikolai,

Thank you for the report and test case.

regards,
Umesh
[14 Jan 2019 14:09] MySQL Verification Team
test results

Attachment: 93875.results (application/octet-stream, text), 11.08 KiB.

[8 Apr 2019 10:35] MySQL Verification Team
Does this option have any affect?

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_no-tablespaces
[25 Nov 2021 16:39] Margaret Fisher
Posted by developer:
 
Changelog entry added for MySQL 8.0.28:

Producing a per-table dump using mysqldump in MySQL 5.7 and 8.0 requires a longer execution time compared to MySQL 5.6. This is because the information_schema.files table, which is queried for information on log file groups by mysqldump, contains information about InnoDB data files as well as NDB data files from MySQL 5.7. In MySQL 8.0, the issue has been fixed by rewriting the query to select only the appropriate data files. In MySQL 5.7, Information Schema tables do not have indexes, so a full table scan is still required.