mysql "too many connections"

[lisa99]

Every now and then I get an email from a customer that says they're getting a mysql_error() message "too many connections".

I'm on a shared server at HostGator (one of their reseller packages) and recently found out that they only allow 25 mysql connections per cpanel user which seems really low to me.

I don't have enough permissions to change this setting and their support guys say they're not allowed to change it. They certainly don't advertise this fact when you sign up, but are otherwise a reasonably good host, I think.

Short of upgrading to a virtual private server (double the price) is there another way this can be addressed?

Does each query count as a "connection"? With the low number of users (about 30-50 a day) I don't really see why this is an issue because they aren't all on at the exact same time. If each query counts as a "connection" it would make more sense because there are some scripts that make 5 or 10 queries on a page.

I have not been using mysql_close() because I read in the PHP manual that all connections are closed at the end of the script anyway. Is this correct?

[bradgrafelman]

lisa99 wrote:

they only allow 25 mysql connections per cpanel user which seems really low to me.

I agree - seems a bit low. But then again, it's not surprising - shared hosting almost always comes with many drawbacks/limitations like that (e.g. "500 GB bandwidth free!" can easily include a gotcha if you read the fine print, e.g... "... with a daily bandwidth limit of 16 GB ...").

lisa99 wrote:

Short of upgrading to a virtual private server (double the price) is there another way this can be addressed?

Though I'm no DB guru, I don't really think so, no. If a page needs information from a DB, then you have to connect to the DB and query for the information... you could consider caching some of the content if it's not dynamic.

lisa99 wrote:

Does each query count as a "connection"?

A connection is made when you call mysql_connect() (hence the name) and left open until either a) the script terminates (at which point PHP does some cleaning up and closes any open connections) or b) you manually close the connection, e.g. with mysql_close().

lisa99 wrote:

With the low number of users (about 30-50 a day) I don't really see why this is an issue because they aren't all on at the exact same time.

Don't forget to consider spiders/bots such as the ones search engines use. Also don't forget that these bots can be set to crawl several pages simultaneously, so GoogleBot alone could take up several connections at any given point. Then there's MSN, Yahoo, Altavista, etc.

lisa99 wrote:

I have not been using mysql_close() because I read in the PHP manual that all connections are closed at the end of the script anyway. Is this correct?

Yes, assuming you aren't using persistent connections (e.g. [man]mysql_pconnect/man). If you have a lot of processing being done after you've finished getting data from your SQL DB, you could always consider using mysql_close() to close the connection as soon as you're done with it, though.

[lisa99]

No, I'm not using persistent connections.

I couldn't get enough access to the mysql server to monitor the connections like it says you can in the manual, but I did set up a little logging script right after mysql_connect() to see who or what was actually opening the connections.

It does seem that bots are most of the problem. They are not being shown in my google analytics, so I didn't take them into account. It also seems like the problem bots are coming out of Russia. I'm getting nearly one Russian-based bot a minute.

I don't want to stop bots from coming to my site because my search engine rankings are good and getting better and bots don't always follow the instructions in the robots.txt file -- so even if I disallow, that would only keep the cooperative ones out (which are the ones I probably want).

I really don't see any alternative other than to upgrade to a virtual private server or start banning the bots that are causing the problem by checking the user-agent and kicking them out to an error page before mysql_connect() from their user-agent declaration...unless there are other suggestions?

Also, what would be a normal number of simultaneous mysql connections that I should look for in my next hosting package? I'm thinking 200 - 500? At what point would mysql slow down or become unstable?