content_image/708766.jpg
innodb_buffer_pool_instances  to a value greater than 1 can improve the scalability on a busy server. 

A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine’s physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary. 

•Competition for physical memory can cause paging in the operating system. 

• InnoDB  reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size. 

•Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses. 

•The time to initialize the buffer pool is roughly proportional to its size. On instances with large buffer pools, initialization time might be significant. To reduce the initialization period, you can save the buffer pool state at server shutdown and restore it at server startup. See Section 15.8.3.6, “Saving and Restoring the Buffer Pool State” . 

When you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size  variable, which has a default of 128 MB. 

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size  * innodb_buffer_pool_instances . If you alter the buffer pool size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size  * innodb_buffer_pool_instances , 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 . 

innodb_buffer_pool_size  can be set dynamically, which allows you to resize the buffer pool without restarting the server. The Innodb_buffer_pool_resize_status  status variable reports the status of online buffer pool resizing operations. See Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”  for more information. 

If innodb_dedicated_server  is enabled, the innodb_buffer_pool_size  value is automatically configured if it is not explicitly defined. For more information, see Section 15.8.12, “Enabling Automatic Configuration for a Dedicated MySQL Server” . 

• innodb_change_buffer_max_size 

<img src='content_image/708766.jpg'>

