How to calculate innodb_buffer_pool_size of mysql

A lot of people suggestions that innodb_buffer_pool_size should be up to %80 of the total memory.

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60%.

For Example

mysql> 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;
+——-+
| RIBPS |
+——-+
| 8 |
+——-+
1 row in set (4.31 sec)

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

[mysqld]
innodb_buffer_pool_size=8G
Next, service mysql restart

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.