[MDEV-585] LP:637962 - Considerable performance regression on certain queries in maria-5.1-wl24 Created: 2010-09-14  Updated: 2012-11-07  Resolved: 2012-11-07

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

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug637962.xml     File LPexportBug637962_bug637962-2.test     File LPexportBug637962_bug637962.diff     Zip Archive LPexportBug637962_bug637962.dump.zip    

 Description   

The following query:

SELECT `col_varchar_64_key` FROM `table100000_myisam_int_autoinc`
WHERE ( `col_varchar_64_key` NOT IN ( 'now' , 'rsgxnnowvz' ) OR `col_varchar_64_key` LIKE CONCAT ('Utah' , '%' ) )
AND ( `col_varchar_10_key` BETWEEN 'cr' AND 'really' OR `col_varchar_64_key` IN ( 'j' , 'rcrsgxnn' ) )
AND ( ( `col_varchar_10_key` != 'it' ) OR `col_varchar_10_key` IS NULL )
ORDER BY `col_varchar_64_key` LIMIT 7;

Takes no time on maria-5.1 but takes over 1 second on maria-5.1-wl24 . Even if the filesort is avoided by removing the ORDER BY, considerable performance difference remains.

EXPLAIN under maria-5.1:

id: 1
select_type: SIMPLE
table: table100000_myisam_int_autoinc
type: range
possible_keys: col_varchar_10_key,col_varchar_64_key
key: col_varchar_64_key
key_len: 67
ref: NULL
rows: 99905
Extra: Using where

EXPLAIN under maria-5.1-wl24

id: 1
select_type: SIMPLE
table: table100000_myisam_int_autoinc
type: index_merge
possible_keys: col_varchar_10_key,col_varchar_64_key
key: col_varchar_10_key,col_varchar_64_key
key_len: 13,67
ref: NULL
rows: 54298
Extra: Using sort_union(col_varchar_10_key,col_varchar_64_key); Using where; Using filesort

The table contains 100K rows and will be uploaded shortly.

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

Re: Considerable performance regression on certain queries in maria-5.1-wl24

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

mysqldump of the table
LPexportBug637962_bug637962.dump.zip

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

Re: Considerable performance regression on certain queries in maria-5.1-wl24
Still repeatable with
------------------------------------------------------------
revno: 2935
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.1-wl24
timestamp: Wed 2010-09-15 16:42:56 -0700
message:
Fixed bug #637978.
Fixed a bug in the new code for WL#24 that caused generation of
an invalid index-merge access plan.

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

Re: Considerable performance regression on certain queries in maria-5.1-wl24
In order to reproduce this bug, mysqld must be started using MTR

MTR_VERSION=1 perl mysql-test-run.pl --start-and-exit 1st

This will cause mysqld to be started with certain settings, in particular

--key_buffer_size=1M --sort_buffer=256K --max_heap_table_size=1M

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

Re: Considerable performance regression on certain queries in maria-5.1-wl24
This is a new test case for the bug

  • sort_buffer_size = 1K is used to reduce the number of rows required to trigger the bug
  • only FORCE KEY and IGNORE KEY queries are used to make the EXPLAIN unambiguous
Comment by Philip Stoev (Inactive) [ 2010-09-17 ]

This is a new test case for the bug

  • sort_buffer_size = 1K is used to reduce the number of rows required to trigger the bug
  • only FORCE KEY and IGNORE KEY queries are used to make the EXPLAIN unambiguous
    new test case
    LPexportBug637962_bug637962-2.test
Comment by Philip Stoev (Inactive) [ 2010-09-17 ]

Re: Considerable performance regression on certain queries in maria-5.1-wl24
The procedure to repeat is as follows:

0. Take a 64-bit machine

1. Branch a fresh tree

bzr branch lp:maria/5.1 maria-5.1-bug637962

2. Clear ccache

ccache -C

3. Compile

./BUILD/compile-pentium-debug-max

4. Run.

perl mysql-test-run.pl --record --no-check-testcases t/bug637962-2.test

If successfull, MTR will report

mysqltest: At line 1462: "Bug #637962 is repeatable"

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

Re: Considerable performance regression on certain queries in maria-5.1-wl24

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

Diff between the FORCE KEY and IGNORE KEY result sets
LPexportBug637962_bug637962.diff

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

Re: Considerable performance regression on certain queries in maria-5.1-wl24
Please ignore all comments after comment #2, they were about another bug.

Comment by Rasmus Johansson (Inactive) [ 2010-10-26 ]

Launchpad bug id: 637962

Comment by Igor Babaev [ 2010-10-26 ]

Re: Considerable performance regression on certain queries in maria-5.1-wl24
This bug should be reproducible with mariadb-5.1/mysql-5.1

Comment by Igor Babaev [ 2012-10-31 ]

This is a serious performance degradation that can be observed for queries with
ORDER BY a LIMIT n
when there is an index on the field a and a range condition over field a,
but at the same time optimizer chooses an index merge scan.

The problem is that when making a cost-based choice between an index scan and an index-merge scan the optimizer does not take into account ORDER BY ...LIMIT n.

Comment by Igor Babaev [ 2012-10-31 ]

I'm afraid we don't have enough time to fix the problem for 5.3.10. The fixing patch will require a good testing.
I

Comment by Igor Babaev [ 2012-10-31 ]

The problem is observed in mysql-5.6.7 (but not in prior releases).
I've reported the bug http://bugs.mysql.com/67432.

Comment by Igor Babaev [ 2012-11-07 ]

The bug was fixed and the fix was pushed into 5.3.

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