Register Members List Search Today's Posts Mark Forums Read

Reply
 
Mod Options
  #1  
Old 21 Jun 2006, 17:23
99Percent 99Percent is offline
 
Join Date: May 2002
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.
Reply With Quote
Comments
  #2  
Old 21 Jun 2006, 23:14
Joe Joe is offline
 
Join Date: Nov 2001
I would love to hear some solutions as well.
Reply With Quote
  #3  
Old 22 Jun 2006, 01:33
silvrhand silvrhand is offline
 
Join Date: Jun 2004
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.
Reply With Quote
  #4  
Old 22 Jun 2006, 01:43
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Real name: 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 ?
__________________
Former vBulletin.org Staff Member


Cable Forum
Please do not PM me about custom work - I no longer undertake any.
Note: I will not answer support questions via e-mail or PM - please use the relevant thread or forum.
Reply With Quote
  #5  
Old 22 Jun 2006, 13:15
dbembibre's Avatar
dbembibre dbembibre is offline
 
Join Date: Oct 2004
Real name: Daniel
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
Reply With Quote
  #6  
Old 22 Jun 2006, 16:01
InfidelMatt InfidelMatt is offline
 
Join Date: Oct 2005
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
Reply With Quote
  #7  
Old 22 Jun 2006, 16:09
BoardTracker's Avatar
BoardTracker BoardTracker is offline
 
Join Date: Dec 2005
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.
__________________
BoardTracker Search Mod ~ advanced search engine for your board with no server load! (vb3.5, 3.6 and 3.0)
BoardTracker Tagging Mod ~ add thread tagging, tag search and cool tagclouds! (vb3.5, 3.6 and 3.0)

All our mods allow for single or multi-board and global options for true web2.0 functionality..
Reply With Quote
  #8  
Old 22 Jun 2006, 16:22
99Percent 99Percent is offline
 
Join Date: May 2002
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.
Reply With Quote
  #9  
Old 22 Jun 2006, 18:21
silvrhand silvrhand is offline
 
Join Date: Jun 2004
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.
Reply With Quote
  #10  
Old 23 Jun 2006, 05:16
99Percent 99Percent is offline
 
Join Date: May 2002
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.
Reply With Quote
  #11  
Old 23 Jun 2006, 08:42
dbembibre's Avatar
dbembibre dbembibre is offline
 
Join Date: Oct 2004
Real name: Daniel
Originally Posted by 99Percent
We already have fulltext search.
Disbale boolean search to all users and allow to admin and mods.
Combine your fulltext search with google search (i attach a image to you)

And a link to implement http://www.vbulletin.org/forum/showthread.php?t=109906
Attached Images
File Type: jpg buscar.JPG (21.1 KB, 210 views)
Reply With Quote
  #12  
Old 23 Jun 2006, 23:27
99Percent 99Percent is offline
 
Join Date: May 2002
I have a question regarding table locking during searches. Does the search actually cause a table lock? If so, is it done programatically by vBulletin, or is built in into mysql itself?

Why would it need to lock ables if no modifications are actually being done to the them?
Reply With Quote
  #13  
Old 23 Jun 2006, 23:33
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Real name: Paul M
mySQL locks the table(s) with a read lock, obviously because it's reading them. That will prevent any write locks until the query has finished, and as I recall - depending on your settings - those queued write locks will then cause further read lock requests to queue, thus a query queue will build up.
__________________
Former vBulletin.org Staff Member


Cable Forum
Please do not PM me about custom work - I no longer undertake any.
Note: I will not answer support questions via e-mail or PM - please use the relevant thread or forum.
Reply With Quote
  #14  
Old 24 Jun 2006, 17:26
99Percent 99Percent is offline
 
Join Date: May 2002
Originally Posted by Paul M
mySQL locks the table(s) with a read lock, obviously because it's reading them. That will prevent any write locks until the query has finished, and as I recall - depending on your settings - those queued write locks will then cause further read lock requests to queue, thus a query queue will build up.
But if a read lock is issued to a table, that doesn't prevent other reads from happening, only writes, no? So I don't understand why other people cannot still browse the forum while a user is doing a search for example. Only people who are making a post would have to wait until the read lock is cleared.

What we notice happens sporadically in our forum is that a surge of new Apache connections occurs. Here are several Apache Server Status just seconds apart which I managed to capture in real time just before the server crashed:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

And then the server status request through http also fails because all the Apache slots have been filled and are busy. At this point, only a restart of Apache service restores the server to normal.

During this time, I can access other non vBulletin pages of the server, while actual vBulletin pages remain hanging, so this indicates to me that its either a PHP or a mySQL problem which is causing the Apache connections to wait and surge. We can make this happening to a degree with a search but sometimes the connections surge to some and then it falls back to normal after a couple of minutes. So we suspect that with a specific type of search is how the server ultimately crashes.
Reply With Quote
  #15  
Old 24 Jun 2006, 20:00
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Have a look here (and many other pages): http://dev.mysql.com/doc/refman/4.1/...l-locking.html

Simply put:
MyIsam uses table level locking (ie. it doesn't lock a single row, but the entire table).
There are 2 lock wait queues: WriteLock and ReadLock queue.
Many simultaneous Read-lock can be placed on a single table at once.
Only 1 write-lock can be active on a table.
Writes get priority over reads.

Scenario A with searching and nobody is trying to write to the table:
The search is a longer running read, that places a read-lock on the table.
Others also want to read from the same table. This is not a problem since you can have multilple read-locks on the same table.

Scenario B with searching, 1 want to write to the table, and many others want also to read from teh table:
The search is a longer running read, that places a read-lock on the table.
A write request is made. A write-lock can not be obtained, since there is already a read-lock in place. So write is placed in the write-lock queue and waits for the previous read to finish.
Others also want to read from the same table. Since Writes go before reads, these new reads are placed in the read-lock queue, and will not be server until the write-lock queue is empty.

If you are unlucky with scenario B, new writes will be queued, while we are still waiting for the search to finish. Meanwhile the read-lock queue is building up, etc....

In some situations it might be advisable to change some tables from MyIsam (table-level locking, no transactions) to InnoDB (row-level locking, with transactions). Using this kind of tuning, requires probably an experienced MySQL-administrator.

PS A lot more documentation on tabletypes and locking can be found on this at the mysql.com website.
Reply With Quote
Reply

Similar Mod
Mod Developer Type Replies Last Post
Large title over Forums kall vBulletin 3.0 Template Modifications 4 25 Feb 2005 10:32



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Mod Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 10:03.

Layout Options | Width: Wide Color: