MySQL中innodb_buffer_pool_size的配置

---

关注我，获取更多分享。

---

innodb_buffer_pool_size参数

对于参数innodb_buffer_pool_size的参数到底需要设置为多少比较合适？我们这里展开讨论一下。

概念理解

它的是一个内存区域，用来缓存InnoDB存储引擎的表中的数据和索引数据。以便提高对InnoDB存储引擎表中数据的查询访问速度。

在查看innodb_buffer_pool_size这个参数值的时候，是以字节(byte)为单位进行展现的。它的默认值为134217728字节，也就是128MB(134217728/1204/1024=128MB，1MB=1024KB,1KB=1024Byte)。

在MySQL 5.7.5版本后，可以在不重启MySQL数据库服务的情况下，动态修改这个参数的值，但是在此版本之前的老版本修改后需要重启数据库服务才可以生效。

---

相关连的参数

innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size默认值为134217728字节，即128MB。它可以按照1MB的单位进行增加或减小。

可以简单的把它理解成是innodb_buffer_pool_size增加或缩小最小单位。innodb_buffer_pool_size是有一个或多个innodb_buffer_pool_chunk_size组成的。

如果修改了innodb_buffer_pool_chunk_size的值将会导致innodb_buffer_pool_size的值改变。在修改该参数的时候，需要计算好最后的innodb_buffer_pool_size是否符合服务器的硬件配置。

参数innodb_buffer_pool_chunk_size的修改，需要重启MySQL数据库服务，不能再数据库服务运行的过程中修改。如果修改MySQL的配置文件my.cnf之后，需要重启MySQL服务。

---

innodb_buffer_pool_instances

innodb_buffer_pool_instances的默认值为1，最大可以设置为64。

当innodb_buffer_pool_instances不为1的时候，表示需要启用多个缓冲池实例，即把整个innodb_buffer_pool_size在逻辑上划分为多个缓存池，多实例可以提高并发性，可以减少不同线程读写缓存页面时的争用。

参数innodb_buffer_pool_instances的修改，需要重启MySQL数据库服务，不能再数据库服务运行的过程中修改。

---

关联参数之间的关系

innodb_buffer_pool_size这个参数的设置，离不开另外两个参数的影响，它们分别是：innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances。

它们三者存在这样的数学公式如下：

innodb_buffer_pool_size = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * x

注意：

• 上述公式中的x的值要满足x>=1。
• 如果设置的innodb_buffer_pool_size的值不是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances两者的乘积或两者乘积的整数倍(即x倍)，那么innodb_buffer_pool_size会自动调整为另外一个值，使得这个值是上述两者的乘积或者乘积的整数倍，所以当我们设置innodb_buffer_pool_size的值之后，最后的结果并不一定会是我们所设置的结果。

三个参数之间的关系如下：

从上图可以看出三个参数之间大概的关系

• chunk的数目最多到1000。

• instance的数目最多到64。

• 一个instance里面可以有一个或多个chunk，到底有几个chunk，取决于chunk的大小是多少。在buffer pool大小一定的情况下，instance数目一定的情况下，每一个instance下面放多少给chunk，取决于每一个chunk的大小。
  例如：buffer pool大小设置为4GB，instance的值设置为8，那么chunk的值可以设置为默认的128MB，也可以设置为512MB，也可以设置为256MB。
  因为：

  • 4GB=8*128MB*4，此时chunk的值为128MB,每个instance里有4个chunk
  • 4GB=8*512MB*1，此时chunk的值为512MB,每个instance里有1个chunk
  • 4GB=8*256MB*2，此时chunk的值为256MB,每个instance里有2个chunk

  其中的4、1、2就是每一个instance中chunk的数目。

---

设置innodb_buffer_pool_size时的注意事项

除了上面的数学公式之外，还需要满足以下几个约束：

1. innodb_buffer_pool_instances的取值范围为[1,64]。
2. 为了避免潜在的性能问题，chunk的数目不要超过1000这个阈值。也就是说：innodb_buffer_pool_size / innodb_buffer_pool_chunk_size的值需要在[1,1000)之间。
3. 当innodb_buffer_pool_instances的值大于1的时候，也就把说当把总的innodb_buffer_pool_size的大小按照innodb_buffer_pool_instances的数量划分为多个逻辑上的buffer_pool的时候，每个逻辑上的buffer_pool的大小要大于1GB。换算成公式就是说：innodb_buffer_pool_size / innodb_buffer_pool_instances > 1GB
4. innodb_buffer_pool_size的取值范围建议设置为物理服务器内存的50%_{75%，要留一些内存供服务器操作系统本身使用。如果在数据库服务器上面除了部署MySQL数据库服务，还有部署其他应用和服务，则可以适当再减少对应的百分比。原则上是去除其他应用服务，剩余的可用内存的50%}70%作为innodb_buffer_pool_size的配置值。

占50%~75%的内存是否真合适

这里，我们再仔细想一想，前面我们提到的innodb_buffer_pool_size的值配置成服务器可用内存的75%是否对所有的服务都适用？

比如你有一个256GB内存的服务器来作为MySQL数据库服务器，并且这个服务器上面没有其他的应用，只是用来部署MySQL数据库服务。那么此时我们把innodb_buffer_pool_size的大小设置为192GB(256GB * 0.75 = 192GB)，那么此时剩余的内存为64GB(256GB - 192GB = 64GB)。

此时我们的服务器操作系统在运行的时候，是需要使用64GB的内存吗？显然是不需要那么多内存给服务器操作系统使用的。8GB的内存够操作系统使用了吧！

那还有56GB(64GB - 8GB = 56GB)的内存就是浪费掉了。所以这个75%的比例是不太合适的。应该是留足操作系统使用的内存，其余的就都给innodb_buffer_pool_size来使用就可以了。

我们不管它目前是不是需要那么多内存，既然我们已经觉得把一个256GB的服务器单独给MySQL来使用了，那么它现在用不了那么多的innodb_buffer_pool_size，不代表1年以后仍然用不了那么多的innodb_buffer_pool_size。所以我们在配置部署的时候，就把分配好的资源都配置好，即便现在不用，以后用到也不用再次排查调优配置了。

---

所以我们应该把服务器划分为不同的层级，然后再不同的层级采用不同的分配方式。

• 对于内存较小的系统(<= 1GB)
  对于内存少于1GB的系统，InnoDB缓冲池大小最好采用MySQL默认配置值128MB。

• 对于中等RAM (1GB - 32GB)的系统
  考虑到系统的RAM大小为1GB - 32GB的情况，我们可以使用这个粗糙的启发式计算OS需要:256MB + 256 * log2(RAM大小GB)
  这里的合理之处是，对于低RAM配置，我们从操作系统需要的256MB的基础值开始，并随着RAM数量的增加以对数的方式增加分配。这样，我们就可以得出一个确定性的公式来为我们的操作系统需求分配RAM。我们还将为MySQL的其他需求分配相同数量的内存。
  例如，在一个有3GB内存的系统中，我们会合理分配660MB用于操作系统需求，另外660MB用于MySQL其他需求，这样InnoDB的缓冲池大小约为1.6GB。

• 对于拥有较大RAM (> 32GB)的系统
  对于RAM大于32GB的系统，我们将返回到计算系统RAM大小的5%的OS需求，以及计算MySQL其他需求的相同数量。因此，对于一个RAM大小为192GB的系统，我们的方法将InnoDB缓冲池大小设为165GB左右，这也是使用的最佳值。

这里有一个简单他示例供大家参考：

判断现在的innodb_buffer_pool_size是否合适

这个参数的默认值为128MB，我们该怎么判断一个数据库中的这个参数配置的值是否合适？我们是应该增加它的值？还是要减少它的值呢？

判断依据

在开始判断innodb_buffer_pool_size设置的值是否合适之前，我们先来了解几个参数，因为在判断innodb_buffer_pool_size的值是否合适的时候，需要用到这几个参数的值去做比对衡量。
所以我们需要先了解这几个参数的具体含义才可以进行

• innodb_buffer_pool_reads：
  The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
  翻译过来就是：缓存池中不能满足的逻辑读的次数，这些读需要从磁盘总直接读取。
  备注：逻辑读是指从缓冲池中读，物理读是指从磁盘读。
• innodb_buffer_pool_read_requests：
  The number of logical read requests.
  翻译过来就是：从buffer pool中逻辑读请求次数。

从查询SQL命中buffer pool缓存的数据的概率上来判断当前buffer pool是否满足需求，计算从buffer pool中读取数据的百分比如下：

percent = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%

上述的percent >= 99%，则表示当前的buffer pool满足当前的需求。否则需要考虑增加innodb_buffer_pool_size的值。

---

• innodb_buffer_pool_pages_data：
  The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
  clean pages.
  翻译过来就是：innodb buffer pool中的数据页的个数，它的值包括存储数据的数据页和脏数据页。

• innodb_buffer_pool_pages_total：
  The total size of the InnoDB buffer pool, in pages.
  翻译过来就是：innodb buffer pool中的总的数据页数目

• innodb_page_size：
  InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
  easily converted to bytes.
  翻译过来就是：innodb中的单个数据页的大小。

根据上述的参数解释，我们可以大概得到如下的公式，这个公式可以用来验证我们MySQL中的innodb_buffer_pool_pages_total的实际值是否匹配现在数据库中的两个参数的比值。

innodb_buffer_pool_pages_total = innodb_buffer_pool_size / innodb_page_size

从buffer pool中缓存的数据占用的数据页的百分比来判断buffer pool的大小是否合适。以上可以得到这样一个数学公式：

percent = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

上述的percent >= 95%则表示我们的innodb_buffer_pool_size的值是可以满足当前的需求的。
否则可以考虑增加innodb_buffer_pool_size的大小。
具体设置大小的值，可以参考下面的计算出来的值。

innodb_buffer_pool_size = Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

---

微信搜索“coder-home”或扫一扫下面的二维码，
关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我

---