Beyond TPC-H: Scaling IA2 for Real-World Database Optimization

Table of Links

Abstract and 1. Introduction

2. Related Works

   2.1 Traditional Index Selection Approaches

   2.2 RL-based Index Selection Approaches

3. Index Selection Problem

4. Methodology

   4.1 Formulation of the DRL Problem

   4.2 Instance-Aware Deep Reinforcement Learning for Efficient Index Selection

5. System Framework of IA2

   5.1 Preprocessing Phase

   5.2 RL Training and Application Phase

6. Experiments

   6.1 Experimental Setting

   6.2 Experimental Results

   6.3 End-to-End Performance Comparison

   6.4 Key Insights

7. Conclusion and Future Work, and References

7 Conclusion and Future Work

This study introduces the Instance-Aware Index Advisor (IA2), employing the TD3-TD-SWAR model for efficient index selection in databases, showcasing adept handling of complex dependencies and generalization to unseen workloads. Demonstrated through TPC-H benchmarks, IA2 achieves superior efficiency, setting a new standard in index configuration optimization across varied database environments.

Future iterations of this work will aim to expand the discussion on the index choices across IA2 and comparative systems, delving into the nuances of performance differences across various workloads and training epochs. Testing IA2 on a broader set of workloads beyond the TPC-H benchmark and exploring its performance in dynamically changing environments are pivotal steps forward. Such explorations will not only validate IA2’s adaptability and efficiency but also enhance its applicability across diverse database environments. Acknowledging the current evaluation’s focus and the limitation in workload diversity, additional evaluations on a more expansive range of real-world workloads and database schemas are planned. Furthermore, exploring compression technologies to enhance IA2’s scalability represents a crucial area of development. These future directions aim to broaden IA2’s effectiveness and applicability in diverse database scenarios, ensuring its readiness for the dynamic and varied demands of contemporary database systems and paving the way for more resilient, efficient, and intelligent database optimization strategies.

References

[1] Surajit Chaudhuri and Vivek R Narasayya. 1997. An efficient, costdriven index selection tool for Microsoft SQL server. In VLDB, Vol. 97. San Francisco, 146–155.

[2] Debabrata Dash, Neoklis Polyzotis, and Anastasia Ailamaki. 2011. Cophy: A scalable, portable, and interactive index advisor for large workloads. arXiv preprint arXiv:1104.3214 (2011).

[3] Scott Fujimoto, Herke Van Hoof, and David Meger. 2018. Addressing function approximation error in actor-critic methods. arXiv:1802.09477 (2018).

[4] Vijay R Konda and John N Tsitsiklis. 2000. Actor-critic algorithms. In NeurIPS.

[5] Jan Kossmann, Stefan Halfpap, Marcel Jankrift, and Rainer Schlosser. 2020. Magic mirror in my hand, which is the best in the land? an experimental evaluation of index selection algorithms. Proceedings of the VLDB Endowment 13, 12 (2020), 2382–2395.

[6] Jan Kossmann, Alexander Kastius, and Rainer Schlosser. 2022. SWIRL: Selection of Workload-aware Indexes using Reinforcement Learning.. In EDBT. 2–155.

[7] Hai Lan, Zhifeng Bao, and Yuwei Peng. 2020. An index advisor using deep reinforcement learning. In Proceedings of the 29th ACM International Conference on Information & Knowledge Management. 2105– 2108.

[8] Vincent Y Lum and Huei Ling. 1971. An optimization problem on the selection of secondary keys. In Proceedings of the 1971 26th annual conference. 349–356.

[9] Stratos Papadomanolakis and Anastassia Ailamaki. 2007. An integer linear programming approach to database design. In 2007 IEEE 23rd International Conference on Data Engineering Workshop. IEEE, 442–449.

[10] Zahra Sadri, Le Gruenwald, and Eleazar Lead. 2020. DRLindex: deep reinforcement learning index advisor for a cluster database. In Proceedings of the 24th Symposium on International Database Engineering & Applications. 1–8.

[11] Rainer Schlosser, Jan Kossmann, and Martin Boissier. 2019. Efficient scalable multi-attribute index selection using recursive strategies. In 2019 IEEE 35th International Conference on Data Engineering (ICDE). IEEE, 1238–1249.

[12] Hao Sun and Taiyi Wang. 2022. Toward Causal-Aware RL: State-Wise Action-Refined Temporal Difference. arXiv preprint arXiv:2201.00354 (2022).

[13] Richard S Sutton and Andrew G Barto. 2018. Reinforcement learning: An introduction. MIT press.

[14] Gary Valentin, Michael Zuliani, Daniel C Zilio, Guy Lohman, and Alan Skelley. 2000. DB2 advisor: An optimizer smart enough to recommend its own indexes. In Proceedings of 16th International Conference on Data Engineering (Cat. No. 00CB37073). IEEE, 101–110.

[15] Jinsung Yoon, James Jordon, and Mihaela van der Schaar. 2018. INVASE: Instance-wise variable selection using neural networks. In ICLR.