1

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.

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
Sherif Buzz
  • 283
  • 1
  • 3
  • 16
  • Do you have cron jobs that correlate with the slowdown? – HTTP500 Oct 12 '11 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. – jgoldschrafe Oct 12 '11 at 18:55
  • @jasondbecker - i disabled all crons for a while, issue still there. – Sherif Buzz Oct 12 '11 at 18:58
  • @jgoldschrafe - yes it's a vz vps. i have involved my host but they have not been very helpful so far. – Sherif Buzz Oct 12 '11 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 '11 at 19:18
  • Have you logged into mysql and checked to see if any jobs are running? if there are any locks holding things back (i.e. jobs waiting on locks)? – mdpc Oct 12 '11 at 19:28
  • Have you connected to the mysql process via strace/truss to see what type of system calls there are? What about doing an lsof on the mysql process, which will tell you what files it is using, maybe some help. – mdpc Oct 12 '11 at 19:31
  • @jasondbecker have updated the question with my.cnf, vps config and mysql version. – Sherif Buzz Oct 12 '11 at 20:45
  • @Sherif, Wow, MySQL 4.1. You know that 4.1 was EOL almost 2 years ago? You didn't really say what your distribution of MYISAM to INNODB tables is but you should never leave innodb_log_file_size at the default (5M) on a production server. You probably want to increase that to at least 64M but note that you have to cleanly shutdown your server, (re)move the ib_logfile* files, make the edit in my.cnf and start the server. – HTTP500 Oct 12 '11 at 21:05
  • @jasondbecker - sorry typo, it's 5.1.56. all of my tables are innodb except for log tables. – Sherif Buzz Oct 12 '11 at 21:10

3 Answers3

3

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.

rjmackay
  • 31
  • 2
  • You might want to dump the lock wait table as well. I can't recall the precise syntax. – mdpc Oct 12 '11 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. – Sherif Buzz Oct 12 '11 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.

Sherif Buzz
  • 283
  • 1
  • 3
  • 16
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/

Sean Milheim
  • 176
  • 2