How much memory do I need for InnoDB buffer pool?

How much memory do I need for InnoDB buffer pool?

The following query gives you the recommended InnoDB buffer pool size based on all InnoDB Data and Indexes with an additional 60%:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS RIBPS_GB FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

+-------+
| RIBPS |
+-------+
|     8 |
+-------+

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

[mysqld]
innodb_buffer_pool_size=8G

After few days, run this query to see the actualy GB of memory in use in the InnoDB buffer pool:

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

You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces):

If you want to accommodate an addition 10%, plus account for 25% increase in data and indexes over time, the following query will produce exactly what you need to set innodb_buffer_pool_size in /etc/mysql/mysql.conf.d/mysqld.cnf:

SET @growth = 1.25;

SELECT
 CONCAT(
  CEILING(RIBPS / POWER(1024, pw)),
  SUBSTR(' KMGT', pw + 1, 1)
 ) Recommended_InnoDB_Buffer_Pool_Size
FROM
 (
  SELECT
   RIBPS,
   FLOOR(LOG(RIBPS) / LOG(1024)) pw
  FROM
   (
    SELECT
     SUM(data_length + index_length) * 1.1 * @growth AS RIBPS
    FROM
     information_schema. TABLES AAA
    WHERE
     ENGINE = 'InnoDB'
    GROUP BY
     ENGINE
   ) AA
 ) A;

Reference:

http://blog.ijun.org/2016/02/innodb-memory-usage-buffer-pool-status.html

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

https://dba.stackexchange.com/questions/125164/information-schema-global-variables-alternative-in-5-7-more-info-about-show-com

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/