[Spinball]

I just deleted the fulltext indexes from post and thread and got the following error when undeleting a thread:

Database error in vBulletin 3.6.8:

Invalid SQL:

SELECT thread.threadid, MATCH(thread.title) AGAINST ('Premium Pack £190') AS score
FROM thread AS thread

WHERE MATCH(thread.title) AGAINST ('Premium Pack £190')
AND thread.open <> 10
AND thread.threadid <> 528589

LIMIT 5;

MySQL Error : Can't find FULLTEXT index matching the column list
Error Number : 1191
Date : Wednesday, November 7th 2007 @ 09:50:56 AM
Script : http://www.avforums.com/forums/posti...hread&t=528589
Referrer : http://www.avforums.com/forums/posti...hread&t=528589
IP Address : 82.37.224.75
Username : Stuart Wright
Classname : vB_Database

HELP!

(I recreated the index for now)

[Marco van Herwaarden]

Most likely something to do with Similar Threads. You can try disabling this in your vBulletin Options.

[Spinball]

If it is similar threads and I want to keep the functionality, is there a work around?

[Marco van Herwaarden]

Then you shold not remove the index.

[weeno]

Originally Posted by Marco van Herwaarden

Then you shold not remove the index.

I suppose this answers a question I had about similar threads. I guess similar threads uses full text search. Anyone interested in hacking it to use sphix?

arn

[eoc_Jason]

You know I was thinking about how to add some of the search features back on the search page.

The current scenario is a basic thread & post index, and then doing delta's of those two. However doing a thread full reindex doesn't take that long (20 seconds or less for me), and the thread table holds very relevant info such as number of replies, number of views, etc..

That's my first goal for this upcoming weekend project. Then I'm going to work on phrases & boolean since sphinx supports that. Google does phrases by using quotes, and so I figure doing a little regular expression matching to set a flag on the search time would work well for sphinx. Also I think I might have to do some character conversion to go between the common boolean options to the sphinx ones.

------------------

To answer someone's question above, usually vB will start to take a dive in search performance after about 2 million posts. It really depends on the amount of content in each post.

The search issue arises like this... A person does a search, so mysql is running that query. In the mean time people can view posts on the forum as usual. However, as soon as a person posts a new thread or reply, that INSERT has to wait until the search is complete. A side effect of that INSERT is that every SELECT afterwards (people just browsing the forum) also has to wait until the INSERT is complete (which is waiting on the search). So queries really start to build up in queue as people are waiting. And if your users are like most that are impatient, if the page doesn't load in a couple seconds they click refresh, which sends ANOTHER query to the DB.... All this has a downward spiral as memory is being used up and the server starts to swap to disk. By then your server is probably completely unresponsive.

----------------

As for the "new posts", that is a simple search based on a 'date' column, it has nothing to do with the post content searching. The relevant fields should already have indexes and it should run pretty good. It can never really be cached by mysql though since the date is different for each person and the results are constantly changing because of replies and such.

---------------

Don't even get me started about the vB search code that can remove valid results due to the extra vB weighting and such.

---------------
Okay, that was a little off track... hehe... Anyhow, I'll try to post whatever new code I come across. I rely on Sphinx to keep my forum search useable, so I won't be going anywhere.

--------------- Added 09 Nov 2007 at 14:43 ---------------

Just realized the thread full re-index thing won't work as I initially hoped. Searching post contents uses only the post index which includes some thread info (like replies). Though I think I can write some secondary cleanup code to re-search and gather realtime info after the sphinx query has run.

[weeno]

my searchd quit on its own today for unclear reasons. I restarted it manually.

How are you guys keeping it running? And why would it quit?

arn

[amcd]

never had it quit on its own.

[eoc_Jason]

Originally Posted by weeno

my searchd quit on its own today for unclear reasons. I restarted it manually.

How are you guys keeping it running? And why would it quit?

It has never died on my forum, but I have had rare instances where it does die every now and then on another forum that I help a guy out with. He is running x86_64, I'm still 32bit. Unfortunatly I could never find any logs or reason for it to die, it was just totally random it seemed.

On my site I use SIM from rfxnetworks (it does service monitoring and will restart a downed service). I just coded a custom addition for searchd. But I've never been notified of it going down (on my site). You could just as easly write a little cron script doing a 'ps aux | grep searchd' type thing... Or maybe something with inittab?


Unfortunatly I didn't get to work on any coding for sphinx this weekend due to some unexpected happenings. I have all the logic worked out in my head I just need to convert that into useable code.

[weeno]

Originally Posted by eoc_Jason

It has never died on my forum, but I have had rare instances where it does die every now and then on another forum that I help a guy out with. He is running x86_64, I'm still 32bit. Unfortunatly I could never find any logs or reason for it to die, it was just totally random it seemed.

On my site I use SIM from rfxnetworks (it does service monitoring and will restart a downed service). I just coded a custom addition for searchd. But I've never been notified of it going down (on my site). You could just as easly write a little cron script doing a 'ps aux | grep searchd' type thing... Or maybe something with inittab?

here's a short shell script I pieced together based on some of your code

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

cron'd it every minute to keep searchd running. Just checks to see if searchd is running, if not, it starts it.

arn

[UK Jimbo]

Originally Posted by weeno

cron'd it every minute to keep searchd running. Just checks to see if searchd is running, if not, it starts it.

What he said. This is similar to what I have.

I've seen it die for no reason two or three times in the last eight months on a busy box.

[TheComputerGuy]

i wonder how this will work on a shared server enviroment.

[UK Jimbo]

Originally Posted by TheComputerGuy

i wonder how this will work on a shared server enviroment.

It depends on how you want to run it. Sphinx runs as a TCP based daemon so it's possible to have more than one client (vBulletin instance) connecting in to the same sphinx daemon.

It's also possible to build the index in one place and then copy it out to a number of servers all running the daemon. This can be handy if you don't want to place excessive load on the servers while replicating.

[amcd]

methinks he was talking about one server shared among many websites

[UK Jimbo]

Originally Posted by amcd

methinks he was talking about one server shared among many websites

Now that I've had a coffee and woken up more I think so too

I think I read "distributed" or something