Performance Analysis of MySQL Row‑Based Binlog Replay for Large Table Delete without WHERE Clause

Problem

On the master a DELETE without a WHERE clause was executed on a table with more than 500,000 rows. The binlog format is mixed, transaction isolation is RC, so the DML is recorded in row mode. The table has no primary key and only a non‑unique index, causing the slave to take more than 10 hours to replay the statement.

Analysis

In row mode the slave replays the relay log by reading the BEFORE IMAGE and AFTER IMAGE of each row event. The relevant functions are Rows_log_event::do_apply_event and Rows_log_event::do_before_row_operations for DELETE, which update the internal command counter.

The event type matrix shows which images are present for each DML event:

+------------------+--------------+-------------+
|    EVENT TYPE    | BEFORE IMAGE | AFTER IMAGE |
+------------------+--------------+-------------+
| WRITE_ROWS_EVENT| No           | Yes         |
| DELETE_ROWS_EVENT| Yes         | No          |
| UPDATE_ROWS_EVENT| Yes         | Yes         |
+------------------+--------------+-------------+

DELETE and UPDATE contain a lookup operation that uses the BI (before image) to find the target rows. The function Rows_log_event::do_before_row_operations allocates memory for the search, then calls Rows_log_event::row_operations_scan_and_key_setup to decide the search algorithm.

The search algorithm is chosen by Rows_log_event::decide_row_lookup_algorithm based on table index information and the slave_rows_search_algorithms setting. The possible strategies are:

I – Index scan / search

T – Table scan

H – Hash scan

Hi – Hash over index

Ht – Hash over the entire table

Decision matrix (simplified):

|--------------+-----------+------+------+------|
| Index/Option | I , T , H | I, T | I, H | T, H |
|--------------+-----------+------+------+------|
| PK / UK      | I         | I    | I    | Hi   |
| K            | Hi        | I    | Hi   | Hi   |
| NoIndex      | Ht        | Ht   | T    | Ht   |
|--------------+-----------+------+------+------|

By default the slave uses TABLE_SCAN for tables without a primary key and INDEX_SCAN for indexed tables.

Test

A test table ants_bnzbw_temp with several varchar and int columns and three BTREE indexes was created. The following commands were executed on the master:

CREATE TABLE `ants_bnzbw_temp` (
  `accrued_status` varchar(1) DEFAULT NULL,
  `contract_no` varchar(32) DEFAULT NULL,
  `business_date` date DEFAULT NULL,
  `prin_bal` int(11) DEFAULT NULL,
  `ovd_prin_bal` int(11) DEFAULT NULL,
  `ovd_int_bal` int(11) DEFAULT NULL,
  `int_amt` int(11) DEFAULT NULL,
  `ovd_prin_pnlt_amt` int(11) DEFAULT NULL,
  `ovd_int_pnlt_amt` int(11) DEFAULT NULL,
  KEY `accrued_status` (`accrued_status`) USING BTREE,
  KEY `contract_no` (`contract_no`) USING BTREE,
  KEY `business_date` (`business_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Row count: 522,490. The DELETE statement affected all rows in 25.86 seconds on the master.

Two slaves were configured with different slave_rows_search_algorithms values:

Slave 1: INDEX_SCAN,HASH_SCAN Slave 2: TABLE_SCAN,INDEX_SCAN Execution times observed:

Slave 1 completed the transaction in ~2,000 seconds.

Slave 2 was still active after 11,145 seconds.

Stack traces from both slaves show the replay path passing through Rows_log_event::do_apply_event and the corresponding index or hash scan functions.

Conclusion

Using INDEX_SCAN,HASH_SCAN for slave_rows_search_algorithms dramatically improves replication performance for large table deletes without a WHERE clause, provided enough memory is available to build the hash table.

Recommendations

Avoid full‑table DELETE or UPDATE; use TRUNCATE when possible.

Ensure tables have a primary key when using row‑based binlog.

Set slave_rows_search_algorithms to INDEX_SCAN,HASH_SCAN to gain the performance benefit.