[MDEV-2334] LP:637160 - Seven-fold performance regression with maria-5.3-dsmrr-cpk Created: 2010-09-13  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug637160.xml     Zip Archive LPexportBug637160_bug637160.sql.zip    

 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.

 Comments   
Comment by Philip Stoev (Inactive) [ 2010-09-13 ]

Re: Seven-fold performance regression with maria-5.3-dsmrr-cpk

Comment by Philip Stoev (Inactive) [ 2010-09-13 ]

mysqldump of tables and data
LPexportBug637160_bug637160.sql.zip

Comment by Sergei Petrunia [ 2010-09-16 ]

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.

Comment by Igor Babaev [ 2011-11-22 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-11-22 ]

Launchpad bug id: 637160

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