Picking the right innodb_buffer_pool_size

Question

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?

RolandoMySQLDBA · Accepted Answer · 2012-01-27T17:49:32.213

10

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.

edited Jan 27 '12 at 17:49

answered Jan 27 '12 at 17:06

RolandoMySQLDBA

16,364
3
47
80

Before I run it, is it safe to run on a production machine? Not that MySQL will lock up because it takes long to run :) ? – Mr.Boon Jan 27 '12 at 17:09
It quoted 23GB with that query. Would you recommend upgrading my RAM to say double so I can increase the buffer size to well over 23GB to allow growth. – Mr.Boon Jan 27 '12 at 17:17
I once ran this query in a high-write production DB and it took like 2 hours. It will not interfere with MySQL Queries. However, it takes information_schema.tables a long time to wait because it has to have that free microsecond to read info about the table. – RolandoMySQLDBA Jan 27 '12 at 17:19
Run the query during off hours. – RolandoMySQLDBA Jan 27 '12 at 17:19
Oh it ran that fast. – RolandoMySQLDBA Jan 27 '12 at 17:20
If your indexes are the same size as the tables, you need to remove unused and redundant indexes. – RolandoMySQLDBA Jan 27 '12 at 17:21

score 1 · Answer 2 · answered Jan 27 '12 at 16:58

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.

score 0 · Answer 3 · answered Jul 01 '18 at 18:20

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.

Picking the right innodb_buffer_pool_size

3 Answers3