Documentation Home
MySQL 8.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.3 Reference Manual  /  ...  /  Configuring InnoDB Buffer Pool Size

17.8.3.1 配置 InnoDB 缓冲池大小

您可以离线或在线配置 InnoDB 缓冲池大小。该节描述的行为适用于这两种方法。有关在线配置缓冲池大小的更多信息,请参阅 在线配置 InnoDB 缓冲池大小

当增加或减少 innodb_buffer_pool_size 时,操作将以块的形式执行。块大小由 innodb_buffer_pool_chunk_size 配置选项定义,默认值为 128M。有关更多信息,请参阅 配置 InnoDB 缓冲池块大小

缓冲池大小必须始终等于或是 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 设置为 8Ginnodb_buffer_pool_instances 设置为 16innodb_buffer_pool_chunk_size128M,这是默认值。

8G 是一个有效的 innodb_buffer_pool_size 值,因为 8Ginnodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M 的倍数,即 2G

Press CTRL+C to copy
$> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16
Press CTRL+C to copy
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024; +------------------------------------------+ | @@innodb_buffer_pool_size/1024/1024/1024 | +------------------------------------------+ | 8.000000000000 | +------------------------------------------+

在这个示例中,innodb_buffer_pool_size 设置为 9Ginnodb_buffer_pool_instances 设置为 16innodb_buffer_pool_chunk_size128M,这是默认值。在这种情况下,9G 不是 innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M 的倍数,因此 innodb_buffer_pool_size 将被调整为 10G,这是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。

Press CTRL+C to copy
$> mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16
Press CTRL+C to copy
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024; +------------------------------------------+ | @@innodb_buffer_pool_size/1024/1024/1024 | +------------------------------------------+ | 10.000000000000 | +------------------------------------------+
配置 InnoDB 缓冲池块大小

innodb_buffer_pool_chunk_size 可以以 1MB(1048576 字节)单位增加或减少,但只能在启动时、命令行字符串或 MySQL 配置文件中修改。

命令行:

Press CTRL+C to copy
$> mysqld --innodb-buffer-pool-chunk-size=134217728

配置文件:

Press CTRL+C to copy
[mysqld] innodb_buffer_pool_chunk_size=134217728

在更改 innodb_buffer_pool_chunk_size 时,以下条件适用:

  • 如果新的 innodb_buffer_pool_chunk_size 值 * innodb_buffer_pool_instances 大于当前缓冲池大小时,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,如下所示:

    Press CTRL+C to copy
    $> mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4 --innodb-buffer-pool-chunk-size=1073741824;
    Press CTRL+C to copy
    mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 2147483648 | +---------------------------+ mysql> SELECT @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 4 | +--------------------------------+ # Chunk size was set to 1GB (1073741824 bytes) on startup but was # truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 536870912 | +---------------------------------+
  • 缓冲池大小必须始终等于或是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。如果您更改 innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_size 将自动调整为等于或是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。该调整将在缓冲池初始化时发生。

    Press CTRL+C to copy
    # The buffer pool has a default size of 128MB (134217728 bytes) mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ # The chunk size is also 128MB (134217728 bytes) mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 134217728 | +---------------------------------+ # There is a single buffer pool instance mysql> SELECT @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 1 | +--------------------------------+ # Chunk size is decreased by 1MB (1048576 bytes) at startup # (134217728 - 1048576 = 133169152): $> mysqld --innodb-buffer-pool-chunk-size=133169152 mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 133169152 | +---------------------------------+ # Buffer pool size increases from 134217728 to 266338304 # Buffer pool size is automatically adjusted to a value that is equal to # or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 266338304 | +---------------------------+

    以下示例演示了相同的行为,但具有多个缓冲池实例:

    Press CTRL+C to copy
    # The buffer pool has a default size of 2GB (2147483648 bytes) mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 2147483648 | +---------------------------+ # The chunk size is .5 GB (536870912 bytes) mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 536870912 | +---------------------------------+ # There are 4 buffer pool instances mysql> SELECT @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 4 | +--------------------------------+ # Chunk size is decreased by 1MB (1048576 bytes) at startup # (536870912 - 1048576 = 535822336): $> mysqld --innodb-buffer-pool-chunk-size=535822336 mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 535822336 | +---------------------------------+ # Buffer pool size increases from 2147483648 to 4286578688 # Buffer pool size is automatically adjusted to a value that is equal to # or a multiple of 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 的影响,以确保结果缓冲池大小是可接受的。

Note

为了避免潜在的性能问题,块数(innodb_buffer_pool_size / innodb_buffer_pool_chunk_size)不应超过 1000。

InnoDB 缓冲池在线调整大小

可以使用 SET 语句动态设置 innodb_buffer_pool_size 配置选项,从而允许在不重新启动服务器的情况下调整缓冲池大小。例如:

Press CTRL+C to copy
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Note

缓冲池大小必须等于或是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。更改这些变量设置需要重新启动服务器。

在调整缓冲池大小之前,需要完成活动事务和通过 InnoDB API 执行的操作。当启动调整操作时,新的事务和需要访问缓冲池的操作必须等待调整操作完成。唯一的例外是,允许并发访问缓冲池,以便在缓冲池大小减少时撤回页面。允许并发访问的缺点是可能会导致暂时性的页面短缺,而页面正在被撤回。

Note

嵌套事务可能在缓冲池调整大小操作开始后失败。

在线缓冲池调整大小进度监控

变量 Innodb_buffer_pool_resize_status 报告一个字符串值,指示缓冲池调整大小进度;例如:

Press CTRL+C to copy
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'; +----------------------------------+----------------------------------+ | Variable_name | Value | +----------------------------------+----------------------------------+ | Innodb_buffer_pool_resize_status | Resizing also other hash tables. | +----------------------------------+----------------------------------+

您也可以使用 Innodb_buffer_pool_resize_status_codeInnodb_buffer_pool_resize_status_progress 状态变量来监控在线缓冲池调整大小操作,这些变量报告数字值,适合程序化监控。

状态变量 Innodb_buffer_pool_resize_status_code 报告在线缓冲池调整大小操作的阶段状态代码。状态代码包括:

  • 0:没有调整大小操作正在进行

  • 1:开始调整大小

  • 2:禁用 AHI(自适应哈希索引)

  • 3:撤回块

  • 4:获取全局锁

  • 5:调整池大小

  • 6:调整哈希大小

  • 7:调整大小失败

状态变量 Innodb_buffer_pool_resize_status_progress 报告每个阶段的进度百分比值。百分比值在每个缓冲池实例处理后更新。当状态(由 Innodb_buffer_pool_resize_status_code 报告)从一个状态更改为另一个状态时,百分比值将重置为 0。

以下查询返回一个字符串值,指示缓冲池调整大小进度,一个代码,指示当前操作阶段,以及当前阶段的进度百分比值:

Press CTRL+C to copy
SELECT variable_name, variable_value FROM performance_schema.global_status WHERE LOWER(variable_name) LIKE "innodb_buffer_pool_resize%";

缓冲池调整大小进度也可以在服务器错误日志中查看。以下示例显示了在增加缓冲池大小时记录的日志:

Press CTRL+C to copy
[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.

以下示例显示了在减少缓冲池大小时记录的日志:

Press CTRL+C to copy
[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.

使用 --log-error-verbosity=3 启动服务器时,会在错误日志中记录在线缓冲池调整大小操作的附加信息。附加信息包括 Innodb_buffer_pool_resize_status_code 报告的状态代码和 Innodb_buffer_pool_resize_status_progress 报告的百分比进度值。

Press CTRL+C to copy
[Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 1073741824 bytes) [Note] [MY-013954] [InnoDB] Status code 1: Resizing buffer pool from 134217728 to 1073741824 (unit=134217728). [Note] [MY-013953] [InnoDB] Status code 1: 100% complete [Note] [MY-013952] [InnoDB] Status code 1: Completed [Note] [MY-013954] [InnoDB] Status code 2: Disabling adaptive hash index. [Note] [MY-011885] [InnoDB] disabled adaptive hash index. [Note] [MY-013953] [InnoDB] Status code 2: 100% complete [Note] [MY-013952] [InnoDB] Status code 2: Completed [Note] [MY-013954] [InnoDB] Status code 3: Withdrawing blocks to be shrunken. [Note] [MY-013953] [InnoDB] Status code 3: 100% complete [Note] [MY-013952] [InnoDB] Status code 3: Completed [Note] [MY-013954] [InnoDB] Status code 4: Latching whole of buffer pool. [Note] [MY-013953] [InnoDB] Status code 4: 14% complete [Note] [MY-013953] [InnoDB] Status code 4: 28% complete [Note] [MY-013953] [InnoDB] Status code 4: 42% complete [Note] [MY-013953] [InnoDB] Status code 4: 57% complete [Note] [MY-013953] [InnoDB] Status code 4: 71% complete [Note] [MY-013953] [InnoDB] Status code 4: 85% complete [Note] [MY-013953] [InnoDB] Status code 4: 100% complete [Note] [MY-013952] [InnoDB] Status code 4: Completed [Note] [MY-013954] [InnoDB] Status code 5: Starting pool resize [Note] [MY-013954] [InnoDB] Status code 5: buffer pool 0 : resizing with chunks 1 to 8. [Note] [MY-011891] [InnoDB] buffer pool 0 : 7 chunks (57339 blocks) were added. [Note] [MY-013953] [InnoDB] Status code 5: 100% complete [Note] [MY-013952] [InnoDB] Status code 5: Completed [Note] [MY-013954] [InnoDB] Status code 6: Resizing hash tables. [Note] [MY-011892] [InnoDB] buffer pool 0 : hash tables were resized. [Note] [MY-013953] [InnoDB] Status code 6: 100% complete [Note] [MY-013954] [InnoDB] Status code 6: Resizing also other hash tables. [Note] [MY-011893] [InnoDB] Resized hash tables at lock_sys, adaptive hash index, dictionary. [Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 1073741824. [Note] [MY-011895] [InnoDB] Re-enabled adaptive hash index. [Note] [MY-013952] [InnoDB] Status code 6: Completed [Note] [MY-013954] [InnoDB] Status code 0: Completed resizing buffer pool at 220826 6:25:46. [Note] [MY-013953] [InnoDB] Status code 0: 100% complete
在线缓冲池调整大小内部机制

调整大小操作由后台线程执行。当增加缓冲池大小时,调整大小操作:

  • (块大小由 innodb_buffer_pool_chunk_size 定义)添加页面

  • 将哈希表、列表和指针转换为使用新地址

  • 将新页面添加到空闲列表

在这些操作进行时,其他线程被阻止访问缓冲池。

当减少缓冲池大小时,调整大小操作:

  • 碎片整理缓冲池并撤回(释放)页面

  • (块大小由 innodb_buffer_pool_chunk_size 定义)移除页面

  • 将哈希表、列表和指针转换为使用新地址

在这些操作中,只有碎片整理缓冲池和撤回页面允许其他线程并发访问缓冲池。