Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;
Takes less than 0.1 seconds on maria-5.3 and more than 0.7 seconds on maria-5.3-dsmrr-cpk with mrr_sort_keys=ON, and many minutes with maria_sort_keys=OFF.
The query demonstrates both A) the performance regression as a ratio between the current running time and the new running time and B) the fact that a previously instantaneous query now takes a period of time that would be noticed by the user.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Seven-fold performance regression with maria-5.3-dsmrr-cpk
Re: Seven-fold performance regression with maria-5.3-dsmrr-cpk
Trying on the latest 5.3-dsmrr-cpk and 5.3-main, compiled with BUILD/compile-pentium-max, I got this:
5.3-main
--------
MariaDB [bug637160]> explain SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;
-----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | table1 | index | NULL | col_int_key | 5 | NULL | 1251 | Using index |
| 1 | SIMPLE | table3 | ALL | NULL | NULL | NULL | NULL | 1251 | Using join buffer |
| 1 | SIMPLE | table2 | eq_ref | PRIMARY,col_varchar_key | PRIMARY | 4 | bug637160.table3.col_int_nokey | 1 | Using where |
-----------------------------------------------------------------------------------------------------------------------+
(The same with @@join_cache_level=6)
Average query execution time is 0.22 sec
5.3-dsmrr-cpk
-------------
MariaDB [bug637160]> explain SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;
-----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | table1 | index | NULL | col_int_key | 5 | NULL | 874 | Using index |
| 1 | SIMPLE | table3 | ALL | NULL | NULL | NULL | NULL | 874 | Using join buffer |
| 1 | SIMPLE | table2 | eq_ref | PRIMARY,col_varchar_key | PRIMARY | 4 | bug637160.table3.col_int_nokey | 1 | Using where |
-----------------------------------------------------------------------------------------------------------------------+
MariaDB [bug637160]> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)
MariaDB [bug637160]> explain SELECT table1 . `col_int_key` AS field1 FROM ( E AS table1 STRAIGHT_JOIN ( ( DD AS table2 INNER JOIN E AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_nokey` > table2 . `col_varchar_key` ) ) LIMIT 8786;
------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | table1 | index | NULL | col_int_key | 5 | NULL | 874 | Using index |
| 1 | SIMPLE | table3 | ALL | NULL | NULL | NULL | NULL | 874 | Using join buffer |
| 1 | SIMPLE | table2 | eq_ref | PRIMARY,col_varchar_key | PRIMARY | 4 | bug637160.table3.col_int_nokey | 1 | Using where; Using join buffer |
------------------------------------------------------------------------------------------------------------------------------------+
Query execution times:
join_cache_level=default: 0.22 sec (same as in 5.3-main)
join_cache_level=6: 1.89 sec
If one sets mrr_sort_keys=off, then DS-MRR/CPK gets disabled, and execution
plan becomes the same as in 5.3-main, or with join_cache_level=default, with the same query time within 0.20 ...0.25 sec range.
Re: Seven-fold performance regression with maria-5.3-dsmrr-cpk
This cannot be considered as a performance regression.
The fact is that with BKA join first join buffer is refilled with the combinations of tables t1,t3 , and only after this the records are read from the buffer. There are 1000*1000 such combinations and only the first 8786 are sent to the result set.
So it does not make sense to use BKA join for this query at all.
The future implementation of the fair choice between block-based join algorithms and simple join algorithms should take into
account the limit clause.
Launchpad bug id: 637160
mysqldump of tables and data
LPexportBug637160_bug637160.sql.zip