Survey of Approaches to Parameter Tuning for Database Systems

Cao Rong; Bao Liang; Cui Jiangtao; Li Hui; Zhou Heng

doi:10.7544/issn1000-1239.202110976

Journal of Computer Research and Development > 2023 > 60(3): 635-653. > DOI: 10.7544/issn1000-1239.202110976

Cao Rong, Bao Liang, Cui Jiangtao, Li Hui, Zhou Heng. Survey of Approaches to Parameter Tuning for Database Systems[J]. Journal of Computer Research and Development, 2023, 60(3): 635-653. DOI: 10.7544/issn1000-1239.202110976

Citation:

PDF (1568 KB)

Survey of Approaches to Parameter Tuning for Database Systems

1.
School of Computer Science and Technology, Xidian University, Xi’an 710071
2.
Inspur Group Co., Ltd., Jinan 250014

Funds: This work was supported by the National Natural Science Foundation of China (62172316), the Ministry of Education Humanities and Social Science Project of China (17YJA790047), the Soft Science Research Plans of Shaanxi Province (2020KRZ018), the Research Project on Major Theoretical and Practical Problems of Philosophy and Social Sciences in Shaanxi Province (20JZ-25), the Key Research and Development Program of Shaanxi Province (2019ZDLGY13-03-02), the Natural Science Foundation of Shaanxi Province (2019JM-368), and the Key Research and Development Program of Hebei Province (20310102D).

More Information

Received Date: September 29, 2021
Revised Date: February 22, 2022
Available Online: February 26, 2023

Graphical Abstract

Abstract

Abstract

Database systems contain a vast number of configuration parameters controlling nearly all aspects of runtime operation. Different parameter settings may lead to different performance values. Parameter tuning can improve the adaptability of database to current environment by selecting appropriate parameter settings. However, parameter tuning faces several challenges. The first challenge is the complexity of parameter space, while the second is the insufficient samples caused by the expensive performance measurements. Moreover, the optimal parameter configuration is not universal when the environment changes. Therefore, regular users and even expert administrators grapple with understanding and tuning configuration parameters to achieve good performance. We summarize and analyze the existing work on parameter tuning for database systems and classify them into two categories: tuning approaches under fixed environments and tuning approaches under changed enviroments, according to whether the approaches have the ability to cope with environmental changes. For the first one, the research work is divided into traditional parameter tuning and machine learning-based parameter tuning according to whether the approaches can learn from historical tasks. For the second one, the existing approaches are introduced according to different environmental change scenarios, respectively. Finally, we summarize the pros and cons of various approaches and discuss some open research problems for parameter tuning.
- database systems,
- parameter tuning,
- performance tuning,
- machine learning,
- self-driving database

FullText(HTML)

References (68)

References

[1]	Duan Songyun, Thummala V, Babu S. Tuning database configuration parameters with iTuned[J]. Proceedings of the VLDB Endowment, 2009, 2(1): 1246−1257 doi: 10.14778/1687627.1687767
[2]	Van Aken D. On automatic database management system tuning using machine learning[D]. Pittsburgh, PA: Carnegie Mellon University, 2021
[3]	Van Aken D, Pavlo A, Gordon G J, et al. Automatic database management system tuning through large-scale machine learning[C] //Proc of the 2017 ACM Int Conf on Management of Data. New York: ACM, 2017: 1009−1024
[4]	Xu Tianyin, Zhang Jiaqi, Huang Peng, et al. Do not blame users for misconfigurations[C] //Proc of the 24th ACM Symp on Operating Systems Principles. New York: ACM, 2013: 244−259
[5]	Babu S, Borisov N, Duan Songyun, et al. Automated experiment-driven management of (database) systems[C/OL] //Proc of the 12th Workshop on Hot Topics in Operating Systems (HotOS). New York: ACM, 2009 [2021-09-10]. https://www.usenix.org/legacy/event/hotos09/tech/full_papers/babu/babu.pdf
[6]	李国良, 周煊赫, 孙佶, 等. 基于机器学习的数据库技术综述[J], 计算机学报, 2020, 43(11): 2019−2049 Li Guoliang, Zhou Xuanhe, Sun Ji, et al. A survey of machine-learning-based database techniques[J]. Chinese Journal of Computers, 2020, 43(11): 2019−2049 (in Chinese)
[7]	Chaudhuri S. An overview of query optimization in relational systems[C] //Proc of the 17th ACM SIGACT-SIGMOD-SIGART Symp on Principles of Database Systems. New York: ACM, 1998: 34−43
[8]	Kossmann D. The state of the art in distributed query processing[J]. ACM Computing Surveys, 2000, 32(4): 422−469 doi: 10.1145/371578.371598
[9]	Lu Hongjun. Query Processing in Parallel Relational Database Systems[M]. Los Alamitos, CA: IEEE Computer Society Press, 1994
[10]	Herodotou H, Chen Yuxing, Lu Jiaheng. A survey on automatic parameter tuning for big data processing systems[J]. ACM Computing Surveys, 2020, 53(2): Article No. 43
[11]	Huang Changwu, Li Yuanxiang, Yao Xin. A survey of automatic parameter tuning methods for metaheuristics[J]. IEEE Transactions on Evolutionary Computation, 2019, 24(2): 201−216
[12]	柴茗珂,范举,杜小勇. 学习式数据库系统: 挑战与机遇[J]. 软件学报,2020,31(3):806−830 Chai Mingke, Fan Ju, Du Xiaoyong. Learnable database systems: Challenges and opportunities[J]. Journal of Software, 2020, 31(3): 806−830 (in Chinese)
[13]	孙路明,张少敏,姬涛,等. 人工智能赋能的数据管理技术研究[J]. 软件学报,2020,31(3):600−619 doi: 10.13328/j.cnki.jos.005909 Sun Luming, Zhang Shaomin, Ji Tao, et al. Survey of data management techniques powered by artificial intelligence[J]. Journal of Software, 2020, 31(3): 600−619 (in Chinese) doi: 10.13328/j.cnki.jos.005909
[14]	孟小峰,马超红,杨晨. 机器学习化数据库系统研究综述[J]. 计算机研究与发展,2019,56(9):1803−1820 doi: 10.7544/issn1000-1239.2019.20190446 Meng Xiaofeng, Ma Chaohong, Yang Chen. Survey on machine learning for database systems[J]. Journal of Computer Research and Development, 2019, 56(9): 1803−1820 (in Chinese) doi: 10.7544/issn1000-1239.2019.20190446
[15]	Costa R L C, Moreira J, Pintor P, et al. A survey on data-driven performance tuning for big data analytics platforms[J]. Big Data Research, 2021, 25: 100206
[16]	Lu Jiaheng, Chen Yuxing, Herodotou H, et al. Speedup your analytics: Automatic parameter tuning for databases and big data systems[J]. Proceedings of the VLDB Endowment, 2019, 12(12): 1970−1973 doi: 10.14778/3352063.3352112
[17]	Yan Zhengtong, Lu Jiaheng, Chainani N, et al. Workload-aware performance tuning for autonomous DBMSs[C] //Proc of the 37th IEEE Int Conf on Data Engineering (ICDE). Piscataway, NJ: IEEE, 2021: 2365−2368
[18]	Van Aken D, Yang Dongsheng, 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 doi: 10.14778/3450980.3450992
[19]	Renouard J. MySQLTuner [CP/OL]. (2014-02-22)[2021-06-08].https://github.com/major/MySQLTuner-perl
[20]	Oracle Corporation. Oracle database performance tuning guides [EB/OL]. 2014[2021-06-08]. https://docs.oracle.com/cd/E11882_01/server.112/e41573/toc.htm
[21]	International Business Machines Corporation. IBM tuning guides [EB/OL]. 2012[2021-06-08]. https://www.ibm.com/docs/en/control-center/6.1.2?topic=processor-tuning-database-server
[22]	Microsoft Corporation. Microsoft Azure SQL database tuning guides [EB/OL]. 2021[2021-09-13]. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning
[23]	Oracle Corporation. WebLogic database tuning [EB/OL]. 2018[2021-06-08].https://docs.oracle.com/cd/E13222_01/wls/docs92/perform/dbtune.html
[24]	Xu Tianyin, Jin Long, Fan Xuepeng, et al. Hey, you have given me too many knobs!: Understanding and dealing with over-designed configuration in system software[C] //Proc of the 10th Joint Meeting on Foundations of Software Engineering. New York: ACM, 2015: 307−319
[25]	Jin Dongpu, Qu Xiao, Cohen M B, et al. Configurations everywhere: Implications for testing and debugging in practice[C] //Proc of the 36th Int Conf on Software Engineering. New York: ACM, 2014: 215−224
[26]	Kwan E, Lightstone S, Storm A, et al. Automatic configuration for IBM DB2 universal database[R]. New York: IBM, 2002
[27]	Storm A J, Garcia-Arellano C, Lightstone S S, et al. Adaptive self-tuning memory in DB2[C] //Proc of the 32nd Int Conf on Very Large Data Bases. New York: ACM, 2006: 1081−1092
[28]	Tian Wenhu, Martin P, Powley W. Techniques for automatically sizing multiple buffer pools in DB2[C] //Proc of the 2003 Conf of the Centre for Advanced Studies on Collaborative Research. New York: ACM, 2003: 294−302
[29]	Dias K, Ramacher M, Shaft U, et al. Automatic performance diagnosis and tuning in Oracle[C/OL] //Proc of the 2nd Biennial Conf on Innovative Data Systems Research (CIDR). 2005: 84−94. [2021-07-14].https://database.cs.wisc.edu/cidr/cidr2005/papers/P07.pdf
[30]	Yagoub K, Belknap P, Dageville B, et al. Oracle’s SQL performance analyzer[J]. IEEE Data Engineering Bulletin, 2008, 31(1): 51−58
[31]	Belknap P, Dageville B, Dias K, et al. Self-tuning for SQL performance in Oracle database 11g[C] //Proc of the 25th IEEE Int Conf on Data Engineering. Piscataway, NJ: IEEE, 2009: 1694−1700
[32]	Narayanan D, Thereska E, Ailamaki A. Continuous resource monitoring for self-predicting DBMS[C] //Proc of the 13th IEEE Int Symp on Modeling, Analysis, and Simulation of Computer and Telecommunication Systems. Piscataway, NJ: IEEE, 2005: 239−248
[33]	Tran D N, Huynh P C, Tay Y C, et al. A new approach to dynamic self-tuning of database buffers[J]. ACM Transactions on Storage, 2008, 4(1): Article No. 3
[34]	Sullivan D G, Seltzer M I, Pfeffer A. Using probabilistic reasoning to automate software tuning[J]. ACM SIGMETRICS Performance Evaluation Review, 2004, 32(1): 404−405 doi: 10.1145/1012888.1005739
[35]	Yoon D Y, Niu Ning, Mozafari B. DBSherlock: A performance diagnostic tool for transactional databases[C] //Proc of the 2016 Int Conf on Management of Data. New York: ACM, 2016: 1599−1614
[36]	Wei Zhijie, Ding Zuohua, Hu Jueliang. Self-tuning performance of database systems based on fuzzy rules[C] //Proc of the 11th Int Conf on Fuzzy Systems and Knowledge Discovery (FSKD). Piscataway, NJ: IEEE, 2014: 194−198
[37]	Zhu Yuqing, Liu Jianxun, Guo Mengying, et al. BestConfig: Tapping the performance potential of systems via automatic configuration tuning[C]//Proc of the 8th Symp on Cloud Computing. New York: ACM, 2017: 338−350
[38]	Mahgoub A, Wood P, Ganesh S, et al. Rafiki: A middleware for parameter tuning of NoSQL datastores for dynamic metagenomics workloads[C] //Proc of the 18th ACM/IFIP/USENIX Middleware Conf. New York: ACM, 2017: 28−40
[39]	Debnath B K, Lilja D J, Mokbel M F. SARD: A statistical approach for ranking database tuning parameters[C] //Proc of the 24th IEEE Int Conf on Data Engineering Workshop. Piscataway, NJ: IEEE, 2008: 11−18
[40]	Lima M I V, de Farias V A E, Praciano F D B S, et al. Workload-aware parameter selection and performance prediction for in-memory databases[C/OL] //Proc of the 33rd SBC Brazilian Symp on Databases (SBBD). 2018: 169−180. [2021-08-01].https://sbbd.org.br/2018/wp-content/uploads/sites/5/2018/08/169-sbbd_2018-fp.pdf
[41]	Zhang Bohan, Aken D V, Wang J, et al. A demonstration of the ottertune automatic database management system tuning service[J]. Proceedings of the VLDB Endowment, 2018, 11(12): 1910−1913 doi: 10.14778/3229863.3236222
[42]	Ishihara Y, Shiba M. Dynamic configuration tuning of working database management systems[C] //Proc of the 2nd IEEE Global Conf on Life Sciences and Technologies (LifeTech). Piscataway, NJ: IEEE, 2020: 393−397
[43]	Tan Jian, Zhang Tieying, Li Feifei, et al. iBTune: Individualized buffer tuning for large-scale cloud databases[J]. Proceedings of the VLDB Endowment, 2019, 12(10): 1221−1234 doi: 10.14778/3339490.3339503
[44]	Rodd S F, Kulkarni U P. Adaptive tuning algorithm for performance tuning of database management system[J]. arXiv preprint, arXiv: 1005.0972, 2010
[45]	Zheng Chonghuan, Ding Zuohua, Hu Jieliang. Self-tuning performance of database systems with neural network[C] //Proc of the 10th Int Conf on Intelligent Computing: Intelligent Computing Theory. Berlin: Springer, 2014: 1−12
[46]	Bao Liang, Liu Xin, Wang Fangzheng, et al. ACTGAN: Automatic configuration tuning for software systems with generative adversarial networks[C] //Proc of the 34th IEEE/ACM Int Conf on Automated Software Engineering (ASE). Piscataway, NJ: IEEE, 2019: 465−476
[47]	Siegmund N, Grebhahn A, Apel S, et al. Performance-influence models for highly configurable systems[C] //Proc of the 10th Joint Meeting on Foundations of Software Engineering. New York: ACM, 2015: 284−294
[48]	Nair V, Menzies T, Siegmund N, et al. Using bad learners to find good configurations[C] //Proc of the 11th Joint Meeting on Foundations of Software Engineering. New York: ACM, 2017: 257−267
[49]	Ha H, Zhang Hongyu. DeepPerf: Performance prediction for configurable software with deep sparse neural network[C] //Proc of the 41st IEEE/ACM Int Conf on Software Engineering (ICSE). Piscataway, NJ: IEEE, 2019: 1095−1106
[50]	Zhang Ji, Liu Yu, Zhou Ke, et al. An end-to-end automatic cloud database tuning system using deep reinforcement learning[C] //Proc of the 2019 Int Conf on Management of Data. New York: ACM, 2019: 415−432
[51]	Li Guoliang, Zhou Xuanhe, Li Shifu, et al. QTune: A query-aware database tuning system with deep reinforcement learning[J]. Proceedings of the VLDB Endowment, 2019, 12(12): 2118−2130 doi: 10.14778/3352063.3352129
[52]	Tibshirani R. Regression shrinkage and selection via the Lasso[J]. Journal of the Royal Statistical Society:Series B (Methodological), 1996, 58(1): 267−288 doi: 10.1111/j.2517-6161.1996.tb02080.x
[53]	Rasmussen C E. Gaussian processes in machine learning[C] //Proc of the 2003 Summer School on Machine Learning. Berlin: Springer, 2003: 63−71
[54]	Lillicrap T P, Hunt J J, Pritzel A, et al. Continuous control with deep reinforcement learning[J]. arXiv preprint, arXiv: 1509.02971, 2015
[55]	Hasselt H. Double Q-learning[J]. Advances in Neural Information Processing Systems, 2010, 23: 2613−2621
[56]	沈忱,邰凌翔,彭煜玮. 面向自动参数调优的动态负载匹配方法[J]. 计算机应用,2021,41(3):657−661 Shen Chen, Tai Lingxiang, Peng Yuwei. Dynamic workload matching for automatic parameter tuning[J]. Journal of Computer Applications, 2021, 41(3): 657−661 (in Chinese)
[57]	Mahgoub A, Wood P, Medoff A, et al. SOPHIA: Online reconfiguration of clustered NoSQl databases for time-varying workloads[C] //Proc of the 2019 USENIX Annual Technical Conf (USENIX ATC’19). Berkeley, CA: USENIX Association, 2019: 223−240
[58]	Mahgoub A, Medoff A M, Kumar R, et al. OPTIMUSCLOUD: Heterogeneous configuration optimization for distributed databases in the cloud[C] //Proc of the 2020 USENIX Annual Technical Conf (USENIX ATC’20). Berkeley, CA: USENIX Association, 2020: 189−203
[59]	Kossmann J, Schlosser R. A framework for self-managing database systems[C] //Proc of the 35th IEEE Int Conf on Data Engineering Workshops (ICDEW). Piscataway, NJ: IEEE, 2019: 100−106
[60]	Kossmann J, Schlosser R. Self-driving database systems: A conceptual approach[J]. Distributed and Parallel Databases, 2020, 38(4): 795−817 doi: 10.1007/s10619-020-07288-w
[61]	Mühlbauer S, Apel S, Siegmund N. Identifying software performance changes across variants and versions[C] //Proc of the 35th IEEE/ACM Int Conf on Automated Software Engineering (ASE). Piscataway, NJ: IEEE, 2020: 611−622
[62]	Valov P, Petkovich J C, Guo Jianmei, et al. Transferring performance prediction models across different hardware platforms[C] //Proc of the 8th ACM/SPEC Int Conf on Performance Engineering. New York: ACM, 2017: 39−50
[63]	Jamshidi P, Velez M, Kästner C, et al. Transfer learning for improving model predictions in highly configurable software[C] //Proc of the 12th IEEE/ACM Int Symp on Software Engineering for Adaptive and Self-Managing Systems (SEAMS). Piscataway, NJ: IEEE, 2017: 31−41
[64]	Zhang Xinyi, Wu Hong, Chang Zhuo, et al. ResTune: Resource oriented tuning boosted by meta-learning for cloud databases[C] //Proc of the 2021 Int Conf on Management of Data. New York: ACM, 2021: 2102−2114
[65]	Jamshidi P, Siegmund N, Velez M, et al. Transfer learning for performance modeling of configurable systems: An exploratory analysis[C] //Proc of the 32nd IEEE/ACM Int Conf on Automated Software Engineering (ASE). Piscataway, NJ: IEEE, 2017: 497−508
[66]	Javidian M A, Jamshidi P, Valtorta M. Transfer learning for performance modeling of configurable systems: A causal analysis[J]. arXiv preprint, arXiv: 1902.10119, 2019
[67]	Krishna R, Nair V, Jamshidi P, et al. Whence to learn? Transferring knowledge in configurable systems using BEETLE[J]. IEEE Transactions on Software Engineering, 2021, 47(12): 2956−2972 doi: 10.1109/TSE.2020.2983927
[68]	Pavlo A, Angulo G, Arulraj J, et al. Self-driving database management systems[C/OL] //Proc of the 8th Biennial Conf on Innovative Data Systems Research (CIDR). 2017 [2021-07-01].https://15721.courses.cs.cmu.edu/spring2020/papers/27-selfdriving/p42-pavlo-cidr17.pdf

[1]	Lin Liansheng, Zheng Huanqin, Su Shen, Lei Kai, Chen Xiaofeng, Tian Zhihong. An On-Chain Mechanism Against DeFi Price Manipulation Attacks[J]. Journal of Computer Research and Development, 2025, 62(2): 443-457. DOI: 10.7544/issn1000-1239.202330291
[2]	Song Shuwei, Ni Xiaoze, Chen Ting. Gas Optimization for Smart Contracts: A Survey[J]. Journal of Computer Research and Development, 2023, 60(2): 311-325. DOI: 10.7544/issn1000-1239.202220887
[3]	Ying Chenhao, Xia Fuyuan, Li Jie, Si Xueming, Luo Yuan. Incentive Mechanism Based on Truth Estimation of Private Data for Blockchain-Based Mobile Crowdsensing[J]. Journal of Computer Research and Development, 2022, 59(10): 2212-2232. DOI: 10.7544/issn1000-1239.20220493
[4]	Feng Jingyu, Yang Jinwen, Zhang Ruitong, Zhang Wenbo. A Spectrum Sharing Incentive Scheme Against Location Privacy Leakage in IoT Networks[J]. Journal of Computer Research and Development, 2020, 57(10): 2209-2220. DOI: 10.7544/issn1000-1239.2020.20200453
[5]	Hai Mo, Zhu Jianming. A Propagation Mechanism Combining an Optimal Propagation Path and Incentive in Blockchain Networks[J]. Journal of Computer Research and Development, 2019, 56(6): 1205-1218. DOI: 10.7544/issn1000-1239.2019.20180419
[6]	He Yunhua, Li Mengru, Li Hong, Sun Limin, Xiao Ke, Yang Chao. A Blockchain Based Incentive Mechanism for Crowdsensing Applications[J]. Journal of Computer Research and Development, 2019, 56(3): 544-554. DOI: 10.7544/issn1000-1239.2019.20170670
[7]	He Haiwu, Yan An, Chen Zehua. Survey of Smart Contract Technology and Application Based on Blockchain[J]. Journal of Computer Research and Development, 2018, 55(11): 2452-2466. DOI: 10.7544/issn1000-1239.2018.20170658
[8]	Xiong Jinbo, Ma Rong, Niu Ben, Guo Yunchuan, Lin Li. Privacy Protection Incentive Mechanism Based on User-Union Matching in Mobile Crowdsensing[J]. Journal of Computer Research and Development, 2018, 55(7): 1359-1370. DOI: 10.7544/issn1000-1239.2018.20180080
[9]	Wang Bo, Huang Chuanhe, Yang Wenzhong, Dan Feng, and Xu Liya. An Incentive-Cooperative Forwarding Model Based on Punishment Mechanism in Wireless Ad Hoc Networks[J]. Journal of Computer Research and Development, 2011, 48(3): 398-406.
[10]	Yue Guangxue, Li Renfa, Chen Zhi, Zhou Xu. Analysis of Free-riding Behaviors and Modeling Restrain Mechanisms for Peer-to-Peer Networks[J]. Journal of Computer Research and Development, 2011, 48(3): 382-397.

Cited By

Cited by

Periodical cited type(2)

1.	李硕，王馨爽. 多场景融合的码号数据分发架构及关键技术研究. 数据通信. 2024(06): 1-3+11 .
2.	俞惠芳，李磊. 基于椭圆曲线签密的跨链医疗数据共享方案. 通信学报. 2024(12): 57-66 .