What you don't know about PT Heartbeat

pt-heartbeat Principle Research

First, Introduction

Mysql Seconds_behind_masterparameters are not accurate for master-slave delay measurements because his statistics are based onslave Sqlthreadand theI/O threadthe time difference, ifI/O threadbeNetwork Impact, this valuation is very inaccurate. More accurate master-slave delay detection is generally usedpt-heartbeat. pt-heartbeatdivided into two partsthe first one forUpdate, which occurs on the main library and updates the timestamp. a second sectionto beMonitororCheck, which occurs from the library, checks the time stamp passed from the main library and compares it from the library system time. Checkand theMonitorThe difference isCheckjust run once and quit.Monitoris the continuous checking of the master-slave delay condition. --update, --monitor, and--check is mutually exclusive, only one option can be selected.

Second, the principle analysis

From library:

Perl/usr/bin/pt-heartbeat--user=dbadmin--password=nv7yvbpn88cg4wjcvlzd--host=10.128.6.94--monitor--port=3306-- create-table-d Test--interval=10--log=/tmp/testmysql.log--daemonize--file=/tmp/test_lag.log

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/87/4A/wKiom1fagq3y5iOuAAB2xFVc5Vg668.png-wh_500x0-wm_3 -wmp_4-s_900404525.png "title=" P1.png "alt=" Wkiom1fagq3y5iouaab2xfvc5vg668.png-wh_50 "/>

can findHBwhen there iscreate-tablesettings are automatically created in the development library.HeartbeatTable (--create-table-d Test, if you do not specify- Dthe default isMySQLNew in the library,HeartbeatIf the table is deleted in the middle , it will be-logand no longer monitor the delay ), the record is inserted automatically when there is no data in the table, and the data is inserted (IPand timestamps) contain host information (bySHOW SLAVE STATUS) and repository information (performed on the standby)SELECT @ @server_id)

can find HB each interval Ten seconds to query the time stamp of the main library because it sets the --interval=10 .

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/87/47/wKioL1fagsiCwV5gAAAl_6_mQz4345.png-wh_500x0-wm_3 -wmp_4-s_131953859.png "style=" Float:none; "title=" P2.png "alt=" Wkiol1fagsicwv5gaaal_6_mqz4345.png-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/87/4A/wKiom1fagsjz-quEAAAse1n9xs8759.png-wh_500x0-wm_3 -wmp_4-s_1738354803.png "style=" Float:none; "title=" P3.png "alt=" Wkiom1fagsjz-queaaase1n9xs8759.png-wh_50 "/>

He compares the time stamp from the main library to the system time, and draws the delay value.

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/87/47/wKioL1faguDj8CyeAAAoCVuXiUA319.png-wh_500x0-wm_3 -wmp_4-s_1335166032.png "title=" P4.png "alt=" Wkiol1fagudj8cyeaaaocvuxiua319.png-wh_50 "/>

---file Information for Lag The information:

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/87/47/wKioL1fagvrjopZDAAAFw_NN768859.png-wh_500x0-wm_3 -wmp_4-s_2328863441.png "title=" P5.png "alt=" Wkiol1fagvrjopzdaaafw_nn768859.png-wh_50 "/>

default: now [ 1M,5M,15M] delay, can be changed - Frames parameter to adjust the average delay calculation time. The sampling time principle is to set each interval The delay information is credited to memory, and then according to -frames time averaged.

PH when to start checking for delays: Regardless of the primary library's Update and from the library Monitor format, will start in full seconds , Monitor also need to add Skew (Delay check time).

Main Library:

Perl/usr/bin/pt-heartbeat--user=dbadmin--password=nv7yvbpn88cg4wjcvlzd--host=10.128.6.93--port=3308-- create-table-d test--update--interval=10--log=/tmp/lagmysql.log--daemonize

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/87/4A/wKiom1fagxDR2YHiAAB_UoKoEdU942.png-wh_500x0-wm_3 -wmp_4-s_3459005862.png "title=" P6.png "alt=" Wkiom1fagxdr2yhiaab_uokoedu942.png-wh_50 "/>

can be found also createtable , and then each of the master libraries interval plug in the current timestamp data ( where conditions for the primary library ID )

> You see shows 9 second delay, > Is it really a nine-second delay? ? Answer not , because think about the main library and start heartbeat time biased update less than a inteval

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/87/4A/wKiom1fagyWDQ4STAAAKuY90AXQ180.png-wh_500x0-wm_3 -wmp_4-s_1099679638.png "title=" P7.png "alt=" Wkiom1fagywdq4staaakuy90axq180.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/87/47/wKioL1fag16ApZJYAADjZtQB9Lk768.png-wh_500x0-wm_3 -wmp_4-s_3962059348.png "title=" P8.png "alt=" Wkiol1fag16apzjyaadjztqb9lk768.png-wh_50 "/>

#### you can see two new information from the library, but a new one for the main library. The master-slave switch when speaking there will be hidden trouble, mind to add -replace option from the library , regardless of the master and slave data will do replace operation.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/87/47/wKioL1fag3jx5IMsAAAjDQLfgU8919.png-wh_500x0-wm_3 -wmp_4-s_2206841437.png "title=" P9.png "alt=" Wkiol1fag3jx5imsaaajdqlfgu8919.png-wh_50 "/>

Third, Cascade application

Master slave from:

M1 ( Server ID 1 ) ->s1 ( Server Id2 ) ->s2

M1 S1 Open Heartbeat Update

pt-heartbeat--daemonize-d Test--update-h ( M1 Machine )

pt-heartbeat--daemonize-d Test--update-h ( S1 Machine )

S2 with the M1 The time stamp is compared and the result S2 with the M1 the delay, if not specified Master-server-id , S2 will look for his direct master . Timestamp comparison (i.e. S1 )

pt-heartbeat-d Test--master-server-id 1--check (S2 machine)

S2 with the S1 The time stamp is compared and the result S2 with the S1 the delay

pt-heartbeat-d Test--master-server-id 2--check slave2 (S2 machine)

This article is from the "diligence and desolate" blog, please be sure to keep this source http://10574662.blog.51cto.com/10564662/1852989

What you don't know about PT Heartbeat