Skip to content
  • Olav Sandstaa's avatar
    07cad655
    WL#7340 IO aware cost estimate function for data access · 07cad655
    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.
    07cad655
    WL#7340 IO aware cost estimate function for data access
    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.
Loading