Sphinx Search

[Kevlar]

This post gives some insight to what they did to eliminate locking...
http://www.vbulletin.com/forum/entry...nges-in-vB-4-0

You can read it if you want, but basically it allows changing the table from myISAM to InnoDB so it is no longer table locking but row locking instead (if I understand it correctly).

[alexi]

Originally Posted by eoc_Jason

I dunno what the average size vB forum is... But you would think if you are selling a forum product, you *want* your clients to be successful. If that means they are going to hit a brick wall after a few million posts in their DB, then you are severely limiting your market. There are quite a few sites that are running very old versions of VB, opting to just patch and write custom code on their own then wait for jelsoft to come up with the official features.

FYI, Searchlight base cost is $2,000 USD...

I haven't had a chance to install 4.0 yet, but I'll check through the code to see what they did to eliminate the table locking. It's still moot though because if search results don't return within a second then people start getting click-happy or give up (both unacceptable).

Imagine if Google took 5 minutes to return every search you make? How many people would be googling?

It is a lot of money but in terms of happy customers it's one of the best investments we ever made. I also looked at it in terms of how much time we would have to spend producing a home grown sphinx solution and then the fact that it would have a lot of the glitches described in this thread. When I added it all up searchlight made sense. I do understand that a lot of boards, even big boards with high traffic run on donations and it's just not affordable.

[RedWingFan]

Originally Posted by Kevlar

vB4 seems unfinished... but then again, most software the comes out these days is far from finished.

Having written my own web apps in PHP (and a few in JS/AJAX), I can agree that just about any software out there is a "work in progress". Especially mine! However, there are some minor but very noticeable quirks in the vB4 interface that should never have gotten past the beta stage. I will say that functionally, the whole package does work...but it's minor interface issues that make using the forum a pain.

One that irks me no end is that if you use Quick Reply, you can't tab once over to a "post" button anymore. When I'm replying to dozens of posts per day, I tend to stick to a keyboard, and I use all of the shortcuts. Having to hop constantly over to a mouse to click one button is a huge oversight IMHO.

Most of what I found are minor quirks like that. Having said that, though, I've already found some usable plugins that replace what I have in my 3.x installations.

Originally Posted by eoc_Jason

I dunno what the average size vB forum is... But you would think if you are selling a forum product, you *want* your clients to be successful. If that means they are going to hit a brick wall after a few million posts in their DB, then you are severely limiting your market.

I may have said this earlier, but I do know search appears to be more modular now. IOW, even though Sphinx is not built in, it appears that you can now plug in a module for a search and it would appear in the search engine selector (under "dbsearch") in admincp.

The search files themselves are in a new location, from what I can tell, and there are also new tables in the database for search. So, I know it has been somewhat reworked but, like you say, it's still slow, and...

Originally Posted by eoc_Jason

Imagine if Google took 5 minutes to return every search you make? How many people would be googling?

I don't think that search speeds of 5-10 seconds are all that bad--as a long-time forum user (as many of our members are), I have come to expect that over the dozen or so years I've been using online forums. BUT...when vB takes 20, 30, sometimes 40 seconds to retrieve an often-used word in our forum, and there's a tool (called Sphinx) that returns same in 0.4 seconds...what choice would I make? Yep, I'd find any way I could to get Sphinx running.

Beside speed, I've mentioned elsewhere in these forums (even in this thread perhaps?) that I can configure what Sphinx indexes. With MySQL, I'm stuck with whatever is set up in the my.cnf file. I could reconfigure it, but then I'm doing customizations on MySQL for only one application (vB) and possibly hampering the performance of others. With Sphinx, our members are happy simply because they can now search for three-letter terms (where previously, it was four), and I've eliminated a lot of words in the stopwords file so that some common music-based titles can now be searched. I'm actually a bona-fide hero now because members can search for "Who's Next"!

Originally Posted by eoc_Jason

FYI, Searchlight base cost is $2,000 USD...

Ummm, no thanks. We'd pay a couple hundred bucks, but for something that costs almost what hosting does for a year, and far more than the vB product itself...? We can't justify that, not on our donation-based (and advertising-free) funding. Might be justifiable for forums that are far busier than ours, but we can barely scrape up enough donations to keep going for a year at a time...

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

Originally Posted by Kevlar

This post gives some insight to what they did to eliminate locking...
http://www.vbulletin.com/forum/entry...nges-in-vB-4-0

You can read it if you want, but basically it allows changing the table from myISAM to InnoDB so it is no longer table locking but row locking instead (if I understand it correctly).

I just read it, and you read it correctly. Although, one thing they overlooked in their description of vB3 is that we had two search choices: built-in vB search, and fulltext. We already had InnoDB table types set up with vB's built-in search, so this is nothing new to us. In fact, it goes back even further when we changed hosts three or four years ago and we hit a brick wall in performance; this was also back when fulltext was not yet an option in vB3.

In a way, it seems vB searching has taken a step backward. *sigh* We're now back to relying on a built-in search.

The main problem is that MySQL just doesn't scale well! Sphinx helps because it bypasses MySQL entirely during the search process. Sphinx isn't that hard to set up, even without root access.

[eoc_Jason]

Switching to InnoDB is not really a viable option... Can you imagine how many more threads there will be specifically for InnoDB tuning? As if the people whom didn't have a clue about my.cnf before are going to magically make this work... lol.

I spent yesterday installing sphinx (again) on my test server, not sure how it got uninstalled... Anyhow I'm using 0.9.9 (released Dec 2, 09)... I also installed the latest 3.8.x vBulletin....

After looking over the search.php file, it seems there's quite a few hooks available, so I'm going to try and code using those instead of having to edit the search.php file directly. I'm going to code this from scratch rather than try to patchwork more of Orban's code...

My question to you guys, what version of vBulletin are you running? For security reasons you can PM me directly if you don't feel comfortable posting in the thread, or you can just say 3.6, 3.7, 3.8, 4.0 and skip the minor version. Also if you could tell me how many threads/posts your forum has that would be helpful too.

I'm not 100% sure how I will release this yet (free vs paid)... Once I get closer to a final working product I'll probably ask a couple of you guys to help beta test this for me. I do not plan on even looking at 4.0 for a while... People just have too many negative things to say right now and there are still too many bugs....

[mute]

Before IB released info on 4.0, they said Sphinx was in the works. Later, when they came out and said "oh, no sorry, no sphinx" I called them out on it and finally got a more concrete answer. From what I've been able to gather, they DO have a Sphinx implementation for 4.0, but they aren't releasing it because they don't consider it finished.

My guess is that they aren't happy with the "search lag" associated with using Delta indexes, and that's why they are holding back. There are realtime updates for Sphinx in the pipeline, but it's not stable yet. Personally, I think it's really stupid that they are probably holding big boards back from upgrading because of the lag between indexes, which is an easy trade off compared to the terrible search performance of the default search.

We're running vb 3.6+backported security fixes with ~61 million posts and ~3.1 million threads. Our Sphinx implementation is based around the one from this thread, but I never got around to finishing it so that "Find all posts/threads by user" searches go to Sphinx. I'd love to have that functionality, but I don't really have the time to do it myself. We do a delta index every 5 minutes, and our users are a lot happier than they were years ago using the fulltext search. Granted, the Sphinx search that most of us are using is sort of crappy and doesn't take advantage of boolean operators and all the cool new stuff available in newer versions of Sphinx, but I'll take .5s search results over that stuff any day of the week.

Like most of you, we're not touching 4.0 with a 10 foot pole until they've figured out what they are doing and fix all of the performance issues 4.0 has, such as the search, or the hundreds of queries per page on the CMS side of things.

As for Searchlight, I like Floren and I think his product is probably great, but I'm not paying that either. Not because I don't have the money, but I just couldn't justify the expenditure with so little information about the product (I realize it's not final yet). Spending 10 times the cost of the vB license for a better search engine? Pass. I'll wait for someone in the community to work out a 4.0 version. Now that the search is full of hooks, you can hook in pre-search-query and return Sphinx results without a bunch of nasty file hacks. It's going to be a lot cleaner than it used to be. I think the only "difficult" part about the whole deal will be setting up Sphinx for people who are cpanel-junkies.

As for InnoDB, we've been running InnoDB on a handful tables for probably 5 years now, and don't have many real complaints about it, even with the drawbacks associated with it.

[kontrabass]

Originally Posted by eoc_Jason

My question to you guys, what version of vBulletin are you running? For security reasons you can PM me directly if you don't feel comfortable posting in the thread, or you can just say 3.6, 3.7, 3.8, 4.0 and skip the minor version. Also if you could tell me how many threads/posts your forum has that would be helpful too.

3.6

7.7M posts
540k threads

Good luck!

[eoc_Jason]

Thanks for the feedback mute. I would be interested to see what the vB team has come out with thus far (for 4.0) but I doubt they would release the file to anyone.

I've been writing things out and reading over the sphinx documentation with a fine tooth comb. Sphinx DOES support Boolean mode, along with like half a dozen other methods. One thing I plan on doing is offering ALL the features on the vB search page, so no template edit required there to strip things down or remove features... Also I'm going to try and implement the various search modes based on how a user inputs their search criteria... i.e. if they use quotes "This must match exactly" then it will use a phrase search.

I have an idea on how to keep near-time up-to-date with the reply #, views, last poster, etc... So that you wouldn't have to wait for your full re-index to update those values.

I also need to make note to utilize the "similar threads" search to go to sphinx. I do not know if there is a hook for that (I remember the code is burred somewhere), that *might* require a file edit, or disable similar threads if you want to delete your fulltext indexes.

[mute]

Originally Posted by eoc_Jason

Sphinx DOES support Boolean mode, along with like half a dozen other methods.

Yeah, what I meant to say is that the Sphinx implementation floating around doesn't support it despite having support in Sphinx. I don't think anyone is going to complain if there are some file edits necessary to have 100% of the vB search functionality replicated on Sphinx. I surely would not

[RedWingFan]

Originally Posted by eoc_Jason

Switching to InnoDB is not really a viable option... Can you imagine how many more threads there will be specifically for InnoDB tuning? As if the people whom didn't have a clue about my.cnf before are going to magically make this work... lol.

A lot of us have already been using InnoDB for years, so it's nothing new. It is always accounted for when we submit to tweaking threads over at vb.com . At many hosts though, InnoDB is nowhere near optimized and, if you're on a shared account, you can't get MySQL tweaked anyway. Folks with boards that small really don't need InnoDB though. (Although I found with the old phpBB2, I did need to change a couple of tables to InnoDB since it was locking for a minute at a time.)

Originally Posted by eoc_Jason

I do not plan on even looking at 4.0 for a while... People just have too many negative things to say right now and there are still too many bugs....

Unfortunately I'm only interested in a 4.0 implementation--Sphinx is the only major reason now that we won't switch our boards over. The 3.x version works good enough for us. I'm at the "don't give a crap" point now with the forum owner. The members can like what they have with Sphinx, or just shut the **** up, IMHO.

Originally Posted by mute

My guess is that they aren't happy with the "search lag" associated with using Delta indexes, and that's why they are holding back. There are realtime updates for Sphinx in the pipeline, but it's not stable yet.

I run deltas every two minutes. The server barely feels it--the posts and threads index in less than a second. If you think about it, how often do you go searching for posts that were made in the last few minutes? Rarely at all. I couldn't care less if there are real time updates via Sphinx. Nobody has complained they can't find a post they made five minutes ago.

Originally Posted by mute

I think the only "difficult" part about the whole deal will be setting up Sphinx for people who are cpanel-junkies.

Good point. I won't even consider a host that uses that Cpanel crap. I've had to use a few for clients who had existing sites I was working on, and it just frustrated me having a lack of control over many things. Give me SSH and SFTP and that's all I need. I've done it that way for 13 years.

Originally Posted by eoc_Jason

I've been writing things out and reading over the sphinx documentation with a fine tooth comb. Sphinx DOES support Boolean mode, along with like half a dozen other methods. One thing I plan on doing is offering ALL the features on the vB search page, so no template edit required there to strip things down or remove features... Also I'm going to try and implement the various search modes based on how a user inputs their search criteria... i.e. if they use quotes "This must match exactly" then it will use a phrase search.

I noticed the same thing when I went over Sphinx's documentation a couple of months ago--there are so many options for both searching AND indexing that it seems a shame we are only able to use part of it. Phrase-based searching (with quotes), boolean, etc., would be a huge jump over what the default offers.

If it doesn't cripple anything, I would not mind beta testing any new search facilities that crop up here on our 3.x production forum. (Specs below, per your request. ) For the 4.0, since it is private, I'm willing to try anything at this point. The staff doesn't mind helping me out on the user end of things, and they seem to find quirks I can never generate on my own.

Our forum: 200,000 threads; 4.6 million posts; 22,000 members. Running vB 3.7.x but looking to upgrade to 3.8.latest since we got the license upgrade several weeks ago.

[mute]

At some point when I actually have a test 4.0 install running with our real dataset, I'm willing to help test as well. Then again, if it works with 1000 posts it's going to work with 60 million, so I'm not really that useful

I would really love an updated version that runs on 3.6 that takes advantage of all the Sphinx goodies, since I don't see us moving to 4.0 for close to a year. All of the custom code we've written has to be ported and tested, and being the lone admin on a site this big has my hands full a lot of the time.