patternsqlMinor
Does MySQL Replication hamper the performance of my DB?
Viewed 0 times
thereplicationmysqlperformancedoeshamper
Problem
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 instantaneous.
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 instantaneous.
Solution
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.
If a busy Master has Slaves with
If either of these metrics become noticeable, you may need to do certain things:
Here are my other posts about setting up replication topologies
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
- Master Log Filename
- Master Log Position
- 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:
- Scale up DB Server
- Place Binary Logs on a Separate Disk
- Faster Disk I/O
- RAID10 (rather than RAID5)
- Is raid 5 suitable for a mysql installation?
- Master - Slave MySQL setup on a VMWare Cloud — Is it needed? (ServerFault)
- More RAM
- Scale out the Topology
- Add More Slaves
- Make Each a Slave Master to Other Slaves
- Create Distribution Masters
Here are my other posts about setting up replication topologies
Aug 20, 2012: Best solution for cross-datacenter MySQL master-slave replication
Aug 20, 2012: How to use MySQL Server 5.6 for GeoIP replication?
Jun 25, 2012: Mysql database replication on different vlan/subnet/another site
Dec 19, 2011: Best way to setup master to multi master replication
Dec 17, 2011: Mysql Master-Master Replication Topologies on >2 machines
Aug 18, 2011: With MySQL Replication, what level of resilience is possible?
Jun 01, 2011: What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Mar 29, 2011: MySQL high availability, failover and replication with Latency
Context
StackExchange Database Administrators Q#37280, answer score: 8
Revisions (0)
No revisions yet.