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.
Related videos on Youtube
spacemonkey
Updated on September 18, 2022Comments
-
spacemonkey over 1 year
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?
-
spacemonkey about 12 yearsthis 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?
-
pkhamre about 12 yearsIf you have the possibility to reproduce it on a test-environment, you can experiment with other values for the innodb_flush_method.
-
pkhamre about 12 yearsDid you check out mysqltuner.pl/mysqltuner.pl ? It will also give you a few hints on performance tuning.