Description
A replication delay exists between the apsaradb RDS for MySQL moderator instance and its read-only instance.
Causes
- Cause 1: you have executed DDL statements that involve large amounts of data.
DDL statements that involve large amounts of data may generate a large number of temporary data tables. In addition, DDL statements are usually executed on a single thread and therefore are relatively slow, such asoptimize table big_data_table. - Cause 2: a large number of DML statements or DML statements that are frequently executed on a single table are executed.
- A large number of DML statements, such as
insertorupdate, are performed on a single table. The read-only instance can only read and execute binlog files on a single thread. Similar to cause 1, the speed is relatively slow. Therefore, a replication delay occurs. - Although high-frequency DML statements can be concurrently executed in multiple threads, the specifications of read-only instances are usually smaller than those of the Master instance. Therefore, the load on read-only instances is greater than that of the Master instance, and it is slow to read and execute binlog files, which eventually produces a replication delay.
- A large number of DML statements, such as
- Cause 3: The transaction has been executed and involves a large amount of data.
When either of the preceding DDL statements or DML statements exists in a transaction, a large number of binlog logs in the row format are generated. Especially for SQL statements that are executed for a single table, the replication will be completed by a single thread, which is relatively slow. - Cause 4: the target data table on which the SQL statement is executed does not have a Primary Key, and a Null value exists in the Unique Key.
Since there is no primary key in the data table, the read-only instance will use the unique key as the priority index when copying binlog files, and there is a null value in the unique key, resulting in low index efficiency and a large number of full table scans.
Solution
The corresponding solution varies according to different Problem Causes. First, use the SQL insight feature to audit all the SQL statements when a fault occurs and check whether any of the preceding conditions exist.
note: For more information about SQL insight, such as how to activate SQL insight and how to use SQL insight, see SQL insight.
If any of the above situations exists, perform specific handling by referring to the following content:
Cause 1: you have executed DDL statements that involve a large amount of data.
Avoid executing such DDL statements during peak business hours.
Cause 2: a large number of DML statements or DML statements that are frequently executed on a single table
Optimize the SQL statement, try to submit the SQL statements in multiple batches, and increase the gap of each submission. Alternatively, you can try to upgrade the specifications of the read-only instance.
Cause 3: transactions that involve large amounts of data have been executed.
This cause contains two cases. For more information, see cause 1 and cause 2.
Reason 4: the target data table on which the SQL statement is executed does not have a Primary Key, and a Null value exists in the Unique Key.
The solution is relatively simple, that is, just add a primary key to the target data table.
Application scope
- ApsaraDB RDS for MySQL