Home / server / Questions / 354299
Accepted
Mr.Boon
Asked: 2012-01-28 08:47:24 +0800 CST

Picking the right innodb_buffer_pool_size

  • 772

I have a 24GB machine running MySQL (only InnoDB tables).

My innodb data set is 11.5GB big, and i have set my innodb_buffer_pool_size to 14GB to allow some growth. So the entire innodb data set is loaded into the ram, which is good.

But today i noticed my indexes size is over 11GB as well.

Would it be a good idea to increase my innodb_buffer_pool_size so that the index space would fit as well? Or does that have nothing to do with it?

mysql tuning innodb

3 Answers

  1. Best Answer

    Don't forget that InnoDB Buffer Pool Houses Data and Indexes

    Please run this query

    SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size 
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables 
WHERE engine='InnoDB') A, 
(SELECT 3 PowerOf1024) B;

    This will tell you how big the InnoDB Buffer Pool Needs to Be. Since you only have 24GB of installed RAM, then innodb_buffer_pool_size needs to be 18G (75% of installed RAM) or the query's suggestion, whichever is smaller.

    • 10

  2. Would it be a good idea to increase my innodb_buffer_pool_size so that the index space would fit as well?

    Yes, it would.

    If your data size is 11.5GB and your indexes are over 11GB you might have too much data indexed, or improperly indexed, and may want to get someone to look at that.

    • 1

  3. If it is a dedicated system for mysql with 24 Gb RAM, you can even try going upto 20 GB of innodb_buffer_pool_size. I have observed that if the RAM size increases beyond 16 GB, we can proportionately increase innodb_buffer_pool_size beyond the thumb-rule of 75% of RAM size. I have documented some of my observations in this blog and have also provided a innodb_buffer_pool_size online calculator.

    • 0