Disk I/O utilization up to 100% after tuning InnoDB-related MySQL configuration
Solution 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.
Solution 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.