Adaptive Workload-Aware Index Recommender Using Reinforcement Learning in Azure-Compatible SQL Engines
Keywords:
reinforcement learning, index tuning, deep Q-learning, workload-aware optimization, Hyperscale databasesAbstract
Deep reinforcement learning is used to construct an adaptable, workload-aware index recommendation architecture for Azure-interacting SQL engines. Target hyperscale distributed database clusters. This system uses a deep Q-learning agent to assess real-time query execution data such query plan structures, cardinality fluctuations, and disc I/O latency. For optimal query speed and storage efficiency, the agent samples and optimises its rules for index construction, removal, and composite key rearrangement. Reward functions that minimise response time and physical storage teach reinforcement learning. This helps the model eventually accept high-impact index combinations. Simulation and production workloads benefit from 30% quicker response time for 99% of queries and 20% less index storage. These outcomes beat rule-based tweaking. Continuous deployment on cloud-native data platforms for multi-tenant workloads with changing schema dynamics is possible.
Downloads
References
S. Agrawal, S. Chaudhuri, and V. Narasayya, "Automated selection of materialized views and indexes in SQL databases," Proceedings of the 26th International Conference on Very Large Data Bases (VLDB), Cairo, Egypt, 2000, pp. 496–505.
V. R. Narasayya and S. Chaudhuri, "Self-tuning database systems: A decade of progress," in Proceedings of the 33rd International Conference on Very Large Data Bases (VLDB), Vienna, Austria, 2007, pp. 3–14.
J. Duggan et al., "Making big data small: Reducing the footprint of materialized views in analytics systems," in Proc. ACM SIGMOD International Conference on Management of Data (SIGMOD), Melbourne, Australia, 2015, pp. 157–172.
H. Herodotou et al., "Starfish: A self-tuning system for big data analytics," in Proc. CIDR, Asilomar, CA, USA, 2011.
J. Ortiz, A. Balazinska, and J. Gehrke, "An empirical analysis of shared workload optimization techniques in cloud databases," in Proc. VLDB Endowment, vol. 10, no. 4, pp. 313–324, 2016.
S. Krishnan et al., "Learning to optimize join queries with deep reinforcement learning," arXiv preprint, arXiv:1808.03196, 2018.
K. Marcus, D. Papaemmanouil, and T. Kraska, "Deep reinforcement learning for join order enumeration," in Proc. ACM SIGMOD, Houston, TX, USA, 2019, pp. 3–18.
A. Pavlo et al., "Self-driving database management systems," in Proc. CIDR, Chaminade, CA, USA, 2017.
L. Ma, Y. Tao, X. Yu, and J. Cheng, "Query workload-driven dynamic index tuning," in Proc. ACM SIGMOD, Amsterdam, Netherlands, 2009, pp. 701–712.
Z. Zhang, C. Wang, H. Wang, and J. Li, "Machine learning-based adaptive indexing for cloud databases," in Proc. 2020 IEEE International Conference on Data Engineering (ICDE), Dallas, TX, USA, 2020, pp. 961–972.
C. Curino et al., "Self-tuning database systems: A decade of progress and a roadmap for the future," Communications of the ACM, vol. 62, no. 10, pp. 64–73, Oct. 2019.
A. Kipf et al., "Estimating cardinalities with deep sketches," in Proc. ACM SIGMOD, Amsterdam, Netherlands, 2019, pp. 1937–1940.
Y. Li et al., "QTune: A query-aware database tuning system with deep reinforcement learning," in Proc. VLDB Endowment, vol. 12, no. 12, pp. 2118–2130, Aug. 2019.
Y. Zhu, L. Ma, Z. Zhang, and H. Wang, "IndexAdvisor++: A workload-adaptive indexing advisor for cloud databases," in Proc. 2021 IEEE 37th International Conference on Data Engineering (ICDE), Chania, Greece, 2021, pp. 1669–1680.
T. Kraska et al., "The case for learned index structures," in Proc. ACM SIGMOD, Houston, TX, USA, 2018, pp. 489–504.
S. Dutt, T. Malkemus, R. Jampani, and T. Kraska, "AutoAdmin: Towards self-managing database systems," IBM Research Report, IBM Almaden, 2005.
H. Xu, D. Grafberger, and T. Kraska, "Neo: A learned query optimizer," in Proc. VLDB Endowment, vol. 14, no. 1, pp. 61–73, Sep. 2020.
A. Kipf, R. Marcus, A. van Renen, et al., "Adaptive learned indexes with decision trees," in Proc. CIDR, Amsterdam, Netherlands, 2020.
Microsoft Azure Documentation, “Hyperscale service tier in Azure SQL Database,”
A. Sorokin and D. Papaemmanouil, "Automated physical design tuning: Workload discovery and analysis," in Proc. EDBT, Bordeaux, France, 2015, pp. 601–612.
