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 am not exactly a 'Qualified' DBA, but yes i am in charge of my own DB which we use in our social app.

I have recently implemented Master/Slave Replication on my Database for obvious reasons.

What I wish to know is whether MySQL Replication kills the performance of my DB since for every user writing on my Master DB creates an additional write on Slave DB (I might be wrong here).

There is no lag between my slave & master. So, Replication is almost instant

share|improve this question

migrated from stackoverflow.com Mar 21 '13 at 20:02

This question came from our site for professional and enthusiast programmers.

1  
This is not appropriate for StackOverflow and belongs on dba.se. –  Kermit Mar 21 '13 at 15:31
    
i thought since stackoverflow provides tags for mysql & database replication it can be allowed here too.. –  Jigar Mar 21 '13 at 15:33
    
It does but the question has more to do with DBA then software development. –  Kermit Mar 21 '13 at 15:35

2 Answers 2

There are two major things a Master has to handle because of a Replication Setup

DISK I/O

The Master has to write every completed SQL transaction to the binary logs

NETWORK I/O

When a Slave is connected, the Master must play traffic cop.

  • Master writes SQL statement to the most recent Binary Log
  • Master polls for DB Connections emanating from Slaves
  • For Each Slave DB Connection, the Master does the Following:
    • Master receives request for the oldest SQL Statement from the Binary Log since the the Slave's last entry in its Relay Logs
    • Master sends the following to the Slave's IO Thread
      1. Master Log Filename
      2. Master Log Position
      3. SQL Statement at the Master's Log Filename and Position

If a busy Master has Slaves with Seconds_Behind_Master : 0, simply marvelous.

If either of these metrics become noticeable, you may need to do certain things:

Here are my other posts about setting up replication topologies

share|improve this answer

To give a simple answer to your question, "No, replication does not kill the performance of your master."

The impact on your master from replication is typically very small compared to all of the other things it has to do, because the master only really has to accomplish two significant things in a replication environment:

  • formulate and write events to the binlog on the local hard drive, and
  • send a copy of every event it writes to the binlog to every connected slave

I do not consider writing the binary log to be a cost of replication, because you should always have binary logging turned on even if you're not replicating. It's an extremely valuable troubleshooting and recovery tool.

The cost of sending the replication events to the slave(s) is also negligible, because the slave is responsible for maintaining a persistent TCP connection to the master, which only has to copy the data onto the socket as the events occur. Beyond that, the master neither knows nor cares whether or when the slave gets around to executing them.

A partial exception to that last statement is semi-synchronous replication, which is not the default. In this mode, the master waits for at least one slave to acknowledge receipt and persistent storage (though not actual execution) of the binary log events from each transaction, before the master returns control to the client on each commit.

But in every case, the master is not responsible for actually executing the updates on the slave -- it just sends one of two things to the slave: either a copy of the actual input query that ran (in statement-based mode) or the data for the rows actually inserted/updated/deleted by each query (in row-based mode). In mixed mode, the query optimizer will decide which format to use on a per-event basis.

share|improve this answer

Your Answer

 
discard

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