Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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
show 5 more comments

3 Answers

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.