-
Olav Sandstaa authored
This worklog extends the optimizer cost model to take into account whether data and indexes must be read from disk or are likely already present in a memory buffer. -The cost model will use estimates from the handler/storage engines about how much of tables and indexes are present in memory (see WL#7168) in its cost calculations. -A new cost constant named "memory_block_read_cost" representing the cost of accessing data that is in a memory buffer inside the storage engine. This constant corresponds to the existing cost constant "io_block_read_cost" that is used when a block needs to be read from disk. In this initial version the default value for memory_block_read_cost is the same as the default value for io_block_read_cost. Per-file comments for some of the test changes: main.index_merge_delete: One query now merges three indexes instead of two due to changes in the model for disk sweeps used by index merge. main.index_merge_myisam: Minor change in cost estimates in JSON output due to changes to cost model for disk sweeps used by index merge. main.innodb_explain_json_non_select_all main.innodb_explain_json_non_select_none main.innodb_explain_non_select_all main.innodb_explain_non_select_none main.myisam_explain_json_non_select_all: main.myisam_explain_json_non_select_none: main.myisam_explain_non_select_all.result myisam_explain_non_select_none.result Minor changes in cost estimates in JSON output due to changes to cost model for disk sweeps used by index merge and DS-MRR. Added more data to two queries to preserve original query plan as index merge. main.partition_index_innodb: Increase number of records in table in order to preserve original query plan. This was needed due to changes in cost model for disk sweeps used by index merge. main.subquery_sj_all: main.subquery_sj_all_bka: main.subquery_sj_all_bka_nixbnl: main.subquery_sj_all_bkaunique: Two queries changes from using FirstMatch to use Materialization due to a small change in the cost estimate for DS-MRR. opt_costmodel.result: Re-recorded to reflect that the engine_cost table now has a new entry for memory_block_read_cost. opt_costmodel_flush.test: opt_costmodel_flush.result: Replaced use of io_block_read_cost with memory_block_read_cost since the test tables are so small that they are in the InnoDB buffer. Changing the value for io_block_read_cost do not longer have any impact on the test queries. opt_costmodel_restart.test: opt_costmodel_restart.result: Replaced use of io_block_read_cost with memory_block_read_cost since the test tables are so small that they are in the InnoDB buffer. Changing the value for io_block_read_cost do not longer have any impact on the test queries. opt_costmodel_warnings.result: Re-recorded to reflect that the engine_cost table now has a new entry for memory_block_read_cost. suite/opt_trace/r/bugs_no_prot_all.result: suite/opt_trace/r/bugs_ps_prot_all.result: suite/opt_trace/r/range_no_prot.result: suite/opt_trace/r/range_ps_prot.result: Minor changes in cost estimates optimizer trace due to changes to cost model for disk sweeps used by index merge and DS-MRR. unittest/gunit/faketable.h: Initialize the handler object's table pointer to point to the table. unittest/gunit/opt_costconstants-t.cc: Added unit tests for the new SE_cost_constants::memory_block_read_cost() function. unittest/gunit/opt_costmodel-t.cc: Added unit tests for the new functions added to the Cost_model_table class.
Olav Sandstaa authoredThis worklog extends the optimizer cost model to take into account whether data and indexes must be read from disk or are likely already present in a memory buffer. -The cost model will use estimates from the handler/storage engines about how much of tables and indexes are present in memory (see WL#7168) in its cost calculations. -A new cost constant named "memory_block_read_cost" representing the cost of accessing data that is in a memory buffer inside the storage engine. This constant corresponds to the existing cost constant "io_block_read_cost" that is used when a block needs to be read from disk. In this initial version the default value for memory_block_read_cost is the same as the default value for io_block_read_cost. Per-file comments for some of the test changes: main.index_merge_delete: One query now merges three indexes instead of two due to changes in the model for disk sweeps used by index merge. main.index_merge_myisam: Minor change in cost estimates in JSON output due to changes to cost model for disk sweeps used by index merge. main.innodb_explain_json_non_select_all main.innodb_explain_json_non_select_none main.innodb_explain_non_select_all main.innodb_explain_non_select_none main.myisam_explain_json_non_select_all: main.myisam_explain_json_non_select_none: main.myisam_explain_non_select_all.result myisam_explain_non_select_none.result Minor changes in cost estimates in JSON output due to changes to cost model for disk sweeps used by index merge and DS-MRR. Added more data to two queries to preserve original query plan as index merge. main.partition_index_innodb: Increase number of records in table in order to preserve original query plan. This was needed due to changes in cost model for disk sweeps used by index merge. main.subquery_sj_all: main.subquery_sj_all_bka: main.subquery_sj_all_bka_nixbnl: main.subquery_sj_all_bkaunique: Two queries changes from using FirstMatch to use Materialization due to a small change in the cost estimate for DS-MRR. opt_costmodel.result: Re-recorded to reflect that the engine_cost table now has a new entry for memory_block_read_cost. opt_costmodel_flush.test: opt_costmodel_flush.result: Replaced use of io_block_read_cost with memory_block_read_cost since the test tables are so small that they are in the InnoDB buffer. Changing the value for io_block_read_cost do not longer have any impact on the test queries. opt_costmodel_restart.test: opt_costmodel_restart.result: Replaced use of io_block_read_cost with memory_block_read_cost since the test tables are so small that they are in the InnoDB buffer. Changing the value for io_block_read_cost do not longer have any impact on the test queries. opt_costmodel_warnings.result: Re-recorded to reflect that the engine_cost table now has a new entry for memory_block_read_cost. suite/opt_trace/r/bugs_no_prot_all.result: suite/opt_trace/r/bugs_ps_prot_all.result: suite/opt_trace/r/range_no_prot.result: suite/opt_trace/r/range_ps_prot.result: Minor changes in cost estimates optimizer trace due to changes to cost model for disk sweeps used by index merge and DS-MRR. unittest/gunit/faketable.h: Initialize the handler object's table pointer to point to the table. unittest/gunit/opt_costconstants-t.cc: Added unit tests for the new SE_cost_constants::memory_block_read_cost() function. unittest/gunit/opt_costmodel-t.cc: Added unit tests for the new functions added to the Cost_model_table class.
Loading