How to Safely Delete Large Log Tables and Resolve MySQL Read‑Replica Lag

Background: The online operation log table resty_log grows excessively large, requiring periodic deletion. A basic SQL command is used to remove records older than 30 days:

DELETE FROM resty_log WHERE create_time < NOW() - INTERVAL 30 DAY;

The NOW() function returns the current timestamp, and INTERVAL 30 DAY defines a 30‑day period. The WHERE clause filters rows whose create_time is earlier than the current time minus 30 days, and the DELETE statement removes them.

Alert Details

RDS Alert

Instance: xxxx‑production‑readonly‑slave01

Metric: Read‑only instance latency

Condition: Triggered 3 consecutive times (1109.5s ≥ 10s)

Current value: 1109.5s

Duration: 2 minutes

ECS Alert

Metric: (Agent)memory.used.utilization

Condition: Triggered 3 consecutive times (80.51% ≥ 80%)

Current value: 80.51%

Duration: 3 minutes

MySQL Read‑Replica Synchronization Delay

Problem Description

Alibaba Cloud RDS read‑replicas use MySQL’s native asynchronous or semi‑asynchronous binlog replication, which inevitably introduces replication lag. Lag can cause data inconsistency between the primary and replica, affect business logic, and lead to binlog accumulation that quickly consumes replica storage.

When the primary generates a large volume of binlog entries, the replica may become locked, producing errors such as:

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

This error often appears while a delete script is running, indicating another transaction is holding locks on rows in resty_log that the script attempts to delete.

Root Causes

Large Transactions – Heavy UPDATE, DELETE, INSERT … SELECT, or REPLACE … SELECT statements generate massive binlog data that must be replayed on the replica. Because MySQL replicates per‑table in a single thread, the replica processes the transaction at the same speed as the primary, causing noticeable lag. Monitoring tools such as SHOW SLAVE STATUS \G reveal a continuously increasing Seconds_Behind_Master while Exec_Master_Log_Pos remains static, indicating the replica’s SQL thread is stuck on a large transaction.

Running SHOW PROCESSLIST; helps locate the blocking thread. If the binlog is in row format, large transactions also produce large binlog files; checking SHOW BINARY LOGS; and comparing each file’s size to max_binlog_size confirms this condition.

Solutions

Use batch deletion to avoid long‑running, lock‑heavy operations.

Limit rows with a WHERE clause – Delete records in manageable chunks based on timestamps or ID ranges. Example:

DELETE FROM resty_log WHERE create_time < '2023-01-01' AND id <= 10000;

Iteratively expand the ID range until all targeted rows are removed.

Use LIMIT (if supported) – MySQL and PostgreSQL allow limiting the number of rows per DELETE, reducing lock time:

DELETE FROM resty_log WHERE create_time < '2023-01-01' LIMIT 1000;

Repeat the statement until the table is cleared.

These approaches mitigate lock contention, lower replication lag, and prevent binlog overflow on read‑only instances.