I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the following values:

  • InnoDB buffer pool to 10GB
  • log file size 5M

Note that my key_buffer_size of MyISAM is 8GB and I'm also allowing Memcache to use 8GB, and have Redis installed (not sure how much he takes).

Are there any more values I should try to configure?

  • What version of MySQL are you running ??? – RolandoMySQLDBA Aug 27 '12 at 14:55
  • Rolando I'm using 5.5.15 – Noam Aug 27 '12 at 15:36

MyISAM Key Cache

You said you have key_buffer_size at 8GB.

Question: Do you really have 8GB of MyISAM indexes?

Please run this query

    KBS/power(1024,0) KBS_BB,
    KBS/power(1024,1) KBS_KB,
    KBS/power(1024,2) KBS_MB,
    KBS/power(1024,3) KBS_GB
    SELECT SUM(index_length) KBS
    FROM information_schema.tables
    WHERE engine='MyISAM' AND table_schema NOT IN
) A;

This will reveal how big your key_buffer_size would need to be IF ALL MYI PAGES WERE LOADED. To get a realistic picture run these please:

   FLOOR((A.variable_value * B.variable_value)/power(1024,2)+0.5) KBS_USED_IN_MB
   information_schema.global_variables A,
   information_schema.global_status B
   A.variable_name = 'key_cache_block_size' AND
   B.variable_name = 'Key_blocks_used'

This will give you how many MB (megabytes) the key_buffer_size is in use. You should round this up to the nearest GB.

Log File Size

5MB is the default size for innodb_log_file_size. Percona's mysqlperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out

SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;

Whatever number comes back for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?

  • Thx! checking everything and will update. Regarding your question, I have ~25GB of MyISAM indexes. – Noam Aug 27 '12 at 17:35
  • What was the KBS_USED_IN_MB from the second query I posted ??? – RolandoMySQLDBA Aug 27 '12 at 17:39
  • KBS_USED_IN_MB 6698MB. Should we consider even a bigger key_buffer_size if we have 30GB of MyISAM indexes? – Noam Aug 28 '12 at 6:58
  • What is the downside of having your key_buffer_size set too large? – jwg2s Oct 11 '13 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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