Separate databases for front-end and back-end

Question

The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more.

I was thinking the best thing to do would be create two databases and synchronize them daily. However:

• I do not know how to do this
• I am worried synchronization will be slow & block db access. I really don't like the idea of taking the site offline, even for 10 minutes at 3 am.

So I am wondering:

• Is separate databases the best solution for this problem, or would something else be better?
• How would I synchronize the database without interfering too much with the 'user experience'?

Thanks very much!

BTW running PHP/MySQL on 'could' servers.

Answer 1

From your comments, one solution might be to make a Master-Slave replication setup (link to mysql replication here)

I would make the backend the Master, and the front-end the slave. If your front-end needs to write (contact forms, tracking etc) you would update your code in the Front-end to read from the slave, and write to the master.

The downside is, depending on the load, your backend-writes might be delayed a few seconds or more. But as an anecdote, I've got a server that handles 120 commands /second (averaged, as reported by munin) and the slave server isn't behind by more than 3 seconds.

Answer 2

You are considering "creating two databases and synchronising them daily", which others have suggested is viable if one can be a read-only slave - however if both need to be read-write you are into Multi Master Replication which is a minefield to say the least. In that case throwing more expensive hardware at the problem would very likely end up cheaper.

Answer 3

Employ the use of read-only slaves.

Map all SELECT queries to those read-only slaves.

Convert all the tables in each read-only slave to MyISAM storage engine with the option ROW_FORMAT=FIXED for faster reads

Employ the use of a distribution master. This would remove the job of performing MySQL Replication away from the DB Master where main DB writes occur.

If you are using MySQL 5.5, you can use Semisynchronous Replication to get the first slave with the minimal "Seconds_Behind_Master" as much as possible.