How much memory do I need for innodb buffer pool?

Question

I read here that

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

On the other hand I've read elswher that innodb_buffer_pool_size must be up to %80 of the memory. So I'm really confused how should I choose the best size for the pool. My database size is about 6GB and my total memory 64GB. Also I'm wondering if I increase the buffer pool size, I should shrink the number of maximum connections to make room for extra buffer, or these parameters are independent. Thanks

• Rick James: Since the buffer_pool is the main RAM consumer, decreasing max_connections is probably not necessary. 80% (51G) is a reasonable value for 64GB of RAM. 80% is too big for, say, 4GB of RAM; even 70% is tight.
• joker: IMHO, you go with 80% rule in any of the following cases: A. You don't want to get busy with calculations of system variables, or B. You know you don't have enough memory so you are allocating as much as you can. You go with actual data size + tolerance (25-60%) if you have a server with multiple applications installed on beside the database server, so you want to allocate suitable and sufficient amount of memory to the database server.

Answer 1

If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:

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 RIBPS
 FROM information_schema.tables AAA,
 (SELECT 1 growth) BBB
 WHERE ENGINE='InnoDB'
 ) AA
) A;

This will produce exactly what you need to set innodb_buffer_pool_size in /etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBB

Recently, I answered another question like this in the DBA StackExchange.

• Shirko: So, are you suggesting that having a small margin over the overal database size is the way to go? Thanks
• RolandoMySQLDBA: If you want a bigger margin to adjust for growth, just set growth to whatever value needed.
• the_nuts: Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'BBB.growth'; this is incompatible with sql_mode=only_full_group_by
• the_nuts: I think you should add GROUP BY growth
• Rick James: That SELECT may give a desired size for the buffer_pool, but it must be capped by some percentage of RAM.

Answer 2

I don't like the sound of that advice at all. If anything, the general rule of thumb should be active data set + a little something to spare, certainly not the entire database. Keep in mind that, at least in a sense, caching is a trade-off between I/O and CPU resources.

SHOW ENGINE INNODB STATUS will give you some essential information about the buffer pool(s), including free buffers and more importantly the buffer pool hit rate. In my opinion, you generally want to keep the hit ratio close to 99%.

If you are interested in learning the fundamentals of MySQL performance, I recommend the book High Performance MySQL by Baron Schwartz, Peter Zaitsev and Vadim Tkachenko. You can read it online on Safaribooks, I do believe they still offer a free trial. The 2012 edition is very much up to date.