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

Performance Issues Reading information_schema.PARAMETERS

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Fix Version/s: 10.11.0
    • Component/s: Server
    • Labels:
      None

      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

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              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.