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

Significant slowdown for query with many outer joins

Details

    Description

      Summary

      With MariaDB 10.2.38,10.3.29,10.4.19,10.5.10,10.6.1 and later version, this request (https://github.com/cypx/mariadb-issue/blob/main/request.sql) launched on this database (https://github.com/cypx/mariadb-issue/blob/main/magentodb.sql anonymized tables from a partial export of a Magento 2 database) need 20x more time to finish.
      I get consistent test results with my laptop and a cloud VM on many version (including latest) of the 10.2,10.3,10.4,10.5 and 10.6 branch. I don't run any test on 10.7 branch or later because they are not officially supported by the affected software (Magento 2).

      Reproduce

      On a Linux system with an up-to-date docker install you could clone the following repository https://github.com/cypx/mariadb-issue and launch the ./bench.sh script followed by the MariaDB you want to test (ex: ./bench.sh 10.2.37 )

      Example on my laptop (CPU: Intel i7-1065G7, Memory: 16 GB)

      $ git clone https://github.com/cypx/mariadb-issue.git
      Cloning into 'mariadb-issue'...
      remote: Enumerating objects: 15, done.
      remote: Total 15 (delta 0), reused 0 (delta 0), pack-reused 15
      Receiving objects: 100% (15/15), 59.30 MiB | 22.61 MiB/s, done.
      Resolving deltas: 100% (3/3), done.
      $ cd mariadb-issue
      $ ./bench.sh 10.2.37
      [+] Running 14/14
       ✔ mariadb 13 layers [⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿]      0B/0B      Pulled                                                                                                                                                                         9.7s
         ✔ 01bf7da0a88c Pull complete                                                                                                                                                                                                     1.1s
         ✔ f3b4a5f15c7a Pull complete                                                                                                                                                                                                     0.3s
         ✔ 57ffbe87baa1 Pull complete                                                                                                                                                                                                     0.3s
         ✔ 7ac3ea3a19e9 Pull complete                                                                                                                                                                                                     0.7s
         ✔ b89d9d23853d Pull complete                                                                                                                                                                                                     0.8s
         ✔ 6512bf94537d Pull complete                                                                                                                                                                                                     1.0s
         ✔ d75afd385b71 Pull complete                                                                                                                                                                                                     1.1s
         ✔ ab1935952eac Pull complete                                                                                                                                                                                                     1.4s
         ✔ 209bb6034896 Pull complete                                                                                                                                                                                                     1.4s
         ✔ a193656db97c Pull complete                                                                                                                                                                                                     1.4s
         ✔ 392397d32761 Pull complete                                                                                                                                                                                                     4.0s
         ✔ 48dab60bfc0f Pull complete                                                                                                                                                                                                     1.7s
         ✔ 76d69b7780e7 Pull complete                                                                                                                                                                                                     1.8s
      [+] Running 2/2
       ✔ Network mariadb-issue_default      Created                                                                                                                                                                                       0.1s
       ✔ Container mariadb-issue-mariadb-1  Healthy                                                                                                                                                                                       1.4s
       # MariaDB 10.2.37 - request start: mer. 04 oct. 2023 16:34:10 CEST
       
      real    4m7,279s
      user    0m0,713s
      sys     0m1,516s
       # MariaDB 10.2.37 - request end: mer. 04 oct. 2023 16:38:18 CEST
      [+] Running 2/2
       ✔ Container mariadb-issue-mariadb-1  Removed                                                                                                                                                                                       2.6s
       ✔ Network mariadb-issue_default      Removed                                                                                                                                                                                       0.3s
      $ ./bench.sh 10.2.38
      [+] Running 12/12
       ✔ mariadb 11 layers [⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿]      0B/0B      Pulled                                                                                                                                                                          10.9s
         ✔ 25fa05cd42bd Pull complete                                                                                                                                                                                                     1.1s
         ✔ 09d7f527a85d Pull complete                                                                                                                                                                                                     0.4s
         ✔ 21ffc1a82bcc Pull complete                                                                                                                                                                                                     0.5s
         ✔ 499ae120a92c Pull complete                                                                                                                                                                                                     0.9s
         ✔ b1026105733a Pull complete                                                                                                                                                                                                     0.8s
         ✔ f93078b4478c Pull complete                                                                                                                                                                                                     1.3s
         ✔ dafd08f8c025 Pull complete                                                                                                                                                                                                     1.2s
         ✔ 25dd530577f8 Pull complete                                                                                                                                                                                                     1.6s
         ✔ 8ea3241729e6 Pull complete                                                                                                                                                                                                     3.6s
         ✔ f489e9c74b8e Pull complete                                                                                                                                                                                                     1.7s
         ✔ cd3a9c186264 Pull complete                                                                                                                                                                                                     1.9s
      [+] Running 2/2
       ✔ Network mariadb-issue_default      Created                                                                                                                                                                                       0.1s
       ✔ Container mariadb-issue-mariadb-1  Healthy                                                                                                                                                                                       2.7s
       # MariaDB 10.2.38 - request start: mer. 04 oct. 2023 16:41:26 CEST
       
      real    102m4,558s
      user    0m0,977s
      sys     0m1,410s
       # MariaDB 10.2.38 - request end: mer. 04 oct. 2023 18:23:30 CEST
      [+] Running 2/2
       ✔ Container mariadb-issue-mariadb-1  Removed                                                                                                                                                                                       2.2s
       ✔ Network mariadb-issue_default      Removed                                                                                                                                                                                       0.4s
      

      Attachments

        1. explain_10237.txt
          3 kB
        2. explain_10418.txt
          3 kB
        3. explain_10419.txt
          3 kB
        4. mdev-32351_v2.test
          73 kB
        5. mdev-32351.test
          73 kB
        6. perf_10.2.37.svg
          137 kB
        7. perf_10.2.38.svg
          48 kB
        8. perf_10.4.32.svg
          314 kB
        9. perf_full_10.4.18_8mn03.svg
          334 kB
        10. perf_full_10.4.19_3h48mn28.svg
          106 kB
        11. perf_full_10.4.19_reverted_7mn52s.svg
          304 kB
        12. trace_10.4.18.json
          161 kB
        13. trace_10.4.19.json
          160 kB

        Issue Links

          Activity

            danblack Daniel Black added a comment -

            Thanks for testing cypx. Good to know the positive case worked. As we arrived at this bug however its making sure all the other queries also perform correctly and fast as well.

            danblack Daniel Black added a comment - Thanks for testing cypx . Good to know the positive case worked. As we arrived at this bug however its making sure all the other queries also perform correctly and fast as well.
            danblack Daniel Black added a comment -

            cypx, I should have also asked, the result was correct for the query?

            danblack Daniel Black added a comment - cypx , I should have also asked, the result was correct for the query?

            Daniel Black, The result was correct and it was the same between 10.4.18 & 10.4.32 (diff & md5 match).

            cypx Cyprien Devillez added a comment - Daniel Black, The result was correct and it was the same between 10.4.18 & 10.4.32 (diff & md5 match).

            igor, it's a problem that there's no visible test coverage for "Don't unpack the buffer if we have MATCH_IMPOSSIBLE" functionality.

            I've backported r_unpack_time_ms patch from 10.6: https://github.com/MariaDB/server/commit/a9cf22654e99b6b40d7eedd6e7443c1a2813da17

            After that, one can do with a very small patch to add tests for this: https://github.com/MariaDB/server/commit/5745468d17e6f48d136935329a55cc97090024e1

            Both patches are in bb-10.4-mdev32351-tests, on top of your fix. What do you think?

            psergei Sergei Petrunia added a comment - igor , it's a problem that there's no visible test coverage for "Don't unpack the buffer if we have MATCH_IMPOSSIBLE" functionality. I've backported r_unpack_time_ms patch from 10.6: https://github.com/MariaDB/server/commit/a9cf22654e99b6b40d7eedd6e7443c1a2813da17 After that, one can do with a very small patch to add tests for this: https://github.com/MariaDB/server/commit/5745468d17e6f48d136935329a55cc97090024e1 Both patches are in bb-10.4-mdev32351-tests, on top of your fix. What do you think?

            ... The fix itself is ok, although it's difficult to understand.

            psergei Sergei Petrunia added a comment - ... The fix itself is ok, although it's difficult to understand.

            People

              igor Igor Babaev (Inactive)
              cypx Cyprien Devillez
              Votes:
              0 Vote for this issue
              Watchers:
              10 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.