MySQL Replication--复制延迟01--源码瞎猜
本人完全不懂MySQL源码，以下文字纯属瞎猜，如有误导，概不负责！、
源码版本：MySQL 5.6.28
在sql/rpl_slave.cc文件中，time_diff的计算代码为：
/* The pseudo code to compute Seconds_Behind_Master: if (SQL thread is running) { if (SQL thread processed all the available relay log) { if (IO thread is running) print 0; else print NULL; } else compute Seconds_Behind_Master; } else print NULL; */ if (mi->rli->slave_running) { /* Check if SQL thread is at the end of relay log Checking should be done using two conditions condition1: compare the log positions and condition2: compare the file names (to handle rotation case) */ if ((mi->get_master_log_pos() == mi->rli->get_group_master_log_pos()) && (!strcmp(mi->get_master_log_name(), mi->rli->get_group_master_log_name()))) { if (mi->slave_running == MYSQL_SLAVE_RUN_CONNECT) protocol->store(0LL); else protocol->store_null(); } else { long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp) - mi->clock_diff_with_master);
/* Apparently on some systems time_diff can be <0. Here are possible reasons related to MySQL: - the master is itself a slave of another master whose time is ahead. - somebody used an explicit SET TIMESTAMP on the master. Possible reason related to granularity-to-second of time functions (nothing to do with MySQL), which can explain a value of -1: assume the master's and slave's time are perfectly synchronized, and that at slave's connection time, when the master's timestamp is read, it is at the very end of second 1, and (a very short time later) when the slave's timestamp is read it is at the very beginning of second 2. Then the recorded value for master is 1 and the recorded value for slave is 2. At SHOW SLAVE STATUS time, assume that the difference between timestamp of slave and rli->last_master_timestamp is 0 (i.e. they are in the same second), then we get 0-(2-1)=-1 as a result. This confuses users, so we don't go below 0: hence the max(). last_master_timestamp == 0 (an "impossible" timestamp 1970) is a special marker to say "consider we have caught up". */ protocol->store((longlong)(mi->rli->last_master_timestamp ? max(0L, time_diff) : 0)); } } else { protocol->store_null(); }
1、当SQL线程停止时，返回NULL
2、当SLAVE正常运行时，如果SQL线程执行的位置是relay log的最后位置则返回0,否则返回NULL
3、当SLAVE正常运行时，复制延迟时间=当前从库系统时间(time(0)) - SQL线程处理的最后binlog的时间戳( mi->rli->last_master_timestamp) - 主从系统时间差(mi->clock_diff_with_master)
主从系统时间差(mi->clock_diff_with_master)
在sql/rpl_slave.cc文件中，主从系统时间差计算代码如下：
/* Compare the master and slave's clock. Do not die if master's clock is unavailable (very old master not supporting UNIX_TIMESTAMP()?). */ DBUG_EXECUTE_IF("dbug.before_get_UNIX_TIMESTAMP", { const char act[]= "now " "wait_for signal.get_unix_timestamp"; DBUG_ASSERT(opt_debug_sync_timeout > 0); DBUG_ASSERT(!debug_sync_set_action(current_thd, STRING_WITH_LEN(act))); };); master_res= NULL; if (!mysql_real_query(mysql, STRING_WITH_LEN("SELECT UNIX_TIMESTAMP()")) && (master_res= mysql_store_result(mysql)) && (master_row= mysql_fetch_row(master_res))) { mysql_mutex_lock(&mi->data_lock); mi->clock_diff_with_master= (long) (time((time_t*) 0) - strtoul(master_row[0], 0, 10)); mysql_mutex_unlock(&mi->data_lock); } else if (check_io_slave_killed(mi->info_thd, mi, NULL)) goto slave_killed_err; else if (is_network_error(mysql_errno(mysql))) { mi->report(WARNING_LEVEL, mysql_errno(mysql), "Get master clock failed with error: %s", mysql_error(mysql)); goto network_err; } else { mysql_mutex_lock(&mi->data_lock); mi->clock_diff_with_master= 0; /* The "most sensible" value */ mysql_mutex_unlock(&mi->data_lock); sql_print_warning("\"SELECT UNIX_TIMESTAMP()\" failed on master, " "do not trust column Seconds_Behind_Master of SHOW " "SLAVE STATUS. Error: %s (%d)", mysql_error(mysql), mysql_errno(mysql)); } if (master_res) { mysql_free_result(master_res); master_res= NULL; }
主从系统时间差=从库当前时间(time((time_t*) 0)) - 主库当前时间(UNIX_TIMESTAMP())，而主库时间是到主库上执行SELECT UNIX_TIMESTAMP()，然后取执行结果(strtoul(master_row[0], 0, 10))。
clock_diff_with_master的值是在IO线程启动时计算的，如果中途修改过主库时间，会导致clock_diff_with_master的值出现偏差。
从库SQL线程读取到relay log中的事件但未开始执行前就会更新last_master_timestamp的值，更新操作以event为单位。
非并行复制下last_master_timestamp计算
在sql/rpl_slave.cc文件中exec_relay_log_event方法中，计算非并行复制的last_master_timestamp的代码如下：
/** Top-level function for executing the next event in the relay log. This is called from the SQL thread. This function reads the event from the relay log, executes it, and advances the relay log position. It also handles errors, etc. This function may fail to apply the event for the following reasons: - The position specfied by the UNTIL condition of the START SLAVE command is reached. - It was not possible to read the event from the log. - The slave is killed. - An error occurred when applying the event, and the event has been tried slave_trans_retries times. If the event has been retried fewer times, 0 is returned. - init_info or init_relay_log_pos failed. (These are called if a failure occurs when applying the event.) - An error occurred when updating the binlog position. @retval 0 The event was applied. @retval 1 The event was not applied. */ static int exec_relay_log_event(THD* thd, Relay_log_info* rli) { DBUG_ENTER("exec_relay_log_event"); /* We acquire this mutex since we need it for all operations except event execution. But we will release it in places where we will wait for something for example inside of next_event(). */ mysql_mutex_lock(&rli->data_lock); /* UNTIL_SQL_AFTER_GTIDS requires special handling since we have to check whether the until_condition is satisfied *before* the SQL threads goes on a wait inside next_event() for the relay log to grow. This is reuired since if we have already applied the last event in the waiting set but since he check happens only at the start of the next event we may end up waiting forever the next event is not available or is delayed. */ if (rli->until_condition == Relay_log_info::UNTIL_SQL_AFTER_GTIDS && rli->is_until_satisfied(thd, NULL)) { rli->abort_slave= 1; mysql_mutex_unlock(&rli->data_lock); DBUG_RETURN(1); } Log_event *ev = next_event(rli), **ptr_ev; DBUG_ASSERT(rli->info_thd==thd); if (sql_slave_killed(thd,rli)) { mysql_mutex_unlock(&rli->data_lock); delete ev; DBUG_RETURN(1); } if (ev) { enum enum_slave_apply_event_and_update_pos_retval exec_res; ptr_ev= &ev; /* Even if we don't execute this event, we keep the master timestamp, so that seconds behind master shows correct delta (there are events that are not replayed, so we keep falling behind). If it is an artificial event, or a relay log event (IO thread generated event) or ev->when is set to 0, or a FD from master, or a heartbeat event with server_id '0' then we don't update the last_master_timestamp. */ if (!(rli->is_parallel_exec() || ev->is_artificial_event() || ev->is_relay_log_event() || ev->when.tv_sec == 0 || ev->get_type_code() == FORMAT_DESCRIPTION_EVENT || ev->server_id == 0)) { rli->last_master_timestamp= ev->when.tv_sec + (time_t) ev->exec_time; DBUG_ASSERT(rli->last_master_timestamp >= 0); }
其中when.tv_sec是事件在主库上的开始时间，而ev->exec_time在主库上的执行时间，只有Query_log_event和Load_log_event才会统计exec_time。
并行复制下last_master_timestamp计算
并行复制有一个分发队列gaq，sql线程将binlog事务读取到gaq，然后再分发给worker线程执行。并行复制时，binlog事件是并发穿插执行的，gaq中有一个checkpoint点称为lwm, lwm之前的binlog都已经执行，而lwm之后的binlog有些执行有些没有执行。 假设worker线程数为2，gap有1,2,3,4,5,6,7,8个事务。worker 1已执行的事务为1 4 6, woker 2执行的事务为2 3 ，那么lwm为4。 并行复制更新gap checkpiont时，会推进lwm点，同时更新last_master_timestamp为lwm所在事务结束的event的时间。因此，并行复制是在事务执行完成后才更新last_master_timestamp，更新是以事务为单位。同时更新gap checkpiont还受slave_checkpoint_period参数的影响。 这导致并行复制下和非并行复制统计延迟存在差距，差距可能为slave_checkpoint_period + 事务在备库执行的时间。这就是为什么在并行复制下有时候会有很小的延迟，而改为非并行复制时反而没有延迟的原因。 另外当sql线程等待io线程时且gaq队列为空时，会将last_master_timestamp设为0。同样此时认为没有延迟，计算得出seconds_Behind_Master为0。 抄自https://www.kancloud.cn/taobaomysql/monthly/140089
参考
