Measurement-Based Cost Calculation Method Focusing on CPU Architecture for Database Query Optimization
Tsuyoshi Tanaka, Hiroshi Ishikawa · 2019
Attempts to improve database performance have led to the use of high-speed and large nonvolatile memory as a durable database medium instead of existing storage devices. For such database systems, the cost of memory access instead of I/O processing decreases, and the CPU cost increases relative to the most suitable join method selected for a database query to minimize query execution time. Moreover, for reducing database administration overhead, the cost model is also required to be capable of application to different generation CPUs through minor modification. In this paper, the measurement-based cost calculation method (MBCC) for solving these requirements is described. This cost calculation formula using MBCC is based on the behavior of the instruction issuing part in the CPU instruction pipeline and the tendency between the statistical information measured by the CPU performance monitor The cost calculation formulas are formed into parts for each element separated into elements repeatedly appearing in the access path of the join, and the cost is calculated combining the parts for an arbitrary number of join tables. In addition, MBCC enables the join cost calculation formula to support a CPU with architecture from a different generation without the need to re-measure the statistical information of the CPU. The evaluation of the accuracy of MBCC cost calculations revealed that the difference between the predicted cross point and the measured cross point was reduced by 74% to 95% compared with the difference between the cross point obtained by the conventional method and the measured cross point, and the updated cost evaluation formulas estimated the cost of joining different generation-based CPUs accurately in 66% of the test cases. In conclusion, the database system using the proposed cost calculation method can select the best join method and can be applied with CPUs from different generations.