5

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.

  • The backend both read and write heavy. The simple answer is it is a web crawler populating the db with good information and deleting information that is no longer relevant. – JIStone May 10 '11 at 17:43
6

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.

  • This looks perfect. Realistically the back-end could be several hours behind and no one would care. – JIStone May 10 '11 at 18:20
4

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.