How to set Innodb_buffer_pool_size in MySQL

Innodb_buffer_pool_size setting method

The cached data of InnoDB tables, indexes, and other auxiliary buffers are stored in the memory area in the buffer pool. The size of the buffer pool is important to system performance. A larger buffer pool can reduce disk I/O for multiple accesses to the same table data. It is feasible for the buffer pool size to be set to 80% of the computer's physical memory size on a dedicated database server

Buffer pool related parameter description

1) System variable parameters

• Innodb_page_size

• InnoDB page size (default is 16KB). There are many values ​​contained in a page, and the page size allows them to be easily converted to bytes.

• Innodb_buffer_pool_chunk_size

• ##innodb_buffer_pool_chunk_size Defines the chunk size for InnoDB buffer pool resizing operations. The default is 128M. The maximum value can be set innodb_buffer_pool_size / innodb_buffer_pool_instances

• innodb_buffer_pool_instances

• The number of areas the InnoDB buffer pool is divided into.

• Innodb_buffer_pool_pages_data

• Number of pages of data contained in the InnoDB buffer pool. This number includes dirty pages and clean pages. When using compressed tables, the reported Innodb_buffer_pool_pages_data value may be greater than Innodb_buffer_pool_pages_total (Bug #59550).

• Innodb_buffer_pool_pages_total

• The total size of the InnoDB buffer pool in pages. When using compressed tables, the reported Innodb_buffer_pool_pages_data value may be greater than Innodb_buffer_pool_pages_total (Bug #59550)

##2) Running status variables

• Innodb_buffer_pool_pages_flushed

The number of requests to flush pages from the buffer pool.
• Innodb_buffer_pool_read_requests

Indicates the number of requests to read logic from memory.
• Innodb_buffer_pool_reads

InnoDB cannot satisfy the number of logical reads from the buffer pool and must read directly from disk .
• Innodb_buffer_pool_wait_free

Normally, writes to the InnoDB buffer pool occur in the background. When InnoDB needs to read or create a page and no clean pages are available, InnoDB first flushes some dirty pages and waits for the operation to complete. This counter counts these waiting instances. If set correctly, innodb_buffer_pool_size should be a smaller value.
Reasonably set buffer pool related parameters

1. innodb_buffer_pool_size is set to 80% of the system memory by default, and then adjusted according to the following rules to suit the actual situation

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_buffer_pool_pages_data | 1894874 |
+-------------------------------+---------+
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 | 1965960 |
+--------------------------------+---------+
1 row in set (0.00 sec)

# 计算是否应该添加内存
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size
当结果 < 95% 则减少 innodb_buffer_pool_size， 可适当较少，当然独享业务机器多了也没啥问题

2. The default setting of innodb_buffer_pool_instances is 8, minimum 1, maximum 64

For systems with buffer pools in the multi-gigabyte range, by reducing Concurrency can be improved by dividing the buffer pool into separate instances due to contention for different threads to read and write cache pages. This feature is typically used on systems with buffer pool sizes in the multi-GB range. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option. You can also adjust the innodb_buffer_pool_size value.

Many data requests can be satisfied by retrieving from memory, especially if the InnoDB buffer pool is large. You may encounter a bottleneck with multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention. Pages in the buffer pool are stored or read by randomly mapping each page to a location in the buffer pool using a hash function. Each buffer pool manages its own free list, flush list, LRU and all other data structures connected to the buffer pool, and is protected by its own buffer pool mutex.

To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (default), up to a maximum of 64 (maximum). This option only takes effect when innodb_buffer_pool_size is set to 1GB or greater. The total size you specify will be allocated to all buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.

Summary:

# cat /etc/my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系统内存%80
innodb_buffer_pool_instances = 大于8的情况下，每个缓冲池实例至少1GB
......

Reference:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters .html

• ##https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.htm

• https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

• Set the innodb_buffer_pool_size parameter

Use Regarding the memory size of the cache index and data, the more the better, of course. Data reading and writing in memory are very fast, which reduces reading and writing to the disk.

When the data is submitted or the checkpoint conditions are met, the memory data is refreshed to the disk at once.

However, the memory is also used by other processes of the operating system or database. Based on experience, it is recommended to set innodb-buffer-pool-size to 75% of the total available memory of the server. If not set properly, memory usage may be wasted or excessive.

对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。

1.Innodb_buffer_pool_pages_data： Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。

show global status like 'Innodb_buffer_pool_pages_data';

2.Innodb_buffer_pool_pages_total： innodb buffer pool的页总数目。单位是page。

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 查看@@innodb_buffer_pool_size大小，单位字节

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G

4.在线调整InnoDB缓冲池大小，如果不设置，默认为128M

set global innodb_buffer_pool_size = 4227858432; ##单位字节

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

• 当结果 < 95% 则减少 innodb_buffer_pool_size， 建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

The above is the detailed content of How to set Innodb_buffer_pool_size in MySQL. For more information, please follow other related articles on the PHP Chinese website!