Indexing database to deal with too many connections error

planetzu

Member
Hi guys, I know there are a few threads that already deal with this topic but my question is a little different.

My forum is around a month old, has around 100 odd members and a maximum of 5 to 15 members online at any given time. Member registration is around 2 to 3 members a week. I also have a wordpress blog running in the root.

Even though the site has such low traffic, I keep getting the 'Too Many Connections' error. Have recorded 4 such errors in one week. When the error occurs, the site goes down for around 5 odd minutes and then comes back up.

I have a reseller account on hostgator and mailed them about this issue asking them if there was a way to increase concurrent MYSQL connections. They replied as follows:

"I am afraid the amount of concurrent connections on a shared environment cannot be increased. Most times, the best thing to do for the database to reduce connections is to index the database so the queries run and complete faster.

This tutorial site should assist in making an index for your databases:http://use-the-index-luke.com/
"


I checked out the tutorial but it sounds a bit too complex for a beginner.

I mailed them again asking if shifting to VPS would help deal with the issue and this was their reply:

"On a VPS you would have the option of increasing the mysql connection limit, but upgrading would really only delay the inevitable need to index the database."

My question is as follows: Is indexing the best possible solution to deal with this issue? If so, how do you get started with this as a beginner? Is there a simpler guide available for Xenforo?

Also, how is indexing different from caching systems like Memcache and will installing Memcache over VPS help deal with the issue in a better manner?

Thank you in advance to anyone who replies and apologies for sounding like a complete noob.
 
Their reply isn't really relevant as XF is already heavily indexed/optimised.

Asking them what the current setting is might be a good start. It might be they're giving you something unreasonably low.
 
Their reply isn't really relevant as XF is already heavily indexed/optimised.

Asking them what the current setting is might be a good start. It might be they're giving you something unreasonably low.

Thank you for clarifying that the indexing thing is not required, that's a relief to know :)

The max connections it seems is set to 25 which I think is a bit on the lower side. So looks like upgrading to VPS is the only solution?
 
With only 5-15 online at a time (that's presumably in the 15 minute period that XF uses by default) I'm a bit surprised you're hitting that limit but yeah, the limit is low.

Are you sure you're not being pounded by the search robots or something? Hopefully @Slavik or someone with more experience can give you some advice :-)
 
I have a wordpress blog running in the root and wonder if that could have anything to do with it. It gets around 700 to 800 visitors a day.

As for search bots, I just updated the robots.txt with the 'crawl delay' setting. Hoping that might reduce the occurrence.
 
Thank you for clarifying that the indexing thing is not required, that's a relief to know :)

The max connections it seems is set to 25 which I think is a bit on the lower side. So looks like upgrading to VPS is the only solution?

I have my max connections set to 6-700 iirc. But yes, 25 is on the low side, and they are probably being eaten up by wordpress.

There are a couple of extra mysql options that can help such connection caches.
 
Top Bottom