Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
I observe the following strange behavior of the mariadb server built from the current LP 5.3 tree when
running against an instance of DBT3 (factor 10) database for innodb.
MariaDB [test]> use dbt3x10_innodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [dbt3x10_innodb]>
MariaDB [dbt3x10_innodb]> set join_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_innodb]> set join_buffer_space_limit=1024*1024*32;
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_innodb]> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_innodb]> set optimizer_switch='mrr_sort_keys=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_innodb]>
MariaDB [dbt3x10_innodb]> explain
-> select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey | 4 | dbt3x10_innodb.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)
MariaDB [dbt3x10_innodb]> explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey | 4 | dbt3x10_innodb.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [dbt3x10_innodb]> analyze table lineitem;
------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
------------------------------------------------+
| dbt3x10_innodb.lineitem | analyze | status | OK |
------------------------------------------------+
1 row in set (1.12 sec)
MariaDB [dbt3x10_innodb]> explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3x10_innodb.orders.o_orderkey | 2 |
--------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
I observe this behavior for both debug and non-debug version of the server on SuSE 10.3 32-bit.
Sometimes the last command returns the following result:
MariaDB [dbt3x10_innodb]> explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3x10_innodb.orders.o_orderkey | 1 |
------------------------------------------------------------------------------------------------------------------------------------------------------
I did not try to execute this sequence of commands with dbt3 of a smaller factor. Maybe the problem can be
reproduced with them as well.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Unpredictable behaviour of setting optimizer_switch='mrr_sort_keys=off'
I don't see anything wrong here.
Attempt#1 and #2 are OK.
ANALYZE command updates index cardinalities. After that, the optimizer starts to prefer a prefix of PRIMARY key over i_l_orderkey index. This is ok, too (even if it wasn't: this would have no relation to mrr_sort_keys switch/code).
optimizer_switch='mrr_sort_keys=off' setting disables key sorting functionality. DS-MRR's operation over a clustered primary key is defined as
- sort the keys
- use sorted keys array to make lookups in key order (note that lookups produce full result records here, there is no separate rnd_pos() step).
that is, if sorting is disabled, then DS-MRR has nothing to do. handler->multi_range_read_info() function will indicate that to BKA code by setting HA_MRR_USE_DEFAULT_IMPL. when BKA code sees this flag, it disables use of join buffering.
I think everything is operating as-designed here.
Re: Unpredictable behaviour of setting optimizer_switch='mrr_sort_keys=off'
Had a discussion with Igor, we came to agreement that the observed behaviour is not a bug.
Launchpad bug id: 711648
Re: Unpredictable behaviour of setting optimizer_switch='mrr_sort_keys=off'
For better readability of the above: all EXPLAINS are for the same query:
explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
Attempt#1: 2nd table is lineitem, type=ref, key=i_l_orderkey, Extra="Using join buffer (flat, BKA join)"
Attempt#2: 2nd table is lineitem, type=ref, key=i_l_orderkey, Extra="Using join buffer (flat, BKA join)" (same as #1)
<ANALYZE is run>
Attempt#3: 2nd table is lineitem, type=ref, key=PRIMARY, Extra="", rows=2
Attempt#4: 2nd table is lineitem, type=ref, key=PRIMARY, Extra="", rows=1