Percona PT-kill Reconstruction Edition (PHP)

/**

Percona PT-kill refactoring (PHP)

https://github.com/hcymysql/pt-kill

UPDATE:

Modified by: hcymysql 2018/11/27

1. Add slow SQL mail alarm function

2. Add slow SQL WeChat alarm function

Environmental preparation:

shell> yum install -y php-process php php-mysql

*/

Overview The native Percona PT-kill(Perl) tool is just a simple KILL to remove running slow SQL, and cannot be used as a monitoring tool, such as lack of email alarm or WeChat alarm function, so it needs to be refactored.

The refactoring PT-kill(PHP) captures running SELECT from the information_schema.PROCESSLIST table| ALTER and other DML/DDL queries that consume too many resources, filter them, and then kill them (optionally not kill) and send email/WeChat alarms to DBAs and related developers to avoid a certain degree of damage to the database due to slow SQL execution time. (Note: Slow SQL is recorded in slow.log after execution, not recorded during execution.)

Usage:

Options:

-u username

-p password

-h host ip

-P port

-B busytime seconds Set slow SQL execution time Trigger alarm

-I interval time seconds Set the interval monitoring time under daemons

--kill Add this option if you want to kill slow queries.

--match-info Match kill SELECT| INSERT| UPDATE statement

--match-user matches killed user

--daemon 1 opens background daemon, 0 closes background daemon

--mail open send mail alarm

--weixin starts sending WeChat alarm

--help Help

Example :

foreground running

shell> php pt-kill.php -u admin -p 123456 -h 10.10.159.31 -P 3306 -B 10 --match-info='select|alter' --match-user='dev' --kill --mail --weixin

running in the background

shell> php pt-kill.php -u admin -p 123456 -h 10.10.159.31 -P 3306 -B 10 -I 15 --match-info='select|alter' --match-user='dev' --kill --mail --weixin --daemon 1

Close background operation

shell> php pt-kill.php --daemon 0

The above is the tool usage and parameter options. Here are some of the more important parameters:

1. --kill If you want to kill slow queries, add this option at the end;

2. --match-info can be used alone or in combination with--match-user;

3, --daemon 1 is to open the background daemon, if you do not add this option, you can use the system crontab instead. This option is used in conjunction with-I 10 (seconds), which monitors every 10 seconds of sleep. 0 is to close the background daemon.

4. --mail To start sending email alarm, you need to set smtp_config.php first and change it to your own email account information.

smtp_config.php

5. --weixin In order to start sending WeChat alarm, you need to install simplejson-3.8.2.tar.gz first.

shell> tar zxvf simplejson-3.8.2.tar.gz

shell> cd simplejson-3.8.2

shell> python setup.py build

shell> python setup.py install

Then edit pt-kill.php script to find $status1 = system("/usr/bin/python wechat.py 'hcymysql' {$row <$'DB ']} library appears stuck slow SQL! '{$content1}'"); replace its 'hcymysql' my micro signal with your own.

Wechat Enterprise Account Settings Move to https://www.example.com to see this tutorial configuration. www.cnblogs.com/linuxprobe/p/5717776.html

6. Kill.txt file will be generated in the tool directory to save slow SQL.

shell> cat kill.txt

2018-11-27 16:41:22

User Name: root

Source IP: localhost

Database name: hcy

Execution time: 18

SQL statement: select sleep(60)

7. By default, only slow SQL in the connection is killed, and the session connection is retained. If you want to kill the connection, remove QUERY.

Change pt-kill.php

//$kill_sql = "KILL QUERY {$row['ID']}";

$kill_sql = "KILL {$row['ID']}";

Please see "pt_kill demo video.avi" for specific demonstration.