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 :
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 = 24Mmax_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=utf8delayed_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
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.