Skip to main content

Advertisement

Springer Nature Link
Log in

SHA: QoS-Aware Software and Hardware Auto-Tuning for Database Systems

  • Regular Paper
  • Published:
Journal of Computer Science and Technology Aims and scope Submit manuscript

Abstract

While databases are widely-used in commercial user-facing services that have stringent quality-of-service (QoS) requirement, it is crucial to ensure their good performance and minimize the hardware usage at the same time. Our investigation shows that the optimal DBMS (database management system) software configuration varies for different user request patterns (i.e., workloads) and hardware configurations. It is challenging to identify the optimal software and hardware configurations for a database workload, because DBMSs have hundreds of tunable knobs, the effect of tuning a knob depends on other knobs, and the dependency relationship changes under different hardware configurations. In this paper, we propose SHA, a software and hardware auto-tuning system for DBMSs. SHA is comprised of a scaling-based performance predictor, a reinforcement learning (RL) based software tuner, and a QoS-aware resource reallocator. The performance predictor predicts its optimal performance with different hardware configurations and identifies the minimum amount of resources for satisfying its performance requirement. The software tuner fine-tunes the DBMS software knobs to optimize the performance of the workload. The resource reallocator assigns the saved resources to other applications to improve resource utilization without incurring QoS violation of the database workload. Experimental results show that SHA improves the performance of database workloads by 9.9% on average compared with a state-of-the-art solution when the hardware configuration is fixed, and improves 43.2% of resource utilization while ensuring the QoS.

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

Access this article

Log in via an institution

Subscribe and save

Springer+ Basic
$34.99 /Month
  • Get 10 units per month
  • Download Article/Chapter or eBook
  • 1 Unit = 1 Article or 1 Chapter
  • Cancel anytime
Subscribe now

Buy Now

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Explore related subjects

Discover the latest articles, news and stories from top researchers in related subjects.

References

  1. Laney D. 3D data management: Controlling data volume, velocity, and variety. META Group Research Note, 2001. https://www.bibsonomy.org/bibtex/742811cb00b303261f79a98e9b80bf49, Mar. 2024.

  2. Russom P. Big data analytics. TDWI best practices report. Fourth Quarter, 2011. https://vivomente.com/wp-content/uploads/2016/04/big-data-analytics-white-paper.pdf, Mar. 2024.

  3. Grad B, Bergin T J. Guest editors’ introduction: History of database management systems. IEEE Annals of the History of Computing, 2009, 31(4): 3–5. DOI: https://doi.org/10.1109/MAHC.2009.99.

    Article  MathSciNet  Google Scholar 

  4. Duan S, Thummala V, Babu S. Tuning database configuration parameters with iTuned. Proceedings of the VLDB Endowment, 2009, 2(1): 1246–1257. DOI: https://doi.org/10.14778/1687627.1687767.

    Article  Google Scholar 

  5. Debnath B K, Lilja D J, Mokbel M F. SARD: A statistical approach for ranking database tuning parameters. In Proc. the 24th International Conference on Data Engineering Workshop, Apr. 2008, pp.11–18. DOI: https://doi.org/10.1109/ICDEW.2008.4498279.

  6. Barroso L A, Hölzle U. The Datacenter as a Computer: An Introduction to the Design of Warehouse-Scale Machines. Springer Cham, 2009. DOI: https://doi.org/10.1007/978-3-031-01722-3.

    Book  Google Scholar 

  7. Dean J, Barroso L A. The tail at scale. Communications of the ACM, 2013, 56(2): 74–80. DOI: https://doi.org/10.1145/2408776.2408794.

    Article  Google Scholar 

  8. Zhang B H, Van Aken D, Wang J, Dai T, Jiang S L, Lao J, Sheng S Y, Pavlo A, Gordon G J. A demonstration of the OtterTune automatic database management system tuning service. Proceedings of the VLDB Endowment, 2018, 11(12): 1910–1913. DOI: https://doi.org/10.14778/3229863.3236222.

    Article  Google Scholar 

  9. Van Aken D, Pavlo A, Gordon G J, Zhang B H. Automatic database management system tuning through large-scale machine learning. In Proc. the 2017 ACM International Conference on Management of Data, May 2017, pp.1009–1024. DOI: https://doi.org/10.1145/3035918.3064029.

  10. Zhu Y Q, Liu J X, Guo M Y, Ma W L, Bao Y G. ACTS in need: Automatic configuration tuning with scalability guarantees. In Proc. the 8th Asia-Pacific Workshop on Systems, Sept. 2017, Article No. 14. DOI: https://doi.org/10.1145/3124680.3124730.

  11. Delimitrou C, Kozyrakis C. Quasar: Resource-efficient and QoS-aware cluster management. ACM SIGPLAN Notices, 2014, 49(4): 127–144. DOI: https://doi.org/10.1145/2644865.2541941.

    Article  Google Scholar 

  12. Delimitrou C, Kozyrakis C. Paragon: QoS-aware scheduling for heterogeneous datacenters. ACM SIGPLAN Notices, 2013, 48(4): 77–88. DOI: https://doi.org/10.1145/2499368.2451125.

    Article  Google Scholar 

  13. Weikum G, Moenkeberg A, Hasse C, Zabback P. Self-tuning database technology and information services: From wishful thinking to viable engineering. In Proc. the 28th International Conference on Very Large Databases, Bernstein P A, Ioannidis Y E, Ramakrishnan R, Papadias D (eds.), Elsevier, 2002, pp.20–31. DOI: https://doi.org/10.1016/B978-155860869-6/50011-1.

  14. Kwan E, Lightstone S, Storm A, Wu L. Automatic configuration for IBM® DB2 universal database™. IBM Performance Technical Report, 2002. https://wwwiti.cs.unimagdeburg.de/~eike/selftuning/sources/automatic_configuration_for_db2.pdf, Mar. 2022.

  15. Tran D N, Huynh P C, Tay Y C, Tung A K H. A new approach to dynamic self-tuning of database buffers. ACM Trans. Storage, 2008, 4(1): Article No. 3. DOI: https://doi.org/10.1145/1353452.1353455.

  16. Storm A J, Garcia-Arellano C, Lightstone S S, Diao Y X, Surendra M. Adaptive self-tuning memory in DB2. In Proc. the 32nd International Conference on Very Large Data Bases, Sept. 2006, pp.1081–1092. https://www.vldb.org/conf/2006/p1081-storm.pdf, Mar. 2024.

  17. Difallah D E, Pavlo A, Curino C, Cudre-Mauroux P. OLTP-Bench: An extensible testbed for benchmarking relational databases. Proceedings of the VLDB Endowment, 2013, 7(4): 277–288. DOI: https://doi.org/10.14778/2732240.2732246.

    Article  Google Scholar 

  18. Cooper B F, Silberstein A, Tam E, Ramakrishnan R, Sears R. Benchmarking cloud serving systems with YCSB. In Proc. the 1st ACM Symposium on Cloud Computing, Jun. 2010, pp.143–154. DOI: https://doi.org/10.1145/1807128.1807152.

  19. Tibshirani R. Regression shrinkage and selection via the lasso. Journal of the Royal Statistical Society: Series B (Methodological), 1996, 58(1): 267–288. DOI: https://doi.org/10.1111/j.2517-6161.1996.tb02080.x.

    Article  MathSciNet  Google Scholar 

  20. Zhang Z H, Li J. Compound Poisson processes, latent shrinkage priors and Bayesian nonconvex penalization. Bayesian Analysis, 2015, 10(2): 247–274. DOI: https://doi.org/10.1214/14-BA892.

    Article  MathSciNet  Google Scholar 

  21. Caruana R, Niculescu-Mizil A. An empirical comparison of supervised learning algorithms. In Proc. the 23rd International Conference on Machine Learning, Jun. 2006, pp.161–168. DOI: https://doi.org/10.1145/1143844.1143865.

  22. Bishop C M. Pattern Recognition and Machine Learning. Springer, 2006.

  23. Bienia C. Benchmarking modern multiprocessors [Ph.D. Thesis]. Princeton University, 2011.

Download references

Author information

Authors and Affiliations

  1. Department of Computer Science, Shanghai Jiao Tong University, Shanghai, 200240, China

    Jin Li  (李 进), Quan Chen  (陈 全) & Min-Yi Guo  (过敏意)

  2. Department of Computer Science and Technology, Shanghai University of Finance and Economics, Shanghai, 200433, China

    Xiao-Xin Tang  (唐晓新)

Authors
  1. Jin Li  (李 进)
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Quan Chen  (陈 全)
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Xiao-Xin Tang  (唐晓新)
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Min-Yi Guo  (过敏意)
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Min-Yi Guo  (过敏意).

Ethics declarations

Conflict of Interest Min-Yi Guo is an editorial board member for Journal of Computer Science and Technology and was not involved in the editorial review of this article. All authors declare that there are no other competing interests.

Additional information

This work is partially sponsored by the National Natural Science Foundation of China under Grant Nos. 62022057, 61832006, 61632017, and 61872240.

Jin Li is now a Ph.D. student in the Department of Computer Science of Shanghai Jiao Tong University, Shanghai. He received his B.S. degree in computer science from East China University of Science and Technology, Shanghai, in 2012. In 2015 and 2016, he was a visiting student in the Department of Computer Science, Carnegie Mellon University, Pittsburgh. His research interests include machine learning and data mining, particularly, statistical methods and deep learning techniques for real-world applications, such as face recognition, software auto-tuning, and recommender systems.

Quan Chen received his B.S. degree in computer science from the Tongji University, Shanghai, in 2007, and his M.S. and Ph.D. degrees in computer science from the Shanghai Jiao Tong University, Shanghai, in 2009, and 2014 respectively. From 2014 to 2016, he was a postdoctoral researcher in the Department of Computer Science, University of Michigan-Ann Arbor. He is now a tenure-track associate professor in the Department of Computer Science and Engineering, Shanghai Jiao Tong University, Shanghai. His research interests include parallel and distributed processing, task scheduling, cloud computing, datacenter management and accelerator management.

Xiao-Xin Tang received his B.S. degree in computer science from the South China University of Technology, Guangzhou, in 2010. He received his Ph.D. degree in the Department of Computer Science and Engineering, Shanghai Jiao Tong University, Shanghai. In 2013 and 2014, he was a visiting student in the Department of Computer Science, University of Otago, Otago. Currently, he is a lecturer in the Department of Computer Science, Shanghai University of Finance and Economics, Shanghai. His research interests include heterogeneous computing, parallel algorithms, blockchain and financial computing.

Min-Yi Guo received his B.S. and M.E. degrees in computer science from Nanjing University, Nanjing, and his Ph.D. degree in information science from the University of Tsukuba, Tsukuba, in 1982, 1986, and 1998 respectively. From 1998 to 2000, he was a research associate of NEC Soft, Ltd. He was a visiting professor in the Department of Computer Science, Georgia Institute of Technology, Aflanta. In addition, he was a full professor with The University of Aizu, Aizuwakamatsu, and is the head of the Department of Computer Science and Engineering, Shanghai Jiao Tong University, Shanghai. He is a fellow of CCF and IEEE and has published more than 200 papers in well-known conferences and journals. His main interests include automatic parallelization and data-parallel languages, bioinformatics, compiler optimization, and high-performance computing.

Electronic supplementary material

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Li, J., Chen, Q., Tang, XX. et al. SHA: QoS-Aware Software and Hardware Auto-Tuning for Database Systems. J. Comput. Sci. Technol. 39, 369–383 (2024). https://doi.org/10.1007/s11390-022-1751-3

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s11390-022-1751-3

Keywords