The default replica databases, disaster recovery instances, and read-only replicas of TencentDB for MySQL use MySQL's native binlog replication technology and may experience delays during async or semi-sync data replication.
You can view the source-replica delay using the monitoring feature. If the delay time is greater than 0, the instances are experiencing a data delay. Common causes are as follows.
Cause
When DML operations (e.g., delete, update, and insert) are performed on big tables, the rows to be modified will be retrieved based on the primary key or secondary index when the binlog application is executed in the replica databases. If binlogs are in the row format and the corresponding table has no primary key or secondary index, a large number of full-table scans will be caused, slowing down the binlog application and leading to data delays.
Solutions
truncate
command to delete all records of the table.Cause
When the source instance executes DML operations involving massive volumes of data, a large number of binlogs will be transferred to the replica database, which needs the same time consumed by the source instance to complete corresponding transactions, leading to data delays in replica database.
Solutions
It is recommended to divide large transactions into smaller ones and use the where
condition to limit the volume of data to be processed at a time. This can help the replica database complete transactions quickly, thereby avoiding data delays in replica database.
Cause
Similar to large transactions, in case of prolonged execution duration of DDL operations in the source instance, the replica database will take the same or even more time to perform the operations, which may jam up DDL operations.
Solutions
It is recommended to perform DDL operations during off-hours. If DDL operations are jammed due to the queries of disaster recovery and read-only replicas, you can kill the relevant sessions to restore source-replica data sync.
Cause
For read-only replicas and disaster recovery instances, lower specifications than that of the source instance and higher loads will result in data delays.
Solutions
It is recommended to make sure that the specifications of read-only replicas and disaster recovery instances are higher than that of the source instance. If their loads are too high due to a large number of analytical businesses, you need to upgrade the instances to appropriate configurations or optimize low-performance SQL statements.
Cause
Transactions which have been executed or uncommitted for a long time will jam up DDL operations and further jam all subsequent operations on the same table.
Solutions
We recommend that you use TencentDB for DBbrain to diagnose the instance and business, check monitoring metrics like slow log, and locate long-running transactions.
Was this page helpful?