Database Statistics Maintenance via Multi-Tiered Gathering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in maintaining accurate statistics in real-time without significantly degrading database performance, as stale statistics can lead to sub-optimal query plans.
Innovation Solution
The proposed solution involves a multi-tiered approach to statistics gathering, including on-the-fly statistics maintained in memory, high-frequency background gathering, and predicted statistics, to balance freshness and performance impact.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If manual statistics gathering is executed frequently to maintain real-time statistics accuracy, then statistics freshness is improved, but database performance deteriorates due to overhead
Solution Approach 1:
The system executes statistics gathering during periodic maintenance windows rather than continuously or on-demand. This periodic execution allows the database to batch statistics gathering operations when user activity is lowest, thereby maintaining statistics freshness while minimizing impact on ongoing database performance
Solution Approach 2:
The system performs statistics gathering in advance during maintenance windows before the statistics become stale. By proactively updating statistics during scheduled maintenance periods, the system ensures accurate statistics are available for query optimization without requiring frequent interruptions to normal database operations
2Measurement precision
If maintenance windows are strictly enforced to perform statistics gathering, then statistics freshness is improved, but query processing time increases when maintenance cannot complete within the window
Solution Approach 1:
The system performs partial statistics gathering during maintenance windows, focusing on the most critical or frequently accessed tables first. If time runs out, the maintenance window can be extended or additional tables are processed in subsequent windows, ensuring that at least some statistics are always updated without requiring the entire maintenance window to complete all tasks
Solution Approach 2:
The system divides the statistics gathering task into segments by table or partition, allowing maintenance windows to process a subset of tables. This segmentation enables the database to make progress on statistics gathering without being blocked by the need to process every single table within a fixed time window, reducing delays when maintenance cannot complete in one cycle
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Techniques are provided for gathering statistics in a database system. The techniques involve gathering some statistics using an "on-the-fly" technique, some statistics through a "high-frequency" technique, and yet other statistics using a "prediction" technique. The technique used to gather each statistic is based, at least in part, on the overhead required to gather the statistic. For example, low-overhead statistics may be gathered "on-the-fly" using the same process that is performing the operation that affects the statistic, while statistics whose gathering incurs greater overhead may be gathered in the background, while the database is live, using the high-frequency technique. The prediction technique may be used for relatively-high overhead statistics that can be predicted based on historical data and the current value of predictor statistics.