Sphinx Search

[scanlover]

Hi,

I managed to integrate sphinx 0.9.8 rc2 with my vbulletin board 3.70 quite easily thanks to this thread.

I followed the instructions here:

https://vborg.vbsupport.ru/showpost....&postcount=387

However the attached config file there was deprecated, I've updated it and attached my version below. I note that in vbulletin, the dateline and lastpost fields are stored as int(10) and not as a timestamp, hence I changed the config file to mark them as "sql_attr_uint" instead.

Also, as my board and database is running totally on utf-8 with some posts in CJK, so I have enabled CJK support in the attached config file as well, hope it is of use to someone

The sphinx.php files I found here seem to be partially buggy. ($coventry? empty arrays?) Will appreciate if someone can post a updated working version, especially for vB 3.7 ?

Lastly, I have converted my thread and post tables to INNODB, since there is now no need for the MYISAM FULLTEXT indices. Hopefully this will alleviate the table locking problems (no stats to show for though). Not sure if there are any repercussions in doing so

Background:
My forum is not exactly big, only about 300K posts but it is running on a very modest dedicated server with only 1G ram. A usual mysql fulltext search takes around 3-5 seconds, but my users tend to use the search gratuitously and it got so bad that I had to disable post body search.

Now with sphinx each search takes around 0.1-0.2 seconds, so everything's cool again.

[PSS]

Just wanted to say I really appreciate your contribution, scanlover. I'll try that code with my big board in couple of weeks, I have to set up a test system first for 3.7.

--------------- Added [DATE]1209851068[/DATE] at [TIME]1209851068[/TIME] ---------------

Originally Posted by andrewkhunn

Well, I know Xorlev *was* working on compiling a 3.7-ready complete version. It also appeared that he had been making some progress with similar threads and other outlaying issues to the current version we're all using.

Has anyone more info? If there is a Sphinx-to-Vb 3.7 (and any new version) integration package you can buy I'm interested. PM me if you can't write here.

[mack101]

I think I have this working on a 36M post board running latest Sphinx and VB 3.7 Great job to everyone who has contributed to this, I was able to get it up and running in about 5 hours (3.5 hours to index and sort)

However, I'm quite new to the process, is there anything that has to be done on the VB admincp side of things, or is it left as is? I removed the full text indexes from MySQL, and it looks to have reverted to internal search. Do the file edits in search.php override this?

Based on the test results in the forum it looks very promising so far. Thanks for any advice you folks can give me.

[scanlover]

I dont think vbulletin will switch to internal search just because you dropped the index.
As far as your vb is concerned, it still thinks the fulltext index is there. For example, I believe the 'similar threads' feature will try to use the fulltext index and so if you have that enabled you might get some errors. I'm not sure where else vB will try to use the fulltext index.

To change to local search you will need to use the 'Search Type' setting from admincp.

[mack101]

Thanks. I think my mistake was removing the fulltext indices from VB admincp, which reverts the system to the VB internal engine. Whether it's actually using it, I can't say for certain.

I can't say I sound very sure of myself at this point

I guess what I need to confirm everything is working is a couple of sanity checks to validate the Sphinx setup and that vB is interfacing with it correctly.

[amcd]

Once you get sphinx working, you will know immediately. Your searches will run in 1/100th of the time it used to take, and your database load will ease out a lot.

Make sure vb adminCP is set to fulltext search. If it is set to default search, then your forum will keep running the code to update the index tables. Once you are sure sphinx is working properly, just drop the fulltext indexes (from mysql, not from vb admincp).

[ivanp]

Not really sure if indexes should be dropped.

I think other functions such is "Similar threads" depend on it. Should be checked.

--------------- Added [DATE]1209912502[/DATE] at [TIME]1209912502[/TIME] ---------------

Scanlover, many thanks for your detailed explanation!

I've just installed Sphinx and it seems to be working fantastic!

[Kevlar]

Upgraded from 3.7 RC1 to 3.7 Gold. Things seem to be moving along fine.

[mack101]

Running into a issue on my production server install.. the indexes grew to 62GB where it maxed out my disk space.

On the test server, using the same db, the indexes only used 39GB... What could be the cause of this?

[ivanp]

Originally Posted by mack101

Running into a issue on my production server install.. the indexes grew to 62GB where it maxed out my disk space.

On the test server, using the same db, the indexes only used 39GB... What could be the cause of this?

My guess is you were using --rotate switch, which temporarily creates copy of indexes. When it finishes, it replaces existing indexes. This is normal behavior.