Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #436  
Old 07 Nov 2007, 09:52
Spinball's Avatar
Spinball Spinball is offline
 
Join Date: Feb 2002
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)
__________________
AVForums.com the UK's largest audio visual home consumer electronics forums. Now using Xenforo because vB5 is not suitable for large forums and vB3 and vB4 are no longer being developed.
Reply With Quote
  #437  
Old 07 Nov 2007, 10:24
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Most likely something to do with Similar Threads. You can try disabling this in your vBulletin Options.
__________________
Marco van Herwaarden
Ex vBulletin.org Coordinator
Reply With Quote
  #438  
Old 07 Nov 2007, 10:50
Spinball's Avatar
Spinball Spinball is offline
 
Join Date: Feb 2002
If it is similar threads and I want to keep the functionality, is there a work around?
__________________
AVForums.com the UK's largest audio visual home consumer electronics forums. Now using Xenforo because vB5 is not suitable for large forums and vB3 and vB4 are no longer being developed.
Reply With Quote
  #439  
Old 07 Nov 2007, 10:57
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Then you shold not remove the index.
__________________
Marco van Herwaarden
Ex vBulletin.org Coordinator
Reply With Quote
  #440  
Old 07 Nov 2007, 17:24
weeno weeno is offline
 
Join Date: Jan 2002
Originally Posted by Marco van Herwaarden View Post
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
Reply With Quote
  #441  
Old 08 Nov 2007, 19:13
eoc_Jason's Avatar
eoc_Jason eoc_Jason is offline
 
Join Date: Dec 2001
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.
__________________
My Site: EXTREME Overclocking

Do not PM me with your iTrader problems or asking for the code. I will just delete your PM without reading it.

Last edited by eoc_Jason; 09 Nov 2007 at 14:43. Reason: Auto-Merged DoublePost
Reply With Quote
  #442  
Old 12 Nov 2007, 03:37
weeno weeno is offline
 
Join Date: Jan 2002
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
Reply With Quote
  #443  
Old 12 Nov 2007, 07:13
amcd amcd is offline
 
Join Date: Oct 2004
never had it quit on its own.
__________________
eXBii.com - Indian community
no XB no fun know XB know fun !
Reply With Quote
  #444  
Old 12 Nov 2007, 14:55
eoc_Jason's Avatar
eoc_Jason eoc_Jason is offline
 
Join Date: Dec 2001
Originally Posted by weeno View Post
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.
__________________
My Site: EXTREME Overclocking

Do not PM me with your iTrader problems or asking for the code. I will just delete your PM without reading it.
Reply With Quote
  #445  
Old 16 Nov 2007, 09:03
weeno weeno is offline
 
Join Date: Jan 2002
Originally Posted by eoc_Jason View Post
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
Reply With Quote
  #446  
Old 16 Nov 2007, 09:19
UK Jimbo's Avatar
UK Jimbo UK Jimbo is offline
 
Join Date: Sep 2002
Originally Posted by weeno View Post
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.
__________________
administrator: visordown.com
Reply With Quote
  #447  
Old 26 Nov 2007, 01:45
TheComputerGuy's Avatar
TheComputerGuy TheComputerGuy is offline
 
Join Date: Oct 2001
Real name: Joe
i wonder how this will work on a shared server enviroment.
__________________
I'll buy your forum!
Reply With Quote
  #448  
Old 26 Nov 2007, 09:37
UK Jimbo's Avatar
UK Jimbo UK Jimbo is offline
 
Join Date: Sep 2002
Originally Posted by TheComputerGuy View Post
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.
__________________
administrator: visordown.com
Reply With Quote
  #449  
Old 26 Nov 2007, 10:15
amcd amcd is offline
 
Join Date: Oct 2004
methinks he was talking about one server shared among many websites
__________________
eXBii.com - Indian community
no XB no fun know XB know fun !
Reply With Quote
  #450  
Old 26 Nov 2007, 10:23
UK Jimbo's Avatar
UK Jimbo UK Jimbo is offline
 
Join Date: Sep 2002
Originally Posted by amcd View Post
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
__________________
administrator: visordown.com
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 12:02.

Layout Options | Width: Wide Color: