From: Olav Sandstaa Date: June 6 2012 11:45am Subject: bzr push into mysql-trunk branch (olav.sandstaa:3916 to 3917) WL#6082 List-Archive: http://lists.mysql.com/commits/144106 Message-Id: <201206061145.q56BjJPr005904@khepri30.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3917 Olav Sandstaa 2012-06-06 WL#6082 Improve the Disk-Sweep Multi-Range Read cost model Patch 5: IO cost estimate is too high compared to "standard MRR" The DS-MRR cost model has two different ways for calculating the IO-cost of doing a "disk sweep scan": a. Disk-sweep model: Used when the data is not stored as a clustered index: The model estimates the number of blocks that will be accessed and models the time to read these using sequential scan/read of a data file. This model is used for MyISAM. b. If the data is stored as a clustered index: The cost model estimates this as having to do one random block access per record. This model does not take into account that we actually will be accessing the table as a scan. This model is used for InnoDB. The focus is on improving the model for InnoDB since this does not take into account any benefit from sequential access of the table. Due to this, the IO-cost estimate will in almost all cases be larger for DS-MRR than "standard MRR" (and thus, the cost based DS-MRR will not be used for InnoDB). The fix for this problem is to switch to use the "disk streaming IO cost model" (as described as a above) also when the data is stored in a clustered index. @ mysql-test/suite/opt_trace/r/bugs_no_prot_all.result WL#6082: Change in cost estimate due the reduced IO-cost for DS-MRR. @ mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result WL#6082: Change in cost estimate due the reduced IO-cost for DS-MRR. @ sql/handler.cc WL#6082: Change the DS-MRR cost model for how the IO-cost of a disk sweep when the data is stored as a clustered index. modified: mysql-test/suite/opt_trace/r/bugs_no_prot_all.result mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result sql/handler.cc 3916 Olav Sandstaa 2012-06-06 [merge] Merge from mysql-trunk to mysql-trunk-wl6082 modified: mysql-test/include/index_merge1.inc mysql-test/include/index_merge2.inc mysql-test/include/index_merge_ror.inc mysql-test/include/index_merge_ror_cpk.inc mysql-test/include/mrr_tests.inc mysql-test/suite/engines/funcs/r/se_join_default.result mysql-test/suite/engines/funcs/t/se_join_cross.test mysql-test/suite/engines/funcs/t/se_join_default.test mysql-test/suite/engines/funcs/t/se_join_inner.test mysql-test/suite/engines/funcs/t/se_join_left.test mysql-test/suite/engines/funcs/t/se_join_left_outer.test mysql-test/suite/engines/funcs/t/se_join_natural_left.test mysql-test/suite/engines/funcs/t/se_join_natural_left_outer.test mysql-test/suite/engines/funcs/t/se_join_natural_right.test mysql-test/suite/engines/funcs/t/se_join_natural_right_outer.test mysql-test/suite/engines/funcs/t/se_join_right.test mysql-test/suite/engines/funcs/t/se_join_right_outer.test mysql-test/suite/engines/funcs/t/se_join_straight.test mysql-test/t/index_merge_innodb.test sql/log_event.h storage/innobase/dict/dict0dict.cc storage/innobase/dict/dict0load.cc storage/innobase/dict/dict0stats.cc storage/innobase/dict/dict0stats_background.cc storage/innobase/handler/ha_innodb.cc storage/innobase/include/dict0dict.h storage/innobase/include/dict0load.h storage/innobase/include/dict0stats.h storage/innobase/include/dict0stats.ic storage/innobase/row/row0mysql.cc strings/ctype-uca.c === modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result' === modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result' --- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2012-06-06 08:17:11 +0000 +++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2012-06-06 11:44:43 +0000 @@ -2192,7 +2192,7 @@ "using_mrr": true, "index_only": false, "rows": 1, - "cost": 2.2, + "cost": 2.1031, "chosen": false, "cause": "cost" }, === modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result' --- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2012-06-06 08:17:11 +0000 +++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2012-06-06 11:44:43 +0000 @@ -2192,7 +2192,7 @@ "using_mrr": true, "index_only": false, "rows": 1, - "cost": 2.2, + "cost": 2.1031, "chosen": false, "cause": "cost" }, === modified file 'sql/handler.cc' --- a/sql/handler.cc 2012-06-06 08:17:11 +0000 +++ b/sql/handler.cc 2012-06-06 11:44:43 +0000 @@ -6444,13 +6444,7 @@ DBUG_ENTER("get_sweep_read_cost"); DBUG_ASSERT(cost->is_zero()); - if (table->file->primary_key_is_clustered()) - { - cost->add_io(table->file->read_time(table->s->primary_key, - (uint)nrows, nrows) * - Cost_estimate::IO_BLOCK_READ_COST()); - } - else + if(nrows > 0) { double n_blocks= ceil(ulonglong2double(table->file->stats.data_file_length) / IO_SIZE); No bundle (reason: useless for push emails).