Home / server / Questions / 320936
In Process
Sherif Buzz
Asked: 2011-10-13 10:41:51 +0800 CST

MySQL becomes unresponsive a few times per hour

  • 772

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.

mysql database-performance

3 Answers

  1. 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.

    • 3

  2. Hosting company tracked it down to an apache misconfiguration, they increased the memory limits for apache and problem is now resolved.

    • 1

  3. Install Cacti on a central management/monitoring server. Then install the mysql-cacti templates.

    I would set this up with system specific monitoring as well as all mysql monitoring. This should give you most of the data needed to diagnose this.

    There is also great documentation at the templates site.

    Refs: http://code.google.com/p/mysql-cacti-templates/

    • 0