Affiliation:
1. New York University, New York, NY
2. Microsoft Corporation, Redmond, WA
3. National University of Singapore, Singapore
Abstract
Current businesses rely heavily on efficient access to their databases. Manual tuning of these database systems by performance experts is increasingly infeasible: For small companies, hiring an expert may be too expensive; for large enterprises, even an expert may not fully understand the interaction between a large system and its multiple changing workloads. This trend has led major vendors to offer tools that automatically and dynamically tune a database system.
Many database tuning knobs concern the buffer pool for caching data and disk pages. Specifically, these knobs control the buffer allocation and thus the cache miss probability, which has direct impact on performance.
Previous methods for automatic buffer tuning are based on simulation, black-box control, gradient descent, and empirical equations. This article presents a new approach, using calculations with an analytically-derived equation that relates miss probability to buffer allocation; this equation fits four buffer replacement policies, as well as twelve datasets from mainframes running commercial databases in large corporations.
The equation identifies a buffer-size limit that is useful for buffer tuning and powering down idle buffers. It can also replace simulation in predicting I/O costs. Experiments with PostgreSQL illustrate how the equation can help optimize online buffer partitioning, ensure fairness in buffer reclamation, and dynamically retune the allocation when workloads change. It is also used, in conjunction with DB2's interface for retrieving miss data, for tuning DB2 buffer allocation to achieve targets for differentiated service.
Funder
National University of Singapore
Publisher
Association for Computing Machinery (ACM)
Subject
Hardware and Architecture
Reference36 articles.
1. Autoadmin. 2006. AutoAdmin: Self-Tuning and self-administering datdbases. http://research.microsoft.com/dmx/autoadmin. Autoadmin. 2006. AutoAdmin: Self-Tuning and self-administering datdbases. http://research.microsoft.com/dmx/autoadmin.
2. Autonomic Computing. 2006. Autonomic computing homepage. http://www.research.ibm.com/autonomic. Autonomic Computing. 2006. Autonomic computing homepage. http://www.research.ibm.com/autonomic.
3. A study of replacement algorithms for a virtual-storage computer
4. Automatic Diagnosis of Performance Problems in Database Management Systems
Cited by
22 articles.
订阅此论文施引文献
订阅此论文施引文献,注册后可以免费订阅5篇论文的施引文献,订阅后可以查看论文全部施引文献
1. K2vTune: A workload-aware configuration tuning for RocksDB;Information Processing & Management;2024-01
2. Asgard: Are NoSQL databases suitable for ephemeral data in serverless workloads?;Frontiers in High Performance Computing;2023-09-04
3. A Survey of Database Knob Tuning with Machine Learning;2023 IEEE 2nd International Conference on Electrical Engineering, Big Data and Algorithms (EEBDA);2023-02-24
4. Parameters tuning of multi-model database based on deep reinforcement learning;Journal of Intelligent Information Systems;2022-11-30
5. HUNTER: An Online Cloud Database Hybrid Tuning System for Personalized Requirements;Proceedings of the 2022 International Conference on Management of Data;2022-06-10