In order to properly calculate the size of the innodb_buffer_pool_size directive in MySql configuration we need to find the RIBPS.
RIBPS is the : Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60%.
So you fire up an sql session and run the following query :
SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS FROM information_schema.tables WHERE engine='InnoDB';
The output is the number we are after :
+-------+
| RIBPS |
+-------+
| 5053 |
+-------+
So in the configuration of MySql server (usually in /etc/mysql/my.cnf)
we add the following :
[mysqld]
innodb_buffer_pool_size=5053M
Given the amount of data and index pages for your dataset, having a small Buffer Pool will just cause data and index pages accessed to rotate out and load new pages as needed (like low memory and swap in Linux). Having the Buffer Pool too big would just wastes RAM so its critical to set it just right.
Tip : Use the following query after the mysql db has run for a week or so (or before restarting it, so that you dont have to wait for another week!) to see how many actual pages of InnoDB data reside in the InnoDB Buffer Pool.
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;
Have fun!
