Detailed explanation of using pt-heartbeat to monitor MySQL replication delay

pt-heartbeat

When the database is replicated between master and slave, the replication status and data delay are very critical indicators. So how to monitor them?

pt-heartbeat is a tool developed by PERCONA that is specifically designed to monitor replication delays in MySQL and PostgreSQL.

It is relatively mature and is used by large companies such as Uber.

Let's take a look at the detailed introduction below:

Monitoring principle

Create a heartbeat table in the master with a timestamp field. pt-heartbeat will periodically modify the timestamp value.

The slave will copy the heartbeat table, which contains the timestamp of the master's modification action, and compare it with the local time of the slave to get a difference, which is the value of the replication delay, so as to determine whether the replication status is normal and whether the delay time meets expectations.

pt-heartbeat is calculated based on the actual replication records, so it doesn't matter what method you use for replication.

pt-heartbeat can monitor any depth of replication hierarchy. Because there is a server_id field in the heartbeat table, you can specify which server_id to refer to when monitoring the latency of a slave. For example, if you want to know the latency between this slave and its master's master, just specify the server_id of the target master.

Because pt-heartbeat is strictly dependent on time, you need to make sure that the time of the master and slave are synchronized.

Usage Examples

Create a heartbeat table for the master and perform a cyclic update operation. Execute the command:

It specifies the connection information of the master. --create-table -D master1 means to create a heartbeat table in the master1 database. The following parameters specify the time interval for executing updates.

Then you can monitor the slave and execute the command:

The --monitor parameter indicates that monitoring is to be performed. The previous parameters are the information of the monitoring target, including the slave connection information, database, and table name.

Output information example:

0.00s indicates the current delay information, and [0.00s, 0.00s, 0.00s] indicates the average value of 1m, 5m, and 15m.

Installation Process

The following is the installation process under CentOS7:

The official documentation address of pt-heartbeat: https://www.percona.com/doc/percona-toolkit/2.1/pt-heartbeat.html

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.