MySQL 9.0.0
Source Code Documentation
cost_constants.h File Reference

Hypergraph optimizer cost constants. More...

Go to the source code of this file.

Variables

constexpr double kUnitCostInMicroseconds = 0.434
 We define the cost unit for the MySQL hypergraph cost model as follows: A cost of 1.0 represents the average cost per row of a "SELECT * FROM t" table scan where t is an InnoDB table with ten integer columns and one million rows. More...
 
constexpr double kReadOneRowCost = 0.1 / kUnitCostInMicroseconds
 Fixed cost of reading a row from the storage engine into the record buffer. More...
 
constexpr double kReadOneFieldCost = 0.02 / kUnitCostInMicroseconds
 Cost of per field in the read set. More...
 
constexpr double kReadOneByteCost = 0.001 / kUnitCostInMicroseconds
 Overhead per byte when reading a row. More...
 
constexpr double kApplyOneFilterCost = 0.025 / kUnitCostInMicroseconds
 Cost of evaluating one filter on one row. More...
 
constexpr double kIndexLookupPageCost = 0.5 / kUnitCostInMicroseconds
 The cost per page that is visited when performing an index lookup in an InnoDB B-tree. More...
 
constexpr double kIndexLookupFixedCost = 1.0 / kUnitCostInMicroseconds
 Fixed cost of an index lookup when AHI is enabled (default). More...
 
constexpr double kIndexLookupDefaultCost = 1.0 / kUnitCostInMicroseconds
 Default cost of an index lookup when we are missing information to compute a more accurate cost estimate. More...
 
constexpr double kSortOneRowCost = 0.15 / kUnitCostInMicroseconds
 Fixed overhead per input row when sorting. More...
 
constexpr double kSortComparisonCost = 0.014 / kUnitCostInMicroseconds
 Cost per comparison during sorting. More...
 
constexpr double kHashBuildOneRowCost = 0.65 / kUnitCostInMicroseconds
 Hash join constants. More...
 
constexpr double kHashProbeOneRowCost = 0.09 / kUnitCostInMicroseconds
 
constexpr double kHashReturnOneRowCost = 0.06 / kUnitCostInMicroseconds
 
constexpr double kAggregateOneRowCost = 0.1 / kUnitCostInMicroseconds
 In need of calibration. More...
 
constexpr double kStreamOneRowCost = 0.01 / kUnitCostInMicroseconds
 
constexpr double kMaterializeOneRowCost = 0.1 / kUnitCostInMicroseconds
 
constexpr double kWindowOneRowCost = 0.1 / kUnitCostInMicroseconds
 
constexpr double kTempTableAggLookupCost = 0.1 / kUnitCostInMicroseconds
 

Detailed Description

Hypergraph optimizer cost constants.

This file contains cost constants that are used during optimization by the hypergraph optimizer. Ideally all (server) cost constants should be contained in this file, but some code paths might still lead to the old cost model (with constants in sql/opt_costconstants.h).

As we integrate more storage engines into the cost model we may add engine-specific constants. Eventually we might make some constants (or groups of related constants) user-configurable to provide users with the opportunity to customize the cost model to better reflect their actual costs.

The cost constants here have generally been calibrated in microseconds using regression analysis on a release build of the server. In order to avoid tying these constants to the execution time on a particular machine we define a cost unit in terms of a fundamental operation in MySQL (reading a row during a table scan,

See also
kUnitCostInMicroseconds). Cost constants are then defined relative to the unit cost, with the idea that the ratio between running times is less sensitive to changes in hardware.

For this batch of constants we include a particular measure of the unit cost in terms of microseconds. When adjusting the cost model in the future the following approach should be adopted:

  1. Determine the unit cost c1 in microseconds.
  2. Determine the cost c2 of the constant of interest in microseonds.
  3. Set the value of the constant to the ratio c2 / c1.

Variable Documentation

◆ kAggregateOneRowCost

constexpr double kAggregateOneRowCost = 0.1 / kUnitCostInMicroseconds
constexpr

In need of calibration.

◆ kApplyOneFilterCost

constexpr double kApplyOneFilterCost = 0.025 / kUnitCostInMicroseconds
constexpr

Cost of evaluating one filter on one row.

Calibrated using simple integer filters, e.g. x < k, so it might be prudent to use a higher number, but then again, almost everything is calibrated on integers.

From calibration experiments we would prefer a cost model for filtering to consist of a fixed cost for filtering the row, together with a variable cost for the number of filter operations:

cost = kFilterOneRowCost + kApplyOneFilterCost * num_filter_evaluations

The expected number of filter evaluations for a row can be estimated. For example, the condition x < k1 AND x < k2 will require more filter evaluations if the selectivity of x < k1 is high, as then the second condition will also have to be evaluated. If we consider x < k1 OR x < k2, then a low selectivity of the first term will make it likely that the second term will have to be evaluated as well. Unfortunately the current cost model only provides partial support for these mechanisms, and does not support using a fixed filtering cost per row, so the constant has been adjusted to reflect this, pending a rewrite/refactoring of the filtering cost.

Note
See the filtering section in hypergraph_cost_model.test.

◆ kHashBuildOneRowCost

constexpr double kHashBuildOneRowCost = 0.65 / kUnitCostInMicroseconds
constexpr

Hash join constants.

◆ kHashProbeOneRowCost

constexpr double kHashProbeOneRowCost = 0.09 / kUnitCostInMicroseconds
constexpr

◆ kHashReturnOneRowCost

constexpr double kHashReturnOneRowCost = 0.06 / kUnitCostInMicroseconds
constexpr

◆ kIndexLookupDefaultCost

constexpr double kIndexLookupDefaultCost = 1.0 / kUnitCostInMicroseconds
constexpr

Default cost of an index lookup when we are missing information to compute a more accurate cost estimate.

Used e.g. with the MEMORY engine when computing the cost of index operations on a secondary non-covering index.

◆ kIndexLookupFixedCost

constexpr double kIndexLookupFixedCost = 1.0 / kUnitCostInMicroseconds
constexpr

Fixed cost of an index lookup when AHI is enabled (default).

◆ kIndexLookupPageCost

constexpr double kIndexLookupPageCost = 0.5 / kUnitCostInMicroseconds
constexpr

The cost per page that is visited when performing an index lookup in an InnoDB B-tree.

When the Adaptive Hash Index (AHI) is disabled the number of pages visited when performing an index lookup is equal to the height of the index since we traverse the tree from the root node to a leaf node, performing a binary search within each page. This constant has been calibrated with AHI disabled.

◆ kMaterializeOneRowCost

constexpr double kMaterializeOneRowCost = 0.1 / kUnitCostInMicroseconds
constexpr

◆ kReadOneByteCost

constexpr double kReadOneByteCost = 0.001 / kUnitCostInMicroseconds
constexpr

Overhead per byte when reading a row.

With a row-based format we have to process more data to extract the same number of fields when rows are larger, as measured by row length in bytes.

Note: This constant has been calibrated on tables with integer columns. We should therefore be careful about applying this cost to variable-length fields that are stored off-page. We use the length of the record buffer (TABLE_SHARE::rec_buff_length).

◆ kReadOneFieldCost

constexpr double kReadOneFieldCost = 0.02 / kUnitCostInMicroseconds
constexpr

Cost of per field in the read set.

Used to account for the increase in cost when reading more fields from a row.

◆ kReadOneRowCost

constexpr double kReadOneRowCost = 0.1 / kUnitCostInMicroseconds
constexpr

Fixed cost of reading a row from the storage engine into the record buffer.

Used in base table access paths such as TABLE_SCAN, INDEX_SCAN, INDEX_RANGE_SCAN.

◆ kSortComparisonCost

constexpr double kSortComparisonCost = 0.014 / kUnitCostInMicroseconds
constexpr

Cost per comparison during sorting.

Calibrated using ORDER BY on a single INT column. The cost is of course higher if we sort on multiple columns, and of the data type is something more complex, but not so much higher that it is clear that it would be worth taking this into account in the cost model.

◆ kSortOneRowCost

constexpr double kSortOneRowCost = 0.15 / kUnitCostInMicroseconds
constexpr

Fixed overhead per input row when sorting.

This represents the cost of reading a row into the sort buffer. The accuracy of the cost model could be further improved if we take into account the amount of data that is read into the sort buffer.

◆ kStreamOneRowCost

constexpr double kStreamOneRowCost = 0.01 / kUnitCostInMicroseconds
constexpr

◆ kTempTableAggLookupCost

constexpr double kTempTableAggLookupCost = 0.1 / kUnitCostInMicroseconds
constexpr

◆ kUnitCostInMicroseconds

constexpr double kUnitCostInMicroseconds = 0.434
constexpr

We define the cost unit for the MySQL hypergraph cost model as follows: A cost of 1.0 represents the average cost per row of a "SELECT * FROM t" table scan where t is an InnoDB table with ten integer columns and one million rows.

We assume that the InnoDB table is optimized (pages are full) and loaded into the buffer pool.

◆ kWindowOneRowCost

constexpr double kWindowOneRowCost = 0.1 / kUnitCostInMicroseconds
constexpr