Survey of Machine Learning for Database Parameter Tuning Techniques

doi:10.3778/j.issn.1002-8331.2304-0101

Abstract

Abstract: Database parameter tuning techniques are facing great challenge under stringent performance requirements due to the huge data scale and complex application scenarios in big data. Traditional heuristic tuning methods or manual intervention methods are lack of the ability to handle various requirements. The development of machine learning provides a brand-new opportunity for database parameter tuning with its power in learning, reasoning, and planning. Through sufficient investigation, the evolution route of database parameter tuning technology based on machine learning is given. According to the thought of content-problem-resolution, this paper describes the traditional parameter tuning techniques and the parameter tuning techniques based on Bayesian optimization (BO) model and reinforcement learning (RL), and the future research directions are proposed as well. Hopefully, this paper can provide some valuable references for researchers in this field.

Key words: database system, parameter tuning, machine learning

摘要： 大数据时代，数据规模庞大、应用场景复杂，为满足苛刻的性能要求，面对庞杂的数据库参数，给出高质量的参数调优结果挑战巨大。传统启发式调优算法或者人工干预方法很难普适地满足各类调优需求。机器学习因其强大的学习和泛化能力被广泛应用于各种复杂场景，包括数据库参数调优。经过充分调研，给出学习式数据库参数调优技术的演化路线、依据路线，按照研究内容-存在问题-解决问题的思路，叙述传统参数调优技术、基于BO模型的参数调优技术以及基于RL的参数调优技术，展望未来的研究方向和所面临的挑战，希望为这一领域科研工作者提供有价值的参考。

关键词: 数据库系统, 参数调优, 机器学习

JIANG Lulu, GAO Jintao. Survey of Machine Learning for Database Parameter Tuning Techniques[J]. Computer Engineering and Applications, 2024, 60(3): 1-16.

姜璐璐, 高锦涛. 面向机器学习的数据库参数调优技术综述[J]. 计算机工程与应用, 2024, 60(3): 1-16.

References

[1] WANG Z Y, HUTTER F, ZOGHI M, et al. Bayesian optimization in a billion dimensions via random embeddings[J]. Journal of Artificial Intelligence Research, 2016, 55: 361-387.
[2] 孙路明, 张少敏, 姬涛, 等. 人工智能赋能的数据管理技术研究[J]. 软件学报, 2020, 31(3): 600-619.
SUN L M, ZHANG S M, JI T, et al. Survey of data management techniques powered by artificial intelligence[J]. Journal of Software, 2020, 31(3): 600-619.
[3] VAN AKEN D, PAVLO A, GORDON G J, et al.Automatic database management system tuning through large-scale machine learning[C]//Proceedings of the International Conference on Management of Data, 2017: 1009-1024.
[4] AKEN D V, YANG D, BRILLARD S, et al. An inquiry into machine learning-based automatic configuration tuning services on real-world database management systems[J].Proceedings of the VLDB Endowment, 2021, 14(7): 1241-1253.
[5] KUNJIR M, BABU S. Black or white? how to develop an autotuner for memory-based analytics[C]//Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data, 2020: 1667-1683.
[6] ZHANG X, WU H, LI Y, et al.Towards dynamic and safe configuration tuning for cloud databases[C]//Proceedings of the 2022 International Conference on Management of Data, 2022: 631-645.
[7] CEREDA S, VALLADARES S, CREMONESI P, et al. CGPTuner: a contextual gaussian process bandit approach for the automatic tuning of IT configurations under varying workload conditions[J]. Proceedings of the VLDB Endowment, 2021, 14(8): 1401-1413.
[8] ZHANG X, WU H, CHANG Z, et al. ResTune: resource oriented tuning boosted by meta-learning for cloud databases[C]//Proceedings of the 2021 International Conference on Management of Data, 2021: 2102-2014.
[9] KANELLIS K, DING C, KROTH B, et al. LlamaTune: sample-efficient DBMS configuration tuning[J].Proceedings of the VLDB Endowment, 2022,15: 2953-2965.
[10] FEKRY A, CARATA L, PASQUIER T, et al.To tune or not to tune?: in search of optimal configurations for data analytics[C]//Proceedings of the 26th ACM SIGKDD Conference on Knowledge Discovery and Data Mining, 2020: 2494-2504.
[11] ZHANG J, LIU Y, ZHOU K, et al.An end-to-end automatic cloud database tuning system using deep reinforcement learning[C]//Proceedings of the 2019 International Conference, 2019: 415-432.
[12] CAI B, LIU Y, ZHANG C, et al. HUNTER: an online cloud database hybrid tuning system for personalized requirements[C]//Proceedings of the 2022 International Conference on Management of Data, 2022: 646-659.
[13] LI G L, ZHOU X H, LI S F, et al. QTune: a query-aware database tuning system with deep reinforcement learning[J].Proceedings of the VLDB Endowment, 2019, 12(12): 2118-2130.
[14] GE J, CHAI Y, CHAI Y. WATuning: a workload-aware tuning system with attention?based deep reinforcement learning[J]. Journal of Computer Science and Technology, 2021, 36(4): 741-761.
[15] ZHANG X, CHANG Z, LI Y, et al. Facilitating database tuning with hyper-parameter optimization[J]. Proceedings of the VLDB Endowment, 2022, 15(9): 1808-1821.
[16] 李国良, 周煊赫, 孙佶, 等.基于机器学习的数据库技术综述[J]. 计算机学报, 2020, 43(11): 2019-2049.
LI G L, ZHOU X H, SUN J, et al. A survey of machine learning based database techniques[J]. Chinese Journal of Computers, 2020, 43(11): 2019-2049.
[17] RENOUARD J M. MySQLTuner-perl[EB/OL].(2022-06-15)[2023-03-15].https://github.com/major/MySQLTuner-perl.
[18] WEI Z, DING Z, HU J. Self-tuning performance of database systems based on fuzzy rules[C]//Proceedings of the International Conference on Fuzzy Systems & Knowledge Discovery, 2014: 194-198.
[19] ZHU Y, LIU J, GUO M, et al. BestConfig: tapping the performance potential of systems via automatic configuration tuning[C]//Proceedings of the 2017 Symposium on Cloud Computing, 2017: 338-350.
[20] 陈镭. 基于机器学习的数据库系统自动调参研究[J]. 软件导刊, 2021, 20(11): 148-151.
CHEN L. Automatic database tuning research based on machine learning[J]. Software Guide, 2021, 20(11): 148-151.
[21] 孟小峰, 马超红, 杨晨. 机器学习化数据库系统研究综述[J]. 计算机研究与发展, 2019, 56(9): 1803-1820.
MENG X F, MA C H, YANG C. Survey on machine learning for database systems[J]. Journal of Computer Research and Development, 2019, 56(9): 1803-1820.
[22] VAN AKEN D, PAVLO A, ZHANG B H. OtterTune[EB/OL]. （2021）[2023-03-15]. https://OtterTune.cs.cmu.edu.
[23] TIBSHIRANI R. Regression shrinkage and selection via the Lasso[J]. Journal of the Royal Statistical Society: Series B (Methodological), 1996, 58(1): 267-288.
[24] RASMUSSEN C E. Gaussian processes in machine learning[C]//Advanced Lectures on Machine Learning, ML Summer Schools, 2003: 63-71.
[25] VAN AKEN D, PAVLO A, ZHANG B H. OtterTune-automated database tuning service[EB/OL].(2021)[2023-03-15]. https://OtterTune.com.
[26] ZMORA N, JACOB G, ZLOTNIK L, et al. Neural network distiller: a python package for DNN compression research[J]. arXiv:1910.12232, 2019.
[27] SRIVASTAVA N, HINTON G, KRIZHEVSKY A, et al. Dropout: a simple way to prevent neural networks from overfitting[J]. Journal of Machine Learning Research, 2014, 15(1): 1929-1958.
[28] PLAPPERT M, HOUTHOOFT R, DHARIWAL P, et al.Parameter space noise for exploration[J]. arXiv:1706.01905, 2017.
[29] BOENDER C G E, MOCKUS J.Bayesian approach to global optimization—theory and applications[J].Mathematics of Computation, 1991, 56: 878-879.
[30] PAUL L T, JAMES H J, DAVID S, et al. Continuous control with deep reinforcement learning[C]//Proceedings of the CoRR, 2015.
[31] DALIBARD V, SCHAARSCHMIDT M, YONEKI E. BOAT: building auto-tuners with structured Bayesian optimization [C]//Proceedings of the 26th International Conference on World Wide Web, 2017: 479-488.
[32] HUTTER F. HOOS H H. LEYTON-BROWN K. Sequential model-based optimization for general algorithm configuration[C]//Proceedings of the International Conference on Learning and Intelligent Optimization, 2011: 507-523.
[33] ESTER M A Density-based algorithm for discovering clusters in large spatial databases with noise[C]//Proceedings of the International Conference on knowledg Discovery & Data Mining, 1996: 226-231.
[34] KRISHNAN A, DAS M, BENDRE M, et al. Transfer learning via contextual invariants for one-to-many crossdomain recommendation[C]//Proceedings of the SIGIR ACM, 2020: 1081-1090.
[35] KRAUSE A, ONG C.Contextual Gaussian process bandit optimization[C]//Advances in Neural Information Processing Systems, 2011: 2447-2455.
[36] ALIPOURFARD O, LIU H H, CHEN J, et al. CherryPick: adaptively unearthing the best cloud configurations for big data analytics[C]//Proceedings of the Networked Systems Design and Implementation, 2017: 469-482.
[37] BROCHU E, HOFFMAN M W, FREITAS N D. Portfolio allocation for Bayesian optimization[J]. UAI, 2010, 35(3): 841-845.
[38] DUAN S, THUMMALA V, BABU S. Tuning database configuration parameters with iTuned[J]. Proceedings of the VLDB Endowment, 2009, 2(1): 1246-1257.
[39] MALAVIYA A. Automated machine learning: methods, systems, challenges[J]. Computing Reviews, 2021(9): 62.
[40] WISTUBA M, SCHILLING N, SCHMIDT-THIEME L. Two-stage transfer surrogate model forautomatic hyperparameter optimization[C]//Proceedings of the Joint European Conference on Machine Learning and Knowledge Discovery in Databases, 2016: 199-214.
[41] NAYEBI A, MUNTEANU A, POLOCZEK M.A framework for Bayesian optimization in embedded subspaces[C]//Proceedings of the International Conference on Machine Learning, 2019: 4752-4761.
[42] BORGONOVO E, PLISCHKE E. Sensitivity analysis: a review of recent advances[J]. European Journal of Operational Research, 2016, 248(3): 869-887.
[43] LIAW A, WIENER M. Classification and regression by random forest[J]. R News, 2002, 23: 18-22.
[44] Amazon EC2 instance pricing[EB/OL].(2018)[2023-03-15]. https://www.amazonaws.cn/en/ec2/pricing.
[45] KRISHNAN S P T, UGIA GONZALEZ J L. Building your next big thing with google cloud platform[M].CA: Apress Berkeley, 2015: 53-81.
[46] ZHANG J, ZHOU K, LI G, et al. CDBTune+: an efficient deep reinforcement learning-based automatic cloud database tuning system[J]. The VLDB Journal, 2021, 30(6): 959-987.
[47] VAN HASSELT H. Double Q-learning[C]//Proceedings of the 23rd International Conference on Neural Information Processing Systems, 2010: 2613-2621.
[48] GENG J K, LI D, WANG S. Accelerating distributed machine learning by smart parameter server[C]//Proceedings of the 3rd Asia-Pacific Workshop on Networking, 2019: 92-98.
[49] LI M, ANDERSEN D G, PARK J W, et al. Scaling distributed machine learning with the parameter server[C]//Proceedings of the 2014 International Conference on Big Data Science and Computing, 2014: 583-598.
[50] KORNACKER M, BEHM A, BITTORF V, et al. Impala: a modern, open-source SQL engine for hadoop[C]//Proceedings of the Conference on Innovative Data Systems Research, 2015: 1957-1968.
[51] 李国良, 周煊赫. 面向AI的数据管理技术综述[J]. 软件学报, 2021, 32(1): 21-40.
LI G L, ZHOU X H. Survey of data management techniques for artificial intelligence[J]. Journal of Software, 2021, 32(1): 21-40.