Sphinx Search

[Mb81]

Still waiting for some help here. Thanks.

Originally Posted by 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
}

[raywjohnson]

Originally Posted by Mb81

Still waiting for some help here. Thanks.

Did you find a solution? I did a line by line comparison to my conf and did not find anything obvious.

You XXed out your db name (sql_db=xx) does that match sphinx.sphinx_counter. What I mean is, does your sql_db=sphinx ? (they should match or you can remove the sphinx. part and it should work as well)

Also, just as a suggestion, I would set up a mysql user other than root (i.e. sql_user = root).

-RayJ

[Mb81]

Originally Posted by raywjohnson

Did you find a solution? I did a line by line comparison to my conf and did not find anything obvious.

You XXed out your db name (sql_db=xx) does that match sphinx.sphinx_counter. What I mean is, does your sql_db=sphinx ? (they should match or you can remove the sphinx. part and it should work as well)

Also, just as a suggestion, I would set up a mysql user other than root (i.e. sql_user = root).

-RayJ

No, the forum is in another database. sphinx is just a database for the counter.
I try the user change; but that shouldn?t make any trouble.

[raywjohnson]

Originally Posted by Mb81

No, the forum is in another database. sphinx is just a database for the counter.
I try the user change; but that shouldn?t make any trouble.

Bummer, I was hoping it was something simple. As far as the user name goes, that just seems to be the default security advice that I run into. So I thought I would pass it on.

I put my counter in the same DB as vBulletin (I am lazy!).

My question is: Does your script have a "mysql_select_db()" command to access the "sphinx" counter DB? I am asking partially due to my own ignorance as I have not taken the time to really learn how vBulletin and Sphinx work together and I am not sure if the counter DB is automatically "selected" for you. (again with the lazy!)

-RayJ

[amcd]

Why do you want to complicate things by keeping the counter in a separate db? What is the issue with keeping it in the same db?

Even if that does not solve the problem, at least we all will know the problem is elsewhere.

[Mb81]

Here i go with sphinx counter in forum db:

debian:/usr/local/sphinx/bin# ./indexer --config /usr/local/sphinx/etc/sphinx.conf --rotate --all
Sphinx 0.9.7-RC2
Copyright (c) 2001-2006, Andrew Aksyonoff

using config file '/usr/local/sphinx/etc/sphinx.conf'...
indexing index 'vbpost'...
collected 6058520 docs, 1721.0 MB
sorted 173.1 Mhits, 100.0% done
total 6058520 docs, 1721024070 bytes
total 1107.264 sec, 1554303.57 bytes/sec, 5471.61 docs/sec
indexing index 'vbpostindex'...
collected 268 docs, 0.1 MB
sorted 0.0 Mhits, 100.0% done
total 268 docs, 67017 bytes
total 0.037 sec, 1802990.42 bytes/sec, 7210.13 docs/sec
indexing index 'vbthreadindex'...
WARNING: zero/NULL attribute 'lastpost' for document_id=159188, fixed up to 1
WARNING: zero/NULL attribute 'lastpost' for document_id=174376, fixed up to 1
WARNING: zero/NULL attribute 'lastpost' for document_id=188952, fixed up to 1
collected 144559 docs, 4.1 MB
sorted 0.4 Mhits, 100.0% done
total 144559 docs, 4113375 bytes
total 2.832 sec, 1452684.27 bytes/sec, 51052.62 docs/sec
indexing index 'vbthreadindexdelta'...
collected 0 docs, 0.0 MB
total 0 docs, 0 bytes
total 0.010 sec, 0.00 bytes/sec, 0.00 docs/sec
skipping index 'vbfulltext' (distributed indexes can not be directly indexed)...
skipping index 'vbfulltextthread' (distributed indexes can not be directly indexed)...
WARNING: failed to read pid_file '/var/run/searchd.pid'.
WARNING: indices NOT rotated.
debian:/usr/local/sphinx/bin# ./searchd --config /usr/local/sphinx/etc/sphinx.conf
Sphinx 0.9.7-RC2
Copyright (c) 2001-2006, Andrew Aksyonoff

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
FATAL: no valid indexes to serve
debian:/usr/local/sphinx/bin#

[raywjohnson]

Originally Posted by Mb81

Here i go with sphinx counter in forum db:
debian:/usr/local/sphinx/bin# ./indexer --config /usr/local/sphinx/etc/sphinx.conf --rotate --all
....
debian:/usr/local/sphinx/bin# ./searchd --config /usr/local/sphinx/etc/sphinx.conf
....

According to Orban

Originally Posted by orban

3. Indexing
Important: When searchd is running, add --rotate, if it's not running, don't add

So....

If searchd is already running
./indexer --config /usr/local/sphinx/etc/sphinx.conf --rotate --all

If searchd is NOT already running
./indexer --config /usr/local/sphinx/etc/sphinx.conf --all

I would delete all the indexes that were created under /var/sphinx/ first.

-RayJ

[Mb81]

Originally Posted by raywjohnson

According to Orban
So....

If searchd is already running
./indexer --config /usr/local/sphinx/etc/sphinx.conf --rotate --all

If searchd is NOT already running
./indexer --config /usr/local/sphinx/etc/sphinx.conf --all

I would delete all the indexes that were created under /var/sphinx/ first.

-RayJ

Thanks alot ! That was the problem. Thanks again.

Another question; what are recommended update settings ?

[eoc_Jason]

Just curious for people's indexer updates, how often do you do them and how to you manage the delta vs full?

Right now I have a cron job running every 5 minutes to do the delta files since they only take a few seconds. I do a full rebuild maybe once a week or so (after disabling the delta cronjob so it doesn't try to run at the same time).

I would like to automate the full rebuild, but changing the delta one would require a really ugly cron looking line. Alternatively I could have another script check the time and figure out which job to run and if a job is already running and all that.

Second, how do you guys rotate your searchd & query log files? The only way I have gotten it to do that is to kill searchd completely and restart it. It would be nice if there was a more graceful method.

Oh boy... found a little issue that might affect some people...

*If* you are running MySQL 4.0.x you can't do the sub-selects in the conf file. Not a *huge* deal as the code can be re-written (which I'm doing now). Yes I know I should be running a much newer version of mysql, but I have an app that uses the old timestamp format and I haven't had a chance to re-write it all completely, thus I'm still using the old mysql.

I'll post the modified conf up later once I fix it compeltely and edit out stuff specific to my forum.

[raywjohnson]

Originally Posted by Mb81

Thanks alot ! That was the problem. Thanks again.

No problem! Having just gone through all this myself, I am happy to help by passing on what I have learned.

I also like to give credit where it is due:
so thank you Orban (
https://vborg.vbsupport.ru/showpost.php?p=1104866 )

Another question; what are recommended update settings ?

If you are asking about indexing, keep on reading!

Originally Posted by eoc_Jason

Just curious for people's indexer updates, how often do you do them and how to you manage the delta vs full?

Right now I have a cron job running every 5 minutes to do the delta files since they only take a few seconds. I do a full rebuild maybe once a week or so (after disabling the delta cronjob so it doesn't try to run at the same time).

I would like to automate the full rebuild, but changing the delta one would require a really ugly cron looking line. Alternatively I could have another script check the time and figure out which job to run and if a job is already running and all that.

Second, how do you guys rotate your searchd & query log files? The only way I have gotten it to do that is to kill searchd completely and restart it. It would be nice if there was a more graceful method.

When to run the indexer seems to be a matter of preference, keeping in mind the usage/size of the database in question. I run two (almost) identical crons, one every 20 min (for the deltas) and one every day (for the full index). The LOCKFILE helps to keep them from stepping on each other.

#!/bin/sh

LOCKFILE=/var/lock/sphinx.cron.lock
INDEXER_CONF=/usr/local/etc/sphinx.conf

# the lockfile is not meant to be perfect, it's just in case the
# two sphinx cron scripts get run close to each other to keep
# them from stepping on each other's toes.

[ -f $LOCKFILE ] && exit 0

trap "{ rm -f $LOCKFILE ; exit 255; }" EXIT

touch $LOCKFILE

cd /usr/local/bin/ #(NOTE: this should be where indexer is located)

DELTAS_ONLY_LINE: ./indexer --config $INDEXER_CONF --rotate YOUR_POST_INDEX_DELTA YOUR_THREAD_INDEX_DELTA >/dev/null 2>&1
OR
FULL_INDEX_LINE ./indexer --all --rotate --config $INDEXER_CONF  >/dev/null 2>&1

exit 0

You could also replace ">/dev/null 2>&1" with "| mail -s "Sphinx Report" YOUR_EMAIL_HERE" to get an email of the output.

I created a folder called: "/etc/cron.20minutes" and then added the line "*/20 * * * * root run-parts /etc/cron.20minutes" to "/etc/crontab"

then put the cron with the DELTAS_ONLY_LINE in that folder and put the cron with FULL_INDEX_LINE in my "/etc/cron.daily" (you could also put it in "/etc/cron.weekly")

As far as log rotation goes, I used the Power of Linux once more! I just created the file:

"/etc/logrotate.d/sphinx"

/var/log/searchd.log /var/log/query.log {
    notifempty
    missingok
}

NOTE: the two paths/files need to match your sphinx.conf
log = /var/log/searchd.log
query_log = /var/log/query.log

Now they rotate all by themselves!

-RayJ