Sphinx Configuration - mariadb/mysql & postgresql

Howtos, instructions and links to related software. Do not ask questions here.
gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Sphinx Configuration - mariadb/mysql & postgresql

Postby gbcox » 22 May 2013, 09:35

Ok, how in the heck do you upload files... everytime I try I get invalid filetype... :shock:
I've uploaded to google docs, and you can download from the urls below...

Much thanks to Barry Hunter over at Sphinx Forum - he figured it out.
This resolves the issue reported here:
viewtopic.php?f=1&t=2053
and here:
viewtopic.php?t=1109&p=4961

I tested both with mariadb and postgresql with sphinx 2.0.8 (Fedora)

Since the config files below are delta, I would recommend you go ahead and change config.php.dist
to set sphinx index to delta as default.

define('SPHINX_INDEX', 'ttrss, delta');
// Index name in Sphinx configuration. You can specify multiple indexes
// as a comma-separated string.

I changed config.php.dist to include the delta index by default since the attached files are delta.
One for mariadb/mysql and the other for postgresql.

Steps would be as follows:
1. Install Sphinx, i.e. yum install sphinx
2. Select the config file for your database (mariadb/mysql or postgresql)
3. Make the necessary changes (instructions on what to change in the comments of each config)
4. Change to the Sphinx conf directory
5. Rename sphinx.conf to sphinx.conf.dist
6. Rename the ttrss specific config you just modified to sphinx.conf
7. Run /bin/indexer --all to initialize the database
8. Start the sphinx process, i.e. systemctl start searchd.service
9. Enable the service to ensure startup at system initialization, i.e. systemctl enable searchd.service
10. Setup cron to run indexer:
@daily /bin/indexer --rotate ttrss >/dev/null 2>&0 # Same as 0 0 * * *
*/20 * * * * /bin/indexer --rotate delta >/dev/null 2>&0 # Every 20 minutes
11. And, last but not least, remember to enable sphinx in the ttrss config.php:
define('SPHINX_ENABLED', true);

Here is the configuration for postgresql:
https://docs.google.com/file/d/0B96ctNl ... sp=sharing

Here is the configuration for mysql/mariadb:
https://docs.google.com/file/d/0B96ctNl ... sp=sharing

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby fox » 22 May 2013, 09:37

Excellent, I'll update the sphinx wiki page.

>define('SPHINX_INDEX', 'ttrss, delta');

Sure.

cqrt
Bear Rating Disaster
Bear Rating Disaster
Posts: 70
Joined: 05 Apr 2013, 00:37

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby cqrt » 23 May 2013, 04:45

Gerald, did you notice in your searchd.log

WARNING: rotating index 'ttrss' without preopen option; use per-index propen=1 or searchd preopen_indexes=1
WARNING: rotating index 'delta' without preopen option; use per-index propen=1 or searchd preopen_indexes=1


Not sure why, as preopen_indexes=1 is set for certain in searchd { }.

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby gbcox » 23 May 2013, 04:52

No, I just looked it's clean... I rotate every 20 minutes... what version of sphinx are you using? I'm on 2.0.8...
I rotate every 20 minutes...

[Wed May 22 16:00:02.480 2013] [25669] rotating index 'delta': success
[Wed May 22 16:00:02.481 2013] [25669] rotating finished
[Wed May 22 16:20:02.379 2013] [25669] caught SIGHUP (seamless=1, in queue=1)
[Wed May 22 16:20:02.397 2013] [25669] rotating index 'delta': success
[Wed May 22 16:20:02.397 2013] [25669] rotating finished
[Wed May 22 16:40:01.981 2013] [25669] caught SIGHUP (seamless=1, in queue=1)
[Wed May 22 16:40:02.006 2013] [25669] rotating index 'delta': success
[Wed May 22 16:40:02.007 2013] [25669] rotating finished
[Wed May 22 17:00:03.123 2013] [25669] caught SIGHUP (seamless=1, in queue=1)
[Wed May 22 17:00:03.507 2013] [25669] rotating index 'delta': success
[Wed May 22 17:00:03.508 2013] [25669] rotating finished
[Wed May 22 17:20:02.491 2013] [25669] caught SIGHUP (seamless=1, in queue=1)
[Wed May 22 17:20:02.542 2013] [25669] rotating index 'delta': success
[Wed May 22 17:20:02.543 2013] [25669] rotating finished
[Wed May 22 17:40:02.084 2013] [25669] caught SIGHUP (seamless=1, in queue=1)
[Wed May 22 17:40:02.089 2013] [25669] rotating index 'delta': success
[Wed May 22 17:40:02.090 2013] [25669] rotating finished

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby gbcox » 23 May 2013, 05:05

One other thought... don't know what the state of the index is... you might consider reinitializing it... it's pretty fast...
cd /bin
systemctl stop searchd.service
./indexer --all
systemctl start searchd.service
Then test rotate:
./indexer --rotate delta

cqrt
Bear Rating Disaster
Bear Rating Disaster
Posts: 70
Joined: 05 Apr 2013, 00:37

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby cqrt » 23 May 2013, 05:19

2.0.4
I'll upgrade and also reinitialize.

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby gbcox » 23 May 2013, 05:40

Let me know if that works for you...thanks!

cqrt
Bear Rating Disaster
Bear Rating Disaster
Posts: 70
Joined: 05 Apr 2013, 00:37

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby cqrt » 23 May 2013, 07:01

Worked. I ended up upgrading to 2.0.9-rel20 through sphinx's PPA, so I can test the weekly releases.

[Thu May 23 14:54:11.851 2013] [27336] listening on all interfaces, port=9312
[Thu May 23 14:54:11.851 2013] [27336] listening on all interfaces, port=9306
[Thu May 23 14:54:12.218 2013] [27337] accepting connections
[Thu May 23 14:54:35.573 2013] [27337] caught SIGHUP (seamless=1, in queue=1)
[Thu May 23 14:54:36.669 2013] [27337] rotating index 'delta': success
[Thu May 23 14:54:36.669 2013] [27337] rotating finished

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby gbcox » 23 May 2013, 07:36

2.0.9? I'm not brave enough for that... LOL... glad your problem is resolved!

pcause
Bear Rating Master
Bear Rating Master
Posts: 144
Joined: 23 Aug 2013, 19:52

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby pcause » 28 Aug 2013, 01:19

This thread is great and helped me get Sphinx running. I had one issue and it took a while to fix and is perhaps Ubuntu specific. Perhaps you could add this to the steps at the beginning of this thread.

To get the Sphinx server to start on Ubuntu, you have to go to /etc/defaults and edit the file sphinxsearch to change the line that is "start=no" to "start=yes".

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby gbcox » 18 Dec 2014, 21:48

There were a few changes made in Sphinx v2.2.6 which require changes to sphinx.conf.
The most important being related to binlog_path which can cause searchd not to start.
The recommendation is to explicitly define it in the configuration file. In addition, several
parameters are no longer needed, and will generate warnings if you have them defined.

Here are the links to the new sample configuration files. Fox, could you consider updating
the wiki page? Thanks!

https://gbcox.fedorapeople.org/tiny_tiny_rss/ttrss_pgsql.sphinx.conf
https://gbcox.fedorapeople.org/tiny_tiny_rss/ttrss_pgsql.sphinx_pre_v226.conf
https://gbcox.fedorapeople.org/tiny_tiny_rss/ttrss_mysql.sphinx.conf
https://gbcox.fedorapeople.org/tiny_tiny_rss/ttrss_mysql.sphinx_pre_v226.conf

TSM
Bear Rating Trainee
Bear Rating Trainee
Posts: 13
Joined: 03 Nov 2015, 16:20

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby TSM » 03 Nov 2015, 16:40

I have seen an area where sphinx could be optimised.
Currently the delta pulls the last 24h of records and indexes again, this could be quite large for some people, on a separate project I use sql_query_pre command to store data in the DB relating to the position to go from on each delta.

ie.

Code: Select all

source ttrss
{
        type                    = mysql # this is a MARIADB/MYSQL database
        sql_host                = database # this is your hostname, typically localhost
        sql_user                = ttrss # this is the ttrss_user name you defined
        sql_pass                = ttrss # this is the ttrss_user password you defined
        sql_db                  = ttrss # this is the name of the ttrss MARIADB/MYSQL database
        sql_port                = 3306  # optional, default is 3306 for MARIADB/MYSQL
        sql_query_pre           = SET NAMES utf8

        sql_query_pre           = SELECT @max_doc_id := MAX(updated) FROM ttrss_entries
        sql_query_pre           = UPDATE ttrss_sphinx_counters SET [email protected]_doc_id,lastrun_main=UTC_TIMESTAMP() WHERE counter_id=1
        sql_query_pre           = SELECT @max_doc_id := max_doc_id FROM ttrss_sphinx_counters WHERE counter_id=1

        sql_ranged_throttle         = 100
        sql_query_range             = SELECT MIN(id),MAX(id) FROM ttrss_entries
        sql_range_step              = 1000


        sql_query               = \
                SELECT int_id AS id, ref_id, UNIX_TIMESTAMP(updated) AS updated, \
                        ttrss_entries.title AS title, link, content, \
                        ttrss_feeds.title AS feed_title, \
                        marked, published, unread, \
                        author, ttrss_user_entries.owner_uid \
                        FROM ttrss_entries, ttrss_user_entries, ttrss_feeds \
                        WHERE ref_id = ttrss_entries.id AND feed_id = ttrss_feeds.id \
                        AND ttrss_entries.updated <= @max_doc_id \
                        AND ttrss_entries.id>=$start AND ttrss_entries.id <=$end;



        sql_attr_uint           = owner_uid
        sql_attr_uint           = ref_id

#       sql_query_info          = \
#               SELECT * FROM ttrss_entries,  \
#                       ttrss_user_entries WHERE ref_id = id AND int_id=$id


}

source ttrss_delta : ttrss {
        sql_query_range                 =
        sql_query_pre                   =
        sql_query_pre                   = SELECT @max_doc_id := MAX(updated) FROM ttrss_entries
        sql_query_pre                   = UPDATE ttrss_sphinx_counters SET lastrun_delta=UTC_TIMESTAMP(),[email protected]_doc_id WHERE counter_id=1
        sql_query_pre                   = SELECT @max_doc_id := max_doc_id, @max_doc_id_delta := max_doc_id_delta FROM ttrss_sphinx_counters WHERE counter_id=1
        sql_query               = \
                SELECT int_id AS id, ref_id, UNIX_TIMESTAMP(updated) AS updated, \
                        ttrss_entries.title AS title, link, content, \
                        ttrss_feeds.title AS feed_title, \
                        marked, published, unread, \
                        author, ttrss_user_entries.owner_uid \
                        FROM ttrss_entries, ttrss_user_entries, ttrss_feeds \
                        WHERE ref_id = ttrss_entries.id AND feed_id = ttrss_feeds.id \
                        AND ttrss_entries.updated >= @max_doc_id AND ttrss_entries.updated <= @max_doc_id_delta;
#                        AND ttrss_entries.updated > NOW() - INTERVAL 24 HOUR \

        sql_query_killlist      = \
                SELECT int_id FROM ttrss_entries, ttrss_user_entries \
                        WHERE ref_id = ttrss_entries.id AND ttrss_entries.updated >= @max_doc_id AND ttrss_entries.updated <= @max_doc_id_delta;


}

index ttrss
{
        source                  = ttrss
        path                    = /var/lib/sphinx/ttrss # Distribution Dependant, VALIDATE
        docinfo                 = extern
        mlock                   = 0
        morphology              = none
        min_word_len            = 1
#       charset_type            = utf-8
        min_prefix_len          = 3
#       prefix_fields           = title, content, feed_title, author
#       enable_star             = 1
        html_strip              = 1
        dict                    = keywords

}

index ttrss_delta : ttrss {
        source                  = ttrss_delta
        path                    = /var/lib/sphinx/ttrss_delta # Distribution Dependant, VALIDATE
}



This creates the table required to store the counters

Code: Select all

CREATE TABLE `ttrss_sphinx_counters` (
   `counter_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `max_doc_id` VARCHAR(255) NULL DEFAULT NULL,
   `max_doc_id_delta` VARCHAR(255) NULL DEFAULT NULL,
   `lastrun_delta` VARCHAR(255) NULL DEFAULT NULL,
   `lastrun_main` VARCHAR(255) NULL DEFAULT NULL,
   PRIMARY KEY (`counter_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC;

TSM
Bear Rating Trainee
Bear Rating Trainee
Posts: 13
Joined: 03 Nov 2015, 16:20

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby TSM » 03 Nov 2015, 20:30

Can $params be passed to $plugin->hook_search as a second param?
It would allow for further sphinx optimisations such as limiting the search on to a particular feed and limits etc.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Sphinx Configuration - mariadb/mysql & postgresql

Postby fox » 03 Nov 2015, 20:53

there's psql native full-text support now which owns absolutely so as far as i'm concerned sphinx plugin is in maintenance mode

feel free to fork it or w/e and post in T&P subforum

also KB is not the forum to discuss this


Return to “Knowledge Base”

Who is online

Users browsing this forum: No registered users and 0 guests