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

Extremely slow performance on specific to Wordpress/Woocommerce joins

    XMLWordPrintable

Details

    Description

      For a rather popular query in determining the product attributes in woocommerce we noted a serious performance issue in MariaDB 10.4 and 10.6 (no other versions were tested, thus this issue might be present in other versions).

      In MariaDB the performance of the same query (in the same dataset) can be up to 100 times slower than the same query in MySQL 5.7/8.0.36.

      The culprit is when we have joins of this form:

       LEFT JOIN 
          (SELECT trs.object_id, ts.name FROM wp_term_relationships trs
           JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id 
           JOIN wp_terms ts ON ts.term_id = xs.term_id
           WHERE xs.taxonomy IN ('pa_size', 'pa_megethos', 'pa_noumero')) AS ts ON p.id = ts.object_id
      LEFT JOIN 
          (SELECT trc.object_id, tc.name FROM wp_term_relationships trc
           JOIN wp_term_taxonomy xc ON xc.term_taxonomy_id = trc.term_taxonomy_id 
           JOIN wp_terms tc ON tc.term_id = xc.term_id
           WHERE xc.taxonomy IN ('pa_color', 'pa_chroma', 'pa_colour')) AS tc ON p.id = tc.object_id}}
      

      We feel that either a tuning parameter needs to be set (to which we are unaware) or there is a major flaw/bug in the core of MariaDB.

      Similar issues have been observed in MS SQL Server. However, in this particular instance MS SQL is on par with MySQL 5.7 performance.

      As this query is valuable in woocommerce installations it will be great if a fix is to be issued and/or guidelines to sort out the issue as it impacts millions of installations.

      Please see our thread there:
      https://github.com/woocommerce/woocommerce/issues/46699#issuecomment-2093998359

      A sample dataset is provided here: https://github.com/woocommerce/woocommerce/files/15212736/BenchmarkDump.zip

      (the scripts can be found here:
      https://github.com/woocommerce/woocommerce/files/15213472/sql-scripts.zip

      Attachments

        1. comparative_performance.png
          125 kB
          Oxford Metadata
        2. join_cache_level_7.json.txt
          5 kB
          Oxford Metadata
        3. optimizer-trace-10.11.6.txt
          89 kB
          Daniel Black
        4. optimizer-trace-11.3.2.txt
          126 kB
          Daniel Black
        5. optimizer-trace-11.3.3.txt
          143 kB
          Daniel Black
        6. screenshot-1.png
          10 kB
          Oxford Metadata
        7. spetrunia-10.11-optimizer-use-cond-selectivity.png
          338 kB
          Sergei Petrunia
        8. spetrunia-analyze-11.0.txt
          5 kB
          Sergei Petrunia
        9. spetrunia-rewrite2a-explain.sql
          2 kB
          Sergei Petrunia
        10. spocsfor34097.txt
          18 kB
          Oxford Metadata

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              oxfordmetadata Oxford Metadata
              Votes:
              1 Vote for this issue
              Watchers:
              11 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.