概述
今天主要分享几个关于innodb的引数，以下考虑主要为 Innodb 引擎, key_buffer_size 不考虑。对于例项级别或执行绪级别引数设定,暂不考虑。innodb_buffer_pool_size
用于快取 索引 和 资料的内存大小, 这个当然是越多越好, 资料读写在内存中非常快, 减少了对磁盘的读写。 当资料提交或满足检查点条件后才一次性将内存资料重新整理到磁盘中。然而内存还有操作系统或数据库其他程序使用, 一般设定 buffer pool 大小为总内存的 3/4 至 4/5。 若设定不当, 内存使用可能浪费或者使用过多。
对于繁忙的服务器, buffer pool 将划分为多个例项以提高系统并发性, 减少执行绪间读写快取的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响, 当然影响较小。
innodb_buffer_pool_instances
buffer pool 被划分为多个快取例项的数量, 为固定值,不动态变更。当较多资料载入到内存时, 使用多快取例项能减少快取争用情况。
当 innodb_buffer_pool_size 大于 1GB 时, innodb_buffer_pool_instances 预设为 8。如有更多buffer pool, 平均每个instances 至少1GB。
innodb_buffer_pool_chunk_size
When increasing or decreasing innodb_buffer_pool_size, the operation is performed in chunks. Chunk size is defined by theinnodb_buffer_pool_chunk_size configuration option, which has a default of 128M.innodb_buffer_pool_chunk_size 预设 128MB (更改不需重启),增加单位为 1MB 。
innodb_buffer_pool_chunk_size 的最大值估算如下：
MAX(innodb_buffer_pool_chunk_size) = innodb_buffer_pool_size / innodb_buffer_pool_instances
innodb_buffer_pool_size is set to 8G, and innodb_buffer_pool_instances is set to 16.innodb_buffer_pool_chunk_size is 128M, which is the default value.
8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 *innodb_buffer_pool_chunk_size=128M, which is 2G.
shell> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
@@innodb_buffer_pool_size/1024/1024/1024
+------------------------------------------+
8.000000000000
+------------------------------------------+
innodb_buffer_pool_size is set to 9G, and innodb_buffer_pool_instances is set to 16.innodb_buffer_pool_chunk_size is 128M, which is the default value. In this case, 9G is not a multiple ofinnodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, so innodb_buffer_pool_size is adjusted to 10G, which is a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
shell> mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
@@innodb_buffer_pool_size/1024/1024/1024
+------------------------------------------+
10.000000000000
+------------------------------------------+
综合以上三个引数：
buffer pool 估算公式：(N 为正整数; buffer pool 应为总内存的 3/4 至 4/5)
innodb_buffer_pool_size = N * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)
当 N=1时, 使三个引数设定刚好满足以下公式：
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
若此时再设定 innodb_buffer_pool_chunk_size 少 1MB , 那么 buffer pool 大小几乎翻倍, 因为 chunk_size * instances
innodb_buffer_pool_size = 2 * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)
#所以较优设定：假设系统内存 = 128 GB, buffer pool 大小预计100GB(128GB*80%)
innodb_buffer_pool_instances = 8 #预设值,或者逻辑CPU数量
innodb_buffer_pool_chunk_size = 128MB #预设值
innodb_buffer_pool_size = 100 GB # N*8*128MG = N GB ,N 刚好为正整数。设 N=100使得 buffer pool 为总内存的 3/4 至 4/5。
innodb_page_size
innodb_page_size 预设 16kb, 资料储存页, 应与操作系统块大小一致(同 innodb_log_write_ahead_size)。 对于 SSD 更小的页可能更好。innodb_page_size 为32k and 64k 时, 行长度最大为 16000 bytes, 且不支援 ROW_FORMAT=COMPRESSED。一个 innodb_buffer_pool_chunk_size 中包含的页数量取决于 innodb_page_size。
预设地： chunk可储存的页数量= innodb_buffer_pool_chunk_size / innodb_page_size = 128*1024/16 = 8192
检视：
mysql> SELECT @@innodb_buffer_pool_size;
mysql> SELECT @@innodb_buffer_pool_instances;
mysql> SELECT @@innodb_buffer_pool_chunk_size;
后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下！
