Skip to main content
SpringerLink
Log in

Enhancing Online Index Tuning with a Learned Tuning Diagnostic

  • Conference paper
  • First Online:
Database and Expert Systems Applications (DEXA 2023)

Part of the book series: Lecture Notes in Computer Science ((LNCS,volume 14146))

Included in the following conference series:

  • 541 Accesses

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.

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Log in via an institution
Chapter
USD 29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD 69.99
Price excludes VAT (USA)
  • Available as EPUB and PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 89.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

References

  1. HypoPG - Hypothetical Indexes for PostgreSQL. https://github.com/HypoPG/hypopg

  2. TPC-DS benchmark. www.tpc.org/tpcds

  3. TPC-H benchmark. www.tpc.org/tpch

  4. Agrawal, S., Chu, E., Narasayya, V.R.: Automatic physical design tuning: workload as a sequence. In: Proceedings of the 2006 ACM SIGMOD International Conference on Management of Data (2006)

    Google Scholar 

  5. Bruno, N., Chaudhuri, S.: Automatic physical database tuning: a relaxation-based approach. In: ACM SIGMOD Conference (2005)

    Google Scholar 

  6. Bruno, N., Chaudhuri, S.: To tune or not to tune?: a lightweight physical design alerter. In: Very Large Data Bases Conference (2006)

    Google Scholar 

  7. Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In: 2007 IEEE 23rd International Conference on Data Engineering, pp. 826–835 (2007)

    Google Scholar 

  8. Chaudhuri, S., Narasayya, V.: AutoAdmin “what-if” index analysis utility. ACM SIGMOD Rec. 27(2), 367–378 (1998)

    Google Scholar 

  9. Chaudhuri, S., Narasayya, V.R.: An efficient cost-driven index selection tool for Microsoft SQL server. In: Very Large Data Bases Conference (1997)

    Google Scholar 

  10. Choenni, S., Blanken, H.M., Chang, T.: Index selection in relational databases. In: Proceedings of ICCI 1993: 5th International Conference on Computing and Information, pp. 491–496 (1993)

    Google Scholar 

  11. Dash, D., Polyzotis, N., Ailamaki, A.: Cophy: a scalable, portable, and interactive index advisor for large workloads. arXiv abs/1104.3214 (2011)

    Google Scholar 

  12. Deep, S., Gruenheid, A., Koutris, P., Naughton, J.F., Viglas, S.: Comprehensive and efficient workload compression. arXiv abs/2011.05549 (2020)

    Google Scholar 

  13. Ding, B., Das, S., Marcus, R., Wu, W., Chaudhuri, S., Narasayya, V.R.: AI meets AI: leveraging query executions to improve index recommendations. In: Proceedings of the 2019 International Conference on Management of Data (2019)

    Google Scholar 

  14. Gao, J., Zhao, N., Wang, N., Hao, S., Wu, H.: Automatic index selection with learned cost estimator. Inf. Sci. 612, 706–723 (2022)

    Article  Google Scholar 

  15. Hilprecht, B., Binnig, C.: Zero-shot cost models for out-of-the-box learned cost prediction. Proc. VLDB Endow. 15, 2361–2374 (2022)

    Article  Google Scholar 

  16. Jain, S., Howe, B., Yan, J., Cruanes, T.: Query2vec: an evaluation of NLP techniques for generalized workload analytics. arXiv, Databases (2018)

    Google Scholar 

  17. Kipf, A., Kipf, T., Radke, B., Leis, V., Boncz, P.A., Kemper, A.: Learned cardinalities: estimating correlated joins with deep learning. arXiv abs/1809.00677 (2018)

    Google Scholar 

  18. Kossmann, J., Halfpap, S., Jankrift, M., Schlosser, R.: Magic mirror in my hand, which is the best in the land? Proc. VLDB Endow. 13, 2382–2395 (2020)

    Article  Google Scholar 

  19. Kossmann, J., Kastius, A., Schlosser, R.: SWIRL: selection of workload-aware indexes using reinforcement learning. In: International Conference on Extending Database Technology (2022)

    Google Scholar 

  20. Lan, H., Bao, Z., Peng, Y.: An index advisor using deep reinforcement learning. In: Proceedings of the 29th ACM International Conference on Information & Knowledge Management (2020)

    Google Scholar 

  21. Li, G., Zhou, X., Li, S., Gao, B.: Qtune: a query-aware database tuning system with deep reinforcement learning. Proc. VLDB Endow. 12, 2118–2130 (2019)

    Article  Google Scholar 

  22. Licks, G.P., Couto, J.C., de Fátima Miehe, P., de Paris, R., Ruiz, D.D., Meneguzzi, F.: SmartIX: a database indexing agent based on reinforcement learning. Appl. Intell. 50, 2575–2588 (2020)

    Article  Google Scholar 

  23. Ma, L., Aken, D.V., Hefny, A.S., Mezerhane, G., Pavlo, A., Gordon, G.J.: Query-based workload forecasting for self-driving database management systems. In: Proceedings of the 2018 International Conference on Management of Data (2018)

    Google Scholar 

  24. Paul, D., Cao, J., Li, F., Srikumar, V.: Database workload characterization with query plan encoders. Proc. VLDB Endow. 15, 923–935 (2021)

    Article  Google Scholar 

  25. Sadri, Z., Gruenwald, L., Leal, E.: DRLindex: deep reinforcement learning index advisor for a cluster database. In: Proceedings of the 24th Symposium on International Database Engineering & Applications (2020)

    Google Scholar 

  26. Schlichtkrull, M., Kipf, T., Bloem, P., van den Berg, R., Titov, I., Welling, M.: Modeling relational data with graph convolutional networks. In: Extended Semantic Web Conference (2017)

    Google Scholar 

  27. Schlosser, R., Kossmann, J., Boissier, M.: Efficient scalable multi-attribute index selection using recursive strategies. In: 2019 IEEE 35th International Conference on Data Engineering (ICDE), pp. 1238–1249 (2019)

    Google Scholar 

  28. Schnaitter, K., Abiteboul, S., Milo, T., Polyzotis, N.: On-line index selection for shifting workloads. In: 2007 IEEE 23rd International Conference on Data Engineering Workshop, pp. 459–468 (2007)

    Google Scholar 

  29. Sharma, A.K., Schuhknecht, F.M., Dittrich, J.: The case for automatic database administration using deep reinforcement learning. arXiv abs/1801.05643 (2018)

    Google Scholar 

  30. Sun, J., Li, G.: An end-to-end learning-based cost estimator. arXiv abs/1906.02560 (2019)

    Google Scholar 

  31. Surajit, C., Narasayya, V.R.: Anytime algorithm of database tuning advisor for Microsoft SQL server (2020)

    Google Scholar 

  32. Tang, X., Wu, S., Song, M., Ying, S., Li, F.Y., Chen, G.: PreQR: pre-training representation for SQL understanding. In: Proceedings of the 2022 International Conference on Management of Data (2022)

    Google Scholar 

  33. Valavala, M., Alhamdani, W.: Automatic database index tuning using machine learning. In: 2021 6th International Conference on Inventive Computation Technologies (ICICT), pp. 523–530. IEEE (2021)

    Google Scholar 

  34. Wu, W., et al.: Budget-aware index tuning with reinforcement learning. In: Proceedings of the 2022 International Conference on Management of Data (2022)

    Google Scholar 

  35. Yan, Y., Yao, S., Wang, H., Gao, M.: Index selection for NoSQL database with deep reinforcement learning. Inf. Sci. 561, 20–30 (2021)

    Article  Google Scholar 

Download references

Author information

Authors and Affiliations

  1. College of Computer Science and Technology, Zhejiang University, Hangzhou, China

    Haitian Hang & Jianling Sun

Authors
  1. Haitian Hang
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Jianling Sun
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Jianling Sun .

Editor information

Editors and Affiliations

  1. University of Vienna, Vienna, Austria

    Christine Strauss

  2. University of Tsukuba, Ibaraki, Japan

    Toshiyuki Amagasa

  3. Johannes Kepler University Linz, Linz, Austria

    Gabriele Kotsis

  4. Vienna University of Technology, Vienna, Austria

    A Min Tjoa

  5. Johannes Kepler University Linz, Linz, Austria

    Ismail Khalil

Rights and permissions

Reprints and permissions

Copyright information

© 2023 The Author(s), under exclusive license to Springer Nature Switzerland AG

About this paper

Check for updates. Verify currency and authenticity via CrossMark

Cite this paper

Hang, H., Sun, J. (2023). Enhancing Online Index Tuning with a Learned Tuning Diagnostic. In: Strauss, C., Amagasa, T., Kotsis, G., Tjoa, A.M., Khalil, I. (eds) Database and Expert Systems Applications. DEXA 2023. Lecture Notes in Computer Science, vol 14146. Springer, Cham. https://doi.org/10.1007/978-3-031-39847-6_14

Download citation

  • DOI: https://doi.org/10.1007/978-3-031-39847-6_14

  • Published:

  • Publisher Name: Springer, Cham

  • Print ISBN: 978-3-031-39846-9

  • Online ISBN: 978-3-031-39847-6

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation