MySQL native full-text search?

[roy7]

Would that be faster/easier than doing it in vbulletin itself through the search index/etc? It also can give you % confidence in matching and so on...

http://mysql.com/doc/F/u/Fulltext_Search.html

-Jonathan

[Wayne Luke]

Not everyone is using one of the 3 latest versions of MySQL. Ed, played around with this a little in development and didn't come up with any overall speed increases but since the technology is new it might get faster.


There is also soundex that could be incorporated into the search. This would allow you to pickup mispelled words.

I am sure the search will be improved in future versions to take advantage of newer technologies.

[roy7]

Thanks. I didn't check for previous threads on this before posting, oops, I see it has been discussed for months.

My only focus really is to make a specific site fast enough to keep using vbulletin, not changing it in general for all sites. For small sites even plain regexp searching would be fast enough. ;)

-Jonathan

[Wayne Luke]

What website? The stats you have stated before 40-50 queries a second and other problems you were talking about don't seem excessive or resource intensive to me.

What kind of server are you using and what are its specifications?

[roy7]

Home Theater forum, www.hometheaterforum.com

We peak in the early weekday afternoons, sometimes over 400 people, and the site just gets too slow. It's a dual cpu machine (not sure on speed, 600mhz or 800mhz, somewhere in there). I have mysqld on a seperate drive from the rest of the box, compiled with agressive optimization settings and gcc 2.95.3. Our key buffers/etc are all set nicely. Thread and other tables that have heavy read/write lock conflicts are all using InnoDB now (for row-locking vs table-locking). Normally, like now, it's very fast. BUt at peak the index.php page can take a while to load, posting can take a long time, etc.

The posting problem might be a conflict between posting and searching, I can't change searchindex to innodb because it's so huge, but I did change 'word' and I change the post's insert to searchindex to be 'delayed'. That helped post speed a little. The queries/second average might not be accurate, maybe I should restart mysqld during peak time, and then see an average over a few minutes. I do have 'low slow queries' turne don, and in a given day we might have 5K-10K queries that took over 10 seconds. Usually searchs, sometimes other things. So one class of queries at a time I'm trying to speed them up. My current efforts are on trying out fulltext on the post database. It looks promising so far.

Another idea is things like template, smilie, bbcode table are read over and over all the time but the data rarely changes. So I'm thinking about copying the data to a file or shared memory segment and flushing it when those tables are updated through the admin interface. The idea beign to "cache" those mostly read-only tables in RAM in the app itself, saving a lot of CPU.

-Jonathan

[Wayne Luke]

At 400 people, I would guess that you are reaching the limit of scalability of MySQL. Sorry, sad but true. You should look into separating the web-server and the database server onto two different machines.

Dual Processors don't mean double processing power. Usually you will get a maximum of 50% more and most often less, sometimes more. Putting the database on another server will improve its capabilities.

Another option would be to switch to a DBMS with true relational capabilities like Sybase. vBulletin has been successfully converted to Sybase in the past on a system much the same size of yours.

Other things you can try are:
1. Hardcoding the forumjump box or removing it completely.
2. Turning off views for individual topics.
3. Putting Avatars as files instead of stored in the database (there is a hack for this.)

Removing the who's online from the index screen would speed it up considerably as well.

[bira]

Wluke, I'm curious about the hard-coding of forum jump: is this really resource intensive? Would you recommend doing it for a site that quite rarely adds other forums? I'm always interested in speeding up pages and lowering load

[Parker Clack]

wluke:

I have already removed the online from the main index.php and had already hard coded the forumjump weeks ago. I also don't use the avatars at all. I also took out the online status from the showthread routine too. Anything else to try?

(roy7 and I are working on the same box at hometheaterforum.com)

Parker

[Wayne Luke]

Originally posted by bira
Wluke, I'm curious about the hard-coding of forum jump: is this really resource intensive? Would you recommend doing it for a site that quite rarely adds other forums? I'm always interested in speeding up pages and lowering load

Putting it on a page dynamically is like putting a mini-version of Forum-Home on every page. The more forums you have the longer it is going to take to render and push across.

[Wayne Luke]

Turn off views. I would remove them completely from the layout and the code. So if a member views ten posts for every one they post, you would eliminate ten queries. These are delayed queries to be sure but still have to go into the database eventually.

[Parker Clack]

wluke:

I know what to take out of the templates for the views but what files should I edit to review the views from them and what should I edit out?

Thanks,
Parker

[Wayne Luke]

In showthread.php remove (well comment out anyway):


if ($noshutdownfunc) {
$DB_site->query("UPDATE thread SET views=views+1 WHERE threadid='$threadid'");
} else {
$shutdownqueries[]="UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='$threadid'";
}


(Just fixing his vB code--Ed)

[Parker Clack]

wluke:

Ok, thanks a lot.

Parker

[Wayne Luke]

Originally posted by wluke
In showthread.php remove (well comment out anyway):


if ($noshutdownfunc) {
$DB_site->query("UPDATE thread SET views=views+1 WHERE threadid='$threadid'");
} else {
$shutdownqueries[]="UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='$threadid'";
}


(Just fixing his vB code--Ed)

Funny, Ed had to fix the code and it was straight out of my RC2 zip file. :)

[Mike Sullivan]

Originally posted by wluke
Funny, Ed had to fix the code and it was straight out of my RC2 zip file. :) Actually, when I said vB code, I didn't mean the PHP code. :) You originally had a [/quote], but nothing to open it.

So, nah :p

Hehe.