Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is all your backend activity read only? Sync does not need to slow down the master - it often just means shipping log files to the read-only slave (though I don't know how this kind of thing works for mysql specifically) –  Jack Douglas May 10 '11 at 17:39
    
@JackPDouglas 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
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
This looks perfect. Realistically the back-end could be several hours behind and no one would care. –  JIStone May 10 '11 at 18:20
    
As usual, your answers are more on point since you are currently a developer with a good sense of MySQL. +1 for you. –  RolandoMySQLDBA May 10 '11 at 18:34
1  
@Rolando It's tough to move away from a developer mentality when that's how you start, I guess! –  Derek Downey May 10 '11 at 18:42
add comment

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.

share|improve this answer
    
Both great answers with useful tips. However DTest was first, so I'm afraid I can only offer you this humble up-vote as thanks. –  JIStone May 10 '11 at 18:27
    
@JIStone Thank You, kind sir !!! Also, +1 for your good question the community could ponder over. –  RolandoMySQLDBA May 10 '11 at 18:35
    
+1 for the slave-optimization point –  Derek Downey May 10 '11 at 18:39
    
@DTest Thank You, kind sir !!! –  RolandoMySQLDBA May 10 '11 at 18:42
add comment

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.

share|improve this answer
    
Your idea is good as well (+1) , especially if any structural changes (DDL) could be done on a Passive server and those structural changes import later by running START SLAVE on the active server and then STOP SLAVE when all DDL from the passive server is run on the Active server. There are a lot of good uses for Circular Replication which can be a minefield unless you are disciplined at writing data independent of auto_increment keys. –  RolandoMySQLDBA May 10 '11 at 18:45
    
@Rolando - thanks. Some of the potential problems are mentioned in the link, but as you suggest, it depends on the workload. –  Jack Douglas May 10 '11 at 19:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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