Details
Major
Description
Observed on 10.0-mdev6384 development tree (10.0 with the patch for MDEV-6384 applied manually)
Query:
SELECT `pk` , MAX( `col_bigint_key` ) FROM `table10000_innodb_int_autoinc` WHERE ( `col_smallint_key` IN ( 255 , 255 ) OR ( `pk` = 144 ) ) AND ( `col_bigint_key` IN ( 3 , 155 ) AND `col_bigint_key` IN ( 255 , 8 , 0 ) ) AND ( `col_bigint_key` IS NULL OR ( `col_bigint_key` <> 1 ) ) OR ( `col_smallint_key` IS NULL AND `pk` BETWEEN 121 AND 4 + 255 ) GROUP BY 1;
Table:
CREATE TABLE `table10000_innodb_int_autoinc` ( `col_smallint_key` smallint(6) DEFAULT NULL, `col_bigint_key` bigint(20) DEFAULT NULL, `col_varchar_64_key` varchar(64) DEFAULT NULL, `col_varchar_10` varchar(10) DEFAULT NULL, `col_varchar_10_key` varchar(10) DEFAULT NULL, `col_varchar_64` varchar(64) DEFAULT NULL, `col_bigint` bigint(20) DEFAULT NULL, `pk` int(11) NOT NULL AUTO_INCREMENT, `col_smallint` smallint(6) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_smallint_key` (`col_smallint_key`), KEY `col_bigint_key` (`col_bigint_key`), KEY `col_varchar_64_key` (`col_varchar_64_key`), KEY `col_varchar_10_key` (`col_varchar_10_key`) ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1
(data dump is attached)
10.0
+------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | table10000_innodb_int_autoinc | range | PRIMARY,col_smallint_key,col_bigint_key | col_smallint_key | 7 | NULL | 21 | 100.00 | Using index condition; Using where; Using temporary; Using filesort |
+------+-------------+-------------------------------+-------+-----------------------------------------+------------------+---------+------+------+----------+---------------------------------------------------------------------+
Execution:
21 rows in set (0.00 sec)
+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 0 | | Handler_icp_attempts | 21 | | Handler_icp_match | 21 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 22 | | Handler_read_last | 0 | | Handler_read_next | 21 | | Handler_read_prev | 0 | | Handler_read_rnd | 21 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 22 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 21 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+
10.0-mdev6384
-----------
+------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | table10000_innodb_int_autoinc | index | PRIMARY,col_smallint_key,col_bigint_key | PRIMARY | 4 | NULL | 10000 | 0.21 | Using where |
+------+-------------+-------------------------------+-------+-----------------------------------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Execution:
21 rows in set (0.06 sec)
+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 0 | | Handler_icp_attempts | 0 | | Handler_icp_match | 0 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 10000 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+
The effect seems to be stable. Persistent statistics doesn't help.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
the problem is: get_range_limit_read_cost() has this condition
if (table->quick_keys.is_set(keynr))
{
double quick_rows= table->quick_rows[keynr];
double tmp;
if (tab && table->quick_n_ranges[keynr] == 1)
It is supposed to fire when there was a ref(const) which is equivalent to the quick select. Apparently quick_n_ranges==1 is a wrong condition.
A patch that should fix all regression and crash issues: psergey-mdev6454-attempt3.diff . The patch is against 10.0 tree, revision 4290, knielsen@knielsen-hq.org-20140711100647-nf3rdaf5ep26pgty.
Elena Stepanova, could you please make another test run with psergey-mdev6454-attempt3.diff? I'm interested in both performance regressions and crashes.
Ok, I've now made a 10.1 tree with a fix for MDEV-6480, fix for this bug, and fix for MDEV-6384 all pushed. It is here: https://github.com/MariaDB/server/tree/bb-10.1-orderby-fixes.
Elena Stepanova, could you make another testing pass?
Checked EXPLAINs again - there are no regressions in current 10.1. Too bad we can't add these testcases to the test suite.
range optimizer:
PRIMARY
{io_count = 1.3946782975235641, avg_io_cost = 1, cpu_cost = 27.810000000000002, import_cost = 0, mem_cost = 0}records=139
cost =
cost.total_cost() = 29.204678297523568
"col_smallint_key"
{io_count = 22, avg_io_cost = 1, cpu_cost = 4.21, import_cost = 0, mem_cost = 0}(gdb) print found_records
$69 = 21
(gdb) p cost
$70 =
(gdb) p cost.total_cost()
$71 = 26.210000000000001
range optimizer picks range(col_smallint_key).
best_access_path():
test_if_skip_sort_order():
finds type=JT_ALL, quick select on idx=1 (col_smallint_key).
test_if_cheaper_ordering():
(gdb) print read_time
$79 = 26.211000000000002
checking key=0 (PRIMARY) // it produces the desired ordering
get_range_limit_read_cost (keynr=0, rows_limit=9709 /* = table_rows */) ...
quick_rows[0] = 139
tmp = ... = 1.3946782975235641
back to test_if_skip_sort_order()...
call to select->test_quick_select()...
... and we dont get the quick select on the PRIMARY key for some reason...
another call to test_if_skip_sort_order() ...
it makes the same choices as the previous one...
and calls select->test_quick_select() again.. which again returns no quick
select...
cursory examination inside shows that get_mm_tree()=NULL.
and then, we start a full index scan on key=PRIMARY.
Need to figure what goes on in the range optimizer.