Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
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

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.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.