CODEWITHSUNDEEP
mysqldatabase-replication
Gokul Muralidharan
Gokul Muralidharan

Reputation: 161

Will Mysql replication fail if we continuously do a lot of write operations in the master

We use Percona mysql on EC2 and have a master / slave setup for HA. What we observe is that the replication to the slave always falls behind in hours or even days as we continuously write in data in the master as it is the nature of our application.

What could be a problem here ?

Upvotes: 0

Views: 265

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44333

First, think about how MySQL Replication is organized

Major Components for MySQL Replication

  • Slave IO Thread: It's responsible for maintaining a constant connection to a Master. It is ready to receives binlogs events from the Master's binlogs and collect them FIFO style in the Slave's relay logs.

  • Slave SQL Thread: It's responsible for reading binlog events stored in the relay logs. The event (DML, DDL, etc) is executed on this internal thread.

  • Seconds_Behind_Master: Each binlog event has the timestamp of the event (DML, DDL, etc). Seconds_Behind_Master is simply the NOW() of the Slave server minus timestamp of the event. Seconds_Behind_Master is displayed in SHOW SLAVE STATUS\G.

What is the Problem ?

If Seconds_Behind_Master is ever increasing, consider the following: The single-threaded execution path for binlog events of MySQL Replication is nothing more than the Serialization of all the SQL commands that were executed on the Master in parallel. Picture this: if 10 UPDATE commands were executed on the Master in parallel and take 1 second each to process, they get placed in the relay logs and executed FIFO style. All the UPDATEs have the same timestamp. Subtracting the timestamp for each UPDATE processed on the Slave yields a 1-second increase in Seconds_Behind_Master. Multiplied by 10, and you get 10 additional seconds of Replication Lag. This reveals the SQL Thread as a Bottleneck.

Suggestions

  • Master and Slave may be underspec'd. Perhaps more Memory and/or Cores so that the Slave can process binlog events faster. (Scaling Up, Slightly Linear Improvements at Best)
  • Try configuring InnoDB for accessing more cores
  • Switch to MySQL 5.6 and implement parallel slave threads (if you have multiple databases)
  • Wait for Percona 5.6, then upgrade and implement parallel slave threads (if you have multiple databases)

Upvotes: 1

Related Questions