Enhancing Online Index Tuning with a Learned Tuning Diagnostic.

Haitian Hang,Jianling Sun
DOI: https://doi.org/10.1007/978-3-031-39847-6_14
2023-01-01
Abstract:Indexes are vital for data retrieval performance. For online scenarios with dynamic workloads, index tuning is challenging. A commonly used strategy is to launch tuning requests periodically, yet resource-intensive tuning sessions can obstruct it, particularly when dealing with frequently varying workloads. To tackle this challenge, we propose a learned tuning diagnostic that can be incorporated into the Monitor-Diagnose-Tune paradigm for online index tuning. Rather than invoking a comprehensive tuning tool every time a triggering condition occurs, the tuning diagnostic serves to determine whether a tuning session should be launched. By formulating the determination of sub-optimal index configurations as a classification task in machine learning, our approach can effectively identify whether the current index configuration is sub-optimal. To circumvent the need for costly data collection for each database instance, we propose a transferable representation of queries and indexes that allows for cross-database learning. Our comprehensive empirical results on the TPC-H and TPC-DS benchmarks demonstrate that our approach can reduce the total time by up to 13.3% and the number of optimizer what-if calls by up to 36% compared to the baselines, and validate the effectiveness of our transferable representation in cross-database learning.
What problem does this paper attempt to address?