Understanding MySQL Replication Delay and System Lock Mechanisms

The article introduces the problem of replication delay in MySQL and shows that each time show slave status is executed the server calls show_slave_status_send_data , whose call stack is displayed to illustrate how the delay is calculated on the fly.

It then presents the core formula used by MySQL to compute the delay: time_diff = ((long)(time(0) - mi->rli->last_master_timestamp) - mi->clock_diff_with_master); where time(0) returns the current slave system time, last_master_timestamp is the timestamp from the event header plus execution time, and clock_diff_with_master represents the time difference between master and slave.

A well‑known pseudo‑code for Seconds_Behind_Master is also reproduced, showing the logic that prints 0 when both SQL and I/O threads are running, otherwise prints NULL or computes the actual lag.

The article clarifies that the binlog is flushed to disk only during the commit phase, while events are generated during statement execution. It lists the timestamps for different event types (GTID, Query, Map, Insert, XID) and demonstrates a typical event lifecycle with an example generated by the infobin tool.

Several common reasons for replication lag are enumerated, including large transactions, DDL on big tables, long‑running uncommitted transactions, missing primary keys, InnoDB row‑level locks, and sub‑optimal slave parameters such as sync_binlog , sync_relay_log , and innodb_flush_log_at_trx_commit .

The core of the analysis focuses on the system lock state observed on the slave. It explains that this state is entered when the SQL thread calls mysql_lock_tables , after which the thread proceeds to the InnoDB layer to locate and modify rows. The relevant part of rpl_slave.cc is shown: while(!sql_slave_killed(thd,rli)) { THD_STAGE_INFO(thd, stage_reading_event_from_the_relay_log); if(exec_relay_log_event(thd,rli)) { /* lock_tables may set system lock */ } }

Further debugging details are provided, listing GDB breakpoints for functions such as mysql_lock_tables , THD::enter_stage , and InnoDB index/read/delete APIs, which help trace the exact point where the system lock is acquired.

Finally, the author recommends reading his dedicated MySQL replication series for a deeper understanding and provides contact information.