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数据库服务的情况下，动态修改这个参数的值，但是在此版本之前的老版本修改后需要重启数据库服务才可以生效。
相关联的参数
innodbbufferpoolchunksize
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服务。
innodbbufferpool_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
。因为：其中的4、1、2就是每一个instance中chunk的数目。
4GB=8*128MB*4
，此时chunk
的值为128MB
,每个instance
里有4
个chunk4GB=8*512MB*1
，此时chunk
的值为512MB
,每个instance
里有1
个chunk4GB=8*256MB*2
，此时chunk
的值为256MB
,每个instance
里有2
个chunk
设置innodbbufferpool_size时的注意事项
除了上面的数学公式之外，还需要满足以下几个约束：
innodb_buffer_pool_instances
的取值范围为[1,64]。为了避免潜在的性能问题，chunk的数目不要超过1000这个阈值。也就是说：
innodb_buffer_pool_size
/innodb_buffer_pool_chunk_size
的值需要在[1,1000)之间。当
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
>1GBinnodb_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左右，这也是使用的最佳值。
这里有一个简单他示例供大家参考：
判断现在的innodbbufferpool_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”或扫一扫下面的二维码， 关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我
