Why Does MySQL Replication Delays?

These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen:

See the bellow image, it represents asynchronous replication on MySQL, I highly recommend you to read my other post:

“How Does MySQL Replication Works?”

How replication works

How you can see, there is a big difference related to the entry point of data on master and slave, while the master has multiple simultaneously threads inserting/updating/deleting data, on the slave is just a single one responsible for deal with all these transactions

Let’s imagine that one transaction (an UPDATE for example) took 50 seconds to be applied on the master, during this time, others threads executed queries on the master, what is gonna happen when the slave receive and execute this transaction? yes, you are right, DELAY, all other pending transaction and the ones which got inserted while the slave is applying this UPDATE will need to wait until it finish to be applied.

If you use MySQL from version 5.6.3 and have your data partitioned per database, you can minimize this “bottleneck” using slave-parallel-workers, with this option you can config the number of SQL_THREAD'S to execute simultaneously transaction since they are in different databases.

Leave a Reply

Your email address will not be published. Required fields are marked *