Seconds_behind_master Meaning and deficiency
The value of Seconds_behind_master is obtained by comparing the current timestamp of the salve server with the timestamp of the event in the binary log, so the delay is reported only when the event is executed.
1.1 If the standby replication thread is not running, the delay is reported as null.
1.2 Some errors such as network instability can cause replication to break or stop copying threads, but Seconds_behind_master will display as 0 instead of displaying errors
1.3 The standby may sometimes be unable to calculate the delay even if the standby thread is running, and if this happens, the standby will report 0 or null.
1.4 A large transaction can cause delay fluctuations, such as a transaction update data up to 1 hours, and last commit. This UPDATE statement will be logged to the binary log one hours later than it actually occurred, and when the repository executes the statement, it will temporarily report the standby for 1 hours and then quickly become 0.
For more information, refer to < High performance MySQL copy section >
Pt-heartbeat principle my improvement is to use the pt-heartbeat of the Percona Toolkit Toolkit, which works as follows:
2.1 Create a heartbeat table on master, update the field of the table at a certain time frequency, mainly by writing the current timestamp to the table
2.2 Connect to Slave to check the time history of the table, and compare the current system time of running pt-heartbeat to get the time difference, this time difference is the replication delay
Note 1: The system time in this 2 is not necessarily the slave system time, if the Pt-heartbeat copy monitoring script is running on master, then the current system time is the current system time of Master, At this point, you do not need to strictly synchronize the master server and the slave server time consistent.
Note 2: The heartbeat table created here must be in the library under replication synchronization, and some textbooks are written in the test library, but sometimes we filter the copy of the test library in the replication configuration.
Pt-heartbeat parameter Description pt-heartbeat usage format as follows, detailed usage can run Pt-heartbeat--help view, the main parameters are described as follows
usage:pt-heartbeat [OPTIONS] [DSN]--update|--monitor|--check|--stop
--update|--monitor|--check|--stop These four parameters must be selected and selected only,--update indicates that the timestamp of the heartbeat table on the master library is updated every default number of seconds-- Monitor indicates that the delay condition is monitored every default second,--check means that it exits only once, and--stop indicates the process of stopping the update timestamp
--daemonize Background execution
--file "--file=output.txt" Print--monitor the latest records to the specified file, good to prevent the full screen is the trouble of data.
--frames "--frames=1m,2m,3m" in the--monitor output in the [] record segment, the default is 1m,5m,15m. You can specify 1, such as:--frames=1s, and multiple separated by commas.
--interval the time between checking and updating. The default is to see 1s. The smallest unit is 0.01s and the maximum precision is two digits after the decimal point, so 0.015 will be adjusted to 0.02.
--log all logs that open daemonized mode will be printed to the file you have developed.
--monitor continuous monitoring from the latency situation
--MASTER-SERVER-ID Specifies the server_id of the master
--recurse the depth of the check for multi-level replication. Mode m-s-s ... Not the last one from all need to open log_slave_updates, so as to check.
Pt-heartbeat Monitoring replication latency and testing typical steps are as follows
4.1 Run the following command on the master server, where information such as Ip,user is the master MySQL information.
pt-heartbeat-d mydb--update-uroot-p 123456-p 3306-h 127.0.0.1--create-table--daemonize
4.2 Run the following command on the master server, where information such as Ip,user is slave MySQL information, and then specify the Master-server-id,
pt-heartbeat-d mydb--monitor-u y-p 123456-p 3306-h 10.0.11.244--master-server-id 101
4.3 At this point we insert a large amount of data on the master, such as an INSERT into SELECT, you can see the monitoring interface on the increase in latency and finally slowly become smaller, specific test code.
4.4 At this time we stop slave on the slave, observe the replication delay, you can see the delay is getting bigger
4.5 then we re-start slave, we can observe that the delay becomes smaller again.
Seconds_behind_master of monitoring replication delay and Pt-heartbeat improvement method
