Bug #83143 | Performance regression in dictionary operations due to metadata locking | ||
---|---|---|---|
Submitted: | 26 Sep 2016 2:52 | Modified: | 11 Dec 2017 15:21 |
Reporter: | Szymon Komendera | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Data Dictionary | Severity: | S5 (Performance) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[26 Sep 2016 2:52]
Szymon Komendera
[26 Sep 2016 11:51]
MySQL Verification Team
Hello Szymon Komendera, Thank you for the report. Thanks, Umesh
[26 Sep 2016 11:52]
MySQL Verification Team
-- Schema CREATE DATABASE IF NOT EXISTS test; use test; DELIMITER // DROP PROCEDURE IF EXISTS ct// CREATE PROCEDURE ct() BEGIN SET @v1 = 100; WHILE @v1 > 0 DO SET @s = CONCAT('DROP DATABASE IF EXISTS ', 's_' , @v1,''); PREPARE stm FROM @s; EXECUTE stm; SET @s = CONCAT('CREATE DATABASE ', 's_' , @v1,''); PREPARE stm FROM @s; EXECUTE stm; SET @v2 = 10000; WHILE @v2 > 0 DO SET @s = CONCAT('CREATE TABLE ','s_',@v1,'.','t',@v2,'(id int not null)'); PREPARE stm FROM @s; EXECUTE stm; SET @v2 = @v2 - 1; END WHILE; SET @v1 = @v1 - 1; END WHILE; END // DELIMITER ; call ct; -- will create 100 Schema, and 10K tables in each schema ^^ recreate SP, and rerun call ct if you encounter any errors -- Alternatively, you can use sysbench to create required schema ./bulk.sh cat bulk.sh #!/bin/bash for i in {1..100} do bin/mysql -uroot -S /tmp/mysql_ushastry.sock -e "CREATE DATABASE IF NOT EXISTS s_$i;" /home/umshastr/bugs/sysbench/bin/sysbench --test="/home/umshastr/bugs/sysbench/sysbench/tests/db/oltp.lua" --num-threads=100 --mysql-table-engine=innodb --mysql-db="s_$i" --oltp-tables-count=10000 --oltp-table-size=1 --mysql-user=root --mysql-socket=/tmp/mysql_ushastry.sock prepare done -- 5.7.15 rm -rf 83141 bin/mysqld --initialize-insecure --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/83141 -v bin/mysqld --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/83141 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/83141/log.err 2>&1 & mysql> call ct; -- will create 100 Schema, and 10K tables in each schema Query OK, 0 rows affected (1 hour 1 min 2.16 sec) [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: time bin/mysqldump --no-data -uroot -S /tmp/mysql_ushastry.sock s_1 > schema.sql real 9m11.213s user 0m2.604s sys 0m3.086s [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: ls -lh schema.sql -rw-r--r-- 1 umshastr common 3.2M Sep 26 11:14 schema.sql -- 8.0 [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-advanced-8.0: cat docs/INFO_SRC commit: 47ba5514e4e485ef74579d6efed09bbbe58f8427 date: 2016-09-22 10:36:52 +0200 build-date: 2016-09-22 17:03:14 +0200 short: 47ba551 branch: mysql-trunk MySQL source 8.0.1 rm -rf 83141 bin/mysqld --initialize-insecure --basedir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0 --datadir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83141 -v bin/mysqld --basedir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0 --datadir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83141 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83141/log.err 2>&1 & mysql> call ct; -- will create 100 Schema, and 10K tables in each schema Query OK, 0 rows affected (1 hour 32 min 24.42 sec) [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-advanced-8.0: time bin/mysqldump --no-data -uroot -S /tmp/mysql_ushastry.sock s_1 > schema.sql real 35m47.119s user 0m2.561s sys 0m3.230s
[11 Dec 2017 15:21]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.4 release, and here's the changelog entry: Operations that rely heavily on the metadata locking (MDL) subsystem caused a performance degradation. Traversal of MDL ticket lists was time consuming in cases where there were large number of MDL tickets.