MySQL becomes unresponsive a few times per hour

database-performanceMySQL

I'm stuck trying to solve a MySQL issue. A few times an hour, for about 60s, MyuSQL becomes unresponsive, causing our site to be inaccessible during that period. I enabled the slow query log and found nothing amiss (all the queries during the outage are very slow, naturally, but no one query seems to be causing the issue).

I disabled all (my) cron jobs, and the issue still persists.

I ran top during the outage and MySQL is pretty much not even running – which makes me feel that it's not a load/bad query issue.

top screenshot is here :
top screenshot
Some stats on my vps :

1.8GB RAM, 2.2Ghz proc. mysql 5.1.56. My site runs blindingly fast except during these periods when it locks up. Average cpu usage is 40%.

Here is my my.cnf :

[mysqld]

safe-show-database
tmp_table_size = 24M

max_heap_table_size = 32M

query_cache_limit=1M

query_cache_size=70M

query_cache_type=1

max_connections=200

collation_server=utf8_unicode_ci
character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10

wait_timeout=400

connect_timeout=20

thread_cache_size=64

key_buffer=50M

join_buffer=1M

max_connect_errors=20

max_allowed_packet=8M

table_cache=1024

record_buffer=1M

sort_buffer_size=2M

read_buffer_size=2M

read_rnd_buffer_size=1M

thread_concurrency=2

myisam_sort_buffer_size=32M

innodb_buffer_pool_size=200M

Any advice on how to track down this issue highly appreciated.

Best Answer

If you can get a mysql console login during the outage try running

SHOW PROCESSLIST;

This should list the mysql threads that are running and what queries these are running. This should give you a little more idea what MySQL is doing that's making it not responsive.

Related Topic