Scenario Description
A large transaction is executed on the Master.
Possible Causes
A large transaction is a transaction containing a large quantity of data change operations, for example, a transaction including tens of thousands of (INSERT, UPDATE, and DELETE) operations, or an SQL statement that updates tens of thousands of rows of data in batches. The execution of a large transaction usually takes a long time (minutes). After a large transaction is executed on the primary instance, a large number of binlogs are generated. It takes much more time for the backup instance to obtain these binlogs than for a common transaction. In addition, it takes at least the same time for the transaction being executed on the primary instance to replay the transaction, resulting in a replication delay on the backup instance.
Solution
• To ensure data consistency between the primary and standby instances, wait until the large transaction is complete, and then the primary/standby replication can be restored.
• Avoid such large transactions on the service and split the large transactions into small transactions and execute them in batches. For example, limit the amount of data to be updated at a time using the WHERE condition or LIMIT statement.