Shulou(Shulou.com)06/01 Report--

Seconds_Behind_Master inaccuracy problem

Seconds_Behind_Mas analytic ter solution

1. Seconds_Behind_Master description:

According to the Seconds_Behind_Master found by show slave status, literally, it is the number of seconds that slave lags behind master. In general, this is true. We can see whether slave lags behind master through Seconds_Behind_Master numbers, but in some environments, it does make us hallucinate.

In the official website of mysql, there is a sentence about Seconds_Behind_Master as follows:

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

It is very clear that the value is SQL thread I _ thread O. The difference between.

2. Seconds_Behind_Master principle

Seconds_Behind_Master is such a difference by comparing the timestamp of event executed by sql_thread with the timestamp of io_thread copied event (abbreviated as ts). As we all know, relay-log is exactly the same as the bin-log of the main library, and the sql statement will be recorded with the current ts at the same time, so the comparative reference value comes from binlog.

3. Factors leading to the inaccuracy of Seconds_Behind_Master

a. When in the case of a fast network connection, I get O thread. Can quickly synchronize sql from master's binlog to slave's relay-log, so that this value can basically determine how many seconds slave lags behind master.

When the network environment is particularly bad, this value does make us hallucinate. Thread O synchronization is very slow. Every time the synchronization comes, SQL thread can execute it immediately. In this way, the Seconds_Behind_Master we see is 0, but really, slave has lagged far behind master. At this point, the comrades of the business department will complain that the slave and master data are wrong, and you will be very depressed when you see that the Seconds_Behind_Master is indeed zero.

In fact, at this time, we take a look at master, and slave can well determine the reasons for this period.

Mysql > show master status\ G

* * 1. Row *

File: *-bin.001291

Position: 896711460

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.69.6.163

Master_User: replica

Master_Port: 3801

Connect_Retry: 60

Master_Log_File: *-bin.001211

Read_Master_Log_Pos: 278633662

Relay_Log_File: *-relay-bin.002323

Relay_Log_Pos: 161735853

Relay_Master_Log_File: *-bin.001211

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 278633662

Relay_Log_Space: 161735853

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

B. There has been no data submission for a long time, slave I thread time O thread time and slave SQL thread time are kept at the old values, such as A (but in fact, the time on master is up to slave I). At this time, the main library commits, and slave I Unip O starts to synchronize the binlog,slave O thread time update with master, but the slave SQL thread time remains at A value, and there is a big delay at this time, but it is uncertain whether there will be a delay.

C.Mysql master-slave synchronization delay is affected by many factors, such as large transactions, slave database query pressure, network delay, etc.; these are relatively common; but they are also affected by the clock difference of the master-slave machine system, which may be easily ignored.

D. Summary: it seems that when we analyze the parameter Seconds_Behind_Master, we should take into account the file name of the binlog log between the master and slave and the specific network environment. Of course, when the network is unblocked, we can see the delay between master and slave directly through this parameter. However, on the whole, many of the factors affecting seconds_behind_master cannot be kept accurate.

4. For inaccurate solutions to second_behind_master

A tool in the mk-heartbeat,Maatkit universal toolkit that is thought to accurately determine the method of replication delay.

The implementation of mk-heartbeat is also realized with the help of timestmp comparison, which first needs to ensure that the master and slave servers must be consistent by synchronizing the clock with the same NTP server. It needs to create a heartbeat table on the master database, which has at least two fields: id and ts. Id is server_id,ts, which is the current timestamp now (). This structure will also be copied to the slave database. After the table is built, a row of update commands will be executed on the master database in the mode of background process, and the insert data in the table will be directed to the table regularly. This period defaults to 1 second. At the same time, the slave library will execute a monitoring command in the background. Compared with the period consistent with the main database, the copied ts value is compared with the same ts value on the main database. A difference of 0 means no delay, and a larger difference means more seconds of delay. We all know that replication is asynchronous and ts is not completely consistent, so the tool allows a gap of half a second, within which differences can be ignored as no latency. This tool cleverly borrows timestamp to check latency through real replication.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.