Disk I/O utilization up to 100% after tuning InnoDB-related MySQL configuration

Question

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?

Answer 1

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 -> https://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.

Answer 2

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.