2

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.

10
  • Do you have cron jobs that correlate with the slowdown?
    – HTTP500
    Oct 12, 2011 at 18:52
  • 2
    I'm guessing from the vzavpsagent process that this is a Virtuozzo VPS, in which case you're going to have a very difficult time trying to track down performance problems unless you get your web host involved. Oct 12, 2011 at 18:55
  • @jasondbecker - i disabled all crons for a while, issue still there. Oct 12, 2011 at 18:58
  • @jgoldschrafe - yes it's a vz vps. i have involved my host but they have not been very helpful so far. Oct 12, 2011 at 18:59
  • Post some info about your VPS. Total RAM, What MySQL engine(s) are you using? Post some of the key my.cnf settings. e.g. innodb_buffer_pool_size & innodb_log_file_size
    – HTTP500
    Oct 12, 2011 at 19:18

3 Answers 3

4

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.

2
  • You might want to dump the lock wait table as well. I can't recall the precise syntax.
    – mdpc
    Oct 12, 2011 at 22:17
  • it doesn't show anything out of the ordinary, except that there are a lot of queries waiting to be executed, but none of them seems to be problematic. Oct 12, 2011 at 22:21
1

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

0

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/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .