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

Drastically slower query performance between CentOS (2sec) and Rocky (48sec)

Details

    Description

      There is a significant difference in query performance with MariaDB 10.6 running on CentOS (2 seconds) compared to on Rocky (48 seconds). When setting split_materialized=off, the query executes a bit faster (7 seconds) for this particular query, but it is still slower than on CentOS.

      Query used to reproduce:

      set session optimizer_switch='split_materialized=on';
      		 
       
      		 
      use b_test;
      		 
       
      		 
      select this_.`RMD_CURRENT_STATUS_VALUE` as y0_, count(*) as y1_
      		 
      from `TEST_MAU_REQUEST_RECORD_VIEW` this_
      		 
      where (lower(this_.`UAR_ALL_EMAILS`) like '%AbeFrazer8@example.com%' escape '!'
      		 
      or lower(this_.`UAR_APPROVER_STLS`) like '%AbeFrazer8@example.com%' escape '!'
      		 
      or lower(this_.`REQUEST_BY`) like '%AbeFrazer8@example.com%' escape '!')
      		 
      group by this_.`RMD_CURRENT_STATUS_VALUE`
      		 
      order by y0_ asc
      		 
      limit 50;

      Attachments

        1. b_test.sql.zip
          2.25 MB
        2. mdev-34043_analyze_centos7.json
          4 kB
        3. mdev-34043_analyze_rl9_ext4.json
          4 kB
        4. mdev-34043_analyze_rl9.json
          4 kB
        5. mdev-34043.tgz
          91 kB
        6. rl9_analyze_json_10.6.txt
          4 kB
        7. rl9_analyze_json_11.5.txt
          7 kB

        Activity

          Ascending order - Click to sort in descending order
          View 11 older comments
          monty Michael Widenius added a comment -

          Pushed to bb-10.5-monty for testing

          monty Michael Widenius added a comment - Pushed to bb-10.5-monty for testing
          monty Michael Widenius added a comment -

          I have worked with Gosselin to test my patch.
          The patch makes split_materialized ON or OFF to have same performance (about 6 seconds) on his machine.
          The given test called maria_delete_all_rows() 678199 times and each call called ftruncate() 3 times.
          I assume that on some OS ftruncate does something similar to a sync, which is causing the performance problem.
          After the patch, ftruncate() is not called at all for internal temporary tables, which fixes the problem.

          monty Michael Widenius added a comment - I have worked with Gosselin to test my patch. The patch makes split_materialized ON or OFF to have same performance (about 6 seconds) on his machine. The given test called maria_delete_all_rows() 678199 times and each call called ftruncate() 3 times. I assume that on some OS ftruncate does something similar to a sync, which is causing the performance problem. After the patch, ftruncate() is not called at all for internal temporary tables, which fixes the problem.
          psergei Sergei Petrunia added a comment -

          Ok to push.

          psergei Sergei Petrunia added a comment - Ok to push.
          monty Michael Widenius added a comment -

          bb-10.5-monty pushed for final testing by buildbot

          monty Michael Widenius added a comment - bb-10.5-monty pushed for final testing by buildbot
          monty Michael Widenius added a comment -

          Pushed to 10.5

          monty Michael Widenius added a comment - Pushed to 10.5

          People

            monty Michael Widenius
            martin.reinhardt@mariadb.com Martin Reinhardt
            Votes:
            1 Vote for this issue
            Watchers:
            12 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.