Bug #93875 mysqldump per-table dump is slow since 5.7 on instances with many tables
Submitted: 10 Jan 13:25 Modified: 14 Jan 14:08
Reporter: Nikolai Ikhalainen Email Updates:
Status: Verified 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 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 14:08] Umesh Shastry
Hello Nikolai,

Thank you for the report and test case.

regards,
Umesh
[8 Apr 10:35] Shane Bester
Does this option have any affect?

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_no-tablespaces