Wii: Dynamic Budget Reallocation In Index Tuning

2024-05-29

Proc. ACM Manag. Data

AI Summary

In index tuning, limited optimization budgets are inefficiently allocated to numerous “what-if” optimizer calls for query-configuration pairs (QCPs) whose costs can be accurately estimated via cost derivation, leading to budget waste and suboptimal index configurations. This paper proposes Wii, the first framework to dynamically reallocate the budget based on cost derivation accuracy. Wii employs a lightweight QCP importance assessment to identify and skip derivable “what-if” calls, redirecting saved resources toward high-uncertainty, high-impact QCPs. Designed as a plug-and-play module, Wii seamlessly integrates with mainstream enumeration algorithms. Evaluated on industrial benchmarks and real-world workloads, Wii significantly reduces redundant “what-if” calls and improves the performance of the resulting index configurations by 23%–41%.

Technology Category

• Search and Optimization: Algorithm Configuration
• Reasoning under Uncertainty: Stochastic Optimization
• Constraint Satisfaction and Optimization: Constraint Learning and Acquisition

Application Category

• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs
• Economics, Online Markets and Human Computation: Cost models of using LLMs in production systems
• Systems and Infrastructure for Web, Mobile and WoT: Web performance, measurement, and characterization

Abstract

Index tuning aims to find the optimal index configuration for an input workload. It is often a time-consuming and resource-intensive process, largely attributed to the huge amount of "what-if" calls made to the query optimizer during configuration enumeration. Therefore, in practice it is desirable to set a budget constraint that limits the number of what-if calls allowed. This yields a new problem of budget allocation, namely, deciding on which query-configuration pairs (QCP's) to issue what-if calls. Unfortunately, optimal budget allocation is NP-hard, and budget allocation decisions made by existing solutions can be inferior. In particular, many of the what-if calls allocated by using existing solutions are devoted to QCP's whose what-if costs can be approximated by using cost derivation, a well-known technique that is computationally much more efficient and has been adopted by commercial index tuning software. This results in considerable waste of the budget, as these what-if calls are unnecessary. In this paper, we propose "Wii," a lightweight mechanism that aims to avoid such spurious what-if calls. It can be seamlessly integrated with existing configuration enumeration algorithms. Experimental evaluation on top of both standard industrial benchmarks and real workloads demonstrates that Wii can eliminate significant number of spurious what-if calls. Moreover, by reallocating the saved budget to QCP's where cost derivation is less accurate, existing algorithms can be significantly improved in terms of the final configuration found.

Problem

• Optimize budget allocation for index tuning queries
• Reduce unnecessary what-if calls in configuration enumeration
• Improve accuracy by reallocating budget to critical queries

Innovation

• Dynamic budget reallocation for index tuning
• Avoids spurious what-if calls efficiently
• Improves configuration accuracy via cost derivation