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 https://vborg.vbsupport.ru/showthread.php?t=109906

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?

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:
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.

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.

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.

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.

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>

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.

Can you tell me more about this setup? Sounds ideal...