Description:
Hi team!
We got a deep concern with DDL performance on 8.0 we would like to share. We are on the process of upgrading from mysql 5.7 to 8.0 (8.0.26 latest at the moment) and we are facing a strong regression when running DDL statements.
There is a notable regression on DDL's from mysql 5.7 to 8, and we know that there are many important changes as for example atomic ddl and other key features, but when monitoring we are checking some interesting finding: that most of the time when CREATE or TRUNCATE a table the times goes to "checking permissions" state.
The regression is evident on iterative testings heuristics when we need to recreate schemas many times and so the usage of DDL is a must.
We find that there is about 700% regression for truncate or create operations, and most of the time goes to "checking permission".
As a main comparative example, when run 1K truncate DDL operations and the results are:
* on 5.7: 0.9 secs.
* on 8.0: 7.1 secs.
And on 8.0 specifically, the events_stages_summary_by_thread_by_event_name shows:
+-----------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-----------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| stage/sql/checking permissions | 1787 | 2762026577000 | 2250000 | 1545622000 | 7578288000 |
| stage/sql/waiting for handler commit | 5356 | 406605240000 | 1348000 | 75915000 | 11924169000 |
| stage/sql/starting | 3575 | 41173808000 | 2422000 | 11517000 | 416278000 |
| stage/sql/freeing items | 1788 | 23946382000 | 4369000 | 13392000 | 532165000 |
| stage/sql/query end | 1788 | 2904761000 | 438000 | 1624000 | 13733000 |
| stage/sql/closing tables | 1788 | 1362747000 | 281000 | 762000 | 527090000 |
| stage/sql/cleaning up | 1788 | 1325702000 | 416000 | 741000 | 23470000 |
When this is not the main stage on mysql for the same operations. "Checking permission" stage on 5.7 is just a few % of times, the "opening" table instead takes the top but not on the same impact.
As part of the analysis we tested many settings sets with no success and also checked that the current set of users + permissions is actually the fewer possible, as there exists only 1 user with wide permissions.
Any ideas if there is some possible action to reduce this "checking permission" state?? Maybe even if unsecuring any feature is needed.
Thanks a lot in advance for any clue on this matter.
Best regards!
Matías
How to repeat:
***************************************************
This is easily reproducible if you wish as follows:
Pulling both version 5.7 & 8 and running
1. Prepare and empty schema
mysql -uroot -e "DROP DATABASE IF EXISTS ttest; CREATE DATABASE ttest"
mysql -uroot ttest -e "
DROP TABLE IF EXISTS fkParent;
CREATE TABLE fkParent (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
textField varchar(10)
);"
for n in $(seq 3000); do
[ $(($n % 100)) -eq 0 ] && echo $n
mysql -uroot ttest -e "
CREATE TABLE fkChild$n (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
fkField$n int(10) unsigned,
CONSTRAINT FK_$n FOREIGN KEY (fkField$n) REFERENCES fkParent(id)
);"
done
2. Truncate tables
echo "SET FOREIGN_KEY_CHECKS=0;" > truncate_test.sql
echo "SET UNIQUE_CHECKS=0;" >> truncate_test.sql
mysql -uroot -A -BN information_schema -e"
select concat('TRUNCATE ttest.', table_name,';')
from tables
where table_schema = 'ttest';" >> truncate_test.sql
time mysql -uroot ttest < truncate_test.sql
Comparing results on same environment shows clear differences.
***************************************************
Description: Hi team! We got a deep concern with DDL performance on 8.0 we would like to share. We are on the process of upgrading from mysql 5.7 to 8.0 (8.0.26 latest at the moment) and we are facing a strong regression when running DDL statements. There is a notable regression on DDL's from mysql 5.7 to 8, and we know that there are many important changes as for example atomic ddl and other key features, but when monitoring we are checking some interesting finding: that most of the time when CREATE or TRUNCATE a table the times goes to "checking permissions" state. The regression is evident on iterative testings heuristics when we need to recreate schemas many times and so the usage of DDL is a must. We find that there is about 700% regression for truncate or create operations, and most of the time goes to "checking permission". As a main comparative example, when run 1K truncate DDL operations and the results are: * on 5.7: 0.9 secs. * on 8.0: 7.1 secs. And on 8.0 specifically, the events_stages_summary_by_thread_by_event_name shows: +-----------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+ | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | +-----------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+ | stage/sql/checking permissions | 1787 | 2762026577000 | 2250000 | 1545622000 | 7578288000 | | stage/sql/waiting for handler commit | 5356 | 406605240000 | 1348000 | 75915000 | 11924169000 | | stage/sql/starting | 3575 | 41173808000 | 2422000 | 11517000 | 416278000 | | stage/sql/freeing items | 1788 | 23946382000 | 4369000 | 13392000 | 532165000 | | stage/sql/query end | 1788 | 2904761000 | 438000 | 1624000 | 13733000 | | stage/sql/closing tables | 1788 | 1362747000 | 281000 | 762000 | 527090000 | | stage/sql/cleaning up | 1788 | 1325702000 | 416000 | 741000 | 23470000 | When this is not the main stage on mysql for the same operations. "Checking permission" stage on 5.7 is just a few % of times, the "opening" table instead takes the top but not on the same impact. As part of the analysis we tested many settings sets with no success and also checked that the current set of users + permissions is actually the fewer possible, as there exists only 1 user with wide permissions. Any ideas if there is some possible action to reduce this "checking permission" state?? Maybe even if unsecuring any feature is needed. Thanks a lot in advance for any clue on this matter. Best regards! Matías How to repeat: *************************************************** This is easily reproducible if you wish as follows: Pulling both version 5.7 & 8 and running 1. Prepare and empty schema mysql -uroot -e "DROP DATABASE IF EXISTS ttest; CREATE DATABASE ttest" mysql -uroot ttest -e " DROP TABLE IF EXISTS fkParent; CREATE TABLE fkParent ( id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, textField varchar(10) );" for n in $(seq 3000); do [ $(($n % 100)) -eq 0 ] && echo $n mysql -uroot ttest -e " CREATE TABLE fkChild$n ( id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, fkField$n int(10) unsigned, CONSTRAINT FK_$n FOREIGN KEY (fkField$n) REFERENCES fkParent(id) );" done 2. Truncate tables echo "SET FOREIGN_KEY_CHECKS=0;" > truncate_test.sql echo "SET UNIQUE_CHECKS=0;" >> truncate_test.sql mysql -uroot -A -BN information_schema -e" select concat('TRUNCATE ttest.', table_name,';') from tables where table_schema = 'ttest';" >> truncate_test.sql time mysql -uroot ttest < truncate_test.sql Comparing results on same environment shows clear differences. ***************************************************