How to Monitor MySQL & PostgreSQL Replication Lag with pt-heartbeat

pt-heartbeat

When doing master-slave replication in a database, monitoring replication status and data lag is critical; how to monitor it?

pt-heartbeat is a tool from Percona designed to monitor replication lag for MySQL and PostgreSQL .

It is mature; large companies such as Uber use it.

Monitoring Principle

On the master a heartbeat table is created with a timestamp column; pt-heartbeat periodically updates the timestamp.

The slave replicates the heartbeat table, which includes the timestamp of the master's update; comparing it with the slave's local time yields the lag value, indicating replication health and expected delay.

pt-heartbeat calculates based on actual replication records, independent of the replication method.

It can monitor any depth of replication hierarchy because the heartbeat table contains a server_id column; when monitoring a specific slave you can specify which server_id to reference, e.g., to see the delay between a slave and its master’s master.

Since pt-heartbeat relies strictly on time, the master and slave clocks must be synchronized.

Usage Example

On the master, create the heartbeat table and run a periodic update with:

pt-heartbeat \
--user=root --ask-pass \
--host=127.0.0.1 \
--create-table -D master1 \
--interval=1 --update \
--replace --daemonize

The --create-table -D master1 option creates the heartbeat table in the master1 database; the interval parameter sets the update frequency.

Then monitor the slave with:

pt-heartbeat \
--user=root --ask-pass \
-h 192.168.31.207 \
-D master1 --table=heartbeat \
--monitor

The --monitor flag starts monitoring; the command includes the slave’s connection info, database, and table name.

Sample output:

0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
...

Here 0.00s shows the current lag; the bracketed values are the 1‑minute, 5‑minute, and 15‑minute averages.

Installation Process

Installation on CentOS 7:

$ wget percona.com/get/percona-toolkit.tar.gz
$ tar zxf percona-toolkit-2.2.19.tar.gz
$ cd percona-toolkit-2.2.19
$ yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Digest-MD5 perl-DBD-MySQL 
$ perl Makefile.PL
$ make
$ make test
$ make install

Summary

Official documentation for pt-heartbeat :

https://www.percona.com/doc/percona-toolkit/2.1/pt-heartbeat.html

It contains detailed explanations of the principle and parameters; interested readers should review it.