How do I monitor the replication latency for MySQL?

Source: Internet
Author: User
Tags local time time interval install perl percona

Pt-heartbeat

Database master from replication, replication status, data latency is a very important indicator of the normal, then how to monitor it?

Pt-heartbeat is a tool set developed by Percona to monitor the replication latency of MySQL and PostgreSQL.

More mature, such as Uber and other large companies are in use.

Monitoring principle

Build a heartbeat table in master, which has a timestamp field that pt-heartbeat periodically modifies the timestamp value.

Slave Copies the heartbeat table, which contains the time stamp for master to perform the modification action, compares it to the local time of the slave, and obtains a difference, which is the value of the replication delay, to determine whether the replication status is normal and whether the latency is in line with expectations.

Pt-heartbeat is calculated based on the actual copy record, so he doesn't care what way you use it to replicate.

Pt-heartbeat can monitor the level of replication at any depth because there are server_id fields in the heartbeat table, and you can specify which server_id to refer to when monitoring a slave delay, such as the slave and its master's Mas ter delay, specify the server_id of the target master.

Because Pt-heartbeat strictly relies on time, it is important to note that master and slave time must be synchronized.

Using the sample

Performs the creation of the heartbeat table on master and performs a circular update operation to execute the command:

Pt-heartbeat \
--user=root--ask-pass \
--host=127.0.0.1 \
--create-table-d master1 \
--interval=1--update \
--replace--daemonize
Where Master's connection information is specified,--create-table-d Master1 refers to creating a heartbeat table in the Master1 database, followed by a parameter that specifies the time interval to perform the update.

Then you can monitor the slave and execute the command:

Pt-heartbeat \
--user=root--ask-pass \
-H 192.168.31.207 \
-D Master1--table=heartbeat \
--monitor
The--monitor parameter indicates that you want to monitor, before you monitor the target information, including the Slave connection information, database, and table name.

Output Information Sample:

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

installation process

The following is the installation process under CentOS7:

$ 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

Pt-heartbeat's Official document address:

Https://www.percona.com/doc/percona-toolkit/2.1/pt-heartbeat.html
There is a detailed introduction of the principle and the use of parameters, interested can be looked at carefully.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.