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.

We have a small 768mb RAM VPS running Ubuntu 10.04 server, hosting a few low-traffic websites and an Etherpad instance. We've been running this setup for more than 6 months, and very recently have begun experiencing problems with the MySQL server (or, at least, noticed it).

We've been getting random MySQL connection errors on our DB-driven sites and apps, that seem to resolve naturally in around 3 to 5 minutes, during witch the server is unresponsive (barely can login with SSH). When that happens, restarting Apache seems to fix the problem, but things that don't use MySQL don't seem to be affected.

I have enough experience with servers to setup and keep running when things are OK, but I'm not a sysadmin and altough I'm fairly confortable with Linux, I don't know where to begin looking for this problem. I've seen things about iotop, we have Munin and our VPS's provider (Linode) monitoring tools installed, but I don't see how to get from the data to identifying the culprit.

How would one begin diagnosing such a problem, and what would be your suppositions as to what the root cause is ?

Thanks !

[UPDATE]

Here's the output I'm getting from vmstat 5

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  1 262136  54516   3052  54844    0    0     0     0    0    0  1  0 98  1
 0  3 262136   9404   3324  96600    0    0  8167    18 4104 2284  3  2 70 25
 0  3 262136  22492   3232  81932    0    0  3651     8 1646  998  1  1 51 48
 1  0 262136   9540   3392  93048    6    2  4130    21 2047 1089  1  1 69 30
 2  0 262136   9804   3392  90996    0    0  2517     2 5527 3171  5  5 75 15
 2  0 262136  10036   3400  91000    0    0     1    10 7136 4402  7  8 85  0
 0  0 262136  10112   3544  91328    0    0    94     3 3319 1991  3  4 92  2
 0  0 262136   9720   3560  92144    0    0   165    30  272  312  0  0 99  1
 0  0 262136  10008   3560  92164    0    0     4     1  252  305  0  0 100  0
 0  0 262136  10852   3624  92584    0    0    99     0  302  338  0  0 87 13
 0  1 262136  10728   3636  92724    0    0    28     5  245  307  0  0 97  3
 0  0 262136  10480   3652  93124    0    0    81     1  270  315  0  0 94  6
share|improve this question
1  
Can you get the output of mysql_error() or equivalent? It would be invaluable in determining the cause. –  BMDan Mar 20 '11 at 23:26
add comment

1 Answer

It's cool that you have monitoring setup already, that's 80% of the work done. In addition to that, try running "vmstat 5" in a screen session to see what's up.

Possible causes:

  • the system could be starving on CPU cycles and MySQL isn't able to process those connection attempts. How's your CPU usage like? If your server is very busy and there aren't enough CPU process, the connections from your webserver to the DB will timeout.
  • low RAM and lots of swapping. See how many swap operations you're doing. If you see lot's of non-zero swap "si" and "so", then you're relying too much on swap, which degrades performance and makes MySQL take a long time to allocate memory to handle new connections.
  • low disk performance. You can diagnose with "iostat". See the "iowait" column. That's a common problem in VPS'es, but by itself it shouldn't cause DB connections to time out. Still, it could make the excessive swapping problem explained above even worse.
  • last but not least, review your setting for maximum number of simultaneous connections (max_connections) on your mysql server. You didn't provide a detailed error message, but if you inspect your mysql driver connection (on PHP, or whatever) you'd see "Too many connections". But honestly I don't think this would be causing your whole server to freeze up, so I don't think this is the problem here.

I hope this helps.

share|improve this answer
    
Thanks for your answer! I'm looking into all your points right now, I've posted vmstat 5's output as an update to my question, I'm not sure how to interpret it. –  Andrei Mar 20 '11 at 23:08
    
At the time you ran vmstat your system was pretty quiet. The only fun stuff that happened were a few blocks of data being written into disk. But that's low volume. –  Yves Junqueira Mar 20 '11 at 23:47
1  
You should try leaving vmstat 5 running all the time, until the problem happens again. Also have a separate screen running: iostat -x 5. Then when your system hangs, copy the last 10 values of the output of those guys and look for suspicious things. But remember we can't troubleshoot everything for you, that's not the purpose of this website. –  Yves Junqueira Mar 20 '11 at 23:49
    
Thanks for the advice. I'll leave it like running the next time, for now I've shut down the Etherpad instance in hopes it was the culprit, things are running smoothly now. I'm very much eager to learn what to look for and to fix it, I'm all for the spirit of this site :). –  Andrei Mar 20 '11 at 23:55
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.