未加星标

Measure Replication Delay with Percona Toolkit Heartbeat (pt-heartbeat)

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

Replication lag occurs when the slave cannot keep up with the changes being made on the master instance. The reasons for a lag can be network congestion, poor performance on the slave or service interruptions of the slave mechanism, leading to a large pile of work to be catched up by the slave. When you use SHOW SLAVE STATUS you can monitor the current delay, which is indicated in the Seconds_Behind_Master: 0 column. Ideally, this value is zero, but the smallest granularity you get for this delay counter is one second.

Percona offers a tool that allows measuring the delay more accurately, by writing timestamps in the master database and calculate the difference when they arrive at the slave: pt-heartbeat

On the Master

Create a dedicated database schema called percona and add the following table.

CREATE TABLE IF NOT EXISTS `percona`.`heartbeat` (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

Then you can launch the script on the master, where it will write new timestamps periodically as long as the script is running.

pt-heartbeat -D percona --update --utc h=127.0.0.1,u=root,p=SECRET,P=3306

The -D flag specifies the database schema where the table for the heartbeat data is stored. The update command is needed on the master for updating the table and the last part is the DSN, which specifies host address, user name and password. Very important is the UTC flag utc, that ensures that the timestamps will be interpreted as UTC, regardless of the timezone setting.

On the Slave

Create a user for reading the replicated heartbeat table like this:

GRANT REPLICATION SLAVE,PROCESS,SELECT ON percona.* TO `percona_checksum`@'10.20.30.40' IDENTIFIED BY 'SECRET';

Then you can run the script and point it to the slave. It will output precise delay counts in fractions of seconds

pt-heartbeat h=127.0.0.1,u=percona_checksum,p=SECRET,P=3306 -D percona --monitor --utc --master-server-id 1

Notice the different DSN, the monitor flag and the master-server id, which needs to be the one of your master of course. You need this because the tool supports hierarchies of masters and therefore you would need to know which one is to be considered.

Results

The results will look similar to this

0.09s [ 0.00s, 0.00s, 0.00s ]
0.02s [ 0.20s, 0.00s, 0.00s ]
0.09s [ 0.00s, 0.00s, 0.00s ]
0.03s [ 0.02s, 0.00s, 0.00s ]
0.09s [ 0.01s, 0.00s, 0.00s ]
0.09s [ 0.01s, 0.00s, 0.00s ]
0.09s [ 0.01s, 0.00s, 0.00s ]
0.08s [ 0.01s, 0.00s, 0.00s ]
0.08s [ 0.01s, 0.00s, 0.00s ]
0.10s [ 0.01s, 0.00s, 0.00s ]
0.12s [ 0.02s, 0.00s, 0.00s ]
0.08s [ 0.01s, 0.00s, 0.00s ]
0.11s [ 0.02s, 0.00s, 0.00s ]
0.08s [ 0.02s, 0.00s, 0.00s ]
0.09s [ 0.02s, 0.00s, 0.00s ]
0.08s [ 0.02s, 0.00s, 0.00s ]
0.08s [ 0.03s, 0.01s, 0.00s ]

The output is the current delay followed by moving averages over 1m, 5m and 15m, as you might know from your favorite OS already.

Have a look at the official documentation , there is a lot of options available.

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:Measure Replication Delay with Percona Toolkit Heartbeat (pt-heartbeat)
本站链接:https://www.codesec.net/view/621072.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(61)