Sphinx Search

[Neil Lock]

Thanx guys, will take a look at the api stuff but on initial inspection get error messages such as
Query failed: searchd error: index 'vbthreadindex': incompatible schemas: non-virtual attributes count mismatch: 4 in schema '/var/data/vbthreadindex', 5 in schema '/var/data/vbpost'.

if this means anything that can be relayed then please mention otherwise gonna spend the afternoon reading and playing...

as for 3.6 we hope to upgrade soon, but i havent ascertained what they have done to this upgrade (ie how much more 'beef' will our front ends need for this version!)

cheers

[mute]

If i were you i'd verify that your sphinx.conf matches the example.

We upgraded to 3.6.2 about 2 weeks ago and it went pretty well, as far as our front end web traffic goes, I don't really notice a difference in terms of load

[orban]

Are you combining wrong indexes together?

[Neil Lock]

Originally Posted by mute

If i were you i'd verify that your sphinx.conf matches the example.

We upgraded to 3.6.2 about 2 weeks ago and it went pretty well, as far as our front end web traffic goes, I don't really notice a difference in terms of load

well thats good news - the upgrade from 3 to 3.5 really hit us hard! we had our db box well tuned for 3 and then they up and move it to the front ends...v. annoying

[ALanJay]

Neil,

The other thing to check is to use the test.php script to check that you can search the files correctly. If that works then you know you need to tweak the search.php code - it is possible as I have tweaked it all the way back to 3.0.x Though I doubt there will be much to change as the major changes occured in the the chnage from 3.0 to 3.5

[Neil Lock]

Hey all, well i have been playing around with no real success with test.php which i can only assume means that my conf file is messed up somewhere, if anyone could take a few secs to see whether I have made any glaring errors

#
# sphinx configuration file sample
#

#############################################################################
## data source definition
#############################################################################

source src1
{
	type				= mysql
	strip_html			= 0
	sql_host			= ******
	sql_user			= vbupgradeforum
	sql_pass			= ******
	sql_db				= vb_upgrade_forums
	sql_port			= 3306


	sql_query_pre		= REPLACE INTO sph_counter SELECT 1, MAX(postid) FROM vb_post

	sql_query_range		= SELECT MIN(postid), MAX(postid) FROM vb_post
	sql_range_step		= 1000
	sql_query = \
		SELECT postid, forumid, vb_post.threadid as threadid, IF(vb_post.userid=0,99999999,vb_post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, vb_post.title, pagetext, vb_post.dateline \
		FROM vb_post \
		INNER JOIN vb_thread AS thread ON(thread.threadid = vb_post.threadid) \
		WHERE vb_post.visible = 1 AND postid >= $start AND postid <= $end \
			AND postid <= ( SELECT max_doc_id FROM sph_counter WHERE counter_id = 1 );

	sql_group_column	= forumid
	sql_group_column	= threadid
	sql_group_column	= userid
	sql_group_column	= postuserid
	sql_date_column		= dateline

	sql_query_post		=
}

source src2 : src1
{

	sql_query_pre		= 
	sql_query_range		= SELECT ( SELECT max_doc_id FROM sph_counter WHERE counter_id = 1 ), MAX(postid) FROM vb_post
	sql_range_step		= 1000
	sql_query = \
		SELECT postid, forumid, vb_post.threadid as threadid, IF(vb_post.userid=0,99999999,vb_post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, vb_post.title, pagetext, vb_post.dateline \
		FROM vb_post \
		INNER JOIN vb_thread AS thread ON(thread.threadid = vb_post.threadid) \
		WHERE vb_post.visible = 1 AND postid >= $start AND postid <= $end \
			AND postid > ( SELECT max_doc_id FROM sph_counter WHERE counter_id = 1 );
}
	

source src3
{
	type				= mysql
	strip_html			= 0
	sql_host			= *****
	sql_user			= vbupgradeforum
	sql_pass			= *****
	sql_db				= vb_upgrade_forums
	sql_port			= 3306


	sql_query_pre		= REPLACE INTO sph_counter SELECT 2, MAX(threadid) FROM vb_thread

	sql_query_range		= SELECT MIN(threadid), MAX(threadid) FROM vb_thread
	sql_range_step		= 1000
	sql_query = \
		SELECT threadid, forumid, title, IF(postuserid=0,99999999,postuserid) AS postuserid, IF(firstpostid=0,99999999,firstpostid) as firstpostid, lastpost \
		FROM vb_thread \
		WHERE visible = 1 AND threadid >= $start AND threadid <= $end \
			AND threadid <= ( SELECT max_doc_id FROM sph_counter WHERE counter_id = 2 );

	sql_group_column	= forumid
	sql_group_column	= postuserid
	sql_group_column	= firstpostid
	sql_date_column		= lastpost

	sql_query_post		=
}

source src4 : src3
{
	sql_query_pre		= 
	sql_query_range		= SELECT ( SELECT max_doc_id FROM sph_counter WHERE counter_id = 2 ), MAX(threadid) FROM vb_thread
	sql_range_step		= 1000
	sql_query = \
		SELECT threadid, forumid, title, IF(postuserid=0,99999999,postuserid) AS postuserid, IF(firstpostid=0,99999999,firstpostid) as firstpostid, lastpost \
		FROM vb_thread \
		WHERE visible = 1 AND threadid >= $start AND threadid <= $end \
			AND threadid > ( SELECT max_doc_id FROM sph_counter WHERE counter_id = 2 );
}

#############################################################################
## index definition
#############################################################################

# local index example
#
# this is an index which is stored locally in the filesystem
# all indexing-time options (such as morphology and charsets) belong to the index
index vbpost
{
	source			= src1
	path			= /var/data/vbpost
	docinfo			= extern
	morphology		= none
	stopwords		=
	min_word_len		= 4
	charset_type		= sbcs
}

index vbpostindex
{
	source			= src2
	path			= /var/data/vbpostindex
        docinfo                 = extern
	morphology		= none
	stopwords		=
	min_word_len		= 4
	charset_type		= sbcs
}

index vbthreadindex
{
	source			= src3
	path			= /var/data/vbthreadindex
        docinfo                 = extern
	morphology		= none
	stopwords		=
	min_word_len		= 4
	charset_type		= sbcs
}

index vbthreadindexdelta
{
	source			= src4
	path			= /var/data/vbthreadindexdelta
        docinfo                 = extern
	morphology		= none
	stopwords		=
	min_word_len		= 4
	charset_type		= sbcs
}

index vbfulltext
{
        type                            = distributed
        local                           = vbpost
        local                           = vbpostindex
}

index vbfulltextthread
{
        type                            = distributed
        local                           = vbthreadindex
        local                           = vbthreadindexdelta
}

#############################################################################
## indexer settings
#############################################################################

indexer
{
	# memory limit
	# can be specified in bytes, kilobytes (mem_limit=1000K) or megabytes (mem_limit=10M)
	# will grow if set unacceptably low
	# will warn if set too low, hurting the performance
	# optional, default is 32M
	mem_limit			= 64M
}

#############################################################################
## searchd settings
#############################################################################

searchd
{
	# port on which search daemon will listen
	port				= 3312


	# log file
	# searchd run info is logged here
	log					= /var/log/searchd.log


	# query log file
	# all the search queries are logged here
	query_log			= /var/log/query.log


	# client read timeout, seconds
	read_timeout		= 5


	# maximum amount of children to fork
	# useful to control server load
	max_children		= 30


	# a file which will contain searchd process ID
	# used for different external automation scripts
	# MUST be present
	pid_file			= /var/log/searchd.pid


	# maximum amount of matches this daemon would retrieve from each index
	# and serve to client
	#
	# this parameter affects per-client memory usage slightly (16 bytes per match)
	# and CPU usage in match sorting phase; so blindly raising it to 1 million
	# is definitely NOT recommended
	#
	# default is 1000 (just like with Google)
	max_matches			= 1500
}

# --eof--

my tables use the extension vb_ and i went through and modified all the table names as per instructs. anything glaringly obvious>?

Neil

[amcd]

i have a problem

Warning: assert() [function.assert]: Assertion failed in /sphinxapi.php on line 284
Query '' retrieved 0 of 0 matches in 0.000 sec.
Query stats:
'barnacles' found 11 times in 11 documents
??\ys?F??[??w? ?B?B??h??A?dYv?ϲ???ĕ?b???A? 
?????߯{fp}?W???A???O??_y?gi\AX??S??ԃC|= ??Dt???"??&?9Xn?ʬ?+?Iܤ??xx?!?l?Y?B?$w?z?] ?>?X???? ?
З??$�???????? ?
[(=??O????�?M& 
$?ѳ? E???%?r?֔?i\;?B?L???GS�???>i%tW3????)T?e?W?,??S8?_?=&?%G?\???u??D???/?;k?L? a???+ I?]??,?=}?/?%B^?? �h?K?4 }? ?_?g??NM??|?)?w???mxG?^6??Oƻ??m?$?!???tG????^a??*??K;???Zo?F-絶?ͮ?Â?�w?K??CX??G??*D�c?�?M%??�??Y5Ij?O?a'??E??<?&۵?????]?8w?)??N҅]y?V,?tm?W7????o�??y9M?o2???>m?yO???AN@�!a?Oh?j?'?54@? ??l)?yam???0?6??V7s??]??`?R&?,?????MFkF? ?A4K :?8? ???H??�?J^-??????A?????????b?kA???!;wG#?;y?????F??u?C?'???�?.???"qm???3??w??؅X??d?qK??f"At???0%??i L?
?3B 
#^?g fG?4????C??C?u_to??@?�c?ډ9?_$??ƈ 

and the gibberish continues for another page or two



to reproduce this error, go to http://www.xboard.us/bbb/forumdisplay.php?f=7 and click on the 'search this forum' link. in the dropdown which opens, type 'barnacles' in the search term field and select 'show results as posts' and click go

happens with other forums and other search terms also, but not always. internet explorer and firefox show only a blank page. to see the error, use opera.

[orban]

Can you try to use the test.php and/or the "search" command tool?

[amcd]

search works 90% of the time, so its not a total failure

search command line tool gives the following output

search -c /usr/local/etc/sphinx.conf barnacles
Sphinx 0.9.7-RC1
Copyright (c) 2001-2006, Andrew Aksyonoff

index 'postmain': query 'barnacles ': returned 11 matches of 11 total in 0.000 sec

displaying matches:
1. document=19361, weight=1, forumid=4, threadid=169, userid=10, postuserid=219, dateline=Thu Nov  4 20:03:03 2004
2. document=19396, weight=1, forumid=4, threadid=169, userid=12, postuserid=219, dateline=Thu Nov  4 21:07:51 2004
3. document=52950, weight=1, forumid=4, threadid=1239, userid=10430, postuserid=10430, dateline=Thu Dec  9 10:29:54 2004
4. document=836679, weight=1, forumid=4, threadid=13920, userid=477, postuserid=27439, dateline=Thu Jun 30 05:07:27 2005
5. document=1629825, weight=1, forumid=4, threadid=370, userid=477, postuserid=6, dateline=Wed Dec 14 02:03:22 2005
6. document=1788408, weight=1, forumid=4, threadid=36177, userid=72112, postuserid=1061, dateline=Wed Jan 18 04:48:09 2006
7. document=1925410, weight=1, forumid=4, threadid=38680, userid=477, postuserid=35714, dateline=Mon Feb 13 08:46:41 2006
8. document=2000574, weight=1, forumid=4, threadid=80294, userid=477, postuserid=1717, dateline=Sun Feb 26 12:33:41 2006
9. document=2000585, weight=1, forumid=4, threadid=80294, userid=21423, postuserid=1717, dateline=Sun Feb 26 12:35:18 2006
10. document=2921752, weight=1, forumid=10, threadid=69489, userid=477, postuserid=477, dateline=Thu Jul 13 19:51:01 2006
11. document=3371748, weight=1, forumid=13, threadid=81940, userid=103694, postuserid=103694, dateline=Sun Sep 10 14:59:51 2006

words:
1. 'barnacles': 11 documents, 11 hits

index 'postdelta': query 'barnacles ': returned 0 matches of 0 total in 0.000 sec
index 'threadmain': query 'barnacles ': returned 0 matches of 0 total in 0.000 sec
index 'threaddelta': query 'barnacles ': returned 0 matches of 0 total in 0.000 sec

[Neil Lock]

Finally got it to work,

Now I have another question, I want to run this from a slave database ie grab the query data but the sphinx requires the REPLACE INTO which obv cant run on a slave instance so my question is this - is it possible to hook this up to run on the master for the replaces and the slave for the other queries. I intend on going and playing but wondered before hand whether anyone had a solution?

Thanks

Neil