I am on shared hosting (non PS) and ever since I upgraded our little forum (1500 members & maybe a maximum of 40 active users) from phpbb2 to phpbb3 our site would go down for hours on end because our database ....
"already has more than 'max_user_connections' active connections "
For the past few months now I have tried to solve this problem with the help of support. Initially I thought I was the only user with this issue, but alas I am not. See: http://www.phpbb.com/community/viewtopic.php?f=46&t=1075665
While the above thread seems to lay its blame on the search function - my analysis of the logs leads me to a different conclusion.
Every night at 1am PST Dreamhost runs its internal mysql backup process which backs up all db's on the mysql server.
It's usually around 04:30am - 05:30am PST that it seems to get round to do our database. In the time it is trying to back up the database it locks its tables to avoid any changes being made to it mid backup. As my site is based in the UK this usually ends up slap bang in the middle of lunchbreak (12:30 - 14:00 GMT) when most users will use the site.
Today for example my error log showed that the errors "Premature end of script headers: php5.cgi" started to occur at 5:28AM PST - That would be 13:28 GMT. It is now 14:38 GMT and the site is still down.
It is possible if the forum was busy it would fill up all 300 connections waiting for this table to complete backing up. The backed up connections use more resources and slow the backup down a bit, and would show the 500 errors due to php processes hanging around waiting for mysql to return data for them.
Now the solution I got from support:
"there isn't much we can do in the way of reducing the time the table is locked. If the database needs to be available as much as possible, I would recommend our MySQL PS service"
So basically it is saying ... phpbb3 will not function 100% on shared hosting UNLESS you upgrade to Dreamhost PS, and even then it may still cause problems and downtime.
This is leaving me extremely frustrated. I can not afford PS for mysql - even at the smallest amount (it would increase my yearly bill by $180 for 150MB Ram). So all I can do is put up with the (what could possibly be) daily downtime at peak times.
Does anyone else here have any issues like this? Any imput would be greatly appreciated.