{"id":14391,"date":"2024-01-08T22:17:01","date_gmt":"2024-01-09T05:17:01","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=14391"},"modified":"2024-01-09T07:26:12","modified_gmt":"2024-01-09T14:26:12","slug":"innodb_buffer_pool_instances","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/innodb_buffer_pool_instances\/","title":{"rendered":"innodb_buffer_pool_instances: Configuring Multiple Buffer Pool Instances for Improved Concurrency in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the innodb_buffer_pool_instances<\/code> configuration option to configure multiple buffer pool instances for improved concurrency.<\/p>\n\n\n\n

Buffer pool & concurrency<\/h2>\n\n\n\n

In InnoDB<\/a>, a buffer pool is a memory structure that stores the frequently accessed data and indexes.<\/p>\n\n\n\n

InnoDB utilizes the buffer pool to improve the performance of the MySQL database server by accessing data in memory directly instead of reading data from disk.<\/p>\n\n\n\n

Typically, the more memory you allocate to the buffer pool, the better performance of the MySQL server is.<\/p>\n\n\n\n

To set an optimal size of the buffer pool, you adjust the innodb_buffer_pool_size<\/a> configuration variable.<\/p>\n\n\n\n

When the buffer pool is large (multi-gigabyte range), MySQL serves many data requests by retrieving them from memory. In this case, you might encounter a bottleneck from multiple threads attempting to access the buffer pool at the same time.<\/p>\n\n\n\n

To minimize the contention, you can enable multiple buffer pools. The innodb_buffer_pool_instances<\/code> configuration variable allows you to specify the number of buffer pool instances.<\/p>\n\n\n\n

By default, the innodb_buffer_pool_instances<\/code> is set to 1. To enable multiple buffer pool instances, you set the innodb_buffer_pool_instances<\/code> to a value greater than 1. You can set it up to 64.<\/p>\n\n\n\n

Note that the innodb_buffer_pool_instances<\/code> takes effect only when you set innodb_buffer_pool_size<\/code> to a size of 1GB or more.<\/p>\n\n\n\n

Configuring buffer pool instances<\/h2>\n\n\n\n

First, open the MySQL configuration file<\/a> (typically my.ini<\/code> or my.cnf<\/code> file) in a text editor and set the innodb_buffer_pool_instances<\/code> to a number, such as 2:<\/p>\n\n\n

[mysqld]\ninnodb_buffer_pool_instances=2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because mysqld reads innodb_buffer_pool_instances<\/code>, you need to place the innodb_buffer_pool_instances<\/code> option under the [mysqld]<\/code> section. <\/p>\n\n\n\n
Next, verify if the innodb_buffer_pool_size<\/code> configuration variable is set to a value that is greater than 1GB:<\/p>\n\n\n
[mysqld]\ninnodb_buffer_pool_size=2G<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, save the configuration file and restart MySQL.<\/p>\n\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, verify the innodb_buffer_pool_instances<\/code> configuration option:<\/p>\n\n\n
SHOW<\/span> VARIABLES<\/span> LIKE<\/span> 'innodb_buffer_pool_instances'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------------------+-------+<\/span>\n| Variable_name                | Value |\n+------------------------------+-------+<\/span>\n| innodb_buffer_pool_instances | 2     |\n+------------------------------+-------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Enable multiple buffer pool instances to significantly enhance the concurrency and overall performance of MySQL servers with large buffer pools.<\/li>\n\n\n\n
  • Use the innodb_buffer_pool_instances<\/code> configuration variable to set multiple buffer pool instances for improved concurrency.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
After that, connect to the MySQL server<\/a>:<\/p>\n\n\n