MySQL5.7新特性：innodb-buffer-pool-size新特性

MySQL 5.7.5后Innodb_buffer_pool_size一方面可以动态分配。但另一方面也引入了一个新特性。 bp分配必须是innodb_buffer_pool_chunk_size的倍数。同时最好是：innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances. innodb_buffer_pool_chunk_size默认是128M.

所以于MySQL5.7.5对于Buffer的分配需要提前计算一下。 尽量让innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 从而获取一个较佳的性能。

下面是5.7的官方文档，比较详细：

 innodb_buffer_pool_chunk_size

Introduced 5.7.5 Command-Line Format --innodb_buffer_pool_chunk_size System Variable Name innodb_buffer_pool_chunk_size Variable Scope Global Dynamic Variable No Permitted Values Type integer Default 134217728 Min Value 1048576 Max Value innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_buffer_pool_chunk_size defines the chunk size for online InnoDB buffer pool resizing operations.

As of MySQL 5.7.5, the innodb_buffer_pool_size parameter is dynamic, which allows you to resize the buffer pool without restarting the server. To avoid copying all buffer pool pages during resizing operations, the operation is performed in “chunks”. Chunk size is defined byinnodb_buffer_pool_chunk_size. By default, innodb_buffer_pool_chunk_size is 128MB (134217728 bytes). The number of pages contained in a chunk depends on the value of innodb_page_size. innodb_buffer_pool_chunk_size can be increased or decreased in units of 1MB (1048576 bytes).

The following conditions apply when altering the innodb_buffer_pool_chunk_size value:

If innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances is larger than the current buffer pool size when the buffer pool is initialized, innodb_buffer_pool_chunk_size is truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances.

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alterinnodb_buffer_pool_chunk_size, innodb_buffer_pool_size is automatically adjusted to a value that is equal to or a multiple ofinnodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than the current buffer pool size. The adjustment occurs when the buffer pool is initialized.

Important

Care should be taken when changing innodb_buffer_pool_chunk_size, as changing this value can automatically increase the size of the buffer pool. Before you change innodb_buffer_pool_chunk_size, calculate the effect it will have oninnodb_buffer_pool_size to ensure that the resulting buffer pool size is acceptable.

To avoid potential performance issues, the number of chunks (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) should not exceed 1000.