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.

[kerplunknet]

Originally Posted by Dontom

http://dev.mysql.com/doc/refman/5.0/en/replication.html

I appreciate your help, but I have already read this. I find most of the documents on MySQL's website are not helpful to me. They are very technical and precise. Documents such as these are more likely to help me and is more what I was asking Erwin for:

http://www.howtoforge.com/mysql_database_replication
http://www.onlamp.com/pub/a/onlamp/2.../MySQLian.html

Please don't Google "mysql replication" and post the first link that comes up. I can do that myself.

[Dontom]

the reference manual contains a step by step "How to Set Up Replication" which helped us setting it up...

How to put search on slave :
edit includes/config.php

put your slave config into this:

if (THIS_SCRIPT == search)
{

slave config goes here...

}

in vb 3.5x we had to do it slightly different (use a second master config for THIS_SCRIPT == search)
http://www.vbulletin.com/forum/showp...93&postcount=9

[Erwin]

Originally Posted by Dontom

the reference manual contains a step by step "How to Set Up Replication" which helped us setting it up...

How to put search on slave :
edit includes/config.php

put your slave config into this:

if (THIS_SCRIPT == search.php)
{

slave config goes here...

}

http://www.vbulletin.com/forum/showp...93&postcount=9

You can do it that way but you end up replicating the whole database when you should only replicate user, forum, thread and post.

[Dontom]

This way we can use it as a fallback db, in case the master dies...

[Farcaster]

Originally Posted by Marco van Herwaarden

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.

For large forums is there any reason not to use InnoDB? If you use InnoDB, you also could also modify the forum searches to READ UNCOMMITTED and pickup some performance there without any locking at all.

[BoardTracker]

Originally Posted by Farcaster

For large forums is there any reason not to use InnoDB? If you use InnoDB, you also could also modify the forum searches to READ UNCOMMITTED and pickup some performance there without any locking at all.

InnoDB doesn't have FTS (Full text search) capabilities.

[orban]

There is a patch though, it's linked on the full text forums on mysql.com.

Would love to hear from somebody if that works.

[psico]

Originally Posted by Erwin

You can do it that way but you end up replicating the whole database when you should only replicate user, forum, thread and post.

Erwin, can you explain pls how you did it?

[gorman]

I asked at vbulletin.com but:

Is anybody able to point me to a working hack to disable totally the search with thread results, enabling only post results? If I understand things correctly, it could do wonders for FullText searches...

Is it just a matter of editing templates or what?

[TECK]

Originally Posted by Erwin

You can do it that way but you end up replicating the whole database when you should only replicate user, forum, thread and post.

I'm interested in that also, Erwin. Thanks.