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

Server runs forever on a SELECT query, using 100%CPU, thread stuck in 'Statistics' state when using optimizer_use_condition_selectivity=1

    XMLWordPrintable

Details

    Description

      Running a query like this one is guaranteed to send mysqld spinning:

      SELECT wpsc_posts.* FROM wpsc_posts INNER JOIN wpsc_postmeta ON ( wpsc_posts.ID = wpsc_postmeta.post_id ) INNER JOIN wpsc_postmeta AS mt1 ON ( wpsc_posts.ID = mt1.post_id ) INNER JOIN wpsc_postmeta AS mt2 ON ( wpsc_posts.ID = mt2.post_id ) INNER JOIN wpsc_postmeta AS mt3 ON ( wpsc_posts.ID = mt3.post_id ) INNER JOIN wpsc_postmeta AS mt4 ON ( wpsc_posts.ID = mt4.post_id ) INNER JOIN wpsc_postmeta AS mt5 ON ( wpsc_posts.ID = mt5.post_id )  INNER JOIN wpsc_postmeta AS mt6 ON ( wpsc_posts.ID = mt6.post_id )  INNER JOIN wpsc_postmeta AS mt7 ON ( wpsc_posts.ID = mt7.post_id )  INNER JOIN wpsc_postmeta AS mt8 ON ( wpsc_posts.ID = mt8.post_id )  INNER JOIN wpsc_postmeta AS mt9 ON ( wpsc_posts.ID = mt9.post_id )  INNER JOIN wpsc_postmeta AS mt10 ON ( wpsc_posts.ID = mt10.post_id )  INNER JOIN wpsc_postmeta AS mt11 ON ( wpsc_posts.ID = mt11.post_id )  INNER JOIN wpsc_postmeta AS mt12 ON ( wpsc_posts.ID = mt12.post_id ) WHERE 1=1  AND ( ( wpsc_postmeta.meta_key = 'gender' AND wpsc_postmeta.meta_value = 'Female' ) AND ( mt1.meta_key = 'age' AND mt1.meta_value = '28-30' ) AND ( mt2.meta_key = 'actor_ethnicity' AND mt2.meta_value = 'Maltese' ) AND ( mt3.meta_key = 'complexion' AND mt3.meta_value = 'Fair Freckles' ) AND ( mt4.meta_key = 'natural_accent' AND mt4.meta_value = 'Maltese' ) AND ( mt5.meta_key = 'actor_height' AND mt5.meta_value = '66-70' ) AND ( mt6.meta_key = 'chest' AND mt6.meta_value = '813' ) AND ( mt7.meta_key = 'waist' AND mt7.meta_value = '6' ) AND ( mt8.meta_key = 'hip' AND mt8.meta_value = '191' ) AND ( mt9.meta_key = 'eye_colour' AND mt9.meta_value = 'Cyan' ) AND ( mt10.meta_key = 'hair_colour' AND mt10.meta_value = 'Pink' ) AND ( mt11.meta_key = 'male_clothing_size' AND mt11.meta_value = 'M' ) AND ( mt12.meta_key = 'female_clothing_size' AND mt12.meta_value = '88' ) ) AND wpsc_posts.post_type = 'actors' AND ((wpsc_posts.post_status = 'publish')) GROUP BY wpsc_posts.ID ORDER BY wpsc_posts.post_date DESC;
      

      The number of rows in the two tables are:

      mysql> select count(*) from wpsc_posts;
      +----------+
      | count(*) |
      +----------+
      |     7391 |
      +----------+
      1 row in set (0.00 sec)
       
      mysql> select count(*) from wpsc_postmeta;
      +----------+
      | count(*) |
      +----------+
      |    55585 |
      +----------+
      1 row in set (0.03 sec)
      

      It seems to be data dependent, I tried to replicate it with a small dataset but it worked fine.
      Tested on 10.1.37, 10.2.19, 10.3.11 and 10.4.0, all equally affected.

      The same query run on the same database on Percona 5.7.23-23-57-log returns immediately with:
      Empty set (0.00 sec)
      Where should I send core dumps generated with kill -ABRT?

      Attachments

        1. apache_processes-week.png
          41 kB
          burnley
        2. createtbl.sql
          5 kB
          Lingmei Weng
        3. MDEV-17878.sql
          6 kB
          Roel Van de Paar
        4. slow.sql
          2 kB
          Lingmei Weng

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              burnley burnley
              Votes:
              0 Vote for this issue
              Watchers:
              8 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.