patternsqlMinor
Disk I/O Utilization at 95%
Viewed 0 times
utilizationdiskstackoverflow
Problem
My disk I/O utilization is at 95% at my MySQL server. What are steps I could take to fix this? It is a Ubuntu VP with 16 GB Memory and 8 cores.
Here is a screen-shot of the load:
Here is a screen-shot of the load:
Solution
We had 2 problems:
1) We had one mysql query that used three joins. Turns out this function was crashing Mysql. We rewrote this query to use 4 mysql queries without joins and that solved that problem. (Bit of a hot-fix, we will probably rewrite the function so it is possible to cache it).
2) We were experiencing around 99.9 % I/O wait when we only used 10% cache Mysql only using 10% of cache . We tried to edit the mysql config (quoted at the bottom). This helped a lot but did not fix the problem. It turned out another user on the shared server was causing 99.8% I/O spikes. After contacting our server provider they moving the server to another partition and the problem was fixed.
1) We had one mysql query that used three joins. Turns out this function was crashing Mysql. We rewrote this query to use 4 mysql queries without joins and that solved that problem. (Bit of a hot-fix, we will probably rewrite the function so it is possible to cache it).
2) We were experiencing around 99.9 % I/O wait when we only used 10% cache Mysql only using 10% of cache . We tried to edit the mysql config (quoted at the bottom). This helped a lot but did not fix the problem. It turned out another user on the shared server was causing 99.8% I/O spikes. After contacting our server provider they moving the server to another partition and the problem was fixed.
table_open_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 128k
query_cache_size= 128M
query_cache_type = 1
tmp_table_size = 64M
thread_cache_size = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_file_per_table innodb_file_format = BarracudaCode Snippets
table_open_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 128k
query_cache_size= 128M
query_cache_type = 1
tmp_table_size = 64M
thread_cache_size = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_file_per_table innodb_file_format = BarracudaContext
StackExchange Database Administrators Q#121248, answer score: 3
Revisions (0)
No revisions yet.