Monitoring master-slave replication latency using Pt-heartbeat

MySQL master-slave replication is an important part of MySQL's highly available architecture, which can be used for load balancing, high availability and failover, as well as providing backup and more. For master-slave replication monitoring, relying solely on the show slave status provided by MySQL itself is unreliable. Pt-heartbeat is a good choice for master-slave replication latency Monitoring, this article describes the delay monitoring under the master-slave replication scenario and gives the corresponding example.

Pt-heartbeat is one of the Percona-toolkit kits, so it is necessary to install Percona-toolkit before use, please refer to: Percona-toolkit Installation and introduction

1, the role of Pt-heartbeat
Pt-heartbeat measures replication lag on a MySQL or PostgreSQL server.  You can use it to update a master or monitor a replica.  If possible, MySQL connection options is read from your. my.cnf file. For more details, please use the--HELP option, or try ' perldoc/usr/bin/pt-heartbeat ' for complete documentation.

Pt-heartbeat is a two-part MySQL and PostgreSQL replication delay Monitoring System then measures delay by looking at ACTU Al replicated data. This avoids reliance in the replication mechanism itself, which is unreliable. (for example, SHOW SLAVE STATUS on MySQL).

2, the principle of pt-heartbeat
The first part was an--update instance of Pt-heartbeat, connects to a master and updates a timestamp ("Heartbeat Recor D ") every--interval seconds. Since The heartbeat table may contain records from multiple masters (see "Multi-slave HIERARCHY"), the server ' s ID (@ @serv ER_ID) is used to identify records.
There is a table heartbeat on the main library to check for delays, which can be created manually or automatically
Pt-heartbeat uses the--update parameter to connect to the main library and continuously (according to the set--interval parameters) to update to the table using a timestamp heartbeat

The second part was a--monitor or--check instance of Pt-heartbeat that connects to a slave, examines the replicated heart Beat record from it immediate master or the specified--master-server-id, and computes the difference from the current SY Stem time. If replication between the slave and the master is delayed or broken, the computed difference would be greater than zero a D otentially Increase if--monitor is specified.
Pt-heartbeat use--monitor or--check to connect to the library, check the timestamp from the main library, and compare to the current system timestamp to produce a difference,
This value is used to determine the delay. (Note, the precondition is that the main library should be kept in sync with the slave library)

    you must either manually create the heartbeat table on the M Aster or use--create-table. See --create-table for the proper heartbeat table structure. The MEMORY storage engine is suggested, and not re-quired of course, for MySQL.
    The heartbeat table must contain a heartbeat row. By default, a heartbeat row is inserted if it doesn ' t exist. This feature can disabled with the--[no]insert-heartbeat-row option in case the database user does not have  INSERT privileges.

Pt-heartbeat depends only on the heartbeat record being replicated to the slave, so it works regardless of the replication Mechanism (built-in replication, a system such as continuent tungsten, etc). It works at any depth in the replication hierarchy; For example, it'll reliably report how far a slave lags its master ' s master. And if replication is stopped, it'll continue to work and report (accurately!), the slave is falling further and F Urther behind the master.
Pt-heartbeat has a maximum resolution of 0.01 second. The clocks on the master and slave servers must is closely synchronized via NTP. By default,--update checks happen on the edge of the second (e.g. 00:01) and--monitor checks happen halfway between Seco NDS (e.g. 00:01.5). As long as the servers ' clocks is closely synchronized and replication events is propagating in less than half a second, Pt-heartbeat would report zero seconds of delay.
Pt-heartbeat would try to reconnect if the connection have an error, but would not retry if it can ' t get a connection when it First starts.
The--dbi-driver option lets you with pt-heartbeat to monitor PostgreSQL as well. It's reported to work well with Slony-1 replication.

3, get pt-heartbeat Help information
A, get help
  [[email protected] ~]# pt-heartbeat #直接输入pt-heartbeat can get a brief description using the Pt-heartbeat --help get a complete help message
  usage:pt-heartbeat [OPTIONS] [DSN]--update|--monitor|--check|--stop
   
  errors in command-line arguments:
    * Specify at least one of--stop,--update, --monitor or--check
    *--database must be specified

B, a few important parameters
Specify at least one of--stop,--update,--monitor, or--check. #至少指定一个
--update,--monitor, and--check are mutually exclusive. #互斥参数
--daemonize and--check are mutually exclusive. #互斥参数

--check
Check slave delay once and exit. If You also specify--recurse, the tool would try to discover slave ' s of the
Given slave and check and print their lag, too. The hostname or IP and port for each slave are printed before its
Delay. --recurse works with MySQL.

--daemonize
Fork to the background and detach from the shell. POSIX operating systems only.

--frames
type:string; default:1m,5m,15m
Timeframes for averages.
Specifies the timeframes over which to calculate moving averages when--monitor is given. Specify as a
comma-separated list of numbers with suffixes. The suf?x can is s for seconds, M for minutes, h for hours, or D
For days. The size of the largest frame determines the maximum memory usage, as up to the specified number
of Per-second samples is kept in memory to calculate the averages. You can specify as many timeframes as
Like.

--monitor
Monitor slave delay continuously.
Specifies that pt-heartbeat should check the slave ' s delay every second and report to STDOUT (or if--file
is given, to the file instead). The output is the current delay followed by moving averages over the timeframe
given in--frames. For example,
5s [0.25s, 0.05s, 0.02s]

--stop
Stop running instances by creating the Sentinel file.

--update
Update a master ' s heartbeat.

4. Demo using Pt-heartbeat

A, first add table [[email protected] ~]# pt-heartbeat--user=root--password=myxt_gzmms-s/tmp/mysql.sock-d Test >-- master-server-id=11--create-table--update master> select * from heartbeat;+----------------------------+-------- ---+------------------+-----------+-----------------------+---------------------+| ts | server_id | File | Position | Relay_master_log_file | Exec_master_log_pos |+----------------------------+-----------+------------------+-----------+----------------- ------+---------------------+|        2014-12-01t09:48:14.003020 | 11 | mysql-bin.000390 | 677136957 |                 mysql-bin.000179 | |+----------------------------+-----------+------------------+-----------+-----------------------+---------- -----------+b, update heartbeat[[email protected on the main library] ~]# pt-heartbeat--user=root--password=myxt_gzmms-s/tmp/ mysql.sock-d Test >--master-server-id=11--update &[1] 31249c, monitoring delay from the library [[email protected] ~]# pt-hEartbeat--user=root--password=myxt_gzmms-s/tmp/mysql.sock-d Test >--master-server-id=11--monitor-- print-master-server-id1.00s [0.02s, 0.00s, 0.00s] One-#实时延迟, 1-minute delay, 5-minute delay, 15-minute delay 1.00s [0.03s, 0.01s, 0.00s] 11 #  author:leshami1.00s [0.05s, 0.01s, 0.00s] One # blog:http://blog.csdn.net/leshami1.00s [0.07s, 0.01s, 0.00s ] 111.00s [0.08s, 0.02s, 0.01s] 111.00s [0.10s, 0.02s, 0.01s] 111.00s [0.12s, 0.02s, 0.01s] 111.00s [0.1 3s, 0.03s, 0.01s] 11d, other operation example # Schedule update on the main library to use daemon mode [[email protected] ~]# pt-heartbeat--user=root--password= Myxt_gzmms-s/tmp/mysql.sock-d Test >--master-server-id=11--update--daemonize# Modify the update interval on the main library to 2s [[email  Protected] ~]# pt-heartbeat--user=root--password=myxt_gzmms-s/tmp/mysql.sock-d Test >--master-server-id=11--upd Ate--daemonize--interval=2# Stop the Pt-heartbeat daemon on the main library [[email protected] ~]# pt-heartbeat--stopsuccessfully Created file/tmp/pt-heartbeat-sentinel[[email protected]~]# rm-rf/tmp/pt-heartbeat-sentinel# A single view of the delay from the library [[email protected] ~]$ pt-heartbeat--user=root--password= Myxt_gzmms-s/tmp/mysql.sock-d Test >--master-server-id=11--check 1.00# use daemons to monitor and output logs from the library [[email protected] ~] # pt-heartbeat--user=root--password=myxt_gzmms-s/tmp/mysql.sock-d test--master-server-id=11--monitor--print-mast Er-server-id--daemonize--log=/tmp/slave-lag.log

Monitoring master-slave replication latency using pt-heartbeat