heartbeat不断重试会变成内部存款和储蓄器缓慢增加

新近同事反映,在接纳pt-heartbeat监察和控制主从复制延迟的过程中,如若master
down掉了,则pt-heartbeat则会接连战败,但会持续重试。

pt-heartbeat,heartbeat

pt-heartbeat是用来监测主从延迟的事态的,妇孺皆知,守旧的通过show slave
status\G命令中的Seconds_Behind_Master值来剖断主从延迟并不可信赖。

pt-heartbeat的思路比极美丽妙,它在master中插入一条带有当前岁月(MySQL中的now()函数)的记录到心跳表中,然后,该记录会复制到slave中。slave依据当下的系统时间戳(Perl中的time函数)减去heartbeat表中的记录值来判断主从的推移情况。具体,可参照上面–skew参数的表明。

 

大面积用法:

Master上

需用–update参数

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 -D test

其间,–update会每秒更新二回heartbeat表的笔录
-D指的是heartbeat表所在的database

-D是–database的缩写,–database后边即能够直接加参数,如–database
test,也可用等号加参数,如–database=test,但是缩写只可以通过-D
test加参数。

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 --database test 
# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 --database=test 

瞩目:在率先次运转时,需带上 –create-table参数制造heartbeat表并插入第三条记下。也可增加–daemonize参数,让该脚本以往台进度运维。

 

Slave上

可用–monitor参数大概–check参数

–monitor参数是无休止监测并出口结果

# pt-heartbeat -D test –monitor -h 192.168.244.20 –master-server-id=1
-u monitor -p monitor123

10061.00s [ 167.68s, 33.54s, 11.18s ]
10062.00s [ 335.38s, 67.08s, 22.36s ]
10063.01s [ 503.10s, 100.62s, 33.54s ]
...

–check参数是只监测一遍就退出了

# pt-heartbeat -D test –check -h 192.168.244.20 –master-server-id=1
-u monitor -p monitor123

10039.00

在意:–update,
–monitor和–check3者是排斥的,–daemonize只适用于–update场景

 

看望各参数的意思

–ask-pass

    连接数据库时提醒密码

Prompt for a password when connecting to MySQL.

–charset
    short form: -A

    私下认可字符集(个人感觉那一个选项很鸡肋)

short form: -A; type: string
Default character set. If the value is utf8, sets Perl’s binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode
on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

–check

 
 检查从的延迟后退出,假使在级联复制中,还是能钦赐–recurse参数,这时候,会检验从库的从库的推移景况。

Check slave delay once and exit. If you also specify --recurse, the tool will 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 is printed before its
delay. --recurse only works with MySQL.

–check-read-only

   检查server是不是是只读的,假使是只读,则会跳过插入动作。

Check if the server has read_only enabled; If it does, the tool skips doing any inserts.

–config

   Read this comma-separated list of config files; if specified, this
must be the first option on the command line.

   将参数写入到参数文件中,

   有以下几点必要注意:

   1> # pt-heartbeat –config pt-heartbeat.conf,而不可能是#
pt-heartbeat –config=pt-heartbeat.conf 

   2> 参数文件中只援助如下写法

option
option=value

   
option前面不可能带上–,而且option只可以是全拼,无法是缩写,譬如database,无法缩写为-D

   
具体写法可参照:

    试举壹例,如下所示

# cat pt-heartbeat.conf 
host=192.168.244.20
user=monitor
password=monitor123
monitor
database=test
master-server-id=1 

–create-table

   成立heartbeat表要是该表不存在,该表由–database和–table参数来认同。

   在那之中–table表的概念如下所示:

CREATE TABLE 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
);

Create the heartbeat --table if it does not exist.
This option causes the table specified by --database and --table to be created with the following MAGIC_create_heartbeat table definition:
CREATE TABLE 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
);
The heartbeat table requires at least one row. If you manually create the heartbeat table, then you must insert a row by doing:
INSERT INTO heartbeat (ts, server_id) VALUES (NOW(), N);
or if using --utc:
INSERT INTO heartbeat (ts, server_id) VALUES (UTC_TIMESTAMP(), N);
where N is the server’s ID; do not use @@server_id because it will replicate and slaves will insert their own server ID instead of the master’s server ID.
This is done automatically by --create-table.
A legacy version of the heartbeat table is still supported:
CREATE TABLE heartbeat (
id int NOT NULL PRIMARY KEY,
ts datetime NOT NULL
);
Legacy tables do not support --update instances on each slave of a multi-slave hierarchy like “master ->slave1 -> slave2”. To manually insert the one required row into a legacy table:
INSERT INTO heartbeat (id, ts) VALUES (1, NOW());
or if using --utc:
INSERT INTO heartbeat (id, ts) VALUES (1, UTC_TIMESTAMP());
The tool automatically detects if the heartbeat table is legacy.

–create-table-engine

   钦点heartbeat表的蕴藏引擎

type: string
Sets the engine to be used for the heartbeat table. The default storage engine is InnoDB as of MySQL 5.5.5.

–daemonize

 
 脚本以守护进程运营,这样就算脚本推行的顶峰断开了,脚本也不会停下运转。

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

–database

   钦点heartbeat表所在的数据库

short form: -D; type: string
The database to use for the connection.

–dbi-driver

   pt-heartbeat不仅能检验MySQL之间的心跳延迟情况,还足以检验PG。

   该参数钦定连接使用的驱动,默感到mysql,也可内定为Pg

default: mysql; type: string
Specify a driver for the connection; mysql and Pg are supported.

–defaults-file

   钦赐参数文件的岗位,必须为相对路线。

short form: -F; type: string
Only read mysql options from the given file. You must give an absolute pathname.

–file

   将时尚的–monitor消息输出到文件中,注意最新,新的消息会覆盖旧的音信。

 
 若是不加该参数,则monitor的新闻会直接出口到终极上,该选用经常和–daemonize参数一齐利用。

   譬如,

   # pt-heartbeat -D test –monitor -h 192.168.244.20
–master-server-id=1 -u monitor -p monitor123 –file=result

   该命令会在当前目录下生成多少个result文件,记录最新的检查测试消息

# cat result 
1376.00s [ 1126.25s, 225.25s, 75.08s ]

type: string
Print latest --monitor output to this file.
When --monitor is given, prints output to the specified file instead of to STDOUT. The file is opened, truncated,and closed every interval, so it will only contain the most recent statistics. Useful when --daemonize
is given.

–frames

 
 计算的时刻窗口,默认为一m,5m,15m,即分别总结一min,伍min和一伍min内的平分延迟处境。

 
 单位能够是s,m,h,d,注意:时间窗口越大,需求缓存的结果越来越多,对内部存款和储蓄器的开支也越大。

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 suffix can be 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 are kept in memory to calculate the averages. You can specify as many timeframes as
you like.

–help 

Show help and exit.

–host 

   钦命连接的主机,可缩写为-h

short form: -h; type: string
Connect to host.

–[no]insert-heartbeat-row

   官方解释如下:

default: yes
Insert a heartbeat row in the --table if one doesn’t exist.
The heartbeat --table requires a heartbeat row, else there’s nothing to --update, --monitor, or --check! By default, the tool will insert a heartbeat row if one is not already present. You can disable this
feature by specifying --no-insert-heartbeat-row in case the database user does not have INSERT privileges.

    事实上,在实践如下命令时,

    # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123

    假使,heartbeat表为空,则会自行insert一条记下。

   
但如果钦命了–no-insert-heartbeat-row参数,则不会自行创设,此时,会唤醒如下音讯:

# pt-heartbeat -D test --update -h 192.168.244.10 -u monitor -p monitor123 --no-insert-heartbeat-row
No row found in heartbeat table for server_id 1.
At least one row must be inserted into the heartbeat table for server_id 1.
Please read the DESCRIPTION section of the pt-heartbeat POD.

   
PS:在测试的经过中,开掘合法并不曾完好的校验那几个参数,即使传入–no-insert-heartbeat和–insert-heartbeat参数也不会报错,不过传入–12三-insert-heartbeat-ro,会报错“Unknown
option: 1二三-insert-heartbeat-ro”。

default: yes
Insert a heartbeat row in the --table if one doesn’t exist.
The heartbeat --table requires a heartbeat row, else there’s nothing to --update, --monitor, or --check! By default, the tool will insert a heartbeat row if one is not already present. You can disable this
feature by specifying --no-insert-heartbeat-row in case the database user does not have INSERT privileges.

–interval

   update和check heartbeat表的频率,私下认可是①s。

type: float; default: 1.0
How often to update or check the heartbeat --table. Updates and checks begin on the first whole second then repeat every --interval seconds for --update and every --interval plus --skew seconds for
--monitor.
For example, if at 00:00.4 an --update instance is started at 0.5 second intervals, the first update happens at 00:01.0, the next at 00:01.5, etc. If at 00:10.7 a --monitor instance is started at 0.05 second intervals with
the default 0.5 second --skew, then the first check happens at 00:11.5 (00:11.0 + 0.5) which will be --skew seconds after the last update which, because the instances are checking at synchronized intervals, happened at
00:11.0.
The tool waits for and begins on the first whole second just to make the interval calculations simpler. Therefore,the tool could wait up to 1 second before updating or checking.
The minimum (fastest) interval is 0.01, and the maximum precision is two decimal places, so 0.015 will be rounded to 0.02.
If a legacy heartbeat table (see --create-table) is used, then the maximum precision is 1s because the ts column is type datetime.

–log

   在脚本以守护进程推行时,将结果输出到log钦定的文本中。

type: string
Print all output to this file when daemonized.

–master-server-id

 
 指定master的server_id,在检查测试从的延迟时,必须钦命该参数,不然会报如下错误:

The --master-server-id option must be specified because the heartbeat table `test`.`heartbeat` uses the server_id column for --update or --check but the server's master could not be automatically determined.

type: string
Calculate delay from this master server ID for --monitor or --check. If not given, pt-heartbeat attempts to connect to the server’s master and determine its server id.

–monitor

   持续的检验并出口从的推迟情形

   当中,检查测试并出口的成效有–interval参数决定,默感觉壹s

   注意:与–check的分别在于:

      一> –monitor是持续输出的,而–check是检验一次即退出。

      二> –monitor可与–file参数搭配,而–check与–file参数搭配无效。

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 ]

–password

  钦命登6的密码,缩写为-p

short form: -p; type: string
Password to use when connecting. If password contains commas they must be escaped with a backslash:
“exam,ple”

–pid

  创建pid文件

type: string
Create the given PID file. The tool won’t start if the PID file already exists and the PID it contains is different than the current PID. However, if the PID file exists and the PID it contains is no longer running, the tool will
overwrite the PID file with the current PID. The PID file is removed automatically when the tool exits.

–port

  钦赐登陆的端口,缩写为-P

short form: -P; type: int
Port number to use for connection.

heartbeat不断重试会变成内部存款和储蓄器缓慢增加。–print-master-server-id

  同时输出主的server_id,在–monitor景况下,默许输出为

1272.00s [ 21.20s,  4.24s,  1.41s ]

 假使内定了该参数,则输出为

1272.00s [ 21.20s,  4.24s,  1.41s ] 1

Print the auto-detected or given --master-server-id. If --check or --monitor is specified, specifying this option will print the auto-detected or given --master-server-id at the end of each line.

–recurse

 在–check格局下,用于检查测试级联复制中从的延迟境况。在那之中,–recurse用于钦点级联的层级。

type: int
Check slaves recursively to this depth in --check mode.
Try to discover slave servers recursively, to the specified depth. After discovering servers, run the check on each one of them and print the hostname (if possible), followed by the slave delay.
This currently works only with MySQL. See --recursion-method.

–recursion-method

  在级联复制中,找到slave的格局。有show processlist和show slave
hosts二种。

type: array; default: processlist,hosts
Preferred recursion method used to find slaves.
Possible methods are:
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
none Do not find slaves
The processlist method is preferred because SHOW SLAVE HOSTS is not reliable. However, the hosts method is required if the server uses a non-standard port (not 3306). Usually pt-heartbeat does the right thing and finds
the slaves, but you may give a preferred method and it will be used first. If it doesn’t find any slaves, the other methods will be tried.

–replace

 
在–update方式下,暗中同意是接纳update操作实行记录的立异,但神蹟你不太承认heartbeat表中是不是任何笔录时,此时可选用replace操作。

  注意:假如是通过update实行记录的翻新,假使在本子运营的长河中,truncate
heartbeat表,脚本并不会那多少个退出,可是heartbeat表也有不会变动新的笔录。

 
但尽管是因此replace格局举行记录的更新,则正是是在上边那种现象下,heartbeat表依旧会生成新的笔录。个人感觉通过replace操作进行记录的立异更可信赖。

Use REPLACE instead of UPDATE for –update.
When running in --update mode, use REPLACE instead of UPDATE to set the heartbeat table’s timestamp.The REPLACE statement is a MySQL extension to SQL. This option is useful when you don’t know whether
the table contains any rows or not. It must be used in conjunction with –update.

–run-time

  钦点脚本运维的时日,无论是针对–update操作依旧–monitor操作均实用。

type: time
Time to run before exiting.

–sentinel

  “哨兵”,要是钦定的文本存在则提议,默以为/tmp/pt-heartbeat-sentinel

type: string; default: /tmp/pt-heartbeat-sentinel
Exit if this file exists.

 
经测试,固然未有带上–sentinel参数,假使/tmp/pt-heartbeat-sentinel文件存在,则脚本一施行时就直接退出。

  –sentinel功能在于自定义监察和控制文件。

 
譬如在实行如下命令时, /root/1贰三文本并不设有,则该脚本会继续运转,在剧本运转的长河中,创设该文件,则脚本会霎时退出。

  # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123  –sentinel=/root/123

–slave-user

   设置连接slave的用户

type: string
Sets the user to be used to connect to the slaves. This parameter allows you to have a different user with less privileges on the slaves but that user must exist on all slaves.

–slave-password

    设置连接slave的用户密码

type: string
Sets the password to be used to connect to the slaves. It can be used with –slave-user and the password for the user must be the same on all slaves.

 –set-vars

    设置脚本在与MySQL交互进程时的对话变量,但就好像并未怎么用

type: Array
Set the MySQL variables in this comma-separated list of variable=value pairs.
By default, the tool sets:
wait_timeout=10000
Variables specified on the command line override these defaults. For example, specifying --set-vars wait_timeout=500 overrides the defaultvalue of 10000.
The tool prints a warning and continues if a variable cannot be set.

–skew

  钦赐check相对于update的延迟时间。暗许为0.5秒

 
即–update更新一遍后,–check会在0.伍秒后检查本次更新所对应的主导延迟情状。

 
恐怕有人会相比较好奇,脚本是怎么着晓得记录是哪一天更新的,实际上,每一次–update的时刻都是秒的整点值,譬如,在那之中一遍记录的值为“2016-0玖-25T一3:0四:0陆.003130”。然后,0.5s后,脚本获取slave上的类别时间,然后减去heartbeat中记录值,来作为宗旨延迟的时光。那将要求,主从上的体系时间须求保持壹致,不然获得的结果就不曾子舆考价值。

  下边,可看看源码达成,那几个是整个脚本的主题逻辑。

         my ($ts, $hostname, $server_id) = $sth->fetchrow_array();
         my $now = time;
         PTDEBUG && _d("Heartbeat from server", $server_id, "\n",
            " now:", ts($now, $utc), "\n",
            "  ts:", $ts, "\n",
            "skew:", $skew);
         my $delay = $now - unix_timestamp($ts, $utc) - $skew;
         PTDEBUG && _d('Delay', sprintf('%.6f', $delay), 'on', $hostname);

         # Because we adjust for skew, if the ts are less than skew seconds
         # apart (i.e. replication is very fast) then delay will be negative. 
         # So it's effectively 0 seconds of lag.
         $delay = 0.00 if $delay < 0;

type: float; default: 0.5
How long to delay checks.
The default is to delay checks one half second. Since the update happens as soon as possible after the beginning of the second on the master, this allows one half second of replication delay before reporting that the slave lags
the master by one second. If your clocks are not completely accurate or there is some other reason you’d like to delay the slave more or less, you can tweak this value. Try setting the PTDEBUG environment variable to see
the effect this has.

–socket

short form: -S; type: string
Socket file to use for connection.

–table

  钦定心跳表的名字,默以为heartbeat

type: string; default: heartbeat
The table to use for the heartbeat.
Don’t specify database.table; use --database to specify the database.
See --create-table.

–update

  更新master中heartbeat表的记录

Update a master’s heartbeat.

–user

  钦赐连接的用户

short form: -u; type: string
User for login if not current user.

–utc

 
忽略系统时区,而利用UTC。要是要动用该选项,则–update,–monitor,–check中务必同时选择。

Ignore system time zones and use only UTC. By default pt-heartbeat does not check or adjust for different system or MySQL time zones which can cause the tool to compute the lag incorrectly. Specifying this option is
a good idea because it ensures that the tool works correctly regardless of time zones.
If used, this option must be used for all pt-heartbeat instances: --update, --monitor, --check, etc.
You should probably set the option in a --config file. Mixing this option with pt-heartbeat instances not using this option will cause false-positive lag readings due to different time zones (unless all your systems are
set to use UTC, in which case this option isn’t required).

–version

  打字与印刷版本音信

–[no]version-check

  检查pt,连接的MySQL Server,Perl以及DBD::mysql的版本新闻。

  并且打印那些软件特定版本的主题材料

Check for the latest version of Percona Toolkit, MySQL, and other programs.
84 Chapter 2. Tools
Percona Toolkit Documentation, Release 2.2.19
This is a standard “check for updates automatically” feature, with two additional features. First, the tool checks the version of other programs on the local system in addition to its own version. For example, it checks the
version of every MySQL server it connects to, Perl, and the Perl module DBD::mysql. Second, it checks for and warns about versions with known problems. For example, MySQL 5.5.25 had a critical bug and was re-released
as 5.5.25a.
Any updates or known problems are printed to STDOUT before the tool’s normal output. This feature should never interfere with the normal operation of the tool.

 

 

 

   

 

pt-heartbeat是用来监测主从延迟的气象的,驰名中外,古板的通过show slave
status\G命令中的Seconds_Behind_Master值来剖断主从延…

pt-heartbeat是用来监测主从延迟的事态的,无人不知,古板的通过show slave
status\G命令中的Seconds_Behind_Master值来判别主从延迟并不可信。

重试本未有可过分批评,毕竟从使用者的角度来讲,希望pt-heartbeat能不断重试,直到再也连接上数据库。可是,他们发觉,不断的重试会带来内部存款和储蓄器的缓缓增加。

997755.com澳门葡京 ,pt-heartbeat的思路相比相当漂亮妙,它在master中插入一条带有当前岁月(MySQL中的now()函数)的笔录到心跳表中,然后,该记录会复制到slave中。slave依照当前的种类时间戳(Perl中的time函数)减去heartbeat表中的记录值来推断主从的延期景况。具体,可参看上面–skew参数的表明。

 

 

重现

周围用法:

环境:           

Master上

pt-heartbeat v2.2.19,MySQL社区版 v5.6.31,Perl  v5.10.1,RHEL
6.7,内存500M

需用–update参数

为了防止数据库启动和停止对pt-heartbeat内部存款和储蓄器使用率的熏陶,故MySQL和pt-heartbeat分别运行在不相同的主机上。

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 -D test

 

里头,–update会每秒更新3遍heartbeat表的笔录
-D指的是heartbeat表所在的database

运行pt-heartbeat

-D是–database的缩写,–database前边即能够直接加参数,如–database
test,也可用等号加参数,如–database=test,可是缩写只可以通过-D
test加参数。

# pt-heartbeat –update -h 192.168.244.10 -u monitor -p monitor123 -D
test –create-table 

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 --database test 
# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 --database=test 

 

留意:在率先次运转时,需带上 –create-table参数创建heartbeat表并插入第叁条记下。也可拉长–daemonize参数,让该脚本以往台进程运维。

监察和控制pt-heartbeat的内部存款和储蓄器使用率

如:

获取pid

# pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 -D test --create-table --daemonize

# ps -ef |grep pt-heartbeat

 

root       1505   1471  0 19:13 pts/0    00:00:08 perl /usr/local/bin/pt-heartbeat --update -h 192.168.244.10 -u monitor -p monitor123 -D test --create-table
root       1563   1545  2 19:50 pts/3    00:00:00 grep pt-heartbeat

Slave上

查阅该进程的内部存款和储蓄器使用率

可用–monitor参数也许–check参数

# top -p 1505

–monitor参数是无休止监测并出口结果

运维了0:一5.00(TIME+列),MEM一向牢固在三.3%

# pt-heartbeat -D test –monitor -h 192.168.244.20 –master-server-id=1
-u monitor -p monitor123

997755.com澳门葡京 1

10061.00s [ 167.68s, 33.54s, 11.18s ]
10062.00s [ 335.38s, 67.08s, 22.36s ]
10063.01s [ 503.10s, 100.62s, 33.54s ]
...

 

–check参数是只监测2回就淡出了

现关闭数据库

# pt-heartbeat -D test –check -h 192.168.244.20 –master-server-id=1
-u monitor -p monitor123

# service mysqld stop

10039.00

 

专注:–update,
–monitor和–check三者是排斥的,–daemonize只适用于–update场景

刚刚的pt-heartbeat命令不断输出以下新闻

 

997755.com澳门葡京 2

探望各参数的意思

 

–ask-pass

同等CPU时间后,MEM拉长到肆.肆%,
增进了壹%,思虑到内部存款和储蓄器500M,该进程的内部存款和储蓄器占用增添了五M,固然不是成都百货上千,但思索到进程的内部存款和储蓄器增添并从未停下的情致,那个场馆依然要引起注意的。

    连接数据库时提示密码

997755.com澳门葡京 3

Prompt for a password when connecting to MySQL.

 

–charset
    short form: -A

同时,通过pmap命令,发掘,000000000133一千地址的PAJEROSS和Dirry也会升高,增进的速率是4k/s

    私下认可字符集(个人认为这几个选项很鸡肋)

997755.com澳门葡京 4

short form: -A; type: string
Default character set. If the value is utf8, sets Perl’s binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode
on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

 

–check

新兴商讨pt-heartbeat的源码,才发当代码有点bug

 
 检查从的延迟后退出,即使在级联复制中,还是能钦点–recurse参数,那时候,会检验从库的从库的推移情状。

my $tries = 2;
   while ( !$dbh && $tries-- ) {
      PTDEBUG && _d($cxn_string, ' ', $user, ' ', $pass,
         join(', ', map { "$_=>$defaults->{$_}" } keys %$defaults ));

      $dbh = eval { DBI->connect($cxn_string, $user, $pass, $defaults) };

      if ( !$dbh && $EVAL_ERROR ) {
         if ( $EVAL_ERROR =~ m/locate DBD\/mysql/i ) {
            die "Cannot connect to MySQL because the Perl DBD::mysql module is "
               . "not installed or not found.  Run 'perl -MDBD::mysql' to see "
               . "the directories that Perl searches for DBD::mysql.  If "
               . "DBD::mysql is not installed, try:\n"
               . "  Debian/Ubuntu  apt-get install libdbd-mysql-perl\n"
               . "  RHEL/CentOS    yum install perl-DBD-MySQL\n"
               . "  OpenSolaris    pgk install pkg:/SUNWapu13dbd-mysql\n";
         }
         elsif ( $EVAL_ERROR =~ m/not a compiled character set|character set utf8/ ) {
            PTDEBUG && _d('Going to try again without utf8 support');
            delete $defaults->{mysql_enable_utf8};
         }
         if ( !$tries ) {
            die $EVAL_ERROR;
         }
      }
   }
Check slave delay once and exit. If you also specify --recurse, the tool will 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 is printed before its
delay. --recurse only works with MySQL.

以上代码摘自get_dbh函数,用于获取数据库的连日,如果获得退步,则重试1遍,然后通过die函数抛万分退出。

–check-read-only

 

   检查server是不是是只读的,借使是只读,则会跳过插入动作。

然则,通过设置如下断点,开掘当$tries为0时,if函数里面包车型大巴PTDEBUG &&
_d(“$EVAL_E途睿欧ROLX570”)语句能进行,但die函数正是从未抛出卓殊,并退出脚本

Check if the server has read_only enabled; If it does, the tool skips doing any inserts.
PTDEBUG && _d($tries);
if ( !$tries ) {
    PTDEBUG && _d("$EVAL_ERROR"); 
    die $EVAL_ERROR; }

–config

 

   Read this comma-separated list of config files; if specified, this
must be the first option on the command line.

新兴,将上述代码的最终贰个if函数修改如下:

   将参数写入到参数文件中,

if ( !$tries ) {
            die "test:$EVAL_ERROR";
         }

   有以下几点要求留意:

 

   1> # pt-heartbeat –config pt-heartbeat.conf,而无法是#
pt-heartbeat –config=pt-heartbeat.conf 

再一次测试

   贰> 参数文件中只帮助如下写法

起步数据库

option
option=value

# service mysqld start

   
option前面不能够带上–,而且option只能是全拼,不能够是缩写,譬如database,不能够缩写为-D

 

   
具体写法可参看:

执行pt-heartbeat命令

    试举1例,如下所示

# pt-heartbeat –update -h 192.168.244.10 -u monitor -p monitor123 -D
test –create-table

# cat pt-heartbeat.conf 
host=192.168.244.20
user=monitor
password=monitor123
monitor
database=test
master-server-id=1 

 

–create-table

悬停数据库

   创制heartbeat表假设该表不存在,该表由–database和–table参数来确认。

# service mysqld stop

   当中–table表的概念如下所示:

 

CREATE TABLE 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
);

Create the heartbeat --table if it does not exist.
This option causes the table specified by --database and --table to be created with the following MAGIC_create_heartbeat table definition:
CREATE TABLE 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
);
The heartbeat table requires at least one row. If you manually create the heartbeat table, then you must insert a row by doing:
INSERT INTO heartbeat (ts, server_id) VALUES (NOW(), N);
or if using --utc:
INSERT INTO heartbeat (ts, server_id) VALUES (UTC_TIMESTAMP(), N);
where N is the server’s ID; do not use @@server_id because it will replicate and slaves will insert their own server ID instead of the master’s server ID.
This is done automatically by --create-table.
A legacy version of the heartbeat table is still supported:
CREATE TABLE heartbeat (
id int NOT NULL PRIMARY KEY,
ts datetime NOT NULL
);
Legacy tables do not support --update instances on each slave of a multi-slave hierarchy like “master ->slave1 -> slave2”. To manually insert the one required row into a legacy table:
INSERT INTO heartbeat (id, ts) VALUES (1, NOW());
or if using --utc:
INSERT INTO heartbeat (id, ts) VALUES (1, UTC_TIMESTAMP());
The tool automatically detects if the heartbeat table is legacy.

刚刚实施的pt-heartbeat命令万分退出

–create-table-engine

997755.com澳门葡京 5

   内定heartbeat表的囤积引擎

“test:”正是投入的测试字符。

type: string
Sets the engine to be used for the heartbeat table. The default storage engine is InnoDB as of MySQL 5.5.5.

 

–daemonize

结论

 
 脚本以守护进程运营,那样即使脚本试行的极端断开了,脚本也不会停止运作。

很奇怪,只是单纯的die
$EVAL_E奥德赛ROPRADO不会抛出十分,并退出脚本,但修改后的die
“test:$EVAL_E卡宴ROHighlander”却会脱离脚本。

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

很备受瞩目,那着实是个bug,不领会是否与perl的版本有关。

–database

很愕然,战败的连天如何变成内部存款和储蓄器的穿梭增加?

   钦赐heartbeat表所在的数据库

最后,给percona官方提了个bug

short form: -D; type: string
The database to use for the connection.

–dbi-driver

 

   pt-heartbeat不仅能检查实验MySQL之间的心跳延迟情形,还足以检查实验PG。

   该参数钦点连接使用的驱动,默感觉mysql,也可钦命为Pg

default: mysql; type: string
Specify a driver for the connection; mysql and Pg are supported.

–defaults-file

   钦定参数文件的地点,必须为绝对路线。

short form: -F; type: string
Only read mysql options from the given file. You must give an absolute pathname.

–file

   将新型的–monitor新闻输出到文件中,注意最新,新的音讯会覆盖旧的消息。

 
 要是不加该参数,则monitor的音信会直接出口到巅峰上,该采用平时和–daemonize参数一齐行使。

   譬如,

   # pt-heartbeat -D test –monitor -h 192.168.244.20
–master-server-id=1 -u monitor -p monitor123 –file=result

   该命令会在当前目录下生成一个result文件,记录最新的检查评定新闻

# cat result 
1376.00s [ 1126.25s, 225.25s, 75.08s ]

type: string
Print latest --monitor output to this file.
When --monitor is given, prints output to the specified file instead of to STDOUT. The file is opened, truncated,and closed every interval, so it will only contain the most recent statistics. Useful when --daemonize
is given.

–frames

 
 总括的时光窗口,默感觉一m,5m,壹伍m,即分别总结一min,5min和1伍min内的平分延迟境况。

 
 单位能够是s,m,h,d,注意:时间窗口越大,供给缓存的结果更多,对内部存款和储蓄器的损耗也越大。

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 suffix can be 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 are kept in memory to calculate the averages. You can specify as many timeframes as
you like.

–help 

Show help and exit.

–host 

   钦点连接的主机,可缩写为-h

short form: -h; type: string
Connect to host.

–[no]insert-heartbeat-row

   官方解释如下:

default: yes
Insert a heartbeat row in the --table if one doesn’t exist.
The heartbeat --table requires a heartbeat row, else there’s nothing to --update, --monitor, or --check! By default, the tool will insert a heartbeat row if one is not already present. You can disable this
feature by specifying --no-insert-heartbeat-row in case the database user does not have INSERT privileges.

    事实上,在实践如下命令时,

    # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123

    假设,heartbeat表为空,则会活动insert一条记下。

   
但倘使钦赐了–no-insert-heartbeat-row参数,则不会活动创立,此时,会提醒如下音讯:

# pt-heartbeat -D test --update -h 192.168.244.10 -u monitor -p monitor123 --no-insert-heartbeat-row
No row found in heartbeat table for server_id 1.
At least one row must be inserted into the heartbeat table for server_id 1.
Please read the DESCRIPTION section of the pt-heartbeat POD.

   
PS:在测试的长河中,发现合法并不曾完全的校验那几个参数,即使传入–no-insert-heartbeat和–insert-heartbeat参数也不会报错,不过传入–1二三-insert-heartbeat-ro,会报错“Unknown
option: 1二3-insert-heartbeat-ro”。

default: yes
Insert a heartbeat row in the --table if one doesn’t exist.
The heartbeat --table requires a heartbeat row, else there’s nothing to --update, --monitor, or --check! By default, the tool will insert a heartbeat row if one is not already present. You can disable this
feature by specifying --no-insert-heartbeat-row in case the database user does not have INSERT privileges.

–interval

   update和check heartbeat表的作用,暗中同意是一s。

type: float; default: 1.0
How often to update or check the heartbeat --table. Updates and checks begin on the first whole second then repeat every --interval seconds for --update and every --interval plus --skew seconds for
--monitor.
For example, if at 00:00.4 an --update instance is started at 0.5 second intervals, the first update happens at 00:01.0, the next at 00:01.5, etc. If at 00:10.7 a --monitor instance is started at 0.05 second intervals with
the default 0.5 second --skew, then the first check happens at 00:11.5 (00:11.0 + 0.5) which will be --skew seconds after the last update which, because the instances are checking at synchronized intervals, happened at
00:11.0.
The tool waits for and begins on the first whole second just to make the interval calculations simpler. Therefore,the tool could wait up to 1 second before updating or checking.
The minimum (fastest) interval is 0.01, and the maximum precision is two decimal places, so 0.015 will be rounded to 0.02.
If a legacy heartbeat table (see --create-table) is used, then the maximum precision is 1s because the ts column is type datetime.

–log

   在脚本以守护进度施行时,将结果输出到log内定的文件中。

type: string
Print all output to this file when daemonized.

–master-server-id

 
 指定master的server_id,在检查实验从的推移时,必须钦赐该参数,否则会报如下错误:

The --master-server-id option must be specified because the heartbeat table `test`.`heartbeat` uses the server_id column for --update or --check but the server's master could not be automatically determined.

type: string
Calculate delay from this master server ID for --monitor or --check. If not given, pt-heartbeat attempts to connect to the server’s master and determine its server id.

–monitor

   持续的检测并出口从的延期景况

   当中,检查实验并出口的效用有–interval参数决定,默以为1s

   注意:与–check的区分在于:

      1> –monitor是不停输出的,而–check是检验三回即退出。

      二> –monitor可与–file参数搭配,而–check与–file参数搭配无效。

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 ]

–password

  内定登入的密码,缩写为-p

short form: -p; type: string
Password to use when connecting. If password contains commas they must be escaped with a backslash:
“exam,ple”

–pid

  创建pid文件

type: string
Create the given PID file. The tool won’t start if the PID file already exists and the PID it contains is different than the current PID. However, if the PID file exists and the PID it contains is no longer running, the tool will
overwrite the PID file with the current PID. The PID file is removed automatically when the tool exits.

–port

  钦命登6的端口,缩写为-P

short form: -P; type: int
Port number to use for connection.

–print-master-server-id

  同时输出主的server_id,在–monitor情状下,私下认可输出为

1272.00s [ 21.20s,  4.24s,  1.41s ]

 假若钦赐了该参数,则输出为

1272.00s [ 21.20s,  4.24s,  1.41s ] 1

Print the auto-detected or given --master-server-id. If --check or --monitor is specified, specifying this option will print the auto-detected or given --master-server-id at the end of each line.

–recurse

 在–check情势下,用于质量评定级联复制中从的推移意况。其中,–recurse用于钦定级联的层级。

type: int
Check slaves recursively to this depth in --check mode.
Try to discover slave servers recursively, to the specified depth. After discovering servers, run the check on each one of them and print the hostname (if possible), followed by the slave delay.
This currently works only with MySQL. See --recursion-method.

–recursion-method

  在级联复制中,找到slave的艺术。有show processlist和show slave
hosts三种。

type: array; default: processlist,hosts
Preferred recursion method used to find slaves.
Possible methods are:
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
none Do not find slaves
The processlist method is preferred because SHOW SLAVE HOSTS is not reliable. However, the hosts method is required if the server uses a non-standard port (not 3306). Usually pt-heartbeat does the right thing and finds
the slaves, but you may give a preferred method and it will be used first. If it doesn’t find any slaves, the other methods will be tried.

–replace

 
在–update形式下,默许是应用update操作进行记录的更新,但偶尔你不太认同heartbeat表中是不是任何笔录时,此时可利用replace操作。

  注意:假诺是透过update实行记录的换代,如若在本子运转的经过中,truncate
heartbeat表,脚本并不会非凡退出,可是heartbeat表也有不会生成新的笔录。

 
但借使是透过replace模式实行记录的创新,则就是是在地方那种情状下,heartbeat表照旧会生成新的记录。个人感到通过replace操作进行记录的翻新更可相信。

Use REPLACE instead of UPDATE for –update.
When running in --update mode, use REPLACE instead of UPDATE to set the heartbeat table’s timestamp.The REPLACE statement is a MySQL extension to SQL. This option is useful when you don’t know whether
the table contains any rows or not. It must be used in conjunction with –update.

–run-time

  钦赐脚本运转的时间,无论是针对–update操作照旧–monitor操作均实用。

type: time
Time to run before exiting.

–sentinel

  “哨兵”,假若钦赐的文件存在则建议,私下认可为/tmp/pt-heartbeat-sentinel

type: string; default: /tmp/pt-heartbeat-sentinel
Exit if this file exists.

 
经测试,即使未有带上–sentinel参数,如果/tmp/pt-heartbeat-sentinel文件存在,则脚本一施行时就径直退出。

  –sentinel功用在于自定义监察和控制文件。

 
譬如在执行如下命令时, /root/123文本并不设有,则该脚本会继续运转,在本子运转的历程中,成立该文件,则脚本会马上退出。

  # pt-heartbeat -D test –update -h 192.168.244.10 -u monitor -p
monitor123  –sentinel=/root/123

–slave-user

   设置连接slave的用户

type: string
Sets the user to be used to connect to the slaves. This parameter allows you to have a different user with less privileges on the slaves but that user must exist on all slaves.

–slave-password

    设置连接slave的用户密码

type: string
Sets the password to be used to connect to the slaves. It can be used with –slave-user and the password for the user must be the same on all slaves.

 –set-vars

    设置脚本在与MySQL交互进程时的对话变量,但就像并不曾什么用

type: Array
Set the MySQL variables in this comma-separated list of variable=value pairs.
By default, the tool sets:
wait_timeout=10000
Variables specified on the command line override these defaults. For example, specifying --set-vars wait_timeout=500 overrides the defaultvalue of 10000.
The tool prints a warning and continues if a variable cannot be set.

–skew

  钦赐check相对于update的延迟时间。默以为0.五秒

 
即–update更新一遍后,–check会在0.5秒后检查本次更新所对应的为主延迟景况。

 
恐怕有人会相比较好奇,脚本是怎么样知道记录是几时更新的,实际上,每一趟–update的小时都以秒的整点值,譬如,个中二遍记录的值为“2016-0⑨-25T13:0四:0陆.003130”。然后,0.5s后,脚本获取slave上的种类时间,然后减去heartbeat中记录值,来作为主导延迟的日子。那将需要,主从上的种类时间必要保持壹致,不然获得的结果就从未有过参考价值。

  下边,可看看源码落成,这几个是成套脚本的主导逻辑。

         my ($ts, $hostname, $server_id) = $sth->fetchrow_array();
         my $now = time;
         PTDEBUG && _d("Heartbeat from server", $server_id, "\n",
            " now:", ts($now, $utc), "\n",
            "  ts:", $ts, "\n",
            "skew:", $skew);
         my $delay = $now - unix_timestamp($ts, $utc) - $skew;
         PTDEBUG && _d('Delay', sprintf('%.6f', $delay), 'on', $hostname);

         # Because we adjust for skew, if the ts are less than skew seconds
         # apart (i.e. replication is very fast) then delay will be negative. 
         # So it's effectively 0 seconds of lag.
         $delay = 0.00 if $delay < 0;

type: float; default: 0.5
How long to delay checks.
The default is to delay checks one half second. Since the update happens as soon as possible after the beginning of the second on the master, this allows one half second of replication delay before reporting that the slave lags
the master by one second. If your clocks are not completely accurate or there is some other reason you’d like to delay the slave more or less, you can tweak this value. Try setting the PTDEBUG environment variable to see
the effect this has.

–socket

short form: -S; type: string
Socket file to use for connection.

–table

  内定心跳表的名字,默以为heartbeat

type: string; default: heartbeat
The table to use for the heartbeat.
Don’t specify database.table; use --database to specify the database.
See --create-table.

–update

  更新master中heartbeat表的笔录

Update a master’s heartbeat.

–user

  钦命连接的用户

short form: -u; type: string
User for login if not current user.

–utc

 
忽略系统时区,而使用UTC。若是要选拔该选项,则–update,–monitor,–check中务必同时利用。

Ignore system time zones and use only UTC. By default pt-heartbeat does not check or adjust for different system or MySQL time zones which can cause the tool to compute the lag incorrectly. Specifying this option is
a good idea because it ensures that the tool works correctly regardless of time zones.
If used, this option must be used for all pt-heartbeat instances: --update, --monitor, --check, etc.
You should probably set the option in a --config file. Mixing this option with pt-heartbeat instances not using this option will cause false-positive lag readings due to different time zones (unless all your systems are
set to use UTC, in which case this option isn’t required).

–version

  打字与印刷版本音讯

–[no]version-check

  检查pt,连接的MySQL Server,Perl以及DBD::mysql的版本新闻。

  并且打印这个软件特定版本的难点

Check for the latest version of Percona Toolkit, MySQL, and other programs.
84 Chapter 2. Tools
Percona Toolkit Documentation, Release 2.2.19
This is a standard “check for updates automatically” feature, with two additional features. First, the tool checks the version of other programs on the local system in addition to its own version. For example, it checks the
version of every MySQL server it connects to, Perl, and the Perl module DBD::mysql. Second, it checks for and warns about versions with known problems. For example, MySQL 5.5.25 had a critical bug and was re-released
as 5.5.25a.
Any updates or known problems are printed to STDOUT before the tool’s normal output. This feature should never interfere with the normal operation of the tool.

 

 

 

   

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website