Location>code7788 >text

MySQL server-side innodb_buffer_pool_size configuration parameters

Popularity：990 ℃/2024-09-02 15:09:50

What is innodb_buffer_pool_size?

The innodb_buffer_pool isInnoDB The buffer pool, an area of memory that holds the cachedInnoDB Data for tables, indexes, and other auxiliary buffers. innodb_buffer_pool_size is the size of this buffer pool, which defaults to 128M (i.e., 134217728 bytes).

What is innodb_buffer_pool_size used for?

If you do not set innodb_buffer_pool_size, the efficiency of sql execution in the production environment will be greatly reduced, the reason is that the space of the cache becomes smaller, the amount of data that can be cached is limited, the hit rate of the cache is greatly reduced, which will lead to repeatedly go to disk to read the data, and it is well known that the speed of the disk is much lower than the efficiency of the memory execution.

What are the requirements for configuring the value of innodb_buffer_pool_size?

The value of innodb_buffer_pool_size must be (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances) are integer multiples, i.e., they can be equal or greater.

-- Display the size of the currently used innodb buffer pool and the number of buffer pool instances vs. the size of the backbone
SELECT @@innodb_buffer_pool_size as pool_size,
@@innodb_buffer_pool_instances as pool_instances, @@innodb_buffer_pool_instances as
@@innodb_buffer_pool_chunk_size as chunk_size;

The way to set innodb_buffer_pool_size

The difference between the two setups lies in the running state of MySQL. Static setups require a restart of MySQL, and dynamic setups are dynamically adjusted during the running period of MySQL.

Static settings：

  • Modify, in the[mysqld]Scope additionsinnodb_buffer_pool_size = calculated value 。
  • Restart mysql.

dynamic setting：

  • fulfillmentSET GLOBAL innodb_buffer_pool_size=calculated value;. Sets the buffer pool size.
  • fulfillmentSHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'; The query buffer pool size changes state and appearsCompleted resizing buffer pool at timestamp That's done.
  • fulfillmentSELECT @@innodb_buffer_pool_size; Queries the current buffer pool size.
<Previous Next>