HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Disk I/O Utilization at 95%

Submitted by: @import:stackexchange-dba··
0
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:

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.

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 = Barracuda

Code 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 = Barracuda

Context

StackExchange Database Administrators Q#121248, answer score: 3

Revisions (0)

No revisions yet.