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 added following lines to "my.conf":

query_cache_size=128M
innodb_buffer_pool_size=512M
innodb_flush_method=O_DIRECT

After that Disk utilization started raising, reached 100% and holds steady, also CPU is up a bit because of Disk I/O related tasks, but physical memory is just 50% used.

I am bit lost what is going on here?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

As a rule of thumb, the innodb_log_file_size is supposed to be 25% of innodb_buffer_pool_size. In your case, you should add that variable as follows:

[mysqld]
query_cache_size=128M
innodb_buffer_pool_size=512M
innodb_log_file_size=128M
innodb_flush_method=O_DIRECT

Click Here to Learn to Resize innodb_log_file_size -> http://dba.stackexchange.com/a/1265/877

Here is a way you can setup the correct size of innodb_buffer_pool_size based on the amount of InnoDB Data you currently have. 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 2 PowerOf1024) B; 

If recommended_innodb_buffer_pool_size comes back more than 75% of installed RAM, then use 75% of installed RAM as the recommended value. As mentioned before, set the innodb_log_file_size accordingly.

share|improve this answer

According to the innodb_flush_method documentation use of O_DIRECT can have either a positive or negative effect on your performance

Quote from the documentation:

Depending on hardware configuration, setting innodb_flush_method to O_DIRECT can either have either a positive or negative effect on performance. Benchmark your particular configuration to decide which setting to use.

share|improve this answer
    
this is a bit of topic, but I don't want to restart MySQL server right now, and site seems to work so far, how risky is it to leave server running all day with 100% disk utilization? Or should I act immediately in this case? –  spacemonkey Mar 28 '12 at 8:11
    
If you have the possibility to reproduce it on a test-environment, you can experiment with other values for the innodb_flush_method. –  pkhamre Mar 28 '12 at 8:13
    
Did you check out mysqltuner.pl/mysqltuner.pl ? It will also give you a few hints on performance tuning. –  pkhamre Mar 28 '12 at 8:13

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.