MySQL Error: Too many connections

[phppete]

I have received the error: MySQL Error: Too many connections from two sites this morning, 8:40am GMT, 3:40am server time. The user agents were MSN Bot and Yahoos spider.

As well as this at 3:10am server time today Samson was unresponsive, presumably due to mysql backups. I am in the UK and I am not prepared for this daily downtime. I start work at 7am and often need to check a private PHPBB forum that my clients post on. It is unacceptable to not be able to access my own forum on my domain that uses hardly any b/w or resources.

I had this crap with another host, an hour of extreme sluggishness every day and I dumped them and moved here. This issue with Samson has been raised before but obviously nobody here at FQ gives a **** about it. I used 186MB of b/w in June so if you want to lose a low resource account carry on the way you are going.

[phppete]

After a little more consideration on this matter, you can move codecreate.co.uk off Samson and put it on a server that doesn't crawl along at 8:00am GMT every morning.

[Terra]

Which MySQL server are you accessing?

[phppete]

My site on Samson is using MYSQL08.

The 'too many connections' error was from MYSQL10 but as before, its always when spiders are present, usually Yahoo and MSN so I doubt there is much to be done about that.

[Terra]

Pete, the biggest problem we have in doing backups is that we must lock the database and dump (mysqlhotcopy) out the tables in order to back them up, otherwise I'm sure you can understand the inconsistency resulting from multiple tables being updated in different point of time while the backup runs...

We try to lock and unlock a database as quickly as possible, but as you've seen, when there are a pile of requests for tables in that database, the backup routine has to wait its turn, then lock, and in turn the continual queries from the spiders create a huge backlog of queries waiting for the locks to be released... Once the locks are released, then a two ton heavy thing hits the MySQL server trying to clear out its backlog...

We are very much aware of the problem, and we have tried to optimize the backup procedure as much is possible with our current architecture, but as you've felt, we have reached the limits in regards to MySQL loading versus Backup time while the backups are underway...

The next generation of MySQL backups are already in development, with the new dedicated MySQL backup servers ordered and received... We are currently using a ring buddy system to run the backups, which each MySQL server backing up its left neighbor... This worked for awhile until the *number* of different spiders crawling forum sites, blogs, etc pushed this backup methodology to the edge...

The next generation of backups will use MySQL's replication abilities... This will remove the locking problem from the primary MySQL servers and shift it to the Master MySQL server... However I have run into difficulty in trying to have enough resources for MySQL to run as multiple masters... One MySQL instance can only serve as one master, forcing us to run many instances of the MySQL engine on one server... This has turned out to be one ugly mess in trying to keep everything humming to maintain low latency consistency between the Master and Slaves...

1) Lock Slave's database
2) Flush replication queue to Master disk
3) Lock Master's database
4) Ensure Master to Slave consistency (haven't quite figured out best way to successfully do this yet)
5) Release lock on Slave's database
6) Backup locked Master's database
7) Release lock on Master's database
8) Repeat #2 above, so that the next Slave's database lock time is minimized

Now architect that to run smoothly and coordinated across 15 Slaves...

Also, if you have worked with MySQL's replication abilities, you will know that it is a minefield of problems and trying to fix an out of sync Master to Slave replication system is frustrating and time consuming to do... Trying to get a solid *single* Master to Slave replication system is hard enough, but replicating 15 MySQL servers is an enormous challenge to do right... If we don't get it right, you will be back here with another thread of this type... :(

I'm still plugging away at it, but it is sadly not ready for prime time as we have to build our own service routines...

[phppete]

Also, if you have worked with MySQL's replication abilities

Fortunately no, I have it easy with just running mysql on my Mac, the most complex I get is using other machines to test sites on my network, so I suppose I have it easy, very easy.

I suppose I'll just have to live with the early morning sluggishness but its also worth noting, my brothers site for instance does get orders in the early morning. These seem to come from office workers starting their day shopping from what we can gather, we have also observed the slowness on his site which could, especially at xmas time, cause us to lose orders.

For my brothers site, a dedicated mysql server would be the obvious solution, something we might look at later down the line.

Trying to get a solid *single* Master to Slave replication system is hard enough, but replicating 15 MySQL servers is an enormous challenge to do right... If we don't get it right, you will be back here with another thread of this type...

There is obviously little point in creating another thread about it, so I won't.

I'll modify PHPBB to email the actual message posted as well as the notification, that way I can read the posts in the email if the board is too slow in the morning.

Thanks for your attention to this matter.

[hobbes]

Pete - Would it be an option to simply block the heavy spiders altogether or briefly during the morning (i.e., DB backup) hours?

[Terra]

Hobbes, it would help, but wouldn't negate the problem as there are going to be other heavily backlogged databases that may impact his during the course of the backup run... When he is feeling the problem, everyone will feel the problem on same MySQL server, because it is simply overwhelmed by the backlog... However, the database that was currently locked will feel it the worst as while his queries are locked, other client's aren't...

[phppete]

Pete - Would it be an option to simply block the heavy spiders altogether or briefly during the morning (i.e., DB backup) hours?

As Terra stated below but the point is I don't have any actual spiderable content on my site, just one holding page, all less than 10kb. My forum is password protected, so is my project manager application so spiders don't even reach my dynamic content, hence my use of resources being so low.