所以于MySQL5.7.5对于Buffer的分配需要提前计算一下。 尽量让innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 从而获取一个较佳的性能。
下面是5.7的官方文档,比较详细:
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 toinnodb_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.
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.