Separate databases for front-end and back-end

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.

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.

StackExchange Database Administrators Q#2631, answer score: 6