[MDEV-17878] Server runs forever on a SELECT query, using 100%CPU, thread stuck in 'Statistics' state when using optimizer_use_condition_selectivity=1 Created: 2018-11-30  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.37, 10.2.19, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: burnley Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-10.6+, performance, thread_hang

Attachments: File MDEV-17878.sql     PNG File apache_processes-week.png     File createtbl.sql     File slow.sql    
Issue Links:
Relates
relates to MDEV-12343 Slow performance versus MySQL 5.7 Stalled

 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?

 Comments   
Comment by burnley [ 2018-11-30 ]

The query works also fine on MySQL Server version: 5.7.24 MySQL Community Server (GPL)

Comment by burnley [ 2018-11-30 ]

perf report shows:
56.43% mysqld mysqld [.] prev_record_reads
28.92% mysqld mysqld [.] best_access_path
3.95% mysqld mysqld [.] best_extension_by_limited_search
3.82% mysqld mysqld [.] advance_sj_state
1.40% mysqld mysqld [.] ha_innobase::read_time
0.89% mysqld mysqld [.] TABLE::actual_key_flags
0.89% mysqld mysqld [.] ha_innobase::index_flags
0.76% mysqld mysqld [.] Sj_materialization_picker::check_qep

Comment by Alice Sherepa [ 2018-11-30 ]

Could you please provide output of 

SHOW CREATE TABLE wpsc_posts; 
SHOW CREATE TABLE wpsc_postmeta;
 
EXPLAIN query;

Comment by burnley [ 2018-12-02 ] 

 
 
| wpsc_posts | CREATE TABLE `wpsc_posts` (
 
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
 
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
 
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
 
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
 
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
 
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `menu_order` int(11) NOT NULL DEFAULT '0',
 
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
 
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`ID`),
 
  KEY `post_name` (`post_name`(191)),
 
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
 
  KEY `post_parent` (`post_parent`),
 
  KEY `post_author` (`post_author`)
 
) ENGINE=InnoDB AUTO_INCREMENT=9100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
 
 
 
| wpsc_postmeta | CREATE TABLE `wpsc_postmeta` (
 
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
 
  PRIMARY KEY (`meta_id`),
 
  KEY `post_id` (`post_id`),
 
  KEY `meta_key` (`meta_key`(191))
 
) ENGINE=InnoDB AUTO_INCREMENT=74617 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
 
 
 
EXPLAIN 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;
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
 
| id | select_type | table         | partitions | type   | possible_keys                                              | key      | key_len | ref                                 | rows | filtered | Extra                                        |
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
 
|  1 | SIMPLE      | mt12          | NULL       | ref    | post_id,meta_key                                           | meta_key | 767     | const                               |   16 |    10.00 | Using where; Using temporary; Using filesort |
 
|  1 | SIMPLE      | wpsc_posts    | NULL       | eq_ref | PRIMARY,post_name,type_status_date,post_parent,post_author | PRIMARY  | 8       | aditest_screk6qctorsau.mt12.post_id |    1 |     5.00 | Using where                                  |
 
|  1 | SIMPLE      | wpsc_postmeta | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt2           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt3           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt4           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt5           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt11          | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt8           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt6           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt7           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt1           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt9           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt10          | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+

Comment by Alice Sherepa [ 2018-12-03 ]

Please add also EXPLAIN for the query from Percona server and also your .cnf file

Comment by burnley [ 2018-12-03 ] 

On Percona:
 
mysql> EXPLAIN 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;
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-----------------------------------+------+----------+----------------------------------------------+
 
| id | select_type | table         | partitions | type   | possible_keys                                              | key      | key_len | ref                               | rows | filtered | Extra                                        |
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-----------------------------------+------+----------+----------------------------------------------+
 
|  1 | SIMPLE      | mt12          | NULL       | ref    | post_id,meta_key                                           | meta_key | 767     | const                             |   16 |    10.00 | Using where; Using temporary; Using filesort |
 
|  1 | SIMPLE      | wpsc_posts    | NULL       | eq_ref | PRIMARY,post_name,type_status_date,post_parent,post_author | PRIMARY  | 8       | aditest_screk6qctors.mt12.post_id |    1 |     5.00 | Using where                                  |
 
|  1 | SIMPLE      | wpsc_postmeta | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt2           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt3           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt4           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt5           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt11          | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt8           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt6           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt7           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt1           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt9           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
|  1 | SIMPLE      | mt10          | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctors.mt12.post_id |    7 |     0.69 | Using where                                  |
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-----------------------------------+------+----------+----------------------------------------------+
 
14 rows in set, 1 warning (0.03 sec)
 
 
 
On MySQL 5.7.24:
 
mysql> EXPLAIN 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;
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
 
| id | select_type | table         | partitions | type   | possible_keys                                              | key      | key_len | ref                                 | rows | filtered | Extra                                        |
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
 
|  1 | SIMPLE      | mt12          | NULL       | ref    | post_id,meta_key                                           | meta_key | 767     | const                               |   16 |    10.00 | Using where; Using temporary; Using filesort |
 
|  1 | SIMPLE      | wpsc_posts    | NULL       | eq_ref | PRIMARY,post_name,type_status_date,post_parent,post_author | PRIMARY  | 8       | aditest_screk6qctorsau.mt12.post_id |    1 |     5.00 | Using where                                  |
 
|  1 | SIMPLE      | wpsc_postmeta | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt2           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt3           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt4           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt5           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt11          | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt8           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt6           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt7           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt1           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt9           | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
|  1 | SIMPLE      | mt10          | NULL       | ref    | post_id,meta_key                                           | post_id  | 8       | aditest_screk6qctorsau.mt12.post_id |    7 |     0.68 | Using where                                  |
 
+----+-------------+---------------+------------+--------+------------------------------------------------------------+----------+---------+-------------------------------------+------+----------+----------------------------------------------+
 
14 rows in set, 1 warning (0.11 sec)
 
 
 
And some configurations:
 
1. One test MariaDB 10.1.37 server:
 
[mysqld]
 
datadir = /var/lib/mysql
 
log-error = /var/log/mysqld.log
 
socket = /var/lib/mysql/mysql.sock
 
symbolic-links = 0
 
user = mysql
 
query_cache_type=1
 
innodb_buffer_pool_size=512M
 
innodb_log_buffer_size=8M
 
innodb_buffer_pool_instances=1
 
query_cache_limit=4M
 
slow_query_log=1
 
query_cache_size=4M
 
skip_name_resolve=1
 
 
 
2. Another MariaDB , 10.1.36:
 
[mysqld]
 
skip-name-resolve=1
 
key_buffer_size = 128M
 
max_allowed_packet = 16M
 
table_cache = 500
 
sort_buffer_size = 8M
 
read_buffer_size = 4M
 
read_rnd_buffer_size = 4M
 
thread_cache_size = 8
 
query_cache_size = 24M
 
tmp_table_size = 2G
 
max_heap_table_size = 2G
 
innodb_buffer_pool_size=10G
 
innodb_buffer_pool_instances=10
 
innodb_log_buffer_size=32M
 
innodb_log_file_size=128M
 
log-warnings=2
 
max_connect_errors=1000
 
connect_timeout=600
 
net_read_timeout=600
 
max_connections = 101
 
max_user_connections=51
 
open-files-limit=8192
 
slow_query_log=on
 
tmpdir=/var/lib/mysql
 
 
 
3. Percona:
 
[mysqld]
 
server-id=1
 
datadir=/var/lib/mysql
 
socket=/var/lib/mysql/mysql.sock
 
log-error=/var/log/mysqld.log
 
pid-file=/var/run/mysqld/mysqld.pid
 
log-bin
 
max_binlog_files=20
 
symbolic-links=0
 
show_compatibility_56=1
 
skip_name_resolve=1
 
max_connections=101
 
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
 
wsrep_cluster_address=gcomm://192.168.1.181,192.168.1.182,192.168.1.183
 
binlog_format=ROW
 
default_storage_engine=InnoDB
 
wsrep_slave_threads= 8
 
innodb_autoinc_lock_mode=2
 
innodb_locks_unsafe_for_binlog=1
 
innodb_buffer_pool_instances=1
 
innodb_flush_log_at_trx_commit=0
 
wsrep_cluster_name=ddns-pxc-testcluster1
 
wsrep_node_name=ddns-pxc-testcluster1-node1
 
wsrep_sst_method=xtrabackup-v2
 
pxc_strict_mode=PERMISSIVE
 
log_timestamps=SYSTEM
 
enforce_storage_engine=InnoDB
 
query_cache_type=ON
 
query_cache_limit=16M
 
query_cache_size=16M
 
net_buffer_length=1000000
 
sort_buffer_size = 4M
 
read_buffer_size = 4M
 
max_allowed_packet = 16M
 
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
 
 
4. On MySQL 5.7.24:
 
[mysqld]
 
skip_name_resolve = on
 
log_error=/var/log/mysqld.log
 
slow_query_log=1
 
slow_query_log_file=/var/log/mysqld.log
 
skip-log-bin
 
performance_schema = on
 
transaction-isolation = READ-COMMITTED
 
 
 
Any variables in particular you're interested in?

Comment by Silver Asu [ 2018-12-04 ]

This looks similar to MDEV-12343

Comment by Igor Babaev [ 2018-12-05 ]

Alice,
In MDEV-12343 we had different execution plans. Apparently the chosen execution plan resulted in poor performance in MariaDB.
Here we have absolutely the same execution plans. I'm afraid the cause of the problem is different.

Comment by burnley [ 2018-12-05 ]

In our case these transactions are created by a WP plugin called Pods. I guess scripting something that will insert few thousand records in these 2 tables will help in replicating the problem internally. On the affected servers with the current data, these transactions would never complete and this is impacting the web server performance. Look at the attached graph, basically Apache would run out of available slots because the PHP code piles on more and more transactions like this one.

Comment by Alice Sherepa [ 2018-12-06 ]

burnley, I am trying to reproduce it, but unfortunately without any progress. The query with my data is also very slow, but the query plan is different. Could you please also run

optimize table wpsc_posts;
 
optimize table wpsc_postmeta;
 
show indexes from wpsc_posts;
 
show indexes from wpsc_postmeta;

and paste the results.

Comment by burnley [ 2018-12-06 ] 

MariaDB [aditest_mdev_17878]> optimize table wpsc_posts;
 
+-------------------------------+----------+----------+-------------------------------------------------------------------+
 
| Table                         | Op       | Msg_type | Msg_text                                                          |
 
+-------------------------------+----------+----------+-------------------------------------------------------------------+
 
| aditest_mdev_17878.wpsc_posts | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 
| aditest_mdev_17878.wpsc_posts | optimize | status   | OK                                                                |
 
+-------------------------------+----------+----------+-------------------------------------------------------------------+
 
2 rows in set (2.62 sec)
 
 
 
MariaDB [aditest_mdev_17878]> optimize table wpsc_postmeta;
 
+----------------------------------+----------+----------+-------------------------------------------------------------------+
 
| Table                            | Op       | Msg_type | Msg_text                                                          |
 
+----------------------------------+----------+----------+-------------------------------------------------------------------+
 
| aditest_mdev_17878.wpsc_postmeta | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 
| aditest_mdev_17878.wpsc_postmeta | optimize | status   | OK                                                                |
 
+----------------------------------+----------+----------+-------------------------------------------------------------------+
 
2 rows in set (4.19 sec)
 
 
 
MariaDB [aditest_mdev_17878]> show indexes from wpsc_posts;
 
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table      | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| wpsc_posts |          0 | PRIMARY          |            1 | ID          | A         |        7679 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | post_name        |            1 | post_name   | A         |        7679 |      191 | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | type_status_date |            1 | post_type   | A         |          72 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | type_status_date |            2 | post_status | A         |         101 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | type_status_date |            3 | post_date   | A         |        7679 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | type_status_date |            4 | ID          | A         |        7679 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | post_parent      |            1 | post_parent | A         |         426 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_posts |          1 | post_author      |            1 | post_author | A         |          58 |     NULL | NULL   |      | BTREE      |         |               |
 
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
8 rows in set (0.00 sec)
 
 
 
MariaDB [aditest_mdev_17878]> show indexes from wpsc_postmeta;
 
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| wpsc_postmeta |          0 | PRIMARY  |            1 | meta_id     | A         |       60256 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_postmeta |          1 | post_id  |            1 | post_id     | A         |       15064 |     NULL | NULL   |      | BTREE      |         |               |
 
| wpsc_postmeta |          1 | meta_key |            1 | meta_key    | A         |        1136 |      191 | NULL   | YES  | BTREE      |         |               |
 
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [aditest_mdev_17878]>

Comment by Lingmei Weng [ 2023-02-18 ]

I tested the case with the dataset in the attachment on mariadb-10.4.0. The perf result is the same as the reported. I find the problem is on the cost estimation during greedy_search.
Specifically, the default value of the system variable optimizer_use_condition_selectivity (in sys_vars.cc), which is 1 in 10.4.0, causes ineffective searching. If I set it to 4, the query finished within a second.
createtbl.sql slow.sql

Comment by Roel Van de Paar [ 2023-02-18 ]

Issue confirmed. I merged the two SQL files into one and cleaned it up a little. Attached as MDEV-17878.sql.

SET optimizer_use_condition_selectivity=1;
 
SOURCE MDEV-17878.sql

Will get stuck in the 'Statistics' state for the final query:

10.4.29 cacea31687c098c0348deb1e433f4baddd817419 (Build 11/2/23) (Debug)

 
10.4.29-dbg>show processlist;
 
+----+-------------+-----------+------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
 
| Id | User        | Host      | db   | Command | Time | State                    | Info                                                                                                 | Progress |
 
+----+-------------+-----------+------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
 
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
 
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
 
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
 
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                 |    0.000 |
 
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                 |    0.000 |
 
|  9 | root        | localhost | test | Query   |  815 | Statistics               | SELECT wpsc_posts.* FROM wpsc_posts INNER JOIN wpsc_postmeta ON (wpsc_posts.ID=wpsc_postmeta.poST_ID |    0.000 |
 
| 11 | root        | localhost | test | Sleep   |  390 |                          | NULL                                                                                                 |    0.000 |
 
| 12 | root        | localhost | test | Query   |    0 | Init                     | show processlist                                                                                     |    0.000 |
 
+----+-------------+-----------+------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
 
8 rows in set (0.000 sec)

If we instead use:

SET optimizer_use_condition_selectivity=4;
 
SOURCE MDEV-17878.sql

The final query will complete in about 5 seconds.

Comment by Roel Van de Paar [ 2023-02-18 ]

Present in 10.3, 10.5 also

10.3.38 2743a510a156456fe57429032bf41c0da0f11198 (Debug)

 
|  9 | root        | localhost | test | Query   |   77 | Statistics               | SELECT wpsc_posts.* FROM wpsc_posts INNER JOIN wpsc_postmeta ON (wpsc_posts.ID=wpsc_postmeta.poST_ID |    0.000 |

10.5.20 c41c79650aa2ef8eaf3f887b94db8cc7478eadd1 (Debug)

 
|  4 | root | localhost | test | Query   |  171 | Statistics | SELECT wpsc_posts.* FROM wpsc_posts INNER JOIN wpsc_postmeta ON (wpsc_posts.ID=wpsc_postmeta.poST_ID |    0.000 |

Comment by Roel Van de Paar [ 2023-02-18 ]

Not present in 10.6-10.9, the state is 'Sending data' for about 5 seconds before the query completes.

10.7.8 bc656c4fa54c12ceabd857e8ae134f8979d82944 (Debug)

 
10.7.8-dbg>SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State        | Info                                                                                                 | Progress |
 
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 
|  4 | root | localhost | test | Query   |    4 | Sending data | SELECT wpsc_posts.* FROM wpsc_posts INNER JOIN wpsc_postmeta ON (wpsc_posts.ID=wpsc_postmeta.poST_ID |    0.000 |
 
|  5 | root | localhost | test | Query   |    0 | starting     | show processlist                                                                                     |    0.000 |
 
+----+------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 
2 rows in set (0.000 sec)

Comment by Roel Van de Paar [ 2023-02-18 ]

lingmei Thank you for the testcase. Please test on the latest MariaDB 10.6 or later, you should find the issue is resolved there. Thank you.

Generated at Thu Feb 08 08:39:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.