The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
|
|||||||
How did you solve searching in large forums? Details »»
|
|||||||||||||||||||||||||||
|
How did you solve searching in large forums?
Developer Last Online: Jul 2013 
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. Show Your Support
|
|||||||||||||||||||||||||||
About Developer
Visit Web Site
No support by the author.| Comments |
|
#12
06-23-2006, 06:42 AM
|
||||
|
||||
Quote:
Combine your fulltext search with google search (i attach a image to you) And a link to implement https://vborg.vbsupport.ru/showthread.php?t=109906 
|
|
#13
06-23-2006, 09:27 PM
|
|||
|
|||
|
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?
|
|
#14
06-23-2006, 09:33 PM
|
||||
|
||||
|
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.
|
|
#15
06-24-2006, 03:26 PM
|
|||
|
|||
|
Quote:
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: Code:
Current Time: Wednesday, 21-Jun-2006 16:49:28 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 51 minutes 34 seconds Total accesses: 197069 - Total Traffic: 911.2 MB CPU Usage: u4947.26 s378.21 cu.8 cs0 - 30.4% CPU load 11.3 requests/sec - 53.3 kB/second - 4848 B/request 115 requests currently being processed, 14 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWW_WWWWWWWWWWWWWWW_WWWWWWRWWWW__WWWWW___W_______W .................W.............................................. ................................................................ ................................................................ .............................. Code:
Current Time: Wednesday, 21-Jun-2006 16:49:54 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 52 minutes 1 second Total accesses: 197189 - Total Traffic: 911.5 MB CPU Usage: u4790.07 s365.54 cu.78 cs0 - 29.4% CPU load 11.3 requests/sec - 53.3 kB/second - 4847 B/request 139 requests currently being processed, 16 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWW_WWWW __WWW__W_WWWWWW_WW______W__..................................... ................................................................ ................................................................ .............................. Code:
Current Time: Wednesday, 21-Jun-2006 16:50:20 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 52 minutes 26 seconds Total accesses: 197264 - Total Traffic: 911.8 MB CPU Usage: u4748.97 s361.83 cu.77 cs0 - 29.1% CPU load 11.2 requests/sec - 53.2 kB/second - 4846 B/request 170 requests currently being processed, 15 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW _WWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWWW_WW___W__W_WW______....... ................................................................ ................................................................ .............................. Code:
Current Time: Wednesday, 21-Jun-2006 16:50:47 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 52 minutes 54 seconds Total accesses: 197299 - Total Traffic: 912.2 MB CPU Usage: u4749.94 s361.89 cu.77 cs0 - 29.1% CPU load 11.2 requests/sec - 53.2 kB/second - 4848 B/request 199 requests currently being processed, 11 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWWWWWWWWWWWWWWWWWWW WWWWWW_W_W________.............................................. ................................................................ .............................. Code:
Current Time: Wednesday, 21-Jun-2006 16:51:39 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 53 minutes 45 seconds Total accesses: 197505 - Total Traffic: 913.5 MB CPU Usage: u4754.06 s362.19 cu.77 cs0 - 29% CPU load 11.2 requests/sec - 53.1 kB/second - 4850 B/request 262 requests currently being processed, 14 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW_WWWWWWWWWWWWWWWCWWWWWWWWWWWWWWWW WW____WW_W__WW______............................................ .............................. Code:
Current Time: Wednesday, 21-Jun-2006 16:52:10 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 54 minutes 17 seconds Total accesses: 197650 - Total Traffic: 914.0 MB CPU Usage: u4756.07 s362.31 cu.77 cs0 - 29% CPU load 11.2 requests/sec - 53.0 kB/second - 4849 B/request 327 requests currently being processed, 10 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWCWWW__________............. Code:
Current Time: Wednesday, 21-Jun-2006 16:56:06 CDT Restart Time: Wednesday, 21-Jun-2006 11:57:53 CDT Parent Server Generation: 0 Server uptime: 4 hours 58 minutes 12 seconds Total accesses: 197721 - Total Traffic: 915.4 MB CPU Usage: u4689.97 s356.97 cu.77 cs0 - 28.2% CPU load 11.1 requests/sec - 52.4 kB/second - 4854 B/request 350 requests currently being processed, 0 idle workers WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW WWWWWWWWWWWWWWWWWWCWWWWWWWWWWW 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.
|
|
#16
06-24-2006, 06:00 PM
|
|||
|
|||
|
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.
|
|
#17
06-24-2006, 08:18 PM
|
||||
|
||||
|
Marco van Herwaarden's review is correct in principal.
There are some things to take into account when describing MySQL's locking mechanism, including DELAYED INSERT, LOW/HIGH PRIORITY and row level locking which effect how locks and tables interact However.. In case of FTS search, this doesn't really help. When using MyISAM table which are the foundation of MySQL's FTS built in index, locks are always Table-level locks. Further more, when causing many reads to be queued, the load on the DB server, memory usage and overall caused slowness, can greatly drag the server performance down causing it great difficulties in getting rid of the heavy load (creating a vicious circle). This is ESPECIALLY true if you define the memory parameters incorrectly, causing Swap to be heavily used, which may create a situation where the only solution is restarting the web server, DB server or even both in some cases.
|
|
#18
06-25-2006, 08:20 AM
|
|||
|
|||
|
Basically, once you hit the magic number of 3 million posts, the MySQL fulltext search times start to increase exponentially. And it'll only get worse with every new post added. I guess every board has to go through this once it reaches 3-4 million posts.
No amount of server tweaking is going to help here. Converting to InnoDB won't help as InnoDB doesn't have a fulltext search capability. Your only choice is a slave search server, preferably dedicated, though you can try the approach from the first post, it may work if you have a sufficient hardware in your db server. I'll be interesting to know if it worked for you.
|
|
#19
06-25-2006, 08:39 PM
|
|||
|
|||
|
I think vBulletin can solve this issue if it can programmatically create a mirror post and thread tables for searches, with delayed writes to these tables. Time to hack??? <grin>
|
|
#20
08-05-2006, 02:09 AM
|
|||
|
|||
|
We archive all threads that haven't been replied to for x days to a read only database. When a user does a search they can leave the default, which is the current table, and/or they can choose archives. If they choose archives then they must choose a category for the search and cannot search the entire board at one time.
This is on top of full text search, etc.
|
|
#21
08-16-2006, 10:19 PM
|
|||
|
|||
|
Quote:
|
 |
«
Previous Thread
|
Next Thread
»
|
|
All times are GMT. The time now is 03:58 PM.