目錄Seconds_Behind_Master原始實(shí)現(xiàn)last_master_timestampclock_diff_with_master其他exec_time時(shí)間函數(shù)總結(jié)Seconds_Behind_Master

對(duì)于mysql主備實(shí)例，seconds_behind_master是衡量master與slave之間延時(shí)的一個(gè)重要參數(shù)。通過在slave上執(zhí)行'show slave status;'可以獲取seconds_behind_master的值。

原始實(shí)現(xiàn)

Definition：The number of seconds that the slave SQL thread is behind processing the master binary log.

Type：time_t（long）

計(jì)算方式如下：

rpl_slave.cc::show_slave_status_send_data()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); protocol->store( (longlong)(mi->rli->last_master_timestamp ? max(0L, time_diff) : 0)); }

主要分為以下兩種情況：

SQL線程等待IO線程獲取主機(jī)binlog，此時(shí)seconds_behind_master為0，表示備機(jī)與主機(jī)之間無延時(shí)； SQL線程處理relay log，此時(shí)seconds_behind_master通過(long)(time(0) ? mi->rli->last_master_timestamp) ? mi->clock_diff_with_master計(jì)算得到；last_master_timestamp

定義：

主庫binlog中事件的時(shí)間。

type: time_t (long)

計(jì)算方式：

last_master_timestamp根據(jù)備機(jī)是否并行復(fù)制有不同的計(jì)算方式。

非并行復(fù)制：

rpl_slave.cc:exec_relay_log_event()if ((!rli->is_parallel_exec() || rli->last_master_timestamp == 0) && !(ev->is_artificial_event() || ev->is_relay_log_event() || (ev->common_header->when.tv_sec == 0) || ev->get_type_code() == binary_log::FORMAT_DESCRIPTION_EVENT || ev->server_id == 0)){ rli->last_master_timestamp= ev->common_header->when.tv_sec + (time_t) ev->exec_time; DBUG_ASSERT(rli->last_master_timestamp >= 0);}

在該模式下，last_master_timestamp表示為每一個(gè)event的結(jié)束時(shí)間，其中when.tv_sec表示event的開始時(shí)間，exec_time表示事務(wù)的執(zhí)行時(shí)間。該值的計(jì)算在apply_event之前，所以event還未執(zhí)行時(shí)，last_master_timestamp已經(jīng)被更新。由于exec_time僅在Query_log_event中存在，所以last_master_timestamp在應(yīng)用一個(gè)事務(wù)的不同event階段變化。以一個(gè)包含兩條insert語句的事務(wù)為例，在該代碼段的調(diào)用時(shí)，打印出event的類型、時(shí)間戳和執(zhí)行時(shí)間

create table t1(a int PRIMARY KEY AUTO_INCREMENT ,b longblob) engine=innodb;begin;insert into t1(b) select repeat(’a’,104857600);insert into t1(b) select repeat(’a’,104857600);commit;

10T06:41:32.628554Z 11 [Note] [MY-000000] [Repl] event_type: 33 GTID_LOG_EVENT

2020-02-10T06:41:32.628601Z 11 [Note] [MY-000000] [Repl] event_time: 1581316890

2020-02-10T06:41:32.628614Z 11 [Note] [MY-000000] [Repl] event_exec_time: 0

2020-02-10T06:41:32.628692Z 11 [Note] [MY-000000] [Repl] event_type: 2   QUERY_EVENT

2020-02-10T06:41:32.628704Z 11 [Note] [MY-000000] [Repl] event_time: 1581316823

2020-02-10T06:41:32.628713Z 11 [Note] [MY-000000] [Repl] event_exec_time: 35

2020-02-10T06:41:32.629037Z 11 [Note] [MY-000000] [Repl] event_type: 19   TABLE_MAP_EVENT

2020-02-10T06:41:32.629057Z 11 [Note] [MY-000000] [Repl] event_time: 1581316823

2020-02-10T06:41:32.629063Z 11 [Note] [MY-000000] [Repl] event_exec_time: 0

2020-02-10T06:41:33.644111Z 11 [Note] [MY-000000] [Repl] event_type: 30    WRITE_ROWS_EVENT

2020-02-10T06:41:33.644149Z 11 [Note] [MY-000000] [Repl] event_time: 1581316823

2020-02-10T06:41:33.644156Z 11 [Note] [MY-000000] [Repl] event_exec_time: 0

2020-02-10T06:41:43.520272Z 0 [Note] [MY-011953] [InnoDB] Page cleaner took 9185ms to flush 3 and evict 0 pages

2020-02-10T06:42:05.982458Z 11 [Note] [MY-000000] [Repl] event_type: 19   TABLE_MAP_EVENT

2020-02-10T06:42:05.982488Z 11 [Note] [MY-000000] [Repl] event_time: 1581316858

2020-02-10T06:42:05.982495Z 11 [Note] [MY-000000] [Repl] event_exec_time: 0

2020-02-10T06:42:06.569345Z 11 [Note] [MY-000000] [Repl] event_type: 30    WRITE_ROWS_EVENT

2020-02-10T06:42:06.569376Z 11 [Note] [MY-000000] [Repl] event_time: 1581316858

2020-02-10T06:42:06.569384Z 11 [Note] [MY-000000] [Repl] event_exec_time: 0

2020-02-10T06:42:16.506176Z 0 [Note] [MY-011953] [InnoDB] Page cleaner took 9352ms to flush 8 and evict 0 pages

2020-02-10T06:42:37.202507Z 11 [Note] [MY-000000] [Repl] event_type: 16    XID_EVENT

2020-02-10T06:42:37.202539Z 11 [Note] [MY-000000] [Repl] event_time: 1581316890

2020-02-10T06:42:37.202546Z 11 [Note] [MY-000000] [Repl] event_exec_time: 0

并行復(fù)制：

rpl_slave.cc mts_checkpoint_routinets = rli->gaq->empty() ? 0 : reinterpret_cast<Slave_job_group *>(rli->gaq->head_queue())->ts; rli->reset_notified_checkpoint(cnt, ts, true); /* end-of 'Coordinator::'commit_positions' */

在該模式下備機(jī)上存在一個(gè)分發(fā)隊(duì)列g(shù)aq，如果gaq為空，則設(shè)置last_commit_timestamp為0；如果gaq不為空，則此時(shí)維護(hù)一個(gè)checkpoint點(diǎn)lwm，lwm之前的事務(wù)全部在備機(jī)上執(zhí)行完成，此時(shí)last_commit_timestamp被更新為lwm所在事務(wù)執(zhí)行完成后的時(shí)間。該時(shí)間類型為time_t類型。

ptr_group->ts = common_header->when.tv_sec + (time_t)exec_time; // Seconds_behind_master relatedrli->rli_checkpoint_seqno++;

if (update_timestamp) { mysql_mutex_lock(&data_lock); last_master_timestamp = new_ts; mysql_mutex_unlock(&data_lock);}

在并行復(fù)制下，event執(zhí)行完成之后才會(huì)更新last_master_timestamp，所以非并行復(fù)制和并行復(fù)制下的seconds_behind_master會(huì)存在差異。

clock_diff_with_master

定義：

The difference in seconds between the clock of the master and the clock of the slave (second - first). It must be signed as it may be <0 or >0. clock_diff_with_master is computed when the I/O thread starts; for this the I/O thread does a SELECT UNIX_TIMESTAMP() on the master. type: long

rpl_slave.cc::get_master_version_and_clock()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)); DBUG_EXECUTE_IF('dbug.mts.force_clock_diff_eq_0', mi->clock_diff_with_master= 0;); mysql_mutex_unlock(&mi->data_lock); }

該差值僅被計(jì)算一次，在master與slave建立聯(lián)系時(shí)處理。

其他exec_time

定義：

the difference from the statement’s original start timestamp and the time at which it completed executing. type: unsigned long

struct timeval end_time;ulonglong micro_end_time = my_micro_time();my_micro_time_to_timeval(micro_end_time, &end_time);exec_time = end_time.tv_sec - thd_arg->query_start_in_secs();時(shí)間函數(shù)

（1）time_t time(time_t timer) time_t為long類型，返回的數(shù)值僅精確到秒；

（2）int gettimeofday (struct timeval *tv, struct timezone *tz) 可以獲得微秒級(jí)的當(dāng)前時(shí)間；

（3）timeval結(jié)構(gòu)

#include <time.h>stuct timeval { time_t tv_sec; /*seconds*/ suseconds_t tv_usec; /*microseconds*/}總結(jié)

使用seconds_behind_master衡量主備延時(shí)只能精確到秒級(jí)別，且在某些場(chǎng)景下，seconds_behind_master并不能準(zhǔn)確反映主備之間的延時(shí)。主備異常時(shí)，可以結(jié)合seconds_behind_master源碼進(jìn)行具體分析。

以上就是詳解MySQL的Seconds_Behind_Master的詳細(xì)內(nèi)容，更多關(guān)于MySQL Seconds_Behind_Master的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！