[MDEV-2324] LP:711648 - Unpredictable behaviour of setting optimizer_switch='mrr_sort_keys=off' Created: 2011-02-02  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: Critical
Reporter: Igor Babaev Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug711648.xml    

 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.

 Comments   
Comment by Sergei Petrunia [ 2011-02-02 ]

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

Comment by Sergei Petrunia [ 2011-02-02 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-02-03 ]

Launchpad bug id: 711648

Comment by Sergei Petrunia [ 2011-02-03 ]

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.

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