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


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


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!





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


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 2 guests