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

[Philip Stoev]

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)]

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
message:
  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)]

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

[Philip Stoev (pstoev-askmonty)]

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)]

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"

[Philip Stoev (pstoev-askmonty)]

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

[Igor Babaev (igorb-seattle)]

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