View Full Version : Any MySQL experts in the house?
For the past few weeks I've been having random outages on my photoblog. Visitors get the "Database connection error" message instead of my site. Annoyingly due to the way Wordpress works I can't even access the admin page when this happens. I've been speaking with my host and they say its because some script isn't closing the connection. They say they allow 10 MySQL connections at the same time. My site isn't *that* popular, surely. Larger forums work well enough. This forum does. My site gets 2,000 unique visitors a day. Thats a lot for me, but surely not a lot for MySQL or a large Wordpress site. I'm currently debating whether to spend £200 and jump ship, but £200 at the moment is a lot just to fix a number. Is 10 a small limit? I've been told their a reseller posing as a proper company.
Have a look in the configuration for anything about persistant connections (pconnect). Turn them off.
On shared hosting, persistant MySQL connections are the evil spawn of satan. :(
mysql.allow_persistent is turned off. So far my host has told me a few things including deleting and recreating the user. I've just got this feeling that they don't know jack.
10 connections isn't *that* much for a busy site, but it is a lot for an 'average' non-professional site, so it's all down to how busy your site really is.
The only other thing, I guess, could be that the website code could be written badly and it keeps mysql connections open longer than it really needs to?
Have you got MySQL doing any caching?
http://www.databasejournal.com/features/mysql/article.php/3110171
10 connections isn't *that* much for a busy site, but it is a lot for an 'average' non-professional site, so it's all down to how busy your site really is.
Nearly 60,000 visitors a month and 250,000 pageviews a month. Maybe it is a big site, maybe not. While I've been a web designer, I've never worked on technically large sites so I don't know what's "large". I know I never used to have this issue till my site became "popular".
The only other thing, I guess, could be that the website code could be written badly and it keeps mysql connections open longer than it really needs to?
Have you got MySQL doing any caching?
http://www.databasejournal.com/features/mysql/article.php/3110171
I always thought Wordpress was nicely written. Its probably the most popular blog package after Moveable Type. I don't have any access to what MySQL does, shared hosting.
Right. I've looked into this a lot more now. Here's the deal. Wordpress doesn't forceably close MySQL connections because non-persistant connections are automatically closed at the end of a scripts execution. So I told my host that and they said;
Yes, but it’s not the MySQL process causig the problem, but the webserver process. So, since the connections don’t get closed properly and a connection stalls, then it remains active until the webserver process times out, which is longer than the MySQL timeout resulting in your experienced issues.
But the PHP max_execution_timeout is set to 30 seconds. So surely PHP should die after 30 and MySQL should after 60. I've seen some processes in the database sitting there with like 700 seconds on them just called "sleep". Any ideas why this is happening? Is it really a script issue or is my host just a bit iffy?
Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running.
:)
So they could be telling the truth, hmm. So why would I get so many sleeping connections when persistent connections are off, and php will automatically close the connections at the end of the scripts execution. It happens randomly too.
The only thing you can do is to get access to the raw website logs and see what's going on at the time of the error. You might have access from whatever control panel you've been given.
Right. I've looked into this a lot more now. Here's the deal. Wordpress doesn't forceably close MySQL connections because non-persistant connections are automatically closed at the end of a scripts execution.
In other words, lazy programming. All it takes is a single command to close the connection, and they could have used it.
Aye. It's one thing I always do. In fact, I have code that detects connections left open, and will complain (and close them).
In other words, lazy programming. All it takes is a single command to close the connection, and they could have used it.
Maybe, but the documentation does say;
Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. See also freeing resources.
The comments on the page (http://php.net/manual/en/function.mysql-close.php) also say;
RonS is mostly incorrect about mysql_close. It's not necessarily good programming practice to always explicitly close your database connect as it might be in other languages. Your connection will be closed as soon as your script is finished, so there is almost never a need to call mysql_close at the end of a script.
Not defending them, just trying to get a better understanding :)
While the diagnosis seems possible, I would suggest not putting the blame on bad coding before determining whether or not there is some other cause. Only the logs will tell you that. :)
Annoyingly I only have access to the raw access logs which only show http connections. My host said they don't have MySQL logs or anything. I'm trying to setup a local copy so I can do some stress testing.
It's HTTP you need. MySQL doesn't keep human-readable logs anyway.
Oh right. It just looks normal, afaik. Its a 287mb file for access since the 28th Feb. I know I had trouble on the 2nd March in the evening but I'm guessing all I'd find would be connections to my HDR guide and perhaps the Wordpress error page being returned (if it shows that much). The error page basically says it can't connect to the db.
That's quite a lot of traffic for 7 days. I don't know the percentage of wordpress vs HDR, but it's possible you've outgrown your service and not realised it.
If you know when the error happened, you can see how many connections were attempted in the few minutes before the error. There are some useful tools for trawling through logs, but most of them live on *nix.
I'm pretty sure I've outgrown them. The package I'm on was 20gb/month and 1.5gb space. Now don't tell them but in Feb I did 49.9gb and so far this month I've done 37gb *ahem*. If it wasn't for the way that there are sleeping connections left lying around for 10 minutes I'd just accept that my site is getting too much traffic for 10 connections to the server. I've installed a caching script that seems to have helped. But yeah, I do think its new host time.
vBulletin® v3.7.4, Copyright ©2000-2025, Jelsoft Enterprises Ltd.