51. MySQL 缓冲区自适应管理仿真研究.
- Author
-
王 楠 and 吴 云
- Subjects
- *
REINFORCEMENT learning , *ONLINE education , *OPTICAL disks , *FIRST in, first out (Queuing theory) , *ALGORITHMS , *CACHE memory , *SPEED - Abstract
MySQL used configuration parameters to adjust the linear pre-read 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 pre-read algorithm and the hot and cold cache replacement algorithm in MySQL, and clarified the specific impact of the pre-read 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 adding auxiliary fields to evaluate whether the current parameter is large or small in real time. Finally, this paper designed a parameter adjustment model, which uses the performance monitoring indicators of MySQL’s native pre-read 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 pre-read algorithm and hot and cold cache algorithm, the two adaptive algorithms could 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 improved the speed to 1.6 times while ensuring the cache hit ratio. [ABSTRACT FROM AUTHOR]
- Published
- 2023
- Full Text
- View/download PDF