MySQL innodb_buffer_pool_size optimal value

April 12, 2013 - 7:54am - by admin

innodb_buffer_pool_size is a quite important MySQL configuration parameter which can dramatically increase your DB productivity. The larger you set this value, the less disk I/O is needed to access data in tables. Just today I have got one of a heavy SQL query time changed from 15.5 to 1.2 seconds by changing innodb_buffer_pool_size from 23M to 320M! On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Of course, you need to be careful with memory consumption, especially for a non-dedicated server.

To get optimal value of innodb_buffer_pool_size for your DB you may run this SQL:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS

FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes

FROM information_schema.tables WHERE engine='InnoDB') A;

It would return optimal size calculated as sum of DB stored data and indexes multiple by 1.6. But it does not know anything about you physical memory! Thus set innodb_buffer_pool_size not more than 15% of RAM size. To test how much memory your new configured MySQL would maximum consume, use this mysqltuner Perl script. You can also use this script for optimizing of your MySQL DB other settings.

The last but not the least: innodb_buffer_pool_size has to be put in mysqld section of you MySQL configuration file (/etc/my.conf in RHEL):

[mysqld]

innodb_buffer_pool_size=2G

---