Example code about pt-heartbeat (percona toolkit)-Mysql Tutorial-php.cn

pt-heartbeat is a percona tool used to monitor master-slave latency. Most of our MySQL architectures are still based on master-slave replication, such as MHA, MMM, keepalived and other solutions. In the master-slave environment, we are very concerned about the master-slave delay problem. Generally, we execute the following statements in the slave library:

mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.35Master_User: root
Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000016Read_Master_Log_Pos: 299786938Relay_Log_File: mxqmongodb2-relay-bin.000032Relay_Log_Pos: 299787151Relay_Master_Log_File: mysql-bin.000016Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0Last_Error:
Skip_Counter: 0Exec_Master_Log_Pos: 299786938Relay_Log_Space: 299787451Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0Last_IO_Error:
Last_SQL_Errno: 0Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 353306Master_UUID: 806ede0c-357e-11e7-9719-00505693235d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 806ede0c-357e-11e7-9719-00505693235d:666-330347Executed_Gtid_Set: 6a4ab82c-4029-11e7-86b0-00505693235d:1-3,
806ede0c-357e-11e7-9719-00505693235d:1-330347Auto_Position: 1Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:1 row in set (0.00 sec)

Copy after login

You can clearly see the status of the master and slave. Generally, we will monitor the status of the following two processes to determine whether there is an error in the master-slave delay:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

For master-slave delay, what we may monitor most is through SMB (Seconds_Behind_Master), but this is not very reliable. Or we can monitor the difference between Read_Master_Log_Pos-Exec_Master_Log_Pos to see whether there is a delay in the slave library and whether there is a certain delay with the master library. But this one isn't quite perfect either. First, look at SMB. How is this parameter compared? SMB is obtained by comparing the current timestamp of the server with the event timestamp in the binary log, and false positives may occur. For example, when the main library executes a large transaction, it takes a long time to execute. After receiving it from the slave library, it compares the timestamp and finds that it has been delayed, and the SMB value will be increased instantly. The value of Read_Master_Log_Pos-Exec_Master_Log_Pos is not completely reliable. Now pt-heartbeat can help us solve this problem.

Let’s take a look at the principle of pt-heartbeat:

pt-heartbeat will create a table on the master, update the fields of the table at a certain time frequency, and write to the table Enter the current timestamp, and then compare the timestamp of the slave library with the timestamp of the machine where pt-heartbeat is located to determine the master-slave delay.

In fact, there is a problem here. If pt-heartbeat is deployed in the slave library, it must ensure that the time of the master and slave machines are synchronized. This is usually achieved by the system automatically adjusting the time every day. . If pt-heartbeat is deployed in the main library, there will be no such problem. In fact, I personally feel that a better way is to compare the timestamps of this table in the master-slave database to find out that the delay is more reliable. Of course, this must also take into account the problem of master-slave query.

Let’s take a look at the basic usage:

Create the table first:

[root@mxqmongodb2 bin]# ./pt-heartbeat --host=172.16.16.35 --port=3306 --user=root --password=123456 --database=test --update --create-table --daemonize

Copy after login

Take a look Table structure:

mysql> desc heartbeat;+-----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| ts | varchar(26) | NO | | NULL | |
| server_id | int(10) unsigned | NO | PRI | NULL | |
| file | varchar(255) | YES | | NULL | |
| position | bigint(20) unsigned | YES | | NULL | |
| relay_master_log_file | varchar(255) | YES | | NULL | |
| exec_master_log_pos | bigint(20) unsigned | YES | | NULL | |
+-----------------------+---------------------+------+-----+---------+-------+6 rows in set (0.01 sec)
mysql> select * from heartbeat\G*************************** 1. row ***************************ts: 2017-06-23T09:27:49.001580server_id: 353306file: mysql-bin.000016position: 299837221relay_master_log_file: NULL
exec_master_log_pos: NULL1 row in set (0.00 sec)

Copy after login

Let’s look at the link to the database and find that there is an update operation:

UPDATE `test`.`heartbeat` SET ts='2017-06-23T09:32:47.001330', file='mysql-bin.000016', position='29

Copy after login

In fact, the following operation just starts a process and continuously updates the heartbeat table to obtain the latest data.

[root@mxqmongodb2 bin]# ./pt-heartbeat --host=172.16.16.35 --port=3306 --user=root --password=123456 --database=test --update --daemonize

Copy after login

Next we start the monitoring process, which is constantly refreshed:

[root@mxqmongodb2 bin]# ./pt-heartbeat -D test --monitor -h 172.16.16.34 -uroot -P3306 -p1234560.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 [ 0.00s, 0.00s, 0.00s ]0.00s [ 0.00s, 0.00s, 0.00s ]

Copy after login

We specified that there is no delay. Let’s start the stress test now and take another look:

[root@mxqmongodb2 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P3306 -d tpcc -u root -p123456 -w 10 -c 20 -r 10 -l 600

Copy after login

Observe the delay:

0.71s [ 0.68s, 0.16s, 0.05s ]1.71s [ 0.71s, 0.17s, 0.06s ]0.85s [ 0.72s, 0.17s, 0.06s ]0.93s [ 0.74s, 0.18s, 0.06s ]0.00s [ 0.74s, 0.18s, 0.06s ]0.82s [ 0.73s, 0.18s, 0.06s ]0.66s [ 0.75s, 0.18s, 0.06s ]0.00s [ 0.75s, 0.18s, 0.06s ]0.88s [ 0.74s, 0.18s, 0.06s ]1.00s [ 0.74s, 0.19s, 0.06s ]

Copy after login

We can see the delay, we can put these The output results are redirected to a file to monitor the master-slave delay, which is also very effective.

We now manually stop sql_thread from the library:

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.03 sec)

Copy after login

Then look at the output result:

53.00s [ 23.85s, 6.77s, 4.20s ]54.00s [ 24.75s, 6.94s, 4.26s ]55.00s [ 25.67s, 7.12s, 4.32s ]56.00s [ 26.60s, 7.30s, 4.39s ]57.00s [ 27.55s, 7.48s, 4.45s ]58.00s [ 28.52s, 7.67s, 4.51s ]59.00s [ 29.50s, 7.86s, 4.58s ]60.00s [ 30.50s, 8.05s, 4.65s ]61.00s [ 31.50s, 8.23s, 4.71s ]62.00s [ 32.50s, 8.42s, 4.78s ]63.00s [ 33.50s, 8.61s, 4.85s ]64.00s [ 34.50s, 8.80s, 4.92s ]65.00s [ 35.50s, 8.98s, 5.00s ]

Copy after login

It is found that it keeps increasing, and the interval is one second. We start this sql_thread again, and the slave library quickly catches up with the main library.

Or we can monitor through the following:

[root@mxqmongodb2 bin]# ./pt-heartbeat -D test --check -h 172.16.16.34 -uroot -P3306 -p1234561.00[root@mxqmongodb2 bin]# ./pt-heartbeat -D test --check -h 172.16.16.34 -uroot -P3306 -p1234560.00

Copy after login

He will directly return a delay in seconds, This is also relatively reliable. We can directly use this value for monitoring.

The above is the detailed content of Example code about pt-heartbeat (percona toolkit). For more information, please follow other related articles on the PHP Chinese website!