Our database(InnobDb) is spiking at 99% I/O at random times. After the spike it goes down to about 2% until it spikes agian. There are not some "super" queries that are running in these intervals. We are running a vp on digitalocean with 16 GB Ram, 8 CoreProcessor and 160GB SSD Disk. Out databse contains 1BG of data. We suspect there is something wrong with our config- We have 13 GB memory we are not using. Does anybody know whats wring? Do i need to provide more info?

Link to screenshot of spikes: http://imgur.com/na000pm

link to my.conf: http://paste.bradleygill.com/index.php?paste_id=1346993

link to mysql show status: http://paste.bradleygill.com/index.php?paste_id=1346995


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 https://dba.stackexchange.com/questions/121324/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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.