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.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
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 '12 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 '12 at 18:03

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.