Thread

Our site has about 40.000 registered members. We have about 800.000 forum posts.

We noticed, that when a member searches the forum, the whole site slows down.
Checking the process list, we can see that the search is stuck in the “Sending data” stage. All other database functions are getting “Locked up”, probably waiting for the search to complete.

Sometimes, this can take over a minute. Which seems way too long. Especially for a user who has to wait that long to see the requested webpage.

The query that “locks up the” table is something similar to this:

SELECT p.topic_id, p.post_id FROM (exp_forum_posts p, exp_forum_topics t) WHERE t.topic_id = p.topic_id AND ( p.forum_id = '6' OR p.forum_id = '7' OR p.forum_id = '27' OR p.forum_id = '28' OR p.forum_id = '29' OR p.forum_id = '15' OR p.forum_id = '9' OR p.forum_id = '20' OR p.forum_id = '10' OR p.forum_id = '34' OR p.forum_id = '11' OR p.forum_id = '12' OR p.forum_id = '26' OR p.forum_id = '30' OR p.forum_id = '13' OR p.forum_id = '21' OR p.forum_id = '16' OR p.forum_id = '22' OR p.forum_id = '24' OR p.forum_id = '33' OR p.forum_id = '31' OR p.forum_id = '32' OR p.forum_id = '35' OR p.forum_id = '36' ) AND (p.body LIKE 'keyword %' OR p.body LIKE '%keyword%') LIMIT 250

Is there a way to do the search without locking the other tables?

Hi dombi,

Hmm, this shouldn’t be happening if your server hardware is sufficient. I’ve seen forum sites with more than twice the membership and post count handle searches just fine. Could you respond with some specifics about your server? Also, are you running EE 1.7.3 and Forum 2.2.2?

Hi Kevin,

Something is definitely not right with our setup, but we are at EngineHosting, so…
I don’t know much about the actual hardware, but I would imagine that it should be tuned for EE.

Should we talk to them about this problem?

Sometimes a simple reply in the forum can take about 20-30 seconds, with no heavy traffic at all on the site.

Here is a good example:
http://i50.tinypic.com/2vkym1l.png

Just to check your system, are you running EE 1.7.3 and Forum 2.2.2? I would definitely check with EngineHosting on this. While they tune for EE, that doesn’t necessarily mean you’re on the appropriate plan. A high-traffic site probably wouldn’t do well on their least expensive plan, for example.

Let me know what they have to say, and we’ll go from there.

Well, something is definitely not right. As I mentioned, a simple search on the forum takes at least 20-30 seconds, but usually it is closer to a minute.
And during this time, the database gets locked up and basically noone can do anything on the site.

EngineHosting told us that we need to upgrade to a larger plan. Which makes no sense.
It is not the traffic that causes the issue, but the slow search. But they seem to want to bump us to a larger plan.

Why would a forum search take this long?
Why can’t anyone do anything else, if someone is searching the forum?

dombi,

Our forums were about the same size and ran fine - but forums of that size requires a dedicated server (IMHO). In fact, we found that our older dedicated server with 2GB Ram and a dual core 3.0 was not enough at peak time - and we moved to quad-core with 4GB.

A server like this should run you from $150 to $250 (depends on host) per month. If you are trying to run a very busy server on much less, you will eventually hit a wall. This is usually due to MYSQL in one way or another.

EE uses straight mysql searching - that is, there is no add-on to use Spinx or Elasticsearch, etc. which offload the search. It’s well know on ALL forum software that once you get up at your level (3/4 of a million plus) posts, that more horsepower is needed for the search.

All that said, you or your host should check the my.cnf settings and see if you can tune up mysql a bit. Use phpmyadmin and/or mysql tuner and other scripts to determine which cnf lines may need changed.

Thanks for the reply Craig.

What settings do you recommend taking a look at? What could be done to “tune the search”?

Thanks for the reply Craig.

What settings do you recommend taking a look at? What could be done to “tune the search”?

In my experience, most boondoggles are in the mysql settings!

As I said, the best thing is to run a mysql utility and see what it says - there is one in phpmyadmin (called status), and there are others which are perl or sh scripts…..

MySQLTuner is the name of one. If you google “mysql tuning”, you will probably find others.

These scripts look at your mysql logs and make recommendations. As an example, some of one is included below:

“Sort_merge_passes 1.7 k The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.”

I’m not surprised to hear the recommendation to move up to a more robust hosting plan. As Craig noted, forums of your size really require some horsepower from the database server to effectively search.

It looks like you’ve got a good discussion going here regarding MySQL tuning. Would you like me to move this over to Community Help so you can continue the conversation there?

Kevin,  Sure.  Lets do that.

Sure thing!