New features of MySQL_5.7: innodb-buffer-pool-size configuration

This parameter is mainly used to cache indexes, data, and buffer when inserting innodb tables.

Default value: 128 M

The size of the dedicated mysql server: 70%-80% of the operating system memory.

After MySQL 5.7.5, Innodb_buffer_pool_size can be dynamically allocated. However, a new feature is introduced. The bp allocation must be a multiple of innodb_buffer_pool_chunk_size. It is also best:
Innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

Innodb_buffer_pool_chunk_size: The default value is 128 MB.

When Innodb_buffer_pool_size is less than innodb_buffer_pool_chunk_size, innodb_buffer_pool_chunk_size is reduced to innodb_buffer_pool_size/innodb_buffer_pool_instances.

When innodb_buffer_pool_size is greater than innodb_buffer_pool_chunk_size, innodb_buffer_pool_chunk_size is automatically multiples of innodb_buffer_pool_chunk_size to improve performance.

Therefore, we need to calculate the Buffer allocation in advance in MySQL5.7.5. Try innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances to obtain a better performance.

Setting method:

My. cnf file

Innodb_buffer_pool_size = 6G

In addition, this parameter is non-dynamic. To modify this value, you need to restart the mysqld service.

Therefore, you should be very careful when setting.

It is not that the larger the value, the better. If the setting is too large, the system's swap space will be occupied, resulting in a slow operating system, thus reducing the SQL query efficiency.

Example

The code is as follows:

Copy code

Mysql> show global status like 'innodb _ buffer_pool_pages_data '; + ------------------------------- + ------- + | Variable_name | Value | + ------------------------------- + ------- + | Innodb_buffer_pool_pages_data | 1388 | + ------------------------------- + ------- + 1 row in set (0.00 sec) Mysql> show global status like 'innodb _ buffer_pool_pages_total '; + -------------------------------- + -------- + | Variable_name | Value | + -------------------------------- + -------- + | Innodb_buffer_pool_pages_total | 131071 | + -------------------------------- + -------- + 1 row in set (0.00 sec) Mysql> show global status like 'innodb _ page_size '; + ------------------ + ------- + | Variable_name | Value | + ------------------ + ------- + | Innodb_page_size | 16384 | + ------------------ + ------- + 1 row in set (0.00 sec) 'Innodb _ buffer_pool_pages_data 'X 100/'innodb _ buffer_pool_pages_total' When the result is greater than 95%, innodb_buffer_pool_size is increased. We recommend that you use ram total 75%. When the result is <95%, innodb_buffer_pool_size is reduced, We recommend that you 'innodb _ buffer_pool_pages_data 'X 'innodb _ page_size' X 1.05/(1024*1024*1024)