Regression on DDL statements with big stage/sql/checking permissions

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 Percona Server 8.0 (8.0.26-17 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.


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

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


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

Hi Matias, thank you for your the detailed info. I suggest you open a bug report with this in jira.percona.com so that the dev team can take a look.

3 Likes

Thanks a lot igroene! I had just opened bug report on jira.percona as well looking for dev team feedback.

Best Regards!

2 Likes