Sphinx Search

[Mb81]

I got some large forums and i try to use Sphinx now.

Problem 1.)

using config file '/usr/local/sphinx/etc/sphinx.conf'...
WARNING: index 'vbpost': failed to preload schema and docinfos - NOT SERVING
WARNING: index 'vbpostindex': failed to preload schema and docinfos - NOT SERVING
WARNING: index 'vbthreadindex': failed to preload schema and docinfos - NOT SERVING
WARNING: index 'vbthreadindexdelta': failed to preload schema and docinfos - NOT SERVING
WARNING: index 'vbfulltext': no such local index 'vbpost' - NOT SERVING
WARNING: index 'vbfulltext': no such local index 'vbpostindex' - NOT SERVING
WARNING: index 'vbfulltext': no valid local/remote indexes in distributed index - NOT SERVING
WARNING: index 'vbfulltextthread': no such local index 'vbthreadindex' - NOT SERVING
WARNING: index 'vbfulltextthread': no such local index 'vbthreadindexdelta' - NOT SERVING
WARNING: index 'vbfulltextthread': no valid local/remote indexes in distributed index - NOT SERVING

The stuff was build, still i get this. What todo ?

Problem 2.)
Can it be used for multiple forums on the same server ?

[eoc_Jason]

You probably have a configuration error, I would double-check the conf file and compare to the one in the post. One little mistake and the whole thing breaks (took me forever to find the one line I missed).

I don't see why you couldn't use it for multiple forums, just create more things in the config file with different names connecting to the different databases.

[Mb81]

Here it is. I don?t see any mistake.
It would be really nice if someone could confirm it. Thanks alot.

#
# sphinx configuration file sample
#

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

source src1
{
        type                            = mysql
        strip_html                      = 0
        sql_host                        = localhost
        sql_user                        = root
        sql_pass                        = xx
        sql_db                          = xx
        sql_port                        = 3306


        sql_query_pre           = REPLACE INTO sphinx.sphinx_counter SELECT 1, MAX(postid) FROM post
        sql_query_range         = SELECT MIN(postid), MAX(postid) FROM post
        sql_range_step          = 1000
        sql_query = \
                SELECT postid, forumid, post.threadid as threadid, IF(post.userid=0,99999999,post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, post.title, pagetext, post.dateline \
                FROM post \
                INNER JOIN thread AS thread ON(thread.threadid = post.threadid) \
                WHERE post.visible = 1 AND postid >= $start AND postid <= $end \
                        AND postid <= (SELECT max_doc_id FROM sphinx.sphinx_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 sphinx.sphinx_counter WHERE counter_id = 1 ), MAX(postid) FROM post
        sql_range_step          = 1000
        sql_query = \
                SELECT postid, forumid, post.threadid as threadid, IF(post.userid=0,99999999,post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, post.title, pagetext, post.dateline \
                FROM post \
                INNER JOIN thread AS thread ON(thread.threadid = post.threadid) \
                WHERE post.visible = 1 AND postid >= $start AND postid <= $end \
                        AND postid > ( SELECT max_doc_id FROM sphinx.sphinx_counter WHERE counter_id = 1 );
}


source src3
{
        type                            = mysql
        strip_html                      = 0
        sql_host                        = localhost
        sql_user                        = root
        sql_pass                        = xx
        sql_db                          = xx
        sql_port                        = 3306


        sql_query_pre           = REPLACE INTO sphinx.sphinx_counter SELECT 2, MAX(threadid) FROM thread
        sql_query_range         = SELECT MIN(threadid), MAX(threadid) FROM 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 thread \
                WHERE visible = 1 AND threadid >= $start AND threadid <= $end \
                        AND threadid <= ( SELECT max_doc_id FROM sphinx.sphinx_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 sphinx.sphinx_counter WHERE counter_id = 2 ), MAX(threadid) FROM 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 thread \
                WHERE visible = 1 AND threadid >= $start AND threadid <= $end \
                        AND threadid > ( SELECT max_doc_id FROM sphinx.sphinx_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/sphinx/vbpost
        docinfo                 = extern
        morphology              = none
        stopwords               =
        min_word_len            = 4
        charset_type            = sbcs
}

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

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

index vbthreadindexdelta
{
        source                  = src4
        path                    = /var/sphinx/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/run/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
}

[eoc_Jason]

Do you have sphinx as a separate DB? I just made it a table within my forum to keep everything consolidated (since the tables don't hold much data anyhow).

I would double check the sphinx table & field names. Like on mine they are 'sph_counter', not 'sphinx_counter', which is an inconsistency within the documentation and supplied example stuff. Other than that, the code looks okay to me.

[Mb81]

Originally Posted by eoc_Jason

Do you have sphinx as a separate DB? I just made it a table within my forum to keep everything consolidated (since the tables don't hold much data anyhow).

I would double check the sphinx table & field names. Like on mine they are 'sph_counter', not 'sphinx_counter', which is an inconsistency within the documentation and supplied example stuff. Other than that, the code looks okay to me.

I checked it; it seems all fine. I use sphinx.sphinx_counter; that shouldnt make a difference.

[mute]

Did anyone else upgrade to php 5.2.1 and have their sphinx install break? I haven't had time to look into it yet, but mine fails to return results and I'm getting a:

Query '' retrieved -2114543231 of 1 matches in -2147483.222 sec.

Heh.

Hm. It is definitely something PHP 5.2.1 related. I went back to 5.2.0 and it is working just fine. I guess I'll have to look at the changelog in the morning to see if I can figure out what is wrong.

[orban]

Yeah I had that, and recreated all my indices and restarted searchd and then it worked ;/ Really wierd tho.

[mute]

Originally Posted by orban

Yeah I had that, and recreated all my indices and restarted searchd and then it worked ;/ Really wierd tho.

I'm going to try it again, but I had already done that before I downgraded. I just got Andrew to send me a CVS snapshot so I'm going to try that as well, as I've found a segfault in the commandline client that is rather irritating as well.

Originally Posted by mute

I'm going to try it again, but I had already done that before I downgraded. I just got Andrew to send me a CVS snapshot so I'm going to try that as well, as I've found a segfault in the commandline client that is rather irritating as well.

So I upgraded to the new sphinx CVS snapshot, stopped searchd, nuked all my indexes, rebuilt them all and tried to search with php 5.2.1, and it's still broken. php 5.2.0 works just fine, so there is something going on. I've read the changelogs and nothing really stood out so I'm stumped.

I made sure I upgraded my sphinxapi.php file when I upgraded too, and that didn't do it, so it is either that or something in your sphinx.php that is breaking, but I haven't been able to figure out what just yet.

Is anyone else running 5.2.1?

[orban]

I'm running 5.2.1 :/

It didn't work but after recreating all indices and restarting searchd it suddenly did. I didn't have to change any other files.

[mute]

Originally Posted by orban

I'm running 5.2.1 :/

It didn't work but after recreating all indices and restarting searchd it suddenly did. I didn't have to change any other files.

Hmm. Boo. I've done that twice already, I wonder what else it could be? I have multiple webservers setup, and with the same exact settings, the 5.2.0 webservers work and the 5.2.1 webserver does not.

Update: I'm working with the Sphinx author on a fix. It's a 64-bit/PHP 5.2.1 + sphinxapi bug.