[MDEV-23228] Optimizer chooses bad Execution Plan in wordpress query - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.5
Component/s: Optimizer
Labels:

Description

Optimizer chooses bad strategy for some word press queries on table wp_postmeta:

SELECT post_id, meta_key, meta_value

  FROM wp_postmeta

 WHERE post_id IN (27529,...1125 other entries...,10584)

 ORDER BY meta_id ASC

Up to MariaDB 10.2

+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+

| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                 |

+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+

|    1 | SIMPLE      | wp_postmeta | range | post_id       | post_id | 8       | NULL | 25301 | Using index condition; Using filesort |

+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+

Execution time about: 100 - 110 ms.

From MariaDB 10.3

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+

| id   | select_type  | table       | type | possible_keys | key     | key_len | ref          | rows | Extra                           |

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+

|    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | NULL    | NULL    | NULL         | 1128 | Using temporary; Using filesort |

|    1 | PRIMARY      | wp_postmeta | ref  | post_id       | post_id | 8       | tvc_0._col_1 |    4 | Using index condition           |

|    2 | MATERIALIZED | <derived3>  | ALL  | NULL          | NULL    | NULL    | NULL         | 1128 |                                 |

|    3 | DERIVED      | NULL        | NULL | NULL          | NULL    | NULL    | NULL         | NULL | No tables used                  |

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+

Execution time: About 150 ms (50% worse).

We did not find any way to force the optimizer to choose the older execution plan with optimizer_switches:

set session optimizer_switch='materialization=off,semijoin=off';
set session optimizer_switch='derived_merge=OFF';

https://mariadb.org/wp-content/uploads/2018/07/state-of-mariadb-optimizer-r08.pdf
https://mariadb.com/kb/en/derived-table-with-key-optimization/
https://mariadb.com/kb/en/semi-join-materialization-strategy/
https://mariadb.com/kb/en/derived-table-merge-optimization/

Further a Execution Plan of a competitive product did slightly better:

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                            |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

|  1 | SIMPLE      | wp_postmeta | NULL       | range | post_id       | post_id | 8       | NULL | 6768 |   100.00 | Using index condition; Using MRR; Using filesort |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

Execution time: 80 ms (100% improvement to the previous).

We were not able to enforce this better Execution plan with optimizer_switches either:

set session optimizer_switch='mrr=on,mrr_sort_keys=on';

(psergey: edited to make more readable)

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mdev23228-fill-tables.sql
0.8 kB
2020-07-25 10:36
generate-query.txt
0.3 kB
2020-07-25 10:36
mdev23228-q-timings.sql
8 kB
2020-07-25 10:37
run-timings.sql
7 kB
2020-07-25 10:37
screenshot-1.png
62 kB
2020-07-25 10:43
xac
381 kB
2020-07-27 08:54
xaa
9.00 MB
2020-07-27 08:54
xab
9.00 MB
2020-07-27 08:54

Activity

Ascending order - Click to sort in descending order

Oli Sennhauser created issue - 2020-07-20 09:42

Sergei Petrunia made changes - 2020-07-21 15:35

Field	Original Value	New Value
Description	Optimizer chooses bad strategy for some word press queries on table wp_postmeta: SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (27529,...1125 other entries...,10584) ORDER BY meta_id ASC ; * Up to MariaDB 10.2 +------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+ \| 1 \| SIMPLE \| wp_postmeta \| range \| post_id \| post_id \| 8 \| NULL \| 25301 \| Using index condition; Using filesort \| +------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+ Execution time about: 100 - 110 ms. * From MariaDB 10.3 +------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+ \| 1 \| PRIMARY \| <subquery2> \| ALL \| distinct_key \| NULL \| NULL \| NULL \| 1128 \| Using temporary; Using filesort \| \| 1 \| PRIMARY \| wp_postmeta \| ref \| post_id \| post_id \| 8 \| tvc_0._col_1 \| 4 \| Using index condition \| \| 2 \| MATERIALIZED \| <derived3> \| ALL \| NULL \| NULL \| NULL \| NULL \| 1128 \| \| \| 3 \| DERIVED \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| No tables used \| +------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+ Execution time: About 150 ms (50% worse). We did not find any way to force the optimizer to choose the older execution plan with optimizer_switches: set session optimizer_switch='materialization=off,semijoin=off'; set session optimizer_switch='derived_merge=OFF'; https://mariadb.org/wp-content/uploads/2018/07/state-of-mariadb-optimizer-r08.pdf https://mariadb.com/kb/en/derived-table-with-key-optimization/ https://mariadb.com/kb/en/semi-join-materialization-strategy/ https://mariadb.com/kb/en/derived-table-merge-optimization/ Further a Execution Plan of a competitive product did slightly better: +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ \| id \| select_type \| table \| partitions \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \| +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ \| 1 \| SIMPLE \| wp_postmeta \| NULL \| range \| post_id \| post_id \| 8 \| NULL \| 6768 \| 100.00 \| Using index condition; Using MRR; Using filesort \| +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ Execution time: 80 ms (100% improvement to the previous). We were not able to enforce this better Execution plan with optimizer_switches either: set session optimizer_switch='mrr=on,mrr_sort_keys=on';	Optimizer chooses bad strategy for some word press queries on table wp_postmeta: {code:sql} SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (27529,...1125 other entries...,10584) ORDER BY meta_id ASC ; {code} * Up to MariaDB 10.2 {code} +------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+ \| 1 \| SIMPLE \| wp_postmeta \| range \| post_id \| post_id \| 8 \| NULL \| 25301 \| Using index condition; Using filesort \| +------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+ {code} Execution time about: 100 - 110 ms. * From MariaDB 10.3 +------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+ \| 1 \| PRIMARY \| <subquery2> \| ALL \| distinct_key \| NULL \| NULL \| NULL \| 1128 \| Using temporary; Using filesort \| \| 1 \| PRIMARY \| wp_postmeta \| ref \| post_id \| post_id \| 8 \| tvc_0._col_1 \| 4 \| Using index condition \| \| 2 \| MATERIALIZED \| <derived3> \| ALL \| NULL \| NULL \| NULL \| NULL \| 1128 \| \| \| 3 \| DERIVED \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| No tables used \| +------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+ Execution time: About 150 ms (50% worse). We did not find any way to force the optimizer to choose the older execution plan with optimizer_switches: set session optimizer_switch='materialization=off,semijoin=off'; set session optimizer_switch='derived_merge=OFF'; https://mariadb.org/wp-content/uploads/2018/07/state-of-mariadb-optimizer-r08.pdf https://mariadb.com/kb/en/derived-table-with-key-optimization/ https://mariadb.com/kb/en/semi-join-materialization-strategy/ https://mariadb.com/kb/en/derived-table-merge-optimization/ Further a Execution Plan of a competitive product did slightly better: +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ \| id \| select_type \| table \| partitions \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \| +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ \| 1 \| SIMPLE \| wp_postmeta \| NULL \| range \| post_id \| post_id \| 8 \| NULL \| 6768 \| 100.00 \| Using index condition; Using MRR; Using filesort \| +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ Execution time: 80 ms (100% improvement to the previous). We were not able to enforce this better Execution plan with optimizer_switches either: set session optimizer_switch='mrr=on,mrr_sort_keys=on';

Sergei Petrunia made changes - 2020-07-21 15:37

Description

Optimizer chooses bad strategy for some word press queries on table wp_postmeta:

{code:sql}
SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN (27529,...1125 other entries...,10584)
ORDER BY meta_id ASC
;
{code}

* Up to MariaDB 10.2

{code}
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
| 1 | SIMPLE | wp_postmeta | range | post_id | post_id | 8 | NULL | 25301 | Using index condition; Using filesort |
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
{code}
Execution time about: 100 - 110 ms.

* From MariaDB 10.3

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1128 | Using temporary; Using filesort |
| 1 | PRIMARY | wp_postmeta | ref | post_id | post_id | 8 | tvc_0._col_1 | 4 | Using index condition |
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1128 | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+

Execution time: About 150 ms (50% worse).

We did not find any way to force the optimizer to choose the older execution plan with optimizer_switches:

set session optimizer_switch='materialization=off,semijoin=off';
set session optimizer_switch='derived_merge=OFF';

https://mariadb.org/wp-content/uploads/2018/07/state-of-mariadb-optimizer-r08.pdf
https://mariadb.com/kb/en/derived-table-with-key-optimization/
https://mariadb.com/kb/en/semi-join-materialization-strategy/
https://mariadb.com/kb/en/derived-table-merge-optimization/

Further a Execution Plan of a competitive product did slightly better:

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
| 1 | SIMPLE | wp_postmeta | NULL | range | post_id | post_id | 8 | NULL | 6768 | 100.00 | Using index condition; Using MRR; Using filesort |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

Execution time: 80 ms (100% improvement to the previous).

We were not able to enforce this better Execution plan with optimizer_switches either:

set session optimizer_switch='mrr=on,mrr_sort_keys=on';

Optimizer chooses bad strategy for some word press queries on table wp_postmeta:

{code:sql}
SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN (27529,...1125 other entries...,10584)
ORDER BY meta_id ASC
;
{code}

* Up to MariaDB 10.2

{code}
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
| 1 | SIMPLE | wp_postmeta | range | post_id | post_id | 8 | NULL | 25301 | Using index condition; Using filesort |
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
{code}
Execution time about: 100 - 110 ms.

* From MariaDB 10.3

{code}
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1128 | Using temporary; Using filesort |
| 1 | PRIMARY | wp_postmeta | ref | post_id | post_id | 8 | tvc_0._col_1 | 4 | Using index condition |
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1128 | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
{code}

Execution time: About 150 ms (50% worse).

We did not find any way to force the optimizer to choose the older execution plan with optimizer_switches:

set session optimizer_switch='materialization=off,semijoin=off';
set session optimizer_switch='derived_merge=OFF';

https://mariadb.org/wp-content/uploads/2018/07/state-of-mariadb-optimizer-r08.pdf
https://mariadb.com/kb/en/derived-table-with-key-optimization/
https://mariadb.com/kb/en/semi-join-materialization-strategy/
https://mariadb.com/kb/en/derived-table-merge-optimization/

Further a Execution Plan of a competitive product did slightly better:

{code}
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
| 1 | SIMPLE | wp_postmeta | NULL | range | post_id | post_id | 8 | NULL | 6768 | 100.00 | Using index condition; Using MRR; Using filesort |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
{code}

Execution time: 80 ms (100% improvement to the previous).

We were not able to enforce this better Execution plan with optimizer_switches either:

set session optimizer_switch='mrr=on,mrr_sort_keys=on';

(psergey: edited to make more readable)

Varun Gupta (Inactive) added a comment - 2020-07-22 10:51

oli you can disable this optimization on 10.3 by setting in_predicate_conversion_threshold to 0.
I updated the docs to mention it https://mariadb.com/kb/en/conversion-of-big-in-predicates-into-subqueries/

Varun Gupta (Inactive) added a comment - 2020-07-22 10:51 oli you can disable this optimization on 10.3 by setting in_predicate_conversion_threshold to 0. I updated the docs to mention it https://mariadb.com/kb/en/conversion-of-big-in-predicates-into-subqueries/

Oli Sennhauser added a comment - 2020-07-22 14:13

OK. This solves part of the problem: in_predicate_conversion_threshold=1200 or 0 produces the old pre 10.3 QEP. But still slower than competing product. I try to play a bit more with it if I can enforce mrr...

Oli Sennhauser added a comment - 2020-07-22 14:13 OK. This solves part of the problem: in_predicate_conversion_threshold=1200 or 0 produces the old pre 10.3 QEP. But still slower than competing product. I try to play a bit more with it if I can enforce mrr...

Sergei Petrunia made changes - 2020-07-22 17:53

Assignee

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2020-07-22 17:54

Assignee

Sergei Petrunia [ psergey ]

Sergei Petrunia added a comment - 2020-07-22 17:58

oli, we were also thinking about the cause of the slowdown (conversion of IN predicate into a subquery as such should not cause a slowdown).

Could you try the query without the ORDER BY clause on
A. on MariaDB 10.2
B. on MariaDB 10.3, with the default in_predicate_conversion_threshold setting?

Sergei Petrunia added a comment - 2020-07-22 17:58 oli , we were also thinking about the cause of the slowdown (conversion of IN predicate into a subquery as such should not cause a slowdown). Could you try the query without the ORDER BY clause on A. on MariaDB 10.2 B. on MariaDB 10.3, with the default in_predicate_conversion_threshold setting?

Sergei Petrunia added a comment - 2020-07-22 17:59 - edited

I try to play a bit more with it if I can enforce mrr...

This should do it :

set optimizer_switch='mrr=on'

Sergei Petrunia added a comment - 2020-07-22 17:59 - edited I try to play a bit more with it if I can enforce mrr... This should do it : set optimizer_switch='mrr=on'

Oli Sennhauser added a comment - 2020-07-23 06:56

SELECT @@version;
-----------------

@@version

-----------------

10.2.29-MariaDB

-----------------

SELECT @@optimizer_switch\G

- - - - 1. row ***************************
        @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on

------------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

------------------------------------------------------------------------------------------+

SIMPLE

wp_postmeta

range

post_id

NULL

25301

Using index condition

------------------------------------------------------------------------------------------+
about 100 - 110 ms

Oli Sennhauser added a comment - 2020-07-23 06:56 SELECT @@version; ----------------- @@version ----------------- 10.2.29-MariaDB ----------------- SELECT @@optimizer_switch\G 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on ----- ----------- ----------- ----- ------------- ------- ------- ---- ----- ----------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----------- ----- ------------- ------- ------- ---- ----- ----------------------+ 1 SIMPLE wp_postmeta range post_id post_id 8 NULL 25301 Using index condition ----- ----------- ----------- ----- ------------- ------- ------- ---- ----- ----------------------+ about 100 - 110 ms

Oli Sennhauser added a comment - 2020-07-23 06:56

SELECT @@version;
-----------------

@@version

-----------------

10.3.20-MariaDB

-----------------

SELECT @@optimizer_switch\G

- - - - 1. row ***************************
        @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on

SELECT @@in_predicate_conversion_threshold;
-------------------------------------

@@in_predicate_conversion_threshold

-------------------------------------

1000

-------------------------------------

-------------------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-------------------------------------------------------------------------------------------------+

1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1128
1	PRIMARY	wp_postmeta	ref	post_id	post_id	8	tvc_0._col_1	4	Using index condition
2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	1128
3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

-------------------------------------------------------------------------------------------------+
101ms 95ms 102ms 102ms 95ms 102ms 105ms (vs. 150 ms WITH ORDER BY)

Oli Sennhauser added a comment - 2020-07-23 06:56 SELECT @@version; ----------------- @@version ----------------- 10.3.20-MariaDB ----------------- SELECT @@optimizer_switch\G 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on SELECT @@in_predicate_conversion_threshold; ------------------------------------- @@in_predicate_conversion_threshold ------------------------------------- 1000 ------------------------------------- ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- ----------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- ----------------------+ 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1128 1 PRIMARY wp_postmeta ref post_id post_id 8 tvc_0._col_1 4 Using index condition 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 1128 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- ----------------------+ 101ms 95ms 102ms 102ms 95ms 102ms 105ms (vs. 150 ms WITH ORDER BY)

Oli Sennhauser added a comment - 2020-07-23 06:58

SET SESSION optimizer_switch='mrr=on';

With ORDER BY:
-----------------------------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-----------------------------------------------------------------------------------------------------------+

1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1128	Using temporary; Using filesort
1	PRIMARY	wp_postmeta	ref	post_id	post_id	8	tvc_0._col_1	4	Using index condition
2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	1128
3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

-----------------------------------------------------------------------------------------------------------+
145 - 150ms

Without ORDER BY:
-------------------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-------------------------------------------------------------------------------------------------+

1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1128
1	PRIMARY	wp_postmeta	ref	post_id	post_id	8	tvc_0._col_1	4	Using index condition
2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	1128
3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

-------------------------------------------------------------------------------------------------+
around 100 to 105 ms

I cannot see any MRR optimization.

MariaDB docu mentions: https://mariadb.com/kb/en/multi-range-read-optimization/

range, ref and eq_ref access - OK
for between (Range access) - We do not have, we have IN (...)
for on t1.id = t2.id (for BKA) 2 times - not our case

We have: WHERE id IN (1100 values). Possibly MRR is not considered in this case?

I can ask customer if I can send you the data if it helps?

Oli Sennhauser added a comment - 2020-07-23 06:58 SET SESSION optimizer_switch='mrr=on'; With ORDER BY: ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- --------------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- --------------------------------+ 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1128 Using temporary; Using filesort 1 PRIMARY wp_postmeta ref post_id post_id 8 tvc_0._col_1 4 Using index condition 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 1128 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- --------------------------------+ 145 - 150ms Without ORDER BY: ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- ----------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- ----------------------+ 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1128 1 PRIMARY wp_postmeta ref post_id post_id 8 tvc_0._col_1 4 Using index condition 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 1128 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used ----- ------------ ----------- ---- ------------- ------- ------- ------------ ---- ----------------------+ around 100 to 105 ms I cannot see any MRR optimization. MariaDB docu mentions: https://mariadb.com/kb/en/multi-range-read-optimization/ range, ref and eq_ref access - OK for between (Range access) - We do not have, we have IN (...) for on t1.id = t2.id (for BKA) 2 times - not our case We have: WHERE id IN (1100 values). Possibly MRR is not considered in this case? I can ask customer if I can send you the data if it helps?

Sergei Petrunia added a comment - 2020-07-24 10:07

Analyzing of the provided data: So, if one removes the ORDER BY,

10.2 (no conversion) takes 100-110 ms
10.3 (with conversion) takes 95-105 ms

roughly the same.

This seems to prove the theory that:

for MariaDB, there is no time difference between
A: reading rows with range access, and
B: reading rows with ref access.

The difference in query time comes from interplay with filesort:

in the range plan, the query has "Using filesort", which means the filesort code reads the data from quick range select. This is possible when the data to be sorted come from a single table.

in the subquery-based plan, the sorted data comes from several tables. In order to sort it, the runtime needs to write it it to the temp. table and then read it back. Because of that, the query has "Using temporary; Using filesort".

So, the reason for MariaDB's slowdown is writing the data and reading it back...

Sergei Petrunia added a comment - 2020-07-24 10:07 Analyzing of the provided data: So, if one removes the ORDER BY, 10.2 (no conversion) takes 100-110 ms 10.3 (with conversion) takes 95-105 ms roughly the same. This seems to prove the theory that: for MariaDB, there is no time difference between A: reading rows with range access, and B: reading rows with ref access. The difference in query time comes from interplay with filesort: in the range plan, the query has "Using filesort", which means the filesort code reads the data from quick range select. This is possible when the data to be sorted come from a single table. in the subquery-based plan, the sorted data comes from several tables. In order to sort it, the runtime needs to write it it to the temp. table and then read it back. Because of that, the query has "Using temporary; Using filesort". So, the reason for MariaDB's slowdown is writing the data and reading it back...

Sergei Petrunia added a comment - 2020-07-24 10:52

Another thing to check: what is the "Using index condition" for table wp_postmeta?
It is

        "table": {

          "table_name": "wp_postmeta",

          "access_type": "ref",

          "possible_keys": ["post_id"],

          "key": "post_id",

          "key_length": "8",

          "used_key_parts": ["post_id"],

          "ref": ["tvc_0._col_1"],

          "rows": 3,

          "filtered": 100,

          "index_condition": "wp_postmeta.post_id = tvc_0._col_1"

which is a bit odd as wp_postmeta.post_id was defined as

  `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,

and normally the optimizer is able to detect that in such cases the returned row satisfies the WHERE condition..

Sergei Petrunia added a comment - 2020-07-24 10:52 Another thing to check: what is the "Using index condition" for table wp_postmeta? It is "table": { "table_name": "wp_postmeta", "access_type": "ref", "possible_keys": ["post_id"], "key": "post_id", "key_length": "8", "used_key_parts": ["post_id"], "ref": ["tvc_0._col_1"], "rows": 3, "filtered": 100, "index_condition": "wp_postmeta.post_id = tvc_0._col_1" } which is a bit odd as wp_postmeta.post_id was defined as `post_id` bigint(20) unsigned NOT NULL DEFAULT 0, and normally the optimizer is able to detect that in such cases the returned row satisfies the WHERE condition..

Oli Sennhauser added a comment - 2020-07-24 12:02

The query profile supports your idea:

+------------------------+----------+

| Status                 | Duration |

+------------------------+----------+

| Starting               | 0.000343 |

| Checking permissions   | 0.000005 |

| Opening tables         | 0.000015 |

| After opening tables   | 0.000004 |

| System lock            | 0.000004 |

| Table lock             | 0.000005 |

| Init                   | 0.000198 |

| Optimizing             | 0.000135 |

| Statistics             | 0.000026 |

| Preparing              | 0.000022 |

| Creating tmp table     | 0.000011 |

| Sorting result         | 0.000008 |

| Executing              | 0.000002 |

| Sending data           | 0.000135 |

| Removing tmp table     | 0.000003 |

| Sending data           | 0.049319 |

| Creating sort index    | 0.082734 |

| Removing tmp table     | 0.013269 |

| Creating sort index    | 0.000023 |

| End of update loop     | 0.000016 |

| Removing tmp table     | 0.000006 |

| End of update loop     | 0.000003 |

| Query end              | 0.000003 |

| Commit                 | 0.000004 |

| Closing tables         | 0.000002 |

| Removing tmp table     | 0.000024 |

| Closing tables         | 0.000003 |

| Unlocking tables       | 0.000002 |

| Closing tables         | 0.000008 |

| Starting cleanup       | 0.000002 |

| Freeing items          | 0.000042 |

| Updating status        | 0.000016 |

| Reset for next command | 0.000020 |

+------------------------+----------+

+------------------------+----------+

| Status                 | Duration |

+------------------------+----------+

| Starting               | 0.000359 |

| Checking permissions   | 0.000005 |

| Opening tables         | 0.000015 |

| After opening tables   | 0.000004 |

| System lock            | 0.000003 |

| Table lock             | 0.000013 |

| Init                   | 0.000197 |

| Optimizing             | 0.000157 |

| Statistics             | 0.000026 |

| Preparing              | 0.000026 |

| Executing              | 0.000003 |

| Sending data           | 0.000108 |

| Removing tmp table     | 0.000003 |

| Sending data           | 0.105910 |

| End of update loop     | 0.000011 |

| Removing tmp table     | 0.000023 |

| End of update loop     | 0.000003 |

| Query end              | 0.000003 |

| Commit                 | 0.000003 |

| Closing tables         | 0.000002 |

| Removing tmp table     | 0.000003 |

| Closing tables         | 0.000002 |

| Unlocking tables       | 0.000002 |

| Closing tables         | 0.000007 |

| Starting cleanup       | 0.000002 |

| Freeing items          | 0.000034 |

| Updating status        | 0.000012 |

| Reset for next command | 0.000011 |

+------------------------+----------+

By the way: I got OK from customer to upload you the data if needed?

Oli Sennhauser added a comment - 2020-07-24 12:02 The query profile supports your idea: +------------------------+----------+ | Status | Duration | +------------------------+----------+ | Starting | 0.000343 | | Checking permissions | 0.000005 | | Opening tables | 0.000015 | | After opening tables | 0.000004 | | System lock | 0.000004 | | Table lock | 0.000005 | | Init | 0.000198 | | Optimizing | 0.000135 | | Statistics | 0.000026 | | Preparing | 0.000022 | | Creating tmp table | 0.000011 | | Sorting result | 0.000008 | | Executing | 0.000002 | | Sending data | 0.000135 | | Removing tmp table | 0.000003 | | Sending data | 0.049319 | | Creating sort index | 0.082734 | | Removing tmp table | 0.013269 | | Creating sort index | 0.000023 | | End of update loop | 0.000016 | | Removing tmp table | 0.000006 | | End of update loop | 0.000003 | | Query end | 0.000003 | | Commit | 0.000004 | | Closing tables | 0.000002 | | Removing tmp table | 0.000024 | | Closing tables | 0.000003 | | Unlocking tables | 0.000002 | | Closing tables | 0.000008 | | Starting cleanup | 0.000002 | | Freeing items | 0.000042 | | Updating status | 0.000016 | | Reset for next command | 0.000020 | +------------------------+----------+ +------------------------+----------+ | Status | Duration | +------------------------+----------+ | Starting | 0.000359 | | Checking permissions | 0.000005 | | Opening tables | 0.000015 | | After opening tables | 0.000004 | | System lock | 0.000003 | | Table lock | 0.000013 | | Init | 0.000197 | | Optimizing | 0.000157 | | Statistics | 0.000026 | | Preparing | 0.000026 | | Executing | 0.000003 | | Sending data | 0.000108 | | Removing tmp table | 0.000003 | | Sending data | 0.105910 | | End of update loop | 0.000011 | | Removing tmp table | 0.000023 | | End of update loop | 0.000003 | | Query end | 0.000003 | | Commit | 0.000003 | | Closing tables | 0.000002 | | Removing tmp table | 0.000003 | | Closing tables | 0.000002 | | Unlocking tables | 0.000002 | | Closing tables | 0.000007 | | Starting cleanup | 0.000002 | | Freeing items | 0.000034 | | Updating status | 0.000012 | | Reset for next command | 0.000011 | +------------------------+----------+ By the way: I got OK from customer to upload you the data if needed?

Sergei Petrunia added a comment - 2020-07-25 10:28

I cannot see any MRR optimization.
...
We have: WHERE id IN (1100 values). Possibly MRR is not considered in this case?

Sorry, I was not clear enough. I intended to say this:

set in_predicate_conversion_threshold=0;

set optimizer_switch='mrr=off';

Then, MRR is used (The EXPLAIN shows it as "Rowid-ordered scan"):

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                                                     |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

|    1 | SIMPLE      | wp_postmeta | range | post_id       | post_id | 8       | NULL | 8896 | Using index condition; Rowid-ordered scan; Using filesort |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

Without in_predicate_conversion_threshold=0, one can get MRR to be used by enabling batched key access:

set join_cache_level=6;

set optimizer_switch='mrr=on';

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+------------------------------------------------------------------------------------+

| id   | select_type  | table       | type | possible_keys | key     | key_len | ref          | rows | Extra                                                                              |

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+------------------------------------------------------------------------------------+

|    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | NULL    | NULL    | NULL         | 1125 | Using temporary; Using filesort                                                    |

|    1 | PRIMARY      | wp_postmeta | ref  | post_id       | post_id | 8       | tvc_0._col_1 |    3 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |

|    2 | MATERIALIZED | <derived3>  | ALL  | NULL          | NULL    | NULL    | NULL         | 1125 |                                                                                    |

|    3 | DERIVED      | NULL        | NULL | NULL          | NULL    | NULL    | NULL         | NULL | No tables used                                                                     |

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+------------------------------------------------------------------------------------+

...But looking at the numbers of rows to be read, I doubt that this will reliably help.

oli, can you try with "set optimizer_switch='mrr=off'" on MySQL 8? Does it have any effect on the query execution time?

Sergei Petrunia added a comment - 2020-07-25 10:30 - edited

By the way: I got OK from customer to upload you the data if needed?

If it is not too bothersome - let's give it a try, please upload. (Because I haven't been able to reproduce with the test data I have generated).

Sergei Petrunia added a comment - 2020-07-25 10:30 - edited By the way: I got OK from customer to upload you the data if needed? If it is not too bothersome - let's give it a try, please upload. (Because I haven't been able to reproduce with the test data I have generated).

Sergei Petrunia made changes - 2020-07-25 10:36

Attachment

mdev23228-fill-tables.sql [ 52956 ]

Sergei Petrunia made changes - 2020-07-25 10:36

Attachment

generate-query.txt [ 52957 ]

Sergei Petrunia made changes - 2020-07-25 10:37

Attachment

mdev23228-q-timings.sql [ 52958 ]

Sergei Petrunia made changes - 2020-07-25 10:37

Attachment

run-timings.sql [ 52959 ]

Sergei Petrunia made changes - 2020-07-25 10:43

Attachment

screenshot-1.png [ 52960 ]

Sergei Petrunia added a comment - 2020-07-25 10:44

Tried to reproduce: mdev23228-fill-tables.sql generate-query.txt .
The table is 500K rows, 230MB on disk.

The query: mdev23228-q-timings.sql run script: run-timings.sql

The results (all values are time in Microseconds):

mariadb <- c(39564,29761,51184,67787,45390,45925,28415,34310,47551,28976,28867,28716,28740,47749,32246,29879,28710,28456,27248,27210,27068,28185,27041,43309,27428,27405,27292,43344,28557,37942,50461,28806,44525,48870,28603,28640,28852,32789,29075,28708,44070,45492,45734,28364,28545,28099,28890,29055,43470,28090,27918,28130,28797,28680,28018,29292,37149,33905,30729,44792,28428,46337,28211,28296,26706,27054,45606,27876,26668,45248,45038,27915,27691,27656,28180,27833,27939,28058,27654,27751,44741,27874,28128,28000,34058,36523,29250,29171,26854,30993,44383,45185,45307,27665,43790,26546,43534,27905,28103,28173)
mariadb_no_conv <- c(36456,26442,73276,28969,48066,26377,25266,42052,24613,24880,25725,25696,24780,26234,44136,25894,42799,44983,25591,25129,25877,25083,26108,24976,25959,44426,26157,43046,26191,26798,25118,39947,42305,31773,25149,26337,25083,27010,25757,26790,26262,25135,25892,25855,24984,26275,25121,26022,24955,26216,25405,26293,25197,26367,24967,25901,43534,24907,26172,40087,26704,26557,25126,37524,25357,47087,27259,26959,24965,25987,25920,24814,24919,25958,41877,44327,25834,25809,24997,25960,25689,41102,25805,25737,24792,25771,25704,24908,25762,44579,26531,25064,58891,30449,24896,50861,26546,25188,26023,43467).
mysql <- c(37523,33895,33970,34134,34351,34193,34137,34026,33559,33321,34961,33748,34029,33919,33325,32717,33407,33895,33830,34279,33014,33054,33229,34878,33397,33414,32744,32630,32802,33783,33523,33556,33424,33511,33646,33458,33501,33656,33491,33594,33747,33748,33550,33411,33579,33606,33720,33436,33428,33163,33198,33175,33729,33726,33460,33507,33212,33555,33388,33303,33294,33373,33468,33430,33298,33450,33723,33779,33612,33740,33526,33726,33572,33507,33285,33640,33656,33680,33663,33621,34003,33654,34795,33782,34126,33915,33837,33630,33981,33305,33694,34016,34571,34196,34167,33842,33933,33709,33887,33862)

plot(density(mariadb_no_conv), col="blue", ylim=c(0, 0.001))

lines(density(mariadb), col="cyan")

lines(density(mysql), col="red")

legend(60000, 8e-04, legend=c("MariaDB-no-conv", "MariaDB", "MySQL-8"),

       col=c("blue", "cyan", "red"), lty=c(1,1,1))

Sergei Petrunia added a comment - 2020-07-25 10:44 Tried to reproduce: mdev23228-fill-tables.sql generate-query.txt . The table is 500K rows, 230MB on disk. The query: mdev23228-q-timings.sql run script: run-timings.sql The results (all values are time in Microseconds): mariadb <- c(39564,29761,51184,67787,45390,45925,28415,34310,47551,28976,28867,28716,28740,47749,32246,29879,28710,28456,27248,27210,27068,28185,27041,43309,27428,27405,27292,43344,28557,37942,50461,28806,44525,48870,28603,28640,28852,32789,29075,28708,44070,45492,45734,28364,28545,28099,28890,29055,43470,28090,27918,28130,28797,28680,28018,29292,37149,33905,30729,44792,28428,46337,28211,28296,26706,27054,45606,27876,26668,45248,45038,27915,27691,27656,28180,27833,27939,28058,27654,27751,44741,27874,28128,28000,34058,36523,29250,29171,26854,30993,44383,45185,45307,27665,43790,26546,43534,27905,28103,28173) mariadb_no_conv <- c(36456,26442,73276,28969,48066,26377,25266,42052,24613,24880,25725,25696,24780,26234,44136,25894,42799,44983,25591,25129,25877,25083,26108,24976,25959,44426,26157,43046,26191,26798,25118,39947,42305,31773,25149,26337,25083,27010,25757,26790,26262,25135,25892,25855,24984,26275,25121,26022,24955,26216,25405,26293,25197,26367,24967,25901,43534,24907,26172,40087,26704,26557,25126,37524,25357,47087,27259,26959,24965,25987,25920,24814,24919,25958,41877,44327,25834,25809,24997,25960,25689,41102,25805,25737,24792,25771,25704,24908,25762,44579,26531,25064,58891,30449,24896,50861,26546,25188,26023,43467). mysql <- c(37523,33895,33970,34134,34351,34193,34137,34026,33559,33321,34961,33748,34029,33919,33325,32717,33407,33895,33830,34279,33014,33054,33229,34878,33397,33414,32744,32630,32802,33783,33523,33556,33424,33511,33646,33458,33501,33656,33491,33594,33747,33748,33550,33411,33579,33606,33720,33436,33428,33163,33198,33175,33729,33726,33460,33507,33212,33555,33388,33303,33294,33373,33468,33430,33298,33450,33723,33779,33612,33740,33526,33726,33572,33507,33285,33640,33656,33680,33663,33621,34003,33654,34795,33782,34126,33915,33837,33630,33981,33305,33694,34016,34571,34196,34167,33842,33933,33709,33887,33862) plot(density(mariadb_no_conv), col="blue", ylim=c(0, 0.001)) lines(density(mariadb), col="cyan") lines(density(mysql), col="red") legend(60000, 8e-04, legend=c("MariaDB-no-conv", "MariaDB", "MySQL-8"), col=c("blue", "cyan", "red"), lty=c(1,1,1))

Sergei Petrunia added a comment - 2020-07-25 10:51

Mean query time, microsec

MariaDB:            33631.31

MariaDB-no-conv:    30145.06

MySQL-8:            33691.13

Fastest query time, microsec

MariaDB:            26546

MariaDB-no-conv:    24613

MySQL-8:            32630

So, for me
with averages, MariaDB with in-to-subquery is slightly worse than the original, but it's on par with MySQL.

Query time distribution is interesting. MySQL is highly predictable while MariaDB has "unlucky runs" which are much slower than normal.

Sergei Petrunia added a comment - 2020-07-25 10:51 Mean query time, microsec MariaDB: 33631.31 MariaDB-no-conv: 30145.06 MySQL-8: 33691.13 Fastest query time, microsec MariaDB: 26546 MariaDB-no-conv: 24613 MySQL-8: 32630 So, for me with averages, MariaDB with in-to-subquery is slightly worse than the original, but it's on par with MySQL. Query time distribution is interesting. MySQL is highly predictable while MariaDB has "unlucky runs" which are much slower than normal.

Oli Sennhauser made changes - 2020-07-27 08:54

Attachment		xaa [ 52962 ]
Attachment		xab [ 52963 ]
Attachment		xac [ 52964 ]

Oli Sennhauser added a comment - 2020-07-27 08:54

Dump, Create Table and Query: cat xa? > wp_postmeta.tar.gz

Oli Sennhauser added a comment - 2020-07-27 08:54 Dump, Create Table and Query: cat xa? > wp_postmeta.tar.gz

Oli Sennhauser added a comment - 2020-07-27 08:58

set session in_predicate_conversion_threshold = 0;

set session join_cache_level=6;

set session optimizer_switch='mrr=on';

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                                                     |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

|    1 | SIMPLE      | wp_postmeta | range | post_id       | post_id | 8       | NULL | 6768 | Using index condition; Rowid-ordered scan; Using filesort |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

130 - 137 ms vs 152 - 156 ms

Oli Sennhauser added a comment - 2020-07-27 09:07

8.0.20-commercial Enterprise Server

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                            |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

|  1 | SIMPLE      | wp_postmeta | NULL       | range | post_id       | post_id | 8       | NULL | 6768 |   100.00 | Using index condition; Using MRR; Using filesort |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

70 - 80 ms

SET SESSION optimizer_switch='mrr=off,mrr_cost_based=off';

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                 |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

|  1 | SIMPLE      | wp_postmeta | NULL       | range | post_id       | post_id | 8       | NULL | 6768 |   100.00 | Using index condition; Using filesort |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

70 - 80 ms

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000508 |

| Executing hook on transaction  | 0.000007 |

| starting                       | 0.000011 |

| checking permissions           | 0.000009 |

| Opening tables                 | 0.000189 |

| init                           | 0.000009 |

| System lock                    | 0.000011 |

| optimizing                     | 0.000088 |

| statistics                     | 0.001689 |

| preparing                      | 0.000114 |

| executing                      | 0.071103 |

| end                            | 0.000013 |

| query end                      | 0.000005 |

| waiting for handler commit     | 0.000075 |

| closing tables                 | 0.000010 |

| freeing items                  | 0.000037 |

| cleaning up                    | 0.000009 |

+--------------------------------+----------+

Oli Sennhauser added a comment - 2020-07-27 09:07 8.0.20-commercial Enterprise Server +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ | 1 | SIMPLE | wp_postmeta | NULL | range | post_id | post_id | 8 | NULL | 6768 | 100.00 | Using index condition; Using MRR; Using filesort | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+ 70 - 80 ms SET SESSION optimizer_switch='mrr=off,mrr_cost_based=off'; +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | wp_postmeta | NULL | range | post_id | post_id | 8 | NULL | 6768 | 100.00 | Using index condition; Using filesort | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+ 70 - 80 ms +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000508 | | Executing hook on transaction | 0.000007 | | starting | 0.000011 | | checking permissions | 0.000009 | | Opening tables | 0.000189 | | init | 0.000009 | | System lock | 0.000011 | | optimizing | 0.000088 | | statistics | 0.001689 | | preparing | 0.000114 | | executing | 0.071103 | | end | 0.000013 | | query end | 0.000005 | | waiting for handler commit | 0.000075 | | closing tables | 0.000010 | | freeing items | 0.000037 | | cleaning up | 0.000009 | +--------------------------------+----------+

Sergei Petrunia added a comment - 2020-07-29 21:13 - edited

oli, thanks for the dataset.

Played with it on my side. (The query times are microseconds, each query is tried 100 times)
https://gist.github.com/spetrunia/774fad85731338785fa8f0434358c23e

Results

For me, MariaDB was faster.
With ORDER BY clause , In->to->subquery conversion gives about 1.4x slowdown.
Without ORDER BY, In->to->subquery conversion gives 0.93x slowdown (that is, a speedup)

Two angles:

would be interesting to know why the results are different. (My setup is slightly different - self-built binaries, not exactly same 8.0 version, etc)
What (if anything) could be done about the slowdown with ORDER BY.

Sergei Petrunia added a comment - 2020-07-29 21:13 - edited oli , thanks for the dataset. Played with it on my side. (The query times are microseconds, each query is tried 100 times) https://gist.github.com/spetrunia/774fad85731338785fa8f0434358c23e Results For me, MariaDB was faster. With ORDER BY clause , In->to->subquery conversion gives about 1.4x slowdown. Without ORDER BY, In->to->subquery conversion gives 0.93x slowdown (that is, a speedup) Two angles: would be interesting to know why the results are different. (My setup is slightly different - self-built binaries, not exactly same 8.0 version, etc) What (if anything) could be done about the slowdown with ORDER BY.

Sergei Petrunia added a comment - 2020-07-29 21:14

Roel, could you try on your side (the details are in the email)

Sergei Petrunia added a comment - 2020-07-29 21:14 Roel , could you try on your side (the details are in the email)

Roel Van de Paar added a comment - 2020-07-30 10:15 - edited

Original posted issue confirmed. I did this on a large Google cloud instance which was otherwise idle.
1) Used the 'Dump, Create Table and Query' data attached to issue.
2) Started self-build 10.2.33 and 10.3.24 with fresh database instances, no tuning, optimized (release) builds.
3) Remarked EXPLAIN in the query sql file (wp_postmeta_query.sql)
4) Brought up CLI command line and ran SOURCE wp_postmeta_dump.sql;
5) Used SOURCE wp_postmeta_query.sql; to execute the query.
6) Waited for output to finish on one mysqld instance before moving to next one (to avoid tests affecting each other)
7) Compared the results (after significant visual output);

10.2.33 (Optimized)
...
25301 rows in set (0.16 sec)

10.3.24 697273554f0952160baa10606c02be1f6cf3c6c5 (Optimized)
...
25301 rows in set (0.230 sec)

Note the rather large increase for 10.3, which indeed seems like a performance regression.

Subsequent runs of the same query still vary though less; caching of any sort likely kicked in, especially as run times are shorter. The results in that case were 0.11 seconds (10.2.33) and 0.170 seconds (10.3.24). Still a significant difference. This result is repeatable (0.11/0.166 on next run), and is deemed non-sporadic.

I next tested 10.5.5 (again on a freshly created instance) and the result there was:

10.5.5 744919552cc6a2b1229dbaa59f65d798122c6adb (Optimized)
25301 rows in set (0.628 sec)

A significantly worse result on the first query. Subsequent runs gave 0.186 sec and 0.179 sec respectively, which is close to 10.3 performance. It thus seems like there is a regression starting with 10.3 only. I next test 10.4 to see what the outcome was there. For the first query there was;

10.4.14 dc68846ec5ffdd6f08d93dc3bda123ff9cef04fa (Optimized)
25301 rows in set (0.231 sec)

And subsequent queries gave 0.170 sec and 0.170 sec again on the third run. I thus conclude that the performance regression started in 10.3 and continues to exist today.

Note that the use of the CLI may mean that the output is slow (has regressed) versus the actual query being slow. To analyze this further, I re-enabled EXPLAIN to see if there was a difference in query execution plan on my side also. I also re-initalized the server completely to have a fresh environment and avoid any caching from affecting things. Results:

10.2.33 (Optimized)
10.2.33>source /home/roel/test/wp_postmeta_query.sql
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: wp_postmeta
type: range
possible_keys: post_id
key: post_id
key_len: 8
ref: NULL
rows: 25301
Extra: Using index condition; Using filesort
1 row in set (0.01 sec)

10.3.24 697273554f0952160baa10606c02be1f6cf3c6c5 (Optimized)
10.3.24>source /home/roel/test/wp_postmeta_query.sql
************************* 1. row *************************
id: 1
select_type: PRIMARY
table: <subquery2>
type: ALL
possible_keys: distinct_key
key: NULL
key_len: NULL
ref: NULL
rows: 1128
Extra: Using temporary; Using filesort
************************* 2. row *************************
id: 1
select_type: PRIMARY
table: wp_postmeta
type: ref
possible_keys: post_id
key: post_id
key_len: 8
ref: tvc_0._col_1
rows: 1 REF
Extra: Using index condition
************************* 3. row *************************
id: 2
select_type: MATERIALIZED
table: <derived3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1128
Extra:
************************* 4. row *************************
id: 3
select_type: DERIVED
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
4 rows in set (0.001 sec)

In summary; the changed query execution plan seems to lead to a significant performance regression from 10.3 onwards. Looking at the number of rows, perhaps the issue is that in 10.3 onwards, there are 1128 rows x? 1128 = 1272384? rows accessed, versus 25301 rows in the first one? Just an idea. Another reason may be the "using temporary"?

I also checked 10.4 and 10.5 query execution plans, and they almost completely match, though for the number of rows (marked by 'REF' in the last EXPLAIN about above) there are 2 rows instead of 1 row for 10.4.14, and 3 rows instead of 1 row for 10.5.5 which seems very odd. Note also that in the original issue posted it's 4 . Again, all other markers are the same as the last EXPLAIN output above.

I've updated the bug tags.

Roel Van de Paar added a comment - 2020-07-30 10:15 - edited Original posted issue confirmed. I did this on a large Google cloud instance which was otherwise idle. 1) Used the 'Dump, Create Table and Query' data attached to issue. 2) Started self-build 10.2.33 and 10.3.24 with fresh database instances, no tuning, optimized (release) builds. 3) Remarked EXPLAIN in the query sql file (wp_postmeta_query.sql) 4) Brought up CLI command line and ran SOURCE wp_postmeta_dump.sql; 5) Used SOURCE wp_postmeta_query.sql; to execute the query. 6) Waited for output to finish on one mysqld instance before moving to next one (to avoid tests affecting each other) 7) Compared the results (after significant visual output); 10.2.33 (Optimized) ... 25301 rows in set (0.16 sec) 10.3.24 697273554f0952160baa10606c02be1f6cf3c6c5 (Optimized) ... 25301 rows in set (0.230 sec) Note the rather large increase for 10.3, which indeed seems like a performance regression. Subsequent runs of the same query still vary though less; caching of any sort likely kicked in, especially as run times are shorter. The results in that case were 0.11 seconds (10.2.33) and 0.170 seconds (10.3.24). Still a significant difference. This result is repeatable (0.11/0.166 on next run), and is deemed non-sporadic. I next tested 10.5.5 (again on a freshly created instance) and the result there was: 10.5.5 744919552cc6a2b1229dbaa59f65d798122c6adb (Optimized) 25301 rows in set (0.628 sec) A significantly worse result on the first query. Subsequent runs gave 0.186 sec and 0.179 sec respectively, which is close to 10.3 performance. It thus seems like there is a regression starting with 10.3 only. I next test 10.4 to see what the outcome was there. For the first query there was; 10.4.14 dc68846ec5ffdd6f08d93dc3bda123ff9cef04fa (Optimized) 25301 rows in set (0.231 sec) And subsequent queries gave 0.170 sec and 0.170 sec again on the third run. I thus conclude that the performance regression started in 10.3 and continues to exist today. Note that the use of the CLI may mean that the output is slow (has regressed) versus the actual query being slow. To analyze this further, I re-enabled EXPLAIN to see if there was a difference in query execution plan on my side also. I also re-initalized the server completely to have a fresh environment and avoid any caching from affecting things. Results: 10.2.33 (Optimized) 10.2.33>source /home/roel/test/wp_postmeta_query.sql *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wp_postmeta type: range possible_keys: post_id key: post_id key_len: 8 ref: NULL rows: 25301 Extra: Using index condition; Using filesort 1 row in set (0.01 sec) 10.3.24 697273554f0952160baa10606c02be1f6cf3c6c5 (Optimized) 10.3.24>source /home/roel/test/wp_postmeta_query.sql *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <subquery2> type: ALL possible_keys: distinct_key key: NULL key_len: NULL ref: NULL rows: 1128 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: PRIMARY table: wp_postmeta type: ref possible_keys: post_id key: post_id key_len: 8 ref: tvc_0._col_1 rows: 1 *REF* Extra: Using index condition *************************** 3. row *************************** id: 2 select_type: MATERIALIZED table: <derived3> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1128 Extra: *************************** 4. row *************************** id: 3 select_type: DERIVED table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used 4 rows in set (0.001 sec) In summary; the changed query execution plan seems to lead to a significant performance regression from 10.3 onwards. Looking at the number of rows, perhaps the issue is that in 10.3 onwards, there are 1128 rows x? 1128 = 1272384? rows accessed, versus 25301 rows in the first one? Just an idea. Another reason may be the "using temporary"? I also checked 10.4 and 10.5 query execution plans, and they almost completely match, though for the number of rows (marked by ' REF ' in the last EXPLAIN about above) there are 2 rows instead of 1 row for 10.4.14, and 3 rows instead of 1 row for 10.5.5 which seems very odd. Note also that in the original issue posted it's 4 . Again, all other markers are the same as the last EXPLAIN output above. I've updated the bug tags.

Roel Van de Paar made changes - 2020-07-30 10:15

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Roel Van de Paar made changes - 2020-07-30 10:16

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Roel Van de Paar made changes - 2020-07-30 10:16

Labels

performance regression

Roel Van de Paar made changes - 2020-07-30 10:16

Environment

n.a.

Roel Van de Paar made changes - 2020-07-30 10:16

Assignee

Sergei Petrunia [ psergey ]

Roel Van de Paar added a comment - 2020-07-30 10:20

oli Please note that the uploaded data is public and available to anyone even without logging in. Just want to make sure you're clear on that. Thanks.

Roel Van de Paar added a comment - 2020-07-30 10:20 oli Please note that the uploaded data is public and available to anyone even without logging in. Just want to make sure you're clear on that. Thanks.

Roel Van de Paar made changes - 2020-07-31 00:09

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Petrunia made changes - 2020-09-19 10:29

Labels

performance regression

in-to-subquery performance regression

Alice Sherepa made changes - 2020-11-04 13:04

Link

This issue relates to ~~MDEV-24117~~ [ ~~MDEV-24117~~ ]

Alice Sherepa made changes - 2020-11-09 12:02

Link

This issue relates to ~~MDEV-24117~~ [ ~~MDEV-24117~~ ]

Christoph Zysik added a comment - 2021-03-07 04:28

I wonder if this is related,
just did an upgrade from ubuntu 20.04 packaged mariadb (identifies as 10.3.25-MariaDB-0ubuntu0.20.04.1-log ) to mariadbs packaged 10.5.9
and now facing a lot of queries not using indexes anymore leading to extensive CPU usage in certain situations rendering the service unuseable.

possible_keys -> yes
key/key_len/ref NULL
rows -> 33703609
Using where

Only solution is to use force key to get the resource usage down to a sane level again.

Used Query is something like
SELECT `field_a`, SUM(`field_b`) as value FROM `table` WHERE `indexed_field` = 'somevalue' ;

Christoph Zysik added a comment - 2021-03-07 04:28 I wonder if this is related, just did an upgrade from ubuntu 20.04 packaged mariadb (identifies as 10.3.25-MariaDB-0ubuntu0.20.04.1-log ) to mariadbs packaged 10.5.9 and now facing a lot of queries not using indexes anymore leading to extensive CPU usage in certain situations rendering the service unuseable. possible_keys -> yes key/key_len/ref NULL rows -> 33703609 Using where Only solution is to use force key to get the resource usage down to a sane level again. Used Query is something like SELECT `field_a`, SUM(`field_b`) as value FROM `table` WHERE `indexed_field` = 'somevalue' ;

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 111418 ]

MariaDB v4 [ 144282 ]

Julien Fritsch made changes - 2023-04-27 13:55

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2024-09-10 14:58

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Sergei Petrunia

Reporter:: Oli Sennhauser

Votes:: 1 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2020-07-20 09:42

Updated:: 2024-09-10 14:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration