5

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?

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

1 Answer 1

8

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

SELECT
    KBS/power(1024,0) KBS_BB,
    KBS/power(1024,1) KBS_KB,
    KBS/power(1024,2) KBS_MB,
    KBS/power(1024,3) KBS_GB
FROM
(
    SELECT SUM(index_length) KBS
    FROM information_schema.tables
    WHERE engine='MyISAM' AND table_schema NOT IN
    ('information_schema','performance_schema','mysql')
) 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:

SELECT
   FLOOR((A.variable_value * B.variable_value)/power(1024,2)+0.5) KBS_USED_IN_MB
FROM
   information_schema.global_variables A,
   information_schema.global_status B
WHERE
   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;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

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'?

5
  • Thx! checking everything and will update. Regarding your question, I have ~25GB of MyISAM indexes.
    – Noam
    Aug 27, 2012 at 17:35
  • What was the KBS_USED_IN_MB from the second query I posted ??? Aug 27, 2012 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, 2012 at 6:58
  • What is the downside of having your key_buffer_size set too large?
    – jwg2s
    Oct 11, 2013 at 18:01
  • information_schema.global_status does not exist on my system, should we update with a newer answer? or I need to do something on my end ?
    – Mathieu J.
    May 16 at 6:14

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.