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

Bug #637962 reported by Philip Stoev on 2010-09-14
This bug affects 1 person
Affects Status Importance Assigned to Milestone
In Progress
Igor Babaev

Bug 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.

Philip Stoev (pstoev-askmonty) wrote :
Changed in maria:
assignee: nobody → Igor Babaev (igorb-seattle)
milestone: none → 5.1
importance: Undecided → High
Changed in maria:
status: New → Confirmed
status: Confirmed → In Progress
Philip Stoev (pstoev-askmonty) wrote :

Still repeatable with
revno: 2935
committer: Igor Babaev <email address hidden>
branch nick: maria-5.1-wl24
timestamp: Wed 2010-09-15 16:42:56 -0700
  Fixed bug #637978.
  Fixed a bug in the new code for WL#24 that caused generation of
  an invalid index-merge access plan.

Philip Stoev (pstoev-askmonty) wrote :

In order to reproduce this bug, mysqld must be started using MTR

MTR_VERSION=1 perl --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

Philip Stoev (pstoev-askmonty) wrote :

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

Philip Stoev (pstoev-askmonty) wrote :

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


4. Run.

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

If successfull, MTR will report

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

Philip Stoev (pstoev-askmonty) wrote :

Please ignore all comments after comment #2, they were about another bug.

Igor Babaev (igorb-seattle) wrote :

This bug should be reproducible with mariadb-5.1/mysql-5.1

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers