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
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