MySQL innodb_buffer_pool_size参数优化

一、参数简介

作用：该参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲等。

（1）数据缓存：InnoDB数据页面

（2）索引缓存：索引数据

（3）缓冲数据：脏页（在内存中修改尚未刷新(写入)到磁盘的数据）

（4）内部结构：如自适应哈希索引，行锁等。

在MySQL5.5之前，默认使用的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存， 数据读写在内存中进行， 减少了对磁盘的读写，接着当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。所以InnoDB buffer pool空间是否足够会整体影响MySQL的性能。通过实验验证，innodb_buffer_pool_size并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

【注】如上图，实践中对innodb_buffer_pool_size参数优化后，数据库对磁盘的读写明显下降，侧面也说明数据库对内存的读写大大增加，数据库的整体读写性能得到了很大的提升。

二、参数调优计算方法

2.1 参数查看

mysql> show global variables like 'innodb_buffer_pool_size';

mysql> show global status like 'Innodb_buffer_pool_pages_data';

mysql> show global status like 'Innodb_buffer_pool_pages_total';

官网参考URL：https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

2.2 参数调优计算方法

val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%

val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 /(1024*1024*1024)

设置命令：set global innodb_buffer_pool_size = 2097152;

2.3 Innodb缓存命中率计算脚本

如果MySQL实例中的表引擎大部分或者全部为innodb，可以参考innodb buffer 命中率这个指标。对于一般oltp的业务架构来说，缓存命中率一般不会低于99.9%， 但也不是绝对的，具体根据实际业务场景来看。不过用户可以采集一下命中率数据，对比一下历史趋势，如果命中率有明显下降的现象，那么一般可以考虑增大innodb buffer pool了。一般innodb buffer pool size默认都是设置为内存的70%–80%左右（经实验验证一般设置在实际内存的0.5~0.77效果最佳），所以如果要增大innodb buffer pool size，实际上就是需要增加内存大小。

Innodb_buffer_pool_read_requests表示read请求的次数，Innodb_buffer_pool_reads表示从物理磁盘中读取数据的请求次数。

缓存命中率计算方法：（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests*100%

#!/bin/bash
for((i=0;i<10000000;i++));do
echo current_time is $(date -R) >> /root/mysql_memory_used.txt #输出当前时间
mysql -uroot -hxx.xx.xx.xx -pxxxxxx -P3306 -e "show status like 'Innodb_buffer_pool_%'"|grep -E "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"|awk '{print $1,$2}'|column -t >>/root/mysql_memory_used.txt  #检索出字段并且输出到mysql_memory_used.txt文件中
A=$(mysql -uroot -hxx.xx.xx.xx -pxxxxxx -P3306 -e "show status like 'Innodb_buffer_pool_%'"|grep -E "Innodb_buffer_pool_read_requests"|awk '{print $2}')
B=$(mysql -uroot -hxx.xx.xx.xx -pxxxxxx -P3306 -e "show status like 'Innodb_buffer_pool_%'"|grep -E "Innodb_buffer_pool_reads"|awk '{print $2}')
echo "缓存命中率为" $(echo |awk "{print ($A-$B)/$A}") >>/root/mysql_memory_used.txt
sleep 60
done

作者：UStarGao
链接：https://www.starcto.com/mysql/128.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面