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

querying performance schema is slower in 10.5 ff

    XMLWordPrintable

Details

    Description

      Querying PERFORMANCE SCHEMA takes longer (sometimes much longer) in MariaDB 10.5 and all later releases compared to 10.4. Specifcally the latest Enterprise Release were tested: 10.4.34-24 ,10.5.26-20, 10.6.24-20, 11.4.9-6 and 11.8.5-2. Those 3 queries were used:

      Q1:

      SELECT OBJECT_SCHEMA, OBJECT_NAME,
               COUNT_READ_NORMAL,
               COUNT_READ_WITH_SHARED_LOCKS,
               COUNT_READ_HIGH_PRIORITY,
               COUNT_READ_NO_INSERT,
               COUNT_READ_EXTERNAL,
               COUNT_WRITE_ALLOW_WRITE,
               COUNT_WRITE_CONCURRENT_INSERT,
               COUNT_WRITE_LOW_PRIORITY,
               COUNT_WRITE_NORMAL,
               COUNT_WRITE_EXTERNAL,
               SUM_TIMER_READ_NORMAL,
               SUM_TIMER_READ_WITH_SHARED_LOCKS,
               SUM_TIMER_READ_HIGH_PRIORITY,
               SUM_TIMER_READ_NO_INSERT,
               SUM_TIMER_READ_EXTERNAL,
               SUM_TIMER_WRITE_ALLOW_WRITE,
               SUM_TIMER_WRITE_CONCURRENT_INSERT,
               SUM_TIMER_WRITE_LOW_PRIORITY,
               SUM_TIMER_WRITE_NORMAL,
               SUM_TIMER_WRITE_EXTERNAL
      FROM performance_schema.table_lock_waits_summary_by_table
      WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema','information_schema');
      

      Q2:

      SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
      FROM performance_schema.events_waits_summary_global_by_event_name;
      

      Q3:

      SELECT OBJECT_SCHEMA, OBJECT_NAME, 
        ifnull(INDEX_NAME, 'NONE') AS INDEX_NAME, COUNT_FETCH, COUNT_INSERT,
        COUNT_UPDATE, COUNT_DELETE, SUM_TIMER_FETCH, SUM_TIMER_INSERT, 
        SUM_TIMER_UPDATE, SUM_TIMER_DELETE
      FROM performance_schema.table_io_waits_summary_by_index_usage
      WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema');
      

      The execution time depends mostly on the number of tables:

      More details on query timing are in this Google Sheet.

      Attachments

        1. Q1-16thd.png
          Q1-16thd.png
          38 kB
        2. Q2-16thd.png
          Q2-16thd.png
          40 kB
        3. Q3-16thd.png
          Q3-16thd.png
          38 kB
        4. default,10K.png
          default,10K.png
          61 kB
        5. flamegraph.10.4.svg
          86 kB
        6. flamegraph.10.5.svg
          44 kB
        7. flamegraph.10.6.svg
          51 kB
        8. flamegraph.11.4.svg
          52 kB
        9. flamegraph.11.8.svg
          50 kB

        Activity

          People

            serg Sergei Golubchik
            axel Axel Schwenke
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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