Home / server / Questions / 249637
In Process
Andrei
Asked: 2011-03-21 14:22:29 +0800 CST

Intermittent MySQL database connection errors on Ubuntu server

  • 772

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
performance ubuntu mysql apache-2.2

1 Answers

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

    • 1