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:
- MySQL replication: most important config parameters for performance on slave server?
- mysql replication delay very serious
See the bellow image, it represents asynchronous replication on MySQL, I highly recommend you to read my other post:
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.