Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-8070

Regression on DDL statements with big stage/sql/checking permissions (version 8.0)


    • Bug
    • Status: Done
    • Medium
    • Resolution: Won't Do
    • 8.0.26-17 (Q3 2021 feature release)
    • None
    • None


      Hi team!

      Writing in order to forward a concern with DDL performance on Server version: 8.0.26-17 Percona Server. 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:




      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)

      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!




            Unassigned Unassigned
            matias.sanchez Matías Sánchez
            1 Vote for this issue
            3 Start watching this issue



              Smart Checklist