MySQL缓冲区自适应管理仿真研究  

Simulation research on adaptive buffer pool management in MySQL

作　　者：王楠[1,2] 吴云 Wang Nan;Wu Yun(a.State Key Laboratory of Public Big Data,Guizhou University,Guiyang 550025,China;College of Computer Science&Technology,Guizhou University,Guiyang 550025,China)

机构地区：[1]贵州大学公共大数据国家重点实验室,贵阳550025 [2]贵州大学计算机科学与技术学院,贵阳550025

出　　处：《计算机应用研究》2023年第4期1154-1159,共6页Application Research of Computers

基　　金：贵州省科学技术厅资助项目(黔科合基础-ZK[2022]一般119)。

摘　　要：由于MySQL使用配置参数的方式调节线性预读的阈值以及冷热LRU算法的冷热比例,导致缓冲区存在性能瓶颈。针对以上问题,提出一种缓冲区自适应管理的方法,该方法通过遗憾最小化的强化在线学习技术设计了自适应阈值调整算法以及自适应冷热缓存替换算法。首先,对MySQL中的预读算法以及冷热缓存替换算法进行深入研究,明确了预读阈值以及冷热比例大小对两种算法的具体影响;其次,通过FIFO历史队列以及增加辅助字段的方式,设计了一套参数评估流程,实时评估当前参数是偏大或偏小;最后,设计了一种参数调整模型,该模型利用MySQL原生的预读算法以及缓存替换算法的性能监控指标,实现对参数的合理调整。在FIU数据集上进行了900组仿真实验,实验表明,相较于MySQL原生的基准预读算法以及冷热缓存算法,自适应后的两种算法能够在基本不牺牲算法运行速度的基础上,有效减少8%的磁盘I/O以及增加24%的缓存命中率;相对于最新的缓存替换算法,自适应后的冷热缓存替换算法在保证缓存命中率的前提下,将速度提升至1.6倍。MySQL uses configuration parameters to adjust the linear preread threshold and the cold/hot ratio of the cold/hot LRU algorithm,resulting in a performance bottleneck in the buffer.Aiming at the above problems,this paper proposed an adaptive buffer management method,the method designed an adaptive threshold adjustment algorithm and an adaptive hot and cold cache replacement algorithm through the reinforcement online learning technology of regret minimization.First of all,this paper made an in-depth study of the preread algorithm and the hot and cold cache replacement algorithm in MySQL,and clarified the specific impact of the preread threshold and the hot and cold ratio on the two algorithms.Secondly,this paper designed a set of parameter evaluation process by FIFO history queue and added auxiliary fields to evaluate whether the current parameter was large or small in real time.Finally,this paper designed a parameter adjustment model,which used the performance monitoring indicators of MySQL’s native preread algorithm and cache replacement algorithm to achieve reasonable parameter adjustment.This paper conducted 900 simulation experiments on FIU datasets,the experimental results show that compared with MySQL’s native benchmark preread algorithm and hot and cold cache algorithm,the two adaptive algorithms can effectively reduce disk I/O by 8%and increase cache hit ratio by 24%without sacrificing algorithm speed.Compared with the latest cache replacement algorithm,the adaptive hot and cold cache replacement algorithm improves the speed to 1.6 times while ensuring the cache hit ratio.

关 键 词：自适应 缓冲区 遗憾最小化 预读算法 缓存替换算法 

分 类 号：TP391.9[自动化与计算机技术—计算机应用技术]