Description:
regression on DDL statements with stage/sql/checking permissions seen very frequently in 8.0.28 where 5.7.37 shows it less frequently than 8.0!
We found out that there is regression for truncate/create operations and most of the time it goes to "checking permission" which actually slowing down the ddl operation in 8.0 in compare to 5.7
Please see the results from events_stages_summary_by_thread_by_event_name table for 5.7 & 8.0 below:
mysql [localhost:5737] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 5.7.37 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost:5737] {msandbox} ((none)) > SELECT * FROM performance_schema.events_stages_summary_global_by_event_name where EVENT_NAME like "%checking permission%";
+--------------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+--------------------------------+------------+----------------+----------------+----------------+----------------+
| stage/sql/checking permissions | 12014 | 426272795000 | 673000 | 35481000 | 23786915000 |
+--------------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
mysql [localhost:8028] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost:8028] {msandbox} ((none)) > SELECT * FROM performance_schema.events_stages_summary_global_by_event_name where EVENT_NAME like "%checking permission%";
+--------------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+--------------------------------+------------+----------------+----------------+----------------+----------------+
| stage/sql/checking permissions | 15016 | 32297582601000 | 1287000 | 2150877000 | 3161270771000 |
+--------------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
we can see the clear regression between 5.7 & 8.0
How to repeat:
Please follow below steps to repro the issue:
1. prepare schema:
mysql -uroot -e "DROP DATABASE IF EXISTS test1; CREATE DATABASE test1"
mysql -uroot test1 -e "
DROP TABLE IF EXISTS parent;
CREATE TABLE parent (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(10)
);"
for n in $(seq 3000); do
[ $(($n % 100)) -eq 0 ] && echo $n
mysql -uroot test1 -e "
CREATE TABLE child$n (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
fk_field$n int(10) unsigned,
CONSTRAINT fk_$n FOREIGN KEY (fk_field$n) REFERENCES parent(id)
);"
done
#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 test1.', table_name,';') from tables where table_schema = 'test1';" >> truncate_test.sql
time mysql -uroot ttest < truncate_test.sql
2. please see the results by running below query:
SELECT * FROM performance_schema.events_stages_summary_global_by_event_name where EVENT_NAME like "%checking permission%";
Description: regression on DDL statements with stage/sql/checking permissions seen very frequently in 8.0.28 where 5.7.37 shows it less frequently than 8.0! We found out that there is regression for truncate/create operations and most of the time it goes to "checking permission" which actually slowing down the ddl operation in 8.0 in compare to 5.7 Please see the results from events_stages_summary_by_thread_by_event_name table for 5.7 & 8.0 below: mysql [localhost:5737] {msandbox} ((none)) > select version(); +-----------+ | version() | +-----------+ | 5.7.37 | +-----------+ 1 row in set (0.00 sec) mysql [localhost:5737] {msandbox} ((none)) > SELECT * FROM performance_schema.events_stages_summary_global_by_event_name where EVENT_NAME like "%checking permission%"; +--------------------------------+------------+----------------+----------------+----------------+----------------+ | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | +--------------------------------+------------+----------------+----------------+----------------+----------------+ | stage/sql/checking permissions | 12014 | 426272795000 | 673000 | 35481000 | 23786915000 | +--------------------------------+------------+----------------+----------------+----------------+----------------+ 1 row in set (0.00 sec) mysql [localhost:8028] {msandbox} ((none)) > select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+ 1 row in set (0.00 sec) mysql [localhost:8028] {msandbox} ((none)) > SELECT * FROM performance_schema.events_stages_summary_global_by_event_name where EVENT_NAME like "%checking permission%"; +--------------------------------+------------+----------------+----------------+----------------+----------------+ | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | +--------------------------------+------------+----------------+----------------+----------------+----------------+ | stage/sql/checking permissions | 15016 | 32297582601000 | 1287000 | 2150877000 | 3161270771000 | +--------------------------------+------------+----------------+----------------+----------------+----------------+ 1 row in set (0.00 sec) we can see the clear regression between 5.7 & 8.0 How to repeat: Please follow below steps to repro the issue: 1. prepare schema: mysql -uroot -e "DROP DATABASE IF EXISTS test1; CREATE DATABASE test1" mysql -uroot test1 -e " DROP TABLE IF EXISTS parent; CREATE TABLE parent ( id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(10) );" for n in $(seq 3000); do [ $(($n % 100)) -eq 0 ] && echo $n mysql -uroot test1 -e " CREATE TABLE child$n ( id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, fk_field$n int(10) unsigned, CONSTRAINT fk_$n FOREIGN KEY (fk_field$n) REFERENCES parent(id) );" done #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 test1.', table_name,';') from tables where table_schema = 'test1';" >> truncate_test.sql time mysql -uroot ttest < truncate_test.sql 2. please see the results by running below query: SELECT * FROM performance_schema.events_stages_summary_global_by_event_name where EVENT_NAME like "%checking permission%";