Survey on performance optimization for database systems

Shiyue Huang, Yanzhao Qin, Xinyi Zhang, Yaofeng Tu, Zhongliang Li, Bin Cui

Abstract

The performance optimization of database systems has been widely studied for years. From the perspective of the operation and maintenance personnel, it mainly includes three topics: prediction, diagnosis, and tuning. The prediction of future performance can guide the adjustment of configurations and resources. The diagnosis of anomalies can determine the root cause of performance regression. Tuning operations improve performance by adjusting influencing factors, e.g., knobs, indexes, views, resources, and structured query language (SQL) design. In this review, we focus on the performance optimization of database systems and review notable research work on the topics of prediction, diagnosis, and tuning. For prediction, we summarize the techniques, strengths, and limitations of several proposed systems for single and concurrent queries. For diagnosis, we categorize the techniques by the input data, i.e., monitoring metrics, logs, or time metrics, and analyze their abilities. For tuning, we focus on the approaches commonly adopted by the operation and maintenance personnel, i.e., knob tuning, index selection, view materialization, elastic resource, storage management, and SQL antipattern detection. Finally, we discuss some challenges and future work.
BibTeX
@article{huang2023survey,
  author = {Shiyue Huang and Yanzhao Qin and Xinyi Zhang and Yaofeng Tu and Zhongliang Li and Bin Cui},
  title = {{Survey on performance optimization for database systems}},
  journal = {Science China Information Sciences},
  year = {2023},
  doi = {10.1007/s11432-021-3578-6},
}

Loading related content...

Figure