Any MySQL experts in the house?

[petemc]

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.

[Mark]

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.

[petemc]

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.

[Mark]

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.

[Garp]

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/featu...le.php/3110171

[petemc]

Originally Posted by Mark

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

Originally Posted by Garp

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/featu...le.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.

[petemc]

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?

[Garp]

Originally Posted by petemc

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.

[petemc]

Originally Posted by Garp

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

[Mark]

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.