🤖 AI Summary
This work addresses the challenge of automatically selecting high-impact secondary indexes in SQL databases to efficiently utilize CPU, I/O, and storage resources under dynamic workloads. The authors propose AIM (Automatic Index Manager), an end-to-end, configurable index management system that features a workload-driven, wide composite index recommendation algorithm for rapid convergence. AIM incorporates a resource-aware evaluation mechanism that reduces reliance on the query optimizer and provides a strict “no performance regression” guarantee alongside metric-driven, interpretable recommendations. Extensive experiments across thousands of production databases demonstrate that AIM quickly generates near-optimal physical designs, substantially improving resource efficiency while ensuring no degradation in online query performance.
📝 Abstract
This paper describes AIM (Automatic Index Manager), a configurable index management system, which identifies impactful secondary indexes for SQL databases to efficiently use available resources such as CPU, I/O and storage. It has been validated on thousands of databases which support production systems. With AIM, the physical design of the database adapts itself to the changes in the workload.We lay out the end to end design of AIM while calling out the guarantees and tradeoffs associated with our design choices. Some of the salient features of AIM include fast convergence even while recommending wide composite indexes, reduced reliance on the query optimizer and a "no regression" guarantee for production workloads. Each index recommendation from AIM is accompanied with a metrics driven explanation, making it easier to verify machine driven changes.AIM is one of the few industrial strength index recommendation engines that is deployed on production databases at a large scale. The experimental results show that AIM is quick in identifying the most effective indexes and the resulting physical design is close to optimal.