概述

innodb_buffer_pool_size是整个MySQL服务器最重要的变量。在MySQL5.5之前，广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。

今天主要介绍一下innodb_buffer_pool这个值应该如何设置比较合理。

一、MySQL InnoDB buffer pool 里包含什么？

• 数据缓存：InnoDB数据页面
• 索引缓存：索引数据
• 缓冲数据：脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
• 内部结构：如自适应哈希索引，行锁等。

二、设置innodb_buffer_pool_size大小

innodb_buffer_pool_size默认大小为128M。最大值取决于CPU的架构。在32-bit平台上，最大值为2**32 -1,在64-bit平台上最大值为2**64-1。当缓冲池大小大于1G时，将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。

1、配置缓冲池大小注意事项

• 物理内存的竞争可能会导致操作系统分页。
• InnoDB储备额外的内存缓冲区和控制结构,以便总分配空间大于指定的大小大约是10%。
• 地址空间必须是连续的，在通过DLL加载特定地址的Windows系统中，这可能存在问题。
• 初始化缓冲池的时间大致与它的大小成正比。在大型系统中，初始化的时间可能很显著。例如：在现代化的Linux x86_64服务器上，初始化一个10GB的缓冲池大小，大约需要6秒钟。

2、相关参数

 mysql> show variables like 'innodb_buffer_pool%';

说明：

1）innodb_buffer_pool_chunk_size：增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128 MB。

2）innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。

3）innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。

4）innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。

5）innodb_buffer_pool_instances：innodb_buffer_pool_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。一般innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小时有很大的不同，而使用大的 buffer_pool 时，innodb_buffer_pool_instances=1 的表现最棒。

6）innodb_buffer_pool_size：可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小。

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。

如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，

则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。

3、动态调整innodb_buffer_pool_size的值并在线监控调整缓冲池大小操作的状态

可以通过状态变量Innodb_buffer_pool_resize_status报告在线调整缓冲池大小操作的状态。

mysql> show status like 'Innodb_buffer_pool_resize%';

在以下示例中，innodb_buffer_pool_size默认为128M，innodb_buffer_pool_instances设置为1，innodb_buffer_pool_chunk_size默认值为128M，现在动态调整为6G。

3.1、当前值

 mysql> show variables like 'innodb_buffer_pool%';

3.2在线调整InnoDB缓冲池大小为6G

 mysql> set global innodb_buffer_pool_size=6442450944; //缓冲池字节大小，单位kb

3.3、监控在线缓冲池调整进度

 mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

缓冲池大小调整进度也记录在服务器错误日志中，如下：

三、如何判断配置的innodb_buffer_pool_size大小是否合适？

1、分析缓冲池性能（与命中率不谋而合）

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

2、SHOW ENGINE INNODB STATUS\G命令检查内存状态

在专用MySQL服务器上，多余的innodb_buffer内存不会有问题，但是当使用共享服务器时，可能会有性能影响。因为空闲内存对其他程序和操作系统很有用。可以使用SHOW ENGINE INNODB STATUS\G命令检查内存状态

Free buffers :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。

3、通过InnoDB buffer pool 命中率来判断

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

此值低于99%，则可以考虑增加innodb_buffer_pool_size。

4、通过InnoDB缓冲池包含数据的页数来判断

 show global status like '%innodb_buffer_pool_pages%';

说明：

innodb_buffer_pool_size调优参考计算方法：

value = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
value > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%
value < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

设置要根据自己的实际情况来设置，如果设置的值不在合理的范围内，并不是设置越大越好，可能设置的数值太大体现不出优化效果，反而造成系统的swap空间被占用，导致操作系统变慢，降低sql查询性能。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~