A business system recently experienced significant lag on its replica databases, failing to catch up with the primary database, posing considerable operational risks. Despite low CPU, IO, and network usage on the replica servers, indicating no resource bottlenecks, parallel replay was enibled. The SHOW PROCESSLIST command showed continuous replay activity without thread blocking. Examination of relay log files did not reveal large transactions causing delays. ### Process Analysis

Issue Confirmation

Feedback from the operations team indicated severe replication lag. Screenshots of SHOW SLAVE STATUS were provided, showing fluctuating positions and increasing Seconds_Behind_Master. #### Resource Usage

Server resources were observed to be underutilized. Only one thread was primarily handling the replay process on the replica. #### Parallel Replay Parameters

On the primary server, binlog_transaction_dependency_tracking=WRITESET was configured. On the replica, slave_parallel_type=LOGICAL_CLOCK and slave_parallel_workers=64 were set. #### Error Log Comparison

Extracted logs for parallel replay analysis: ```

$ grep 12345 error_log | tail -n 3 2024-01-31T14:07:50.172007+08:00 1234 [Note] Multi-thread slave stats channel 'cluster': elapsed = 120; events = 3318582273; queue full = 207029; waited = 238; conflicts = 348754579743300; occupied = 34529247

Key data highlights long wait times due to non-parallelizable transactions. #### Concurrency Statistics

MySQL replicas rely on `last_committed` from binlogs for parallel replay eligibility. Below is an altered script for concurrency assessment: ```

$ mysqlbinlog --no-defaults log_file |grep -o 'last_commit.*' | sed 's/=/ /g' | awk '{print $2}' |sort -n | uniq -c |awk 'BEGIN {print "group_count Percentage"} {count[$1]=$2; sum+=$2} END {for (i in count) printf "%d %.2f%%\n", i, (count[i]/sum)*100|"sort -k 1,1n"}'

This script categorizes transaction groups by their last_committed value, revealing a significant portion with low concurrency potential. #### last_committed Mechanism Overview

The binlog_transaction_dependency_tracking parameter dictates dependency tracking methods (COMMIT_ORDER, WRITESET, SESSION_WRITESET). WRITESET enhances COMMIT_ORDER by incorporating uniquee key hashes, enabling better concurrency if conditions are met. ### Conclusion Analysis

Based on WRITESET limitations, many single last_committed transactions lack primary keys, forcing fallback to COMMIT_ORDER and serial processing, leading to delays. ### Optimization Measures

1. Add primary keys where feasible. 2. Adjust parameters like binlog_group_commit_sync_delay to optimize commit batching.