Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 7 down vote accepted

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

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

 
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.