Delay monitoring of MySQL master-slave replication

Master-Slave Replication latency Monitoring, my previous practice was to compare the difference (read_master_log_pos,exec_master_log_pos) of the two variables in show slave status\g, setting the difference to a range that you think is reasonable, Seconds_behind_master did not apply, do a resolution today:

The seconds_behind_master is determined by comparing the difference between the time stamp (timestamp) of SQL thread accepting events events and the IO THREAD execution event Timestamp-the number of seconds to determine how much slave is behind Master. If the time difference between the master and slave machine, the calculation of the time will not be affected (if the time is abnormal, then the number of seconds is not reliable)

If slave SQL thread or slave I/O thread is not connected to master, then the value of the variable is null.

0: The master slave replication is not delayed (in most cases).

Positive value: Represents the number of seconds that slave is behind master.

In a fast network situation, I/O thread can quickly get Binlog to slave relay-log from master. In this case, the value of the seconds_behind_master can really represent the number of seconds that slave is behind master. In the case of poor network, I/O thread synchronization is slow, slave received binary log information, SQL thread can be quickly executed. At this time Seconds_behind_master is 0, in this case slave behind master a lot.

In order to eliminate the interference of the network, we can refer to Percona tool pt-heartbeat.

The tool can calculate MySQL replication or PostgreSQL, which can update master or monitor replication. It can also read configuration from MY.CNF. It is based on the comparison of Timestmp, first of all need to ensure that the master and slave server time must be consistent, through with the same NTP server synchronization clock. It needs to create a heartbeat table on the main library, where the timestamp TS is the current timestamp now (), and the structure is copied to the library. After the table is built, will be in the main library in the background process mode to perform a row of updates to the operation of the command, regularly to the table to insert data, this cycle defaults to 1 seconds, and also from the library in the background to perform a monitoring command, and the main library to maintain a consistent cycle + 0.5S (default 0.5S delay check) to compare, copied over the TS value of the record and the same TS value on the main library, the difference of 0 means no delay, the greater the difference means the more the number of seconds delay.

Grammar:

     Pt-heartbeat [OPTION ...] [Dsn]–update|--monitor|--check|--stop

Example:

Update the Test.heartbeat table as a daemon on master.

     pt-heartbeat-d Test--update-h Master-server–daemonize

This column more highlights: http://www.bianceng.cn/database/MySQL/

To monitor replication latency on Slave:

     pt-heartbeat-d test--monitor-h slave-server
     pt-heartbeat-d test--monitor-h slave-server--dbi-driver Pg

Check only once for slave replication latency:

     pt-heartbeat-d Test--check H=slave-server

It consists of two parts: the first part is the--update instance, which connects to the time record of the master updating a table every--interval second. The second part is--monitor or--check instances connected to slave. It checks the timestamp in the new record to determine the delay between the two.

You can use the-create-table option to create a table, or to create it manually.

CREATE TABLE Heartbeat (
  TS                    varchar (num) NOT NULL,
  server_id             int unsigned not null PRIMARY KEY,
  file< C6/>varchar (255) default NULL,    --show master STATUS
  position              bigint unsigned default NULL,--show Master STA TUS
  relay_master_log_file varchar (255) DEFAULT NULL,    --show SLAVE STATUS
  exec_master_log_pos   bigint unsigned DEFAULT NULL  -show SLAVE STATUS
);

The storage engine recommends the use of the memory storage engine. Pt-heartbeat time is accurate to 0.01 seconds, the constant rate of master and slave must be synchronized through the NTP service. As long as the time difference between checks is less than 0.5 seconds, then pt-heartbeat'll is a zero seconds of delay.