Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from serverfault.com Oct 23 '12 at 15:06

This question came from our site for system and network administrators.

2 Answers 2

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.

share|improve this answer
    
So, are you suggesting that having a small margin over the overal database size is the way to go? Thanks –  Shirko Oct 23 '12 at 0:30
    
If you want a bigger margin to adjust for growth, just set growth to whatever value needed. –  RolandoMySQLDBA Oct 23 '12 at 2:48

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.