Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29104

Performance Issues Reading information_schema.PARAMETERS

Details

    Description

      Reading from information_schema.PARAMETERS table in MariaDB is slower than MySQL by a factor of 20x in the best of cases, and deteriorates linearly in MariaDB once the number of reading threads exceeds the number of available cores, whereas in MySQL the deterioration remains more-or-less flat.

      The following table shows relative performance running mysqlslap against the dataset provided in the attached sql file cs0401486-test.sql with this series of commands

      for i in 1 5 20 100
      		 
      do
      		 
      	mysqlslap -q "select * from information_schema.parameters where SPECIFIC_NAME = 'aaa_sp50001'" -i 10 -c $i
      		 
      done

      Tests conducted in CentOS 7 machines with 1G RAM and approx. 800 real IOPS with one and four Intel i7-9750H cores @ 2.592GHz, average times reported:

      CoresThreads MDB 6.7  MySQL 8 Diff %
      		 
        1     1     0.193    0.008   2413%
      		 
        1     5     0.901    0.015   6007%
      		 
        4     1     0.171    0.004   4275%
      		 
        4     5     0.296    0.007   4229%
      		 
        4     20    1.159    0.066   1756%
      		 
        4    100    19.017   0.340   5593%

      Timings all on MariaDB 10.6.7. Confirmed on 10.6.8, 10.7.4, and 10.8.3 in Rocky Linux.

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-29547 prepare 10.11.0 preview releases

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-20609 Full table scan in INFORMATION_SCHEMA.PARAMETERS/ROUTINES

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 4 older comments
            oleg.smirnov Oleg Smirnov added a comment -

            Short overview of how the Data Dictionary architecture has been reworked in MySQL 8.0: https://www.slideshare.net/StleDeraas/data-dictionary-pl17. One of the reasons mentioned is low performance of queries to INFORMATION_SCHEMA tables.

            oleg.smirnov Oleg Smirnov added a comment - Short overview of how the Data Dictionary architecture has been reworked in MySQL 8.0: https://www.slideshare.net/StleDeraas/data-dictionary-pl17 . One of the reasons mentioned is low performance of queries to INFORMATION_SCHEMA tables.
            oleg.smirnov Oleg Smirnov added a comment -

            Implemented an optimization suggested by psergei:

            commit ac6d5ff303323a45b0125a02f41c760b30d0e1fd (HEAD -> bb-10.11-MDEV-29104, origin/bb-10.11-MDEV-29104)
            		 
            Author: Oleg Smirnov <olernov@gmail.com>
            		 
            Date:   Mon Sep 19 21:36:09 2022 +0700
            		 
             
            		 
                MDEV-29104 Optimize queries to INFORMATION_SCHEMA.PARAMETERS/ROUTINES
            		 
             
            		 
                For queries like
            		 
                    "SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
            		 
                      WHERE SPECIFIC_NAME='proc_name'"
            		 
                and
            		 
                    "SELECT * FROM INFORMATION_SCHEMA.ROUTINES
            		 
                      WHERE ROUTINE_NAME='proc_name'"
            		 
                there is a possibility to avoid loading a stored procedure code and
            		 
                parsing it to retrieve parameters.
            		 
                If the name of the procedure/function is specified explicitly then
            		 
                it is possible to filter out routines that do not match at
            		 
                an early stage.

            oleg.smirnov Oleg Smirnov added a comment - Implemented an optimization suggested by psergei : commit ac6d5ff303323a45b0125a02f41c760b30d0e1fd (HEAD -> bb-10.11-MDEV-29104, origin/bb-10.11-MDEV-29104) Author: Oleg Smirnov <olernov@gmail.com> Date: Mon Sep 19 21:36:09 2022 +0700   MDEV-29104 Optimize queries to INFORMATION_SCHEMA.PARAMETERS/ROUTINES   For queries like "SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='proc_name'" and "SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='proc_name'" there is a possibility to avoid loading a stored procedure code and parsing it to retrieve parameters. If the name of the procedure/function is specified explicitly then it is possible to filter out routines that do not match at an early stage.
            psergei Sergei Petrunia added a comment - 

            queries	Average query time		
            queries	improved2-MDEV-29104	improved1-MDEV-20609	10.11-vanilla
            		 
            1	0.006	0.138	0.136
            		 
            5	0.033	0.283	0.293
            		 
            20	0.158	2.152	2.146
            		 
            100	0.8	10.802	10.868

            psergei Sergei Petrunia added a comment - queries Average query time queries improved2-MDEV-29104 improved1-MDEV-20609 10.11-vanilla 1 0.006 0.138 0.136 5 0.033 0.283 0.293 20 0.158 2.152 2.146 100 0.8 10.802 10.868
            ramesh Ramesh Sivaraman added a comment -

            okay to push

            ramesh Ramesh Sivaraman added a comment - okay to push
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed to 10.11.

            oleg.smirnov Oleg Smirnov added a comment - Pushed to 10.11.

            People

              psergei Sergei Petrunia
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.