配置InnoDB缓冲池（Buffer Pool）大小

原创

lhdz__bj 2024-07-22 16:09:17 博主文章分类：Mysql ©著作权

文章标签 buffer pool chunk instance size 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者lhdz__bj的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.配置InnoDB缓冲池（Buffer Pool）大小
当服务器正运行时，用户可以离线（启动时）或在线配置InnoDB缓冲池大小。这部分描述的行为适用这两种方法。
当增加或减少innodb_buffer_pool_size时，该操作按照数据块（chunks）执行。数据块的大小通过innodb_buffer_pool_chunk_size配置选项进行定义，该选项默认为128M.
缓冲池大小必须总是等于innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances或为其倍数。如果将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_size设置为8G,innodb_buffer_pool_instances设置为16。innodb_buffer_pool_chunk_size为默认的128M。8G是一个有效的innodb_buffer_pool_size值，
因为8G为innodb_buffer_pool_instances=16*innodb_buffer_pool_chunk_size=128M=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被设置为9G，innodb_buffer_pool_instances设置为16。innodb_buffer_pool_chunk_size为默认值128M。这种场景，9G不是
innodb_buffer_pool_instances=16*innodb_buffer_pool_chunk_size=128M=2G的倍数，因此，innodb_buffer_pool_size被调整为10G，其为innodb_buffer_pool_chunk_size
*innodb_buffer_pool_instances=2G不小于innodb_buffer_pool_size的下一个倍数。
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 |
+------------------------------------------+

2.配置InnoDB缓冲池数据块（Buffer Pool Chunk）的大小
innodb_buffer_pool_chunk_size能按照1M（1048576字节）单位增加和减少，但该选项只能在启动时在命令行或选项文件中进行修改。
命令行：
shell> mysqld --innodb_buffer_pool_chunk_size=134217728
配置文件：
[mysqld]
innodb_buffer_pool_chunk_size=134217728
当修改innodb_buffer_pool_chunk_size时，下列条件适用：
1）当缓冲池初始化时，如果新的innodb_buffer_pool_chunk_size*innodb_buffer_pool_instacnes大于当前的缓冲池大小，则innodb_buffer_pool_chunk_size被截断为innodb_buffer_pool_size/
innodb_buffer_pool_instances的值。
例如：如果缓冲池以2GB(2147483648字节)初始化，4个缓冲池实例，1GB(1073741824字节)的数据块大小，那么，数据块大小将被截断为等于innodb_buffer_pool_size / innodb_buffer_pool_instances=500MB的值，如下所示：
shell> mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4 --innodb_buffer_pool_chunk_size=1073741824;
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 2147483648 |
+---------------------------+
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
| 4 |
+--------------------------------+
#数据块大小启动时被设置为1GB(1073741824字节)但被截断为innodb_buffer_pool_size / innodb_buffer_pool_instances
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 536870912 |
+---------------------------------+
2）缓冲池大小必须总是等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances或为其倍数。如果调整了innodb_buffer_pool_chunk_size，innodb_buffer_pool_size将自动调整为不小于当前缓冲池大小的等于或数倍于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值。当缓冲池初始化时调整发生。下述实例将对该行为进行说明：
# 缓冲池为默认值128MB(134217728字节)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 |
+---------------------------+
# 数据块大小也是128MB (134217728字节)
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 134217728 |
+---------------------------------+
# 单个缓冲池实例
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
| 1 |
+--------------------------------+
#启动时数据块大小减少1MB (1048576字节)
# (134217728 - 1048576 = 133169152):
shell> mysqld --innodb_buffer_pool_chunk_size=133169152
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 133169152 |
+---------------------------------+
# 缓冲池大小从134217728增加至266338304
# 缓冲池自动调整为不小于当前缓冲池大小的等于或数倍于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 266338304 |
+---------------------------+
下述实例说明了多个缓冲池实例的同样行为：
# 缓冲池大小为2GB(2147483648字节)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 2147483648 |
+---------------------------+
# 数据块大小为0.5 GB (536870912字节)
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 536870912 |
+---------------------------------+
# 有4个缓冲池实例
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
| 4 |
+--------------------------------+
# 启动时数据块大小被减少1MB (1048576字节)
# (536870912 - 1048576 = 535822336):
shell> mysqld --innodb_buffer_pool_chunk_size=535822336
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 535822336 |
+---------------------------------+
# 缓冲池大小从2147483648增大至4286578688
# 缓冲池大小自动调整为不小于目前2147483648字节大小等于或数倍于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 4286578688 |
+---------------------------+
改变innodb_buffer_pool_chunk_size时应该注意，如前述实例所示，改变该选项的值能增加缓冲池的大小。改变innodb_buffer_pool_chunk_size前，计算对innodb_buffer_pool_size的影响，以确保最终缓冲池大小可以接受。
注意：
1）为了避免潜在的性能问题，数据块数（innodb_buffer_pool_size/innodb_buffer_pool_chunk_size）不应该超过1000。

3.在线调整InnoDB缓冲池大小
innodb_buffer_pool_size配置选项能通过set语句进行动态设置，既允许用户不重启服务器的前提下调整缓冲大小。例如：
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
调整缓冲池大小前InnoDB APIs执行的活动事务和操作应该被完成。当初始化调整操作时，直到所有活动事务完成才会开始改操作。一旦调整操作进行，需要访问缓冲池的新事务和操作必须等到该调整操作完成。
除非减少缓冲池时缓冲池整理碎片和回收页期间允许并发访问缓冲池。允许并发访问缓冲池的缺陷是页回收期间可能导致可用页的临时短缺。
注意：
1）缓冲池调整操作开始后嵌套事务初始化可能会失败。

4.监视在线缓冲调整进程
Innodb_buffer_pool_resize_status报告缓冲池调整进程。例如：
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+
缓冲池调整进程也会被记入服务器错误日志。下述实例显示增加缓冲池大小时注意（notes）被记入日志。
[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.
下述实例显示减少缓冲池大小时注意（notes）将被记入日志。
[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages.
(253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.

5.在线调整缓冲池内部机制
缓冲池的调整操作被后台线程执行。增加缓冲池大小时，调整操作：
1）增加数据块中的页（数据块大小由innodb_buffer_pool_chunk_size定义）
2）暗中使（covert）哈希表（hash tables）,链表（lists）和指针（pointers）以使用内存中的新地址
3）将新页加到空闲链表上
当这些操作进行时，其他访问缓冲池的线程被阻塞。
当减少缓冲池大小时，调整操作：
1）整合缓冲区并释放页
2）移去数据块中的页（数据块大小由innodb_buffer_pool_chunk_size定义）
3）使哈希表（hash tbales），链表（lists）和指针（pointers）使用内存中的新地址
这些操作中，仅整理缓冲池和释放页允许其他线程并发的访问该缓冲池。