9

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

Improve this question
2
  • 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.
    – Rick James
    Commented May 1, 2016 at 20:59
  • 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.
    – joker
    Commented Dec 21, 2017 at 12:10

2 Answers 2

Reset to default
12

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.

Improve this answer
5
  • So, are you suggesting that having a small margin over the overal database size is the way to go? Thanks
    – Shirko
    Commented Oct 23, 2012 at 0:30
  • If you want a bigger margin to adjust for growth, just set growth to whatever value needed.
    – RolandoMySQLDBA
    Commented Oct 23, 2012 at 2:48
  • 1
    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
    Commented Jan 17, 2017 at 15:47
  • I think you should add GROUP BY growth
    – the_nuts
    Commented Jan 17, 2017 at 15:47
  • That SELECT may give a desired size for the buffer_pool, but it must be capped by some percentage of RAM.
    – Rick James
    Commented Oct 29, 2020 at 21:45
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.