Sphinx Search

[orban]

Well if it's crashing the server it's not :<

Just curious "orban" having done some more checks when doing just a user "Find Threads Started by user" it is over a minute with the size of files we have - and from what you are saying this is the standard vB result. While once you add an addional key - search string it all works much faster as it is using Sphinx (is that right?).

Is there a reason you didn't code that using Sphinx?

Yeah but you'd have to add a fake string to all posts...mm....that's what you do right?

[ALanJay]

Originally Posted by orban

Yeah but you'd have to add a fake string to all posts...mm....that's what you do right?

If only it was that easy

If you enter a space it thows it away and does the standard search if you enter a single character it just says there are no such matches. If you have a longer string it only finds occurences that match not all all of them

[mute]

We're rolling out our sphinx search when we upgrade our site to 3.6.2 on thursday. I'm hopeful that it will live up to my testing, but I am a tad worried that the "find posts by user" searches will be a bit pokey. I'm don't think it warrants a lot of concern given how often that particular type of search is actually done though..

Orban, have you been working on any other surprises lately?

[orban]

You mean "find posts by user" without key words yeah?

No, haven't had a lot time lately. ;(

[ALanJay]

Originally Posted by mute

We're rolling out our sphinx search when we upgrade our site to 3.6.2 on thursday. I'm hopeful that it will live up to my testing, but I am a tad worried that the "find posts by user" searches will be a bit pokey. I'm don't think it warrants a lot of concern given how often that particular type of search is actually done though..

Just to make clear that "find posts by user" is fine and works very fast it is "Find THREADS STARTED by user" that is solw (still uses the internal code in vB - I think from the comments made).

Overall we have been using sphinx search now for nearly a week and it seems to work very nicely, the DELTA file is generated every 5 mintes with the full file being rebuilt each night in the early hours of the morning. Our vB main data file is around 4Gb in size (Threads: 467,561, Posts: 11,271,241, Members: 173,321).

We have disabled the "Find Threads STARTED by user" option for non admin users from the search options as a temporary measure - I'm not sure anyone used it in anycase.

Obviously in an ideal world it would be nice if this was searchable but it isn't a deal breaker.

[kmike]

AlanJay, what's your MySQL version? I remember dealing with a bug in MySQL 4.0.x when "find threads started by user" query didn't use the proper indexes. Repairing the thread and post tables fixed that, but after some time the problem crept back in.

Update: found the same bug in the bookmarks, it appears not only 4.0.x are affected: http://www.vbulletin.com/forum/bugs....iew&bugid=4159
Looks like an intermittent index loss or corruption.

Also, FYI sphinx-0.9.7-rc1 has been released.
Another update: forgot to say that the crash bug has been fixed in RC1.

[ALanJay]

Originally Posted by kmike

AlanJay, what's your MySQL version? I remember dealing with a bug in MySQL 4.0.x when "find threads started by user" query didn't use the proper indexes. Repairing the thread and post tables fixed that, but after some time the problem crept back in.

We are using 4.1 version of mySQL (clients 4.1.18 to 4.1.21 and server 4.1.19).

Though I suppose the real goal is to remove this from the standard vB search and put it into a search done by sphinx (if possible) as with tables as big as ours I can understand why the results might take a little time to be returned.

Originally Posted by kmike

Update: found the same bug in the bookmarks, it appears not only 4.0.x are affected: http://www.vbulletin.com/forum/bugs....iew&bugid=4159
Looks like an intermittent index loss or corruption.

Thanks will take a look.

Originally Posted by kmike

Also, FYI sphinx-0.9.7-rc1 has been released.
Another update: forgot to say that the crash bug has been fixed in RC1.

Interesting from discussions it sounds like there is quite a number of changes so I assume there might be more to upgrading than a simple rebuild once it is properly released.

[orban]

Yeah, I just saw 0.9.7-RC1 got released.

I think I will wait for one more release candidate or even for the final version, because I'm sure there'll be bugs. Once it's release I'll create a new how-to. It will probably not just be a simple rebuild of indices, yeah.

[ALanJay]

Originally Posted by orban

Yeah, I just saw 0.9.7-RC1 got released.

I think I will wait for one more release candidate or even for the final version, because I'm sure there'll be bugs.

- as the current version works so well I'm in no hurry.

Originally Posted by orban

Once it's release I'll create a new how-to. It will probably not just be a simple rebuild of indices, yeah.

I suspected as much. Good luck when he gets that far.

By the way did you have a sugestion as to where was the best place to do the arraywalk in your sphinx.php code?

Can it be implemented in the lines like?


ie something like


$cl->SetGroups2 ( array_walk($sphinx_groups2, "intvalArray") );

Obvioulsy with the function elsewhere in the code.

function intvalArray(&$item, $key)
{
$item = intval($item);
}

When I try I get another error:

Invalid argument supplied for foreach()

in Sphinxapi.php in the places that the int_val check takes place.

[kmike]

Well, as the creator of the multi group column support for 0.9.5 I can honestly say that it was a terrible copy/paste hack. 0.9.7 has them implemented properly, and the resulting index takes much less space which is always good from the I/O standpoint.