Sphinx Search

[orban]

I am getting an error on the redirect page before the results pop though, nothing bad, just informational.

Wrap "if (is_array($vbulletin->GPC['prefixchoice'])) { .... }" around the that block

1) For some reason my updates on the deltas aren't working. It runs, but never finds anything to index. And there are definitly new posts and threads to index.

Are you sure the sphinx_counter table is set up properly

2) I'm getting an occasional error when people are searching:

Change the line with "$searchhash" to "$searchhash = md5(microtime());"

Try if that works

A couple of things I will have to say I noticed immediatly between the two version. The older version took significantly longer to index and took up more drive space, but the results seem to return much faster.

The newer Sphinx versions are much more complex... how large are your indices? I haven't noticed anything...

[NickCat]

Originally Posted by orban

Wrap "if (is_array($vbulletin->GPC['prefixchoice'])) { .... }" around the that block



Are you sure the sphinx_counter table is set up properly



Change the line with "$searchhash" to "$searchhash = md5(microtime());"

Try if that works



The newer Sphinx versions are much more complex... how large are your indices? I haven't noticed anything...

I'm going to retry the upgrade this weekend.

I will make those edits and see what we get for changes as far as the errors.

The sphinx_counter was working properly, it was updating both counters each time I ran the indexer, it just wasn't adding any info to the deltas.

As far as the speed, my board is 21+ million posts. I could be mistaken, but the size difference seemed rather significant between the two setups.

My indices right now with the old version are as follows:

-rw-r--r--  1 root root 6804366226 May  6 02:27 fulltext.spd
-rw-r--r--  1 root root        276 May  6 02:27 fulltext.sph
-rw-r--r--  1 root root   25592581 May  6 02:27 fulltext.spi
-rw-------  1 root root          0 May  6 02:28 fulltext.spl
-rw-r--r--  1 root root          0 May  6 02:12 fulltext.spm
-rw-r--r--  1 root root 1960560103 May  6 02:27 fulltext.spp
-rw-r--r--  1 root root          0 May  6 12:05 fulltextdelta.spa
-rw-r--r--  1 root root    1092441 May  6 12:05 fulltextdelta.spd
-rw-r--r--  1 root root        276 May  6 12:05 fulltextdelta.sph
-rw-r--r--  1 root root      88374 May  6 12:05 fulltextdelta.spi
-rw-------  1 root root          0 May  6 12:05 fulltextdelta.spl
-rw-r--r--  1 root root          0 May  6 12:05 fulltextdelta.spm
-rw-r--r--  1 root root     320815 May  6 12:05 fulltextdelta.spp
-rw-r--r--  1 root root          0 May  6 02:28 thread.spa
-rw-r--r--  1 root root   82741228 May  6 02:28 thread.spd
-rw-r--r--  1 root root        221 May  6 02:28 thread.sph
-rw-r--r--  1 root root     768559 May  6 02:28 thread.spi
-rw-------  1 root root          0 May  6 02:28 thread.spl
-rw-r--r--  1 root root          0 May  6 02:28 thread.spm
-rw-r--r--  1 root root   10011490 May  6 02:28 thread.spp
-rw-r--r--  1 root root          0 May  6 12:05 threaddelta.spa
-rw-r--r--  1 root root       9361 May  6 12:05 threaddelta.spd
-rw-r--r--  1 root root        221 May  6 12:05 threaddelta.sph
-rw-r--r--  1 root root       3139 May  6 12:05 threaddelta.spi
-rw-------  1 root root          0 May  6 12:05 threaddelta.spl
-rw-r--r--  1 root root          0 May  6 12:05 threaddelta.spm
-rw-r--r--  1 root root       1298 May  6 12:05 threaddelta.spp

I will report back with size of the new ones once I get a chance. It takes about 30-45 minuted for me to reindex the entire site. I used your new config as well, making the necessary edits, when I ran the setup, so that obviously changed the name of some things, but postdelta and thread delta remained the same. The speed difference may have just been perception though, since your script runs through the redirect and the search_sphinx.php I am currently using skips the redirect entirely.

[eoc_Jason]

Hi Orban! Glad you are back.

I too plan on tinkering extensively with sphinx once I get my site upgraded to 3.7. I literally could not run my forum search if it wasn't for sphinx. Also I want to tinker with the vB search code itself because I hate how they filter legit results and can sometimes even give "no results found" when in fact results are returned (before being processed through their miles of code that does god knows what for god knows why)...

Yes, people that use the similar threads option, I know vB 3.6 will look at the fulltext index when creating the similar threads so you have some code to modify to redirect it to sphinx. I would assume 3.7 would do the same. If you drop your fulltext indexes you will probably get an error when creating a new thread.

I have to disagree with the person who posted above saying that large vB sites are a small percentage. I think there are a lot more than you realize, but people attack the search problem in different ways. I've seen some people use lots of slave servers with some serious hardware to try and alievate the problem. Some people just disable their search entirely... or use google... I think someone might have hacked together dtsearch too... But I have to agree that Sphinx is the best (and fastest) choice...

I was really hoping that the vB team would implement some ability to use Sphinx with 3.7, but it seems like that request has gone unanswered...

The biggest problem I see when people do a search (not using sphinx), if it doesn't return results within a matter of seconds they start clicking again, and again, and again... which queues up the same search over and over in mysql... It's transparent to the other member too until someone posts a new message, which then it locks the whole table and anyone just wanting to read another thread has to wait... and they start clicking refresh over and over which again starts sending more and more requests to mysql... Eventually the server runs out of memory and things go ape... A serious problem indeed...

Anyhow, I would love to help out and contribute what I can once I get iTrader coded for 3.7 and those people off my back. I *think* I posted some cron scripts, log rotate, initd script, and other stuff on one of the previous pages. If I didn't and someone would like them let me know and I'll post what I have.

• I have a cron script that updates the delta every 15min (you can change it to whatever) and once a day (like 5am) it rebuilds the whole indexes.
• I also have an initd script (I run redhat) for starting & stopping sphinx. It creates a pid file and all that jazz... It's just a basic script but it works...
• Along with the above initd script I wrote a logrotate script, since I keep logs for the searches & sphinx output.

[ivanp]

In vB 3.7.0 & vB Blog 1.0.4 the following tables have FULLTEXT indices:

post -> FULLTEXT KEY title (title, pagetext);
thread -> FULLTEXT KEY title (title);
blog -> FULLTEXT KEY title (title);
blog_text -> FULLTEXT KEY title (title,pagetext);
socialgroup -> FULLTEXT KEY name (name, description);

The idea is to find a way to use Sphinx to completely replace vB FULLTEXT queries and to drop all these indices.

And then to switch to InnoDB tables, to prevent the rest of the locking queries.

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

Here is my setup... We should bundle similar script in the package.


Cron jobs:

• Every week: /usr/sphinx/indexer.sh --all
• Every hour: /usr/sphinx/indexer.sh thread
• Every 5 minutes: /usr/sphinx/indexer.sh postdelta threaddelta

indexer.sh:

#!/bin/sh

LOCKFILE=/var/lock/sphinx.cron.lock
INDEXER_CONF=/usr/sphinx/etc/sphinx.conf
INDEXER_BIN=/usr/sphinx/bin
INDEXER_LOG=/var/log/sphinx/indexer.log

[ -f $LOCKFILE ] && exit 0

trap "{ rm -f $LOCKFILE ; exit 255; }" EXIT

touch $LOCKFILE

$INDEXER_BIN/indexer --config $INDEXER_CONF --rotate $1 $2 $3 $4 >> $INDEXER_LOG

exit 0

[TechGuy]

I have installed Sphinx with the information provided here, however, now my unanswered threads search is showing answered threads. It's almost as if the the SetFilterRange isn't actually doing anything. I've placed comments in the code to make sure it is getting to the point of setting the filter range, however, it still reveals posts that have more than 0 replies.

The url used to build the search is search.php?do=process&replyless=1&replylimit=0&sea rchdate=30&beforeafter=after&e xclude=54

[Jase2]

Hello,

Sorry if I seem to be missing something already said, but what exactly is this and what does it do ?

[orban]

Originally Posted by TechGuy

I have installed Sphinx with the information provided here, however, now my unanswered threads search is showing answered threads. It's almost as if the the SetFilterRange isn't actually doing anything. I've placed comments in the code to make sure it is getting to the point of setting the filter range, however, it still reveals posts that have more than 0 replies.

The url used to build the search is search.php?do=process&replyless=1&replylimit=0&sea rchdate=30&beforeafter=after&exclude=54

If you are using the script posted in post 1, I wrote there it only works when searching threads, because Sphinx doesn't have replycount integer stored for posts. I don't think I'm checking for "replyless", must have missed that... maybe if you add a check for that variable and force the thread index it might work

[TechGuy]

Orban,

Thanks for the quick reply. I'm not sure what you mean by check for the variable and force the thread index.

The search_sphinx.php has the following code which appears to check for replyless:
if ($vbulletin->GPC['titleonly'] and ($vbulletin->GPC['replyless'] OR $vbulletin->GPC['replylimit'] > 0))

if ($vbulletin->GPC['replyless'] == 1)
$cl->SetFilterRange('replycount', 0, $vbulletin->GPC['replylimit']);
else
$cl->SetFilterRange('replycount', $vbulletin->GPC['replylimit'], 9999999999);

And replycount is in the postsrc in sphinx.conf

[orban]

You added replycount to your postsrc?

You do realize that this is really buggy, because when a thread with 0 replies is in the main post index, it will show up as having 0 replies until you update the main index?

This might be why you're getting answered threads...

You can try to uncomment the lines starting with #

("admin mode", and the two print_r()s, and try to figure out where it's going wrong... of the replycount range is set, and if all returned posts/thread ids actually have replycount 0 or not)

[ivanp]

In vB v3.7.0 fulltext search is used in:

- search.php: there are bunch of hooks we can use. It would be much better solution than to patch search.php itself.

- includes/functions_search.php: in function fetch_similar_threads, there is a hook 'search_similarthreads_fulltext'.

vB Blog v1.0.5:

- includes/class_blog_search.php: no hooks