mysql缓冲池配置及命中率计算

缓冲池原理

管理缓冲池原理及可能出现的问题，如预读失效、缓冲池污染问题等可通过缓冲池 (buffer pool)，这次彻底懂了！！！了解

缓冲池的作用

mysql数据存储在磁盘上，为了避免每次访问都进行磁盘IO，所以将一些索引数据和部分表数据加载到缓冲池中，以获得更好的访问性能

mysql默认使用InnoDB作为存储引擎，可以平衡高可靠性和高性能。
关于更多mysql的InnoDB相关内容可见InnoDB存储引擎

缓冲池配置

查看缓冲池相关配置

show variables like 'innodb_buffer_pool_%';

返回内容
需要重点关注的有以下几个

innodb_buffer_pool_size 缓冲池大小

缓冲池的大小（以字节为单位），InnoDB缓存表和索引数据的内存区域，默认值为134217728字节（128MB）。最大值取决于CPU架构；32位系统的最大值为4294967295（232-1），64位系统为184467407370951615（264-1）

配置

1. 通过命令行配置(重启后失效)mysqld --innodb-buffer-pool-size=8G
2. 通过sql进行配置(重启后失效)set global innodb_buffer_pool_size = 268435456;
3. 修改配置文件（重启后生效）

   [mysqld]
   innodb-buffer-pool-size=268435456
   

配置大小必须是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍，否则会自调整为相应的最低整数倍。
在专用数据库服务器上，可以将缓冲池大小设置为机器物理内存大小的80%。但是需要注意以下的潜在问题，在必要时减少缓冲池大小。

• 对物理内存的竞争可能导致操作系统中的分页
• InnoDB为缓冲区和控制结构保留额外的内存，因此总分配的空间大约比指定的缓冲池大小大10%
• 缓冲池的地址空间必须是连续的，这在具有以特定地址加载的DLL的Windows系统上可能是一个问题
• 初始化缓冲池的时间与其大小大致成正比。在具有大型缓冲池的实例上，初始化时间可能很长。为了缩短初始化周期，您可以在服务器关机时保存缓冲池状态，并在服务器启动时恢复它

innodb_buffer_pool_chunk_size 块大小

InnoDB缓冲池的块大小，默认为128M，可以以Mb为单位增加或减少，需要注意的是为了避免潜在的性能问题innodb_buffer_pool_size / innodb_buffer_pool_chunk_size)的大小不能大于1000.

配置

配置方式可参考innodb_buffer_pool_size

innodb_buffer_pool_instances 实例数

InnoDB缓冲池实例数，对于缓冲池大小大与1G时，多个实例可以通过减少不同线程读取和写入缓存页面的争用来改善并发性。

配置

配置方式可参考innodb_buffer_pool_size
需要注意的是

• 为了达到最佳效果需要保证每个缓冲池大小要大于1G
• 实例数不要超过CPU的核数

缓冲池命中率

前面我们了解到缓冲次可以起到加速访问数据的功能，那么理想情况下sql应该更多的是通过缓冲池进行数据处理而不是每次通过磁盘IO。

查看缓冲池相关使用情况

通过sqlshow global status like 'Innodb_buffer_pool_read%'; 可以获取如下数据

参数含义

Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数
Innodb_buffer_pool_read_ahead: 预读的次数
Innodb_buffer_pool_read_ahead_evicted: 预读的页，但是没有读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率
Innodb_buffer_pool_read_requests: 从缓冲池中读取页的次数

计算命中率

缓冲池命中率= Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads + Innodb_buffer_pool_read_ahead)

也可以直接通过以下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%。

更多详细内容见mysql官方文档-缓冲池配置