{"id":14380,"date":"2024-01-08T20:31:37","date_gmt":"2024-01-09T03:31:37","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=14380"},"modified":"2024-01-09T07:17:19","modified_gmt":"2024-01-09T14:17:19","slug":"innodb_buffer_pool_size","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/innodb_buffer_pool_size\/","title":{"rendered":"innodb_buffer_pool_size: Configure InnoDB Buffer Pool Size"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to configure the size of the buffer pool using the innodb_buffer_pool_size<\/code> configuration option to improve MySQL performance.<\/p>\n\n\n\n

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

In InnoDB<\/a>, a buffer pool is an in-memory structure that caches frequently accessed data and indexes.<\/p>\n\n\n\n

When you query data from a table<\/a>, InnoDB reads data from the disk into the buffer pool. If the data is already in the buffer pool, MySQL can quickly retrieve the data from memory instead of performing a more time-consuming disk I\/O access.<\/p>\n\n\n\n

The buffer pool can significantly improve the read performance, especially when you have high read workloads. In general, the larger the buffer pool, the better the performance of the MySQL server. <\/p>\n\n\n\n

A read workload refers to a scenario where the primary database operations are retrieving existing data, with a focus on querying rather than updating data.<\/p>\n\n\n\n

To change the size of the buffer pool, you use the innodb_buffer_pool_size<\/code> configuration variable.<\/p>\n\n\n\n

The optimal size of the buffer pool depends on the available memory and the nature of the workload. <\/p>\n\n\n\n

A general guideline is that you allocate about 80% of the available memory to the buffer pool on a server dedicated to MySQL.<\/p>\n\n\n\n

Checking innodb_buffer_pool_size<\/h2>\n\n\n\n

First, connect to MySQL<\/a> using the mysql client tool:<\/p>\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
Second, show the current value of the innodb_buffer_pool_size<\/code> option:<\/p>\n\n\n
SHOW<\/span> GLOBAL<\/span> VARIABLES<\/span> LIKE<\/span> 'innodb_buffer_pool_size'<\/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_size | 134217728 |\n+-------------------------+-----------+<\/span>\n1 row in set<\/span> (0.04<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The value of the innodb_buffer_pool_size<\/code> is in bytes. You can change it to megabytes using the following statement:<\/p>\n\n\n
SELECT<\/span> \n   ROUND<\/span>(@@innodb_buffer_pool_size \/ 1024<\/span> \/ 1024<\/span>,0<\/span>) \"innodb_buffer_pool_size (MB)\"<\/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| innodb_buffer_pool_size (MB) |\n+------------------------------+<\/span>\n|                          128 |\n+------------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The default of the innodb_buffer_pool_size<\/code> in MySQL 8.0 is 128MB.<\/p>\n\n\n\n
Changing the buffer pool size using the innodb_buffer_pool_size parameter<\/h2>\n\n\n\n
First, change the value of innodb_buffer_pool_size<\/code> in the MySQL configuration file<\/a> (my.ini<\/code> or my.cnf<\/code>) to a bigger size, for example, 1GB, and save the file:<\/p>\n\n\n
innodb_buffer_pool_size=1GB<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, connect to the MySQL server:<\/p>\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, show the value of the innodb_buffer_pool_size<\/code> variable:<\/p>\n\n\n
SHOW<\/span> GLOBAL<\/span> VARIABLES<\/span> LIKE<\/span> 'innodb_buffer_pool_size'<\/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_size | 1073741824 |\n+-------------------------+------------+<\/span>\n1 row in set<\/span> (0.02<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the value has been updated successfully.<\/p>\n\n\n\n
Configuring innodb_buffer_pool_size online<\/h2>\n\n\n\n
MySQL allows you to set the innodb_buffer_pool_size<\/code>\u00a0dynamically using the SET<\/code> statement. For example:<\/p>\n\n\n
SET GLOBAL<\/span> innodb_buffer_pool_size=1073741824<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
This allows you to resize the buffer pool without restarting the MySQL server. Note that the change will not persist after you restart the MySQL server.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the innodb_buffer_pool_size<\/code> configuration variable to adjust the buffer pool size.<\/li>\n\n\n\n
  • Allocate 80% of memory to the buffer pool on the MySQL dedicated server.<\/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
Note that if you have a buffer pool in the multi-gigabyte range, you should divide the buffer pool into separate instances to improve concurrency. To do that, you adjust the innodb_buffer_pool_instances<\/a>\u00a0configuration option.<\/p>\n\n\n\n
Second, restart the MySQL server<\/a>.<\/p>\n\n\n\n