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

        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.