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

Significant slowdown for query with many outer joins

    XMLWordPrintable

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
          Cyprien Devillez
        2. explain_10418.txt
          3 kB
          Cyprien Devillez
        3. explain_10419.txt
          3 kB
          Cyprien Devillez
        4. mdev-32351_v2.test
          73 kB
          Alice Sherepa
        5. mdev-32351.test
          73 kB
          Alice Sherepa
        6. perf_10.2.37.svg
          137 kB
          Cyprien Devillez
        7. perf_10.2.38.svg
          48 kB
          Cyprien Devillez
        8. perf_10.4.32.svg
          314 kB
          Cyprien Devillez
        9. perf_full_10.4.18_8mn03.svg
          334 kB
          Cyprien Devillez
        10. perf_full_10.4.19_3h48mn28.svg
          106 kB
          Cyprien Devillez
        11. perf_full_10.4.19_reverted_7mn52s.svg
          304 kB
          Cyprien Devillez
        12. trace_10.4.18.json
          161 kB
          Cyprien Devillez
        13. trace_10.4.19.json
          160 kB
          Cyprien Devillez

        Issue Links

          Activity

            People

              igor Igor Babaev
              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.