1

I'm considering adding another server to my one-server setup application with 600GB MySQL DB. The db and the apache are both running from the same server. In order to avoid backup downtime and improve performance, I'm considering to use the additional server as a replication of the db hoping that the write will happen to the master and all the reads will happen from the slave, make it work in parallel.

Is this suitable for an application with many writes? (70%) I assume the write are more effective when done in bulks to the slave. Can I control this? How more effective are they? Is there an easy option to do 'down-time' on one of the servers (e.g. backup) and return to the same system afterwards?

Improve this question

1 Answer 1

Reset to default
1

MySQL Replication would probably victimize you in terms of Replication Lag.

If you run SHOW SLAVE STATUS\G on a Slave whose Master is write-heavy, the data you need to read may not be ready to read on the Slave until the Replication Lag (Seconds_Behind_Master) is 0.

SUGGESTION

If you want reads available on a Slave as well as on a Master, consider using Percona XtraDB Cluster. It does synchronized replication so that the same data exists at any point in time on both Master and Slave. It does not require binary logging or MySQL Replication. In addition, you can use a LoadBalanced DBVIP against Master and Slave to distribute reads.

The only drawback is that this might produce slower writes.

Provided all data is InnoDB, mysqldump backups can be done against the Slave using --single-transaction.

Here are my past posts on Percona XtraDB Cluster

Improve this answer
2
  • Thx. Specifically in my case the lag is not a problem (application wise). I'm more worried whether I will actually make good use of the two servers, and that the load will divide between them instead of ending up with to high-loaded machines.
    – Noam
    Sep 19, 2012 at 18:26
  • I would then advise going with three DB Servers running Percona XtraDB Cluster to do both read and write splitting.
    – RolandoMySQLDBA
    Sep 20, 2012 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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