Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
PT-HEARTBEAT(1)	      User Contributed Perl Documentation      PT-HEARTBEAT(1)

       pt-heartbeat - Monitor MySQL replication	delay.

       Usage: pt-heartbeat [OPTIONS] [DSN] --update|--monitor|--check|--stop

       pt-heartbeat measures replication lag on	a MySQL	or PostgreSQL server.
       You can use it to update	a master or monitor a replica.	If possible,
       MySQL connection	options	are read from your .my.cnf file.

       Start daemonized	process	to update test.heartbeat table on master:

	 pt-heartbeat -D test --update -h master-server	--daemonize

       Monitor replication lag on slave:

	 pt-heartbeat -D test --monitor	-h slave-server

	 pt-heartbeat -D test --monitor	-h slave-server	--dbi-driver Pg

       Check slave lag once and	exit (using optional DSN to specify slave

	 pt-heartbeat -D test --check h=slave-server

       Percona Toolkit is mature, proven in the	real world, and	well tested,
       but all database	tools can pose a risk to the system and	the database
       server.	Before using this tool,	please:

       o   Read	the tool's documentation

       o   Review the tool's known "BUGS"

       o   Test	the tool on a non-production server

       o   Backup your production server and verify the	backups

       pt-heartbeat is a two-part MySQL	and PostgreSQL replication delay
       monitoring system that measures delay by	looking	at actual replicated
       data.  This avoids reliance on the replication mechanism	itself,	which
       is unreliable.  (For example, "SHOW SLAVE STATUS" on MySQL).

       The first part is an "--update" instance	of pt-heartbeat	that connects
       to a master and updates a timestamp ("heartbeat record")	every
       "--interval" seconds.  Since the	heartbeat table	may contain records
       from multiple masters (see "MULTI-SLAVE HIERARCHY"), the	server's ID
       (@@server_id) is	used to	identify records.

       The second part is a "--monitor"	or "--check" instance of pt-heartbeat
       that connects to	a slave, examines the replicated heartbeat record from
       its immediate master or the specified "--master-server-id", and
       computes	the difference from the	current	system time.  If replication
       between the slave and the master	is delayed or broken, the computed
       difference will be greater than zero and	potentially increase if
       "--monitor" is specified.

       You must	either manually	create the heartbeat table on the master or
       use "--create-table".  See "--create-table" for the proper heartbeat
       table structure.	 The "MEMORY" storage engine is	suggested, but not
       required	of course, for MySQL.

       The heartbeat table must	contain	a heartbeat row.  By default, a
       heartbeat row is	inserted if it doesn't exist.  This feature can	be
       disabled	with the "--[no]insert-heartbeat-row" option in	case the
       database	user does not have INSERT privileges.

       pt-heartbeat depends only on the	heartbeat record being replicated to
       the slave, so it	works regardless of the	replication mechanism (built-
       in replication, a system	such as	Continuent Tungsten, etc).  It works
       at any depth in the replication hierarchy; for example, it will
       reliably	report how far a slave lags its	master's master's master.  And
       if replication is stopped, it will continue to work and report
       (accurately!) that the slave is falling further and further behind the

       pt-heartbeat has	a maximum resolution of	0.01 second.  The clocks on
       the master and slave servers must be closely synchronized via NTP.  By
       default,	"--update" checks happen on the	edge of	the second (e.g.
       00:01) and "--monitor" checks happen halfway between seconds (e.g.
       00:01.5).  As long as the servers' clocks are closely synchronized and
       replication events are propagating in less than half a second, pt-
       heartbeat will report zero seconds of delay.

       pt-heartbeat will try to	reconnect if the connection has	an error, but
       will not	retry if it can't get a	connection when	it first starts.

       The "--dbi-driver" option lets you use pt-heartbeat to monitor
       PostgreSQL as well.  It is reported to work well	with Slony-1

       If the replication hierarchy has	multiple slaves	which are masters of
       other slaves, like "master -> slave1 -> slave2",	"--update" instances
       can be ran on the slaves	as well	as the master.	The default heartbeat
       table (see "--create-table") is keyed on	the "server_id"	column,	so
       each server will	update the row where "server_id=@@server_id".

       For "--monitor" and "--check", if "--master-server-id" is not
       specified, the tool tries to discover and use the slave's immediate
       master.	If this	fails, or if you want monitor lag from another master,
       then you	can specify the	"--master-server-id" to	use.

       For example, if the replication hierarchy is "master -> slave1 ->
       slave2" with corresponding server IDs 1,	2 and 3, you can:

	 pt-heartbeat --daemonize -D test --update -h master
	 pt-heartbeat --daemonize -D test --update -h slave1

       Then check (or monitor) the replication delay from master to slave2:

	 pt-heartbeat -D test --master-server-id 1 --check slave2

       Or check	the replication	delay from slave1 to slave2:

	 pt-heartbeat -D test --master-server-id 2 --check slave2

       Stopping	the "--update" instance	one slave1 will	not affect the
       instance	on master.

       The default heartbeat table (see	"--create-table") has columns for
       saving information from "SHOW MASTER STATUS" and	"SHOW SLAVE STATUS".
       These columns are optional.  If any are present,	their corresponding
       information will	be saved.

Percona	XtraDB Cluster
       Although	pt-heartbeat should work with all supported versions of
       Percona XtraDB Cluster (PXC), we	recommend using	5.5.28-23.7 and	newer.

       If you are setting up heartbeat instances between cluster nodes,	keep
       in mind that, since the speed of	the cluster is determined by its
       slowest node, pt-heartbeat will not report how fast the cluster itself
       is, but only how	fast events are	replicating from one node to another.

       You must	specify	"--master-server-id" for "--monitor" and "--check"

       Specify at least	one of "--stop", "--update", "--monitor", or

       "--update", "--monitor",	and "--check" are mutually exclusive.

       "--daemonize" and "--check" are mutually	exclusive.

       This tool accepts additional command-line arguments.  Refer to the
       "SYNOPSIS" and usage information	for details.

	   Prompt for a	password when connecting to MySQL.

	   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 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

	   Check if the	server has read_only enabled; If it does, the tool
	   skips doing any inserts. See	also "--read-only-interval"

	   type: Array

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

	   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.


	   type: string

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

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

	   short form: -D; type: string

	   The database	to use for the connection.

	   default: mysql; type: string

	   Specify a driver for	the connection;	"mysql"	and "Pg" are

	   short form: -F; type: string

	   Only	read mysql options from	the given file.	 You must give an
	   absolute pathname.

	   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.

	   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.

	   Show	help and exit.

	   short form: -h; type: string

	   Connect to host.

	   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.

	   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

	   type: string

	   Print all output to this file when daemonized.

	   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 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	]

	   type: int

	   If specified, pt-heartbeat will fail	after given number of
	   successive DBI errors (failure to connect to	server or issue	a

	   short form: -p; type: string

	   Password to use when	connecting.  If	password contains commas they
	   must	be escaped with	a backslash: "exam\,ple"

	   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.

	   short form: -P; type: int

	   Port	number to use for connection.

	   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.

	   type: int

	   When	"--check-read-only" is specified, the interval to sleep	while
	   the server is found to be read-only.	If unspecified,	"--interval"
	   is used.

	   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".

	   type: array;	default: processlist,hosts

	   Preferred recursion method used to find slaves.

	   Possible methods are:

	     ===========  ==================
	     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.

	   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.

	   type: time

	   Time	to run before exiting.

	   type: string; default: /tmp/pt-heartbeat-sentinel

	   Exit	if this	file exists.

	   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.

	   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.

	   type: Array

	   Set the MySQL variables in this comma-separated list	of
	   "variable=value" pairs.

	   By default, the tool	sets:


	   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

	   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.

	   short form: -S; type: string

	   Socket file to use for connection.

	   Stop	running	instances by creating the sentinel file.

	   This	should have the	effect of stopping all running instances which
	   are watching	the same sentinel file.	 If none of "--update",
	   "--monitor" or "--check" is specified, "pt-heartbeat" will exit
	   after creating the file.  If	one of these is	specified,
	   "pt-heartbeat" will wait the	interval given by "--interval",	then
	   remove the file and continue	working.

	   You might find this handy to	stop cron jobs gracefully if
	   necessary, or to replace one	running	instance with another.	For
	   example, if you want	to stop	and restart "pt-heartbeat" every hour
	   (just to make sure that it is restarted every hour, in case of a
	   server crash	or some	other problem),	you could use a	"crontab" line
	   like	this:

	    0 *	* * * pt-heartbeat --update -D test --stop \
	      --sentinel /tmp/pt-heartbeat-hourly

	   The non-default "--sentinel"	will make sure the hourly "cron" job
	   stops only instances	previously started with	the same options (that
	   is, from the	same "cron" job).

	   See also "--sentinel".

	   type: string; default: heartbeat

	   The table to	use for	the heartbeat.

	   Don't specify database.table; use "--database" to specify the

	   See "--create-table".

	   Update a master's heartbeat.

	   short form: -u; type: string

	   User	for login if not current user.

	   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).

	   Show	version	and exit.

	   default: yes

	   Check for the latest	version	of Percona Toolkit, MySQL, and other

	   This	is a standard "check for updates automatically"	feature, with
	   two additional features.  First, the	tool checks its	own version
	   and also the	versions of the	following software: operating system,
	   Percona Monitoring and Management (PMM), MySQL, Perl, MySQL driver
	   for Perl (DBD::mysql), and Percona Toolkit. 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.

	   A secure connection to Perconaas Version Check database server is
	   done	to perform these checks. Each request is logged	by the server,
	   including software version numbers and unique ID of the checked
	   system. The ID is generated by the Percona Toolkit installation
	   script or when the Version Check database call is done for the
	   first time.

	   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.

	   For more information, visit

       These DSN options are used to create a DSN.  Each option	is given like
       "option=value".	The options are	case-sensitive,	so P and p are not the
       same option.  There cannot be whitespace	before or after	the "="	and if
       the value contains whitespace it	must be	quoted.	 DSN options are
       comma-separated.	 See the percona-toolkit manpage for full details.

       o   A

	   dsn:	charset; copy: yes

	   Default character set.

       o   D

	   dsn:	database; copy:	yes

	   Default database.

       o   F

	   dsn:	mysql_read_default_file; copy: yes

	   Only	read default options from the given file

       o   h

	   dsn:	host; copy: yes

	   Connect to host.

       o   p

	   dsn:	password; copy:	yes

	   Password to use when	connecting.  If	password contains commas they
	   must	be escaped with	a backslash: "exam\,ple"

       o   P

	   dsn:	port; copy: yes

	   Port	number to use for connection.

       o   S

	   dsn:	mysql_socket; copy: yes

	   Socket file to use for connection.

       o   u

	   dsn:	user; copy: yes

	   User	for login if not current user.

       The environment variable	"PTDEBUG" enables verbose debugging output to
       STDERR.	To enable debugging and	capture	all output to a	file, run the
       tool like:

	  PTDEBUG=1 pt-heartbeat ... > FILE 2>&1

       Be careful: debugging output is voluminous and can generate several
       megabytes of output.

       You need	Perl, DBI, DBD::mysql, and some	core packages that ought to be
       installed in any	reasonably new version of Perl.

       For a list of known bugs, see

       Please report bugs at <>.  Include
       the following information in your bug report:

       o   Complete command-line used to run the tool

       o   Tool	"--version"

       o   MySQL version of all	servers	involved

       o   Output from the tool	including STDERR

       o   Input files (log/dump/config	files, etc.)

       If possible, include debugging output by	running	the tool with
       "PTDEBUG"; see "ENVIRONMENT".

       Visit <>	to download
       the latest release of Percona Toolkit.  Or, get the latest release from
       the command line:




       You can also get	individual tools from the latest release:


       Replace "TOOL" with the name of any tool.

       Proven Scaling LLC, SixApart Ltd, Baron Schwartz, and Daniel Nichter

       This tool is part of Percona Toolkit, a collection of advanced command-
       line tools for MySQL developed by Percona.  Percona Toolkit was forked
       from two	projects in June, 2011:	Maatkit	and Aspersa.  Those projects
       were created by Baron Schwartz and primarily developed by him and
       Daniel Nichter.	Visit <> to learn
       about other free, open-source software from Percona.

       This program is copyright 2007-2018 Percona LLC and/or its affiliates,
       2006 Proven Scaling LLC and Six Apart Ltd.

       Feedback	and improvements are welcome.


       This program is free software; you can redistribute it and/or modify it
       under the terms of the GNU General Public License as published by the
       Free Software Foundation, version 2; OR the Perl	Artistic License.  On
       UNIX and	similar	systems, you can issue `man perlgpl' or	`man
       perlartistic' to	read these licenses.

       You should have received	a copy of the GNU General Public License along
       with this program; if not, write	to the Free Software Foundation, Inc.,
       59 Temple Place,	Suite 330, Boston, MA  02111-1307  USA.

       pt-heartbeat 3.2.0

       Hey! The	above document had some	coding errors, which are explained

       Around line 7221:
	   Non-ASCII character seen before =encoding in	'Perconaas'. Assuming

perl v5.32.1			  2020-04-23		       PT-HEARTBEAT(1)


Want to link to this manual page? Use this URL:

home | help