I am in the process of converting an application that has traditionally been a desktop-based app into a "cloud service" which needs to be efficient and can scale well. My application requires use of Full Text Search. I've been using MySQL with the myisam engine for that, but it doesn't work well when the table gets a lot of writes, which it will. So I've done some research and see that a project called Sphinx Search can be installed as a mysql engine and perform better full text searches than myisam. Thing is, I've tried installing it with no success. I'm fairly new to Linux programming and server administration, so it exceeds my capabilities and I need help.
So this is what I would like to hire someone to do:
My current database server is hosted at the Rack Space Cloud. What I would like to do is make this current server a "Master" server in a mysql master/slave setup and convert the tables from myisam to innodb to improve write performance. The master will only be used for insert and update queries only. Next I would like to setup 2 mysql slaves. The slaves will be used for all select full text search queries and will have copies of the master table but use the Sphinx engine instead of myisam or innodb. One of the slaves will be a new image in the Rack Space Cloud and the other slave should be an VirtualBox image I can run off my home DSL connection. I prefer to use Ubuntu Server [url removed, login to view] as this is what the current server is and I'd like to be consistent. I also need instructions on how to add more slaves in the future should I need it and what the most cost effective server from Rackspace is (e.g. better to have more low ram server instances or less high ram servers). Along the way I'd also appreciate any insight on how to make my queries more efficient or a better way to configure the server. Or if you have a better plan for my needs than what I've come up with, I'm open to suggestions.