how to set the value of mysql innodb_buffer_pool_size

innodb_buffer_pool_size is the amount of memory to use to cache tables, indexes and a few other things. This is the main tunable and you will want to set it quite high for performance reasons

run the below query to get the recommended size for your mysql instance

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;


This will give you the recommended InnoDB Buffer Pool Size in GB based on all InnoDB Data and Indexes with an additional 60%.

With this output, you would set the following in /etc/my.cnf

[mysqld]

innodb_buffer_pool_size=8G

After the restart, run mysql for a week or two. Then, run this query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status WHERE variable_name=’Innodb_buffer_pool_pages_data’) A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name=’Innodb_page_size’) B;


This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.

enjoy 🙂

shenno