Installing Sphinx on a master-slave setup

Hi all,

On my quest to find the best search solution I have come across Sphinx which comes highly recommended.

My question is about the setup structure on the following server setup:

2 x webservers load balanced
1 x master (2gb mem, 2 cores)
1 x slave (3gb mem, 4 cores)

All ubuntu.

I have mysql replication across master and slave and now I want to introduce Sphinx.

Ideally I'd want to seed the index on the master and have the sphinx-queries sent to the slave via the php api.

Has anyone got any experience doing this? Is the index r-synced across to the slave to keep it in sync? Do I just install sphinx on the master? What about the web-servers and the php-api, do they both need sphinx installed too?

Any help getting me started is much appreciated!
dolythgoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
In my experience Sphinx only comes highly recommended by people who have not really used Sphinx very much.  It may not be the answer you want to hear, but it is a plain fact that Google knows more about search than you and I do.  I recommend that you go with the mainstream algorithms from Google.
0
dolythgoeAuthor Commented:
This is true but I'm not trying to build Google or use it's index - in fact, I think Google lacks a lot in some areas - e.g filtering and search of technical articles tends to bring back stuff dated in 2001 which isn't that relevant for mysql searches if you want more recent stuff. Goolge does not have social tagging and only recently put +1 in their index. For most things, it's the best but some things it isn't.

Sphinx is there to act as a 'piece' of the overall puzzle because it does a better job than mysql for a specific query type. Of course, if I wanted Google power then it would be the first place to go but in this case, it's not required.

0
johanntagleCommented:
On the contrary I've used Sphinx heavily on some projects and I'm happy with it.  I agree that Google is a much more powerful search (that may be the understatement of the decade), but custom search engines have their place in the world.  A lot of times it is easier to implement in a custom search engine to search only specific database fields than to find out how to do it in google, if it's even possible.  Yes, you'll end up having to handle additional load on your servers, but a lot of times its worth it.

The sphinx engine does not have to be on any of the database servers or web servers actually.  It can be anywhere - on big sites it has its own machine dedicated to it.  It just needs to access the database to read what needs to be indexed, then it stores the index data wherever it is installed - the exact path depends on your configuration.  Your PHP application will then need to know the address of the server where sphinx resides and the port it is running on (default is 9312).

Given your setup I would recommend that you set up sphinx on the slave and have it read data from the slave.  This is because I believe the slave would be the least loaded machine right now and also so that in case the master fails, you still have both database and sphinx ready.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dolythgoeAuthor Commented:
Cheers.

So I'm going to take your advice and go with Sphinx on slave - for indexing and readonly queries to sphinx/mysql and keep master for inserts and other tasks.

I'm setting it up on ubuntu but I think I might need to open some ports between the machines. Is it a case of setting sphinx up to listen to the webservers? Not quite sure how to do that - I'm guessing the sphinx.conf but no idea about the ports!

The setup is not live and a testing rig without load so breaking it doesn't matter too much ;)

Cheers
David
0
johanntagleCommented:
9312 is the default port sphinx uses.  Just make sure that the server-based firewall (if any) allows it.
0
dolythgoeAuthor Commented:
Thanks for your help.

So I got this all setup but went to run searchd and got:

listening on 10.2.7.10:9312
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
bind() failed on 10.2.7.10, retrying...
FATAL: bind() failed on 10.2.7.10: Cannot assign requested address

10.2.7.10 being private IP for web server 1 (10.2.7.11 is webserver 2 but it never got that far)

I put this in the sphinx.conf but I guess it's wrong!

        #Listen on the 2 webservers
      listen                  = 10.2.7.10:9312
      listen                  = 10.2.7.11:9312
      # listen                  = 9312
      # listen                  = /var/run/searchd.sock
      listen                  = 9312
      listen                  = 9306:mysql41

Hopefully this is obvious to you!
0
johanntagleCommented:
No it should be the IP of the slave in your sphinx.conf, not the IP's of the webservers.
0
dolythgoeAuthor Commented:
Thanks for that - searchd is now running - is there anything needed to do to the webservers apart from specify host (which I assume is the slave private IP) and port 9312?

Cheers
0
johanntagleCommented:
I'm not too familiar with PHP (I use mainly Ruby on Rails) but check this out
http://www.sanisoft.com/blog/2010/01/11/sphinx-search-engine-and-php-installation-indexing/

And of course, there are the docs:
http://sphinxsearch.com/docs/ 
http://sphinxsearch.com/wiki/doku.php?id=php_api_docs
0
dolythgoeAuthor Commented:
Thanks for those - I've had the php scrip setup for a little while but just getting nothing back from my tests.

Really confusing, no errors shown, nothing in the logs just blank. It's a difficult doc to traverse and google is giving me rubbish again!

I've set the server IP and port but something must be missing. I think I might have to hunt down to see if a firewall is causing the issue.

Will keep you posted!
0
dolythgoeAuthor Commented:
All done and working :) thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.