mysql 参数调优(1)之内存缓冲池innodb_buffer_pool_size

CrazyKenCN 2021-11-27

　　内存的大小是最能直接反映数据库的性能。InnoDB存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即InnoDB Buffer Pool。因此，内存的大小直接影响了数据库的性能性能测试。

　　Percona公司的CTO Vadin对此做了一次测试，以此反映内存的重要性，结果如下图所示：

　　image.png

　　在上述测试中，数据和索引总大小为18GB，然后将缓冲池的大小分别设为2GB、4GB、6GB、8GB、10GB、12GB、14GB、16GB、18GB、20GB、22GB，再进行sysbench的测试可以发现：随着缓冲池的增大，测试结果TPS(Transaction Per Second)会线性增长。

　　当缓冲池增大到20GB和2GB时，数据库的性能有了极大的提高，因为这时缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能（再将参数调大就回去使用swap空间了）。所以，应该在开发应用前预估“活跃数据库的大小”是多少，并以此确定数据库服务器内存的大小。但是这样的预估通常是不容易达到准确的。

　　经过以上实验，可以看出innodb_buffer_pool_size是mysql参数调优中首当其冲的最重要的一个。增大它能够让mysql的性能得到很大提升！！！

　　专用的mysql服务器
网上很多说innodb_buffer_pool_size为系统的70%，这是错的！因为你真的设了70%你的swap空间（虚拟内存）会被挤压性能反而会变低，你不要忘了你还有os，上面还可能有监控agent端。一旦swap空间被挤压后你的mysql反面严重拖慢读写。

　　此处强烈建议设成内存的20%-65%间（独立的mysql服务器），为什么有一个20%呢？对于<4gb的mysql用服务器来说按照20%系统内存来设置。由于我们是128gb的内存，此处我建议使用72G(56%),如果内存超过128gb，一般我们会把pool instance设成16个，每个开启10g左右 buffer_pool_size，对于256gb内存的服务器来说我们可以这样设。

　　共享服务器
如果你的MySQL服务器与其它应用共享资源，那么上面的经验就不那么适用了。在这样的环境下，设置一个对的数字有点难度。对此我们可以使用缓存池命中率 和 总innodb表文件大小来进行判断。

　　判断当前数据库内存是否达到瓶颈，从而调整缓存池大小
如何判断当前数据库的内存是否已经达到瓶颈了呢？可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，即通过 缓存池命中率来判断，我们可以这样得到相关参数：

　　SHOW GLOBAL STATUS LIKE 'innodb%read%'

　　image.png

　　上述参数的具体含义如下所示：
1、Innodb_buffer_pool_reads 表示InnoDB缓冲池无法满足的请求，从而到物理磁盘读取页的次数
2、Innodb_buffer_pool_read_ahead 预读次数
3、Innodb_buffer_pool_read_ahead_evicted 预读的页
4、Innodb_buffer_pool_read_requests 从缓冲池中读取页的次数
5、Innodb_data_read 表示Innodb启动后，从物理磁盘上读取的字节数总和。
6、Innodb_data_reads 表示Innodb启动后，物理磁盘上发起的IO请求次数，每次读取肯能需要读多个页。

　　缓存池命中率计算方法
x = 缓冲池读次数/( 缓冲池读次数+ 预读数 + 物理磁盘读次数)
= Innodb_buffer_pool_read_requests /(Innodb_buffer_pool_read_requests +Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads )
= 0.9665 = 96%

　　可以直接使用这条sql计算缓冲池命中率

　　SELECT ( SELECT variable_value FROM PERFORMANCE_SCHEMA.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests' ) / ( SELECT SUM( variable_value ) FROM PERFORMANCE_SCHEMA.global_status WHERE variable_name IN ( 'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_read_ahead', 'Innodb_buffer_pool_reads' ) )

　　通常 InnoDB存储引擎的缓冲池的命中率不应该小于99%，所以现在可以调大innodb_buffer_pool_size参数。（我本机内存是8G，我调整到5G，6G的样子只能到98%。无法达到99%，mysql占用内存899M）。在线设置的sql：

　　SET GLOBAL innodb_buffer_pool_size = 6442450944 -- 6G

　　如果命中率太低，则应考虑扩充内存，增加innodb_buffer_pool_size的值。即使缓冲池的大小已经大于数据库文件的大小，这也并不意味着没有磁盘操作。数据库的缓冲池只是一个用来存放热点的区域，后台的线程还负责将脏页异步地写入到磁盘。此外，每次事务提交时还需要将日志写入重做日志文件。

　　物理磁盘上平均每次读取字节数= Innodb_data_read / Innodb_data_reads

　　SELECT variable_value FROM PERFORMANCE_SCHEMA.global_status WHERE variable_name = 'Innodb_data_read'

　　innodb表占用总空间
可以通过下列语句查询得到所有innodb表的数据和索引的总占用空间。可以看到我本机的innodb大小是 1.31G，那么我们至多可以将innodb_buffer_pool_size设置为1.31G了，不过大多数情况你不需要那样做，你只需要缓存你经常使用的数据集。

　　SELECT engine, count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfracFROM information_schema.TABLESWHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')GROUP BY engineORDER BY sum(data_length+index_length) DESC LIMIT 10;

　　image.png

　　调整方式

　　在线修改，5.7以后可以。但是要注意修改innodb_buffer_pool_size 可能造成业务波动。但也最好选择业务低峰期和没有大事务操作时候进行，同时要修改MySQL配置文件，防止重启后恢复到原来的值。

　　SET GLOBAL innodb_buffer_pool_size = 2147483648 #2G

　　配置文件方式，修改后重启

　　[mysqld]innodb_buffer_pool_size = 2147483648 #设置2G innodb_buffer_pool_size = 2G #设置2G innodb_buffer_pool_size = 500M #设置500M

　　innodb_buffer_pool_instances
还有一个和buffer pool 相关的参数看这里 https://www.jianshu.com/p/f84fe0979cdf

原文链接：https://www.jianshu.com/p/5bf36975fd73