research-article

UDO: universal database optimization using reinforcement learning

Authors: Junxiong Wang, Immanuel Trummer, Debabrota BasuAuthors Info & Claims

Proceedings of the VLDB Endowment, Volume 14, Issue 13

Pages 3402 - 3414

https://doi.org/10.14778/3484224.3484236

Published: 01 September 2021 Publication History

Abstract

UDO is a versatile tool for offline tuning of database systems for specific workloads. UDO can consider a variety of tuning choices, reaching from picking transaction code variants over index selections up to database system parameter tuning. UDO uses reinforcement learning to converge to near-optimal configurations, creating and evaluating different configurations via actual query executions (instead of relying on simplifying cost models). To cater to different parameter types, UDO distinguishes heavy parameters (which are expensive to change, e.g. physical design parameters) from light parameters. Specifically for optimizing heavy parameters, UDO uses reinforcement learning algorithms that allow delaying the point at which the reward feedback becomes available. This gives us the freedom to optimize the point in time and the order in which different configurations are created and evaluated (by benchmarking a workload sample). UDO uses a cost-based planner to minimize reconfiguration overheads. For instance, it aims to amortize the creation of expensive data structures by consecutively evaluating configurations using them. We evaluate UDO on Postgres as well as MySQL and on TPC-H as well as TPC-C, optimizing a variety of light and heavy parameters concurrently.

References

[1]

2020. https://github.com/jfcoz/postgresqltuner.

[2]

2020. https://github.com/keras-rl/keras-rl.

[3]

2020. https://github.com/major/MySQLTuner-perl.

[4]

2021. https://github.com/ankane/dexter.

[5]

2021. https://www.eversql.com/.

[6]

Jean-Yves Audibert, Rémi Munos, and Csaba Szepesvári. 2007. Tuning Bandit Algorithms in Stochastic Environments. In Algorithmic Learning Theory, Marcus Hutter, Rocco A. Servedio, and Eiji Takimoto (Eds.). Springer Berlin Heidelberg, Berlin, Heidelberg, 150--165.

Digital Library

[7]

P Auer, N Cesa-bianchi, and P Fischer. 2002. Finite time analysis of the multiarmed bandit problem. Machine Learning 47, 2--3 (2002), 235--256.

Digital Library

[8]

Renata Borovica, Ioannis Alagiannis, and Anastasia Ailamaki. 2012. Automated physical designers: what you see is (not) what you get. In Proceedings of the Fifth International Workshop on Testing Database Systems. 9:1--9:6.

Digital Library

[9]

Sébastien Bubeck, Rémi Munos, Gilles Stoltz, and Csaba Szepesvári. 2011. X-Armed Bandits. Journal of Machine Learning Research 12, 5 (2011).

Digital Library

[10]

Surajit Chaudhuri. 2004. Index selection for databases: A hardness study and a principled heuristic solution. KDE 16, 11 (2004), 1313--1323. http://ieeexplore.ieee.org/xpls/abs{_}all.jsp?arnumber=1339260

Digital Library

[11]

Surajit Chaudhuri, V Narasayya, and Ravi Ramamurty. 2009. Exact cardinality query optimization for optimizer testing. In VLDB. 994--1005.

Digital Library

[12]

CMU Database Group. 2020. https://github.com/cmu-db/ottertune.

[13]

Pierre-Arnaud Coquelin and Rémi Munos. 2007. Bandit Algorithms for Tree Search. Arxiv preprint cs0703062 23, March (2007), 67--74. arXiv:0703062v1 [arXiv:cs] http://arxiv.org/abs/cs/0703062

[14]

Bailu Ding, Sudipto Das, Ryan Marcus, Wentao Wu, Surajit Chaudhuri, and Vivek R. Narasayya. 2019. AI meets AI: Leveraging query executions to improve index recommendations. In SIGMOD. 1241--1258.

Digital Library

[15]

Sylvain Gelly and David Silver. 2007. Combining online and offline knowledge in UCT. Proceedings of the 24th international conference on Machine learning - ICML '07 (2007), 273--280.

Digital Library

[16]

Andrey Gubichev, Peter Boncz, Alfons Kemper, and Thomas Neumann. 2015. How good are query optimizers, really? PVLDB 9, 3 (2015), 204--215.

Digital Library

[17]

Theodore P. Hill. 2009. Knowing when to stop. American Scientist 97, 2 (2009), 126--133.

[18]

Benjamin Hilprecht, Carsten Binnig, and Uwe Röhm. 2019. Towards learning a partitioning advisor with deep reinforcement learning. SIGMOD (2019). arXiv:arXiv:1904.01279v1

Digital Library

[19]

Benjamin Hilprecht, Carsten Binnig, and Uwe Röhm. 2020. Learning a Partitioning Advisor for Cloud Databases. Proceedings of the ACM SIGMOD International Conference on Management of Data (2020), 143--157.

Digital Library

[20]

Pooria Joulani, András György, and Csaba Szepesvari. 2013. Online learning under delayed feedback. 30th International Conference on Machine Learning, ICML 2013 PART 3 (2013), 2503--2511. arXiv:1306.0686

Digital Library

[21]

Andreas Kipf, Thomas Kipf, Bernhard Radke, Viktor Leis, Peter Boncz, and Alfons Kemper. 2018. Learned cardinalities: estimating correlated joins with deep learning. In CIDR. arXiv:1809.00677 http://arxiv.org/abs/1809.00677

[22]

Levente Kocsis and C Szepesvári. 2006. Bandit based monte-carlo planning. In European Conf. on Machine Learning. 282--293. http://www.springerlink.com/index/D232253353517276.pdf

Digital Library

[23]

Guoliang Li, Xuanhe Zhou, Shifu Li, and Bo Gao. 2018. QTune: A QueryAware database tuning system with deep reinforcement learning. PVLDB 12, 12 (2018), 2118--2130.

Digital Library

[24]

Timothy P. Lillicrap, Jonathan J. Hunt, Alexander Pritzel, Nicolas Heess, Tom Erez, Yuval Tassa, David Silver, and Daan Wierstra. 2016. Continuous control with deep reinforcement learning. 4th International Conference on Learning Representations, ICLR 2016 - Conference Track Proceedings (2016). arXiv:1509.02971

[25]

Lin Ma, Bailu Ding, Sudipto Das, and Adith Swaminathan. 2020. Active Learning for ML Enhanced Database Systems. In SIGMOD. 175--191.

Digital Library

[26]

Yongjoo Park, Shucheng Zhong, and Barzan Mozafari. 2020. QuickSel: Quick Selectivity Learning with Mixture Models. In SIGMOD. 1017--1033. arXiv:1812.10568

Digital Library

[27]

Gavin A Rummery and Mahesan Niranjan. 1994. On-line Q-learning using connectionist systems. Vol. 37. University of Cambridge, Department of Engineering Cambridge, UK.

[28]

Zahra Sadri, Le Gruenwald, and Eleazar Lead. 2020. DRLindex: Deep reinforcement learning index advisor for a cluster database. ACM International Conference Proceeding Series (2020).

Digital Library

[29]

Ankur Sharma, Felix Martin Schuhknecht, and Jens Dittrich. 2018. The case for automatic database administration using deep reinforcement learning. arXiv (2018), 1--9. arXiv:1801.05643

[30]

Immanuel Trummer. 2019. Exact cardinality query optimization with bounded execution cost. In SIGMOD. 2--17.

Digital Library

[31]

Immanuel Trummer, Junxiong Wang, Deepak Maram, Samuel Moseley, Saehan Jo, and Joseph Antonakakis. 2019. SkinnerDB: regret-bounded query evaluation via reinforcement learning. In SIGMOD. 1039--1050.

Digital Library

[32]

Dana Van Aken, Dongsheng Yang, Sebastien Brillard, Ari Fiorino, Bohan Zhang, Christian Bilien, and Andrew Pavlo. 2021. An inquiry into machine learning-based automatic configuration tuning services on real-world database management systems. Proceedings of the VLDB Endowment 14, 7 (2021), 1241--1253.

Digital Library

[33]

Junxiong Wang, Immanuel Trummer, and Debabrota Basu. 2021. Demonstrating UDO: A Unified Approach for Optimizing Transaction Code, Physical Design, and System Parameters via Reinforcement Learning. In SIGMOD.

Digital Library

[34]

Lucas Woltmann, Claudio Hartmann, Maik Thiele, and Dirk Habich. 2019. Cardinality estimation with local deep learning models. In aiDM.

Digital Library

[35]

Cong Yan and Alvin Cheung. 2016. Leveraging Lock Contention to Improve OLTP Application Performance. In VLDBJ, Vol. 9. 444--455.

Digital Library

[36]

Zongheng Yang, Badrish Chandramouli, Chi Wang, Johannes Gehrke, Yinan Li, Umar Farooq Minhas, Per Åke Larson, Donald Kossmann, and Rajeev Acharya. 2020. Qd-tree: Learning data layouts for big data analytics. arXiv 2 (2020), 193--208.

Digital Library

[37]

Bohan Zhang, Dana Van Aken, Justin Wang, Tao Dai, Shuli Jiang, Jacky Lao, Siyuan Sheng, Andrew Pavlo, and Geoffrey J Gordon. 1910. A demonstration of the OtterTune automatic database management system tuning service. VLDB 11, 12 (1910), 1910--1913.

Digital Library

[38]

Ji Zhang, Yu Liu, Ke Zhou, Guoliang Li, Zhili Xiao, Bin Cheng, Jiashu Xing, Yangtao Wang, Tianheng Cheng, Li Liu, Minwei Ran, and Zekang Li. 2019. An end-to-end automatic cloud database tuning system using deep reinforcement learning. In SIGMOD. 415--432.

Digital Library

Cited By

Lao JWang YLi YWang JZhang YCheng ZChen WTang MWang J(2024)GPTuner: A Manual-Reading Database Tuning System via GPT-Guided Bayesian OptimizationProceedings of the VLDB Endowment10.14778/3659437.365944917:8(1939-1952)Online publication date: 1-Apr-2024
https://dl.acm.org/doi/10.14778/3659437.3659449
Kanellis KFreischuetz JVenkataraman S(2024)Nautilus: A Benchmarking Platform for DBMS Knob TuningProceedings of the Eighth Workshop on Data Management for End-to-End Machine Learning10.1145/3650203.3663336(72-76)Online publication date: 9-Jun-2024
https://dl.acm.org/doi/10.1145/3650203.3663336
Siddiqui TWu W(2024)ML-Powered Index Tuning: An Overview of Recent Progress and Open ChallengesACM SIGMOD Record10.1145/3641832.364183652:4(19-30)Online publication date: 19-Jan-2024
https://dl.acm.org/doi/10.1145/3641832.3641836
Show More Cited By

Recommendations

QTune: a query-aware database tuning system with deep reinforcement learning

Database knob tuning is important to achieve high performance (e.g., high throughput and low latency). However, knob tuning is an NP-hard problem and existing methods have several limitations. First, DBAs cannot tune a lot of database instances on ...
Demonstrating UDO: A Unified Approach for Optimizing Transaction Code, Physical Design, and System Parameters via Reinforcement Learning
SIGMOD '21: Proceedings of the 2021 International Conference on Management of Data

UDO is a versatile tool for offline tuning of database systems for specific workloads. UDO can consider a variety of tuning choices, reaching from picking transaction code variants over index selections up to database system parameter tuning. UDO uses ...
Oracle High-Performance SQL Tuning

Comments

Information & Contributors

Information

Published In

cover image Proceedings of the VLDB Endowment

Proceedings of the VLDB Endowment Volume 14, Issue 13

September 2021

168 pages

ISSN:2150-8097

Editors:
Xin Luna Dong
Amazon
,
Felix Naumann
HPI, University of Potsdam

Issue’s Table of Contents

Publisher

VLDB Endowment

Publication History

Published: 01 September 2021

Published in PVLDB Volume 14, Issue 13

Qualifiers

Research-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

9
Total Citations
View Citations
143
Total Downloads

Downloads (Last 12 months)71
Downloads (Last 6 weeks)4

Reflects downloads up to 18 Aug 2024

Other Metrics

View Author Metrics

Citations

Cited By

Lao JWang YLi YWang JZhang YCheng ZChen WTang MWang J(2024)GPTuner: A Manual-Reading Database Tuning System via GPT-Guided Bayesian OptimizationProceedings of the VLDB Endowment10.14778/3659437.365944917:8(1939-1952)Online publication date: 1-Apr-2024
https://dl.acm.org/doi/10.14778/3659437.3659449
Kanellis KFreischuetz JVenkataraman S(2024)Nautilus: A Benchmarking Platform for DBMS Knob TuningProceedings of the Eighth Workshop on Data Management for End-to-End Machine Learning10.1145/3650203.3663336(72-76)Online publication date: 9-Jun-2024
https://dl.acm.org/doi/10.1145/3650203.3663336
Siddiqui TWu W(2024)ML-Powered Index Tuning: An Overview of Recent Progress and Open ChallengesACM SIGMOD Record10.1145/3641832.364183652:4(19-30)Online publication date: 19-Jan-2024
https://dl.acm.org/doi/10.1145/3641832.3641836
Wang JTrummer IKara AOlteanu D(2023)ADOPT: Adaptively Optimizing Attribute Orders for Worst-Case Optimal Join Algorithms via Reinforcement LearningProceedings of the VLDB Endowment10.14778/3611479.361148916:11(2805-2817)Online publication date: 24-Aug-2023
https://dl.acm.org/doi/10.14778/3611479.3611489
Zhang XChang ZWu HLi YChen JTan JLi FCui B(2023)A Unified and Efficient Coordinating Framework for Autonomous DBMS TuningProceedings of the ACM on Management of Data10.1145/35893311:2(1-26)Online publication date: 20-Jun-2023
https://dl.acm.org/doi/10.1145/3589331
Richly KSchlosser RBoissier M(2022)Budget-Conscious Fine-Grained Configuration Optimization for Spatio-Temporal ApplicationsProceedings of the VLDB Endowment10.14778/3565838.356585815:13(4079-4092)Online publication date: 1-Sep-2022
https://dl.acm.org/doi/10.14778/3565838.3565858
Perera ROetomo BRubinstein BBorovica-Gajic R(2022)HMABProceedings of the VLDB Endowment10.14778/3565816.356582416:2(216-229)Online publication date: 1-Oct-2022
https://dl.acm.org/doi/10.14778/3565816.3565824
Kanellis KDing CKroth BMüller ACurino CVenkataraman S(2022)LlamaTuneProceedings of the VLDB Endowment10.14778/3551793.355184415:11(2953-2965)Online publication date: 29-Sep-2022
https://dl.acm.org/doi/10.14778/3551793.3551844
Trummer IIves ZBonifati AEl Abbadi A(2022)Demonstrating DB-BERT: A Database Tuning Tool that "Reads" the ManualProceedings of the 2022 International Conference on Management of Data10.1145/3514221.3520171(2437-2440)Online publication date: 10-Jun-2022
https://dl.acm.org/doi/10.1145/3514221.3520171

View Options

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents