How did you solve searching in large forums?

We are experiencing server performance when a user searches through our fairly large forum. The fulltext search aparently locks thread and post tables causing Apache processes to wait until the search is done. This in turn causes a surge of new Apache connections to open. If the search takes too long everyone experiences a downtime of the forum while the search is done.

What is the solution to this? How did you solve it?

The only thing I can think of right now is to install another instance of mysql on this same server and configure it as slave with replication. This instance would have its own database files and a different port.

Then configure vBulletin to use the slaveserver for the search script.

However I notice that vBulletin (in 3.5.3, function db->connect) checks to see if the master and slave have different names. This will not work if both of them are set to localhost even if they are using different ports. So at least a hack will have to be done here.

[Joe]

I would love to hear some solutions as well.

[silvrhand]

Put an alias in your hosts file and connect to that instead, it'll get past the localhost issue.

Originally Posted by 99Percent

We are experiencing server performance when a user searches through our fairly large forum. The fulltext search aparently locks thread and post tables causing Apache processes to wait until the search is done. This in turn causes a surge of new Apache connections to open. If the search takes too long everyone experiences a downtime of the forum while the search is done.

What is the solution to this? How did you solve it?

The only thing I can think of right now is to install another instance of mysql on this same server and configure it as slave with replication. This instance would have its own database files and a different port.

Then configure vBulletin to use the slaveserver for the search script.

However I notice that vBulletin (in 3.5.3, function db->connect) checks to see if the master and slave have different names. This will not work if both of them are set to localhost even if they are using different ports. So at least a hack will have to be done here.

[Paul M]

How big is your forum, because we don't have any issues with searches - also which search method do you use ? Do you allow guests to search ?

[dbembibre]

If you dont have load problems with vBulletin search, dont touch.
I have a combination of fulltext search engine without boolean search and google domain web search.
My server a Dual Dual Xeon 2.8 Ghz 2MB cache 2 Gig of Ram and Ultra SCSI with 1.500.000 of posts and 700 - 800 constants users online has a max load of 1.5 and a media of 0.60 - 0.80

[InfidelMatt]

Originally Posted by Paul M

How big is your forum, because we don't have any issues with searches - also which search method do you use ? Do you allow guests to search ?

As 99Percent hasn't responded yet, I will (I help manage the same server).

Our forum has 3.5 million posts with 3000 or so active members, 300-500 constants. We use fulltext searching. We don't allow guests to search. Our post table is in the neighborhood of 3GB.



Here is our problem: Our load averages are just fine, but when someone runs an advanced search, it locks the entire post table for a bit and apache soon hits the max connections limit as a bunch of requests are waiting due to the locked table.

This graph explains better:



As you can see, everything is just fine until someone runs a search requiring the entire post table to be locked (Blue: apache connections).

A big portion of the problem is our hardware. We are unfortunately on IDE non-raided drives so things don't move as fast as they should.

Until we resolve the hardware issues, having a slave DB on the same server will allow for searches locking the post table (now of the slave DB) while not slowing down or outright stopping those accessing the master post table nor overwhelming Apache.

We'll try the alias method recommended above. Thanks.

Matt

[BoardTracker]

Search is always going to be a problem once your board gets big but of course it is an essential service. You may want to have a look at our search mod which offloads ALL the processing (indexing and search) to our servers while the results are still fully integrated in your board. Its currently being used on some of the biggest boards out there such as boards.ie

There could also be other issues affecting server performance such as db configs and we'd be happy to help out in any way we can.

[99Percent]

Originally Posted by silvrhand

Put an alias in your hosts file and connect to that instead, it'll get past the localhost issue.

Good idea, I hadn't thought about that.

[silvrhand]

switch to fulltext search, I did last night on 2.2 million post forum and it took 22 minutes to add the first index, then about 2 mins on the second one. So be prepared for a wait.

[99Percent]

Originally Posted by silvrhand

switch to fulltext search, I did last night on 2.2 million post forum and it took 22 minutes to add the first index, then about 2 mins on the second one. So be prepared for a wait.

We already have fulltext search.