מושגי ליבה
An adaptive cost model that dynamically optimizes CPU- and I/O-related plan cost parameters at runtime to improve the accuracy of query execution cost estimation and guide the database optimizer towards more optimal query plans.
תקציר
The paper proposes an Adaptive Cost Model (ACM) that dynamically adjusts CPU- and I/O-related cost model parameters in database query optimizers to improve the accuracy of cost estimation and the selection of optimal query execution plans.
The key ideas are:
-
Disk-Relevant Parameters:
- ACM dynamically computes the random page cost parameter for each table based on the hit ratio, which estimates how much of the requested data is already present in the database buffer cache.
- This allows the optimizer to better estimate the cost of random disk access for each table.
-
CPU-Relevant Parameters:
- ACM collects statistics on the execution time and resource usage of individual query operators.
- It then uses lightweight linear regression models to dynamically adjust the CPU-related cost parameters (tuple cost, operator cost, index tuple cost) to better align the estimated and actual execution times for each operator type.
The authors demonstrate that ACM can improve the correlation between estimated cost and actual execution time by 63% and reduce the end-to-end latency of the TPC-H benchmark by 20% compared to the standard cost model.
סטטיסטיקה
The correlation between cost and execution time of plans for TPC-H queries improved from 0.29 with the standard cost model to 0.92 with ACM.
The overall latency improvement for TPC-H queries with a modified plan is 46%, and the latency improvement for the entire benchmark is 20%.
ציטוטים
"The accuracy of the cost model has direct impact on the optimality of execution plans selected by the optimizer and thus, on the resulting query latency."
"Inaccurate parameter setting can lead to suboptimal execution plans and degraded performance. Furthermore, cost parameters may need to be periodically adjusted to adapt to changes in workload, data access patterns, and system performance."