Boost MySQL Performance: How to Use the PHP Rebuilt Percona PT‑kill with Email & WeChat Alerts

Overview

The original Percona pt‑kill (Perl) only kills running slow queries and lacks monitoring features. The PHP reimplementation captures resource‑intensive statements from information_schema.PROCESSLIST, filters them, optionally kills them, and can send email or WeChat alerts.

Installation

Install required PHP packages: yum install -y php-process php php-mysql Source code repository:

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

Functionality

The tool monitors SELECT, ALTER and other DML/DDL statements whose execution time exceeds a configurable threshold. Detected queries are recorded, can be killed (either KILL QUERY or full KILL), and notifications are sent via email or WeChat. Only queries that have finished execution appear in the MySQL slow.log.

Usage and Options

-u username

Database user -p password User password -h host_ip MySQL host -P port MySQL port -B busytime_seconds Execution‑time threshold for slow‑query detection -I interval_seconds Monitoring interval when running as a daemon --kill Enable termination of the identified query --match-info='pattern' Filter by statement type (e.g., select|alter) --match-user='user' Filter by MySQL user --daemon 1|0 Run as background daemon (1) or stop it (0) --mail Enable email alerts (requires smtp_config.php) --weixin Enable WeChat alerts (requires simplejson-3.8.2.tar.gz) --help Show help

Examples

Foreground execution:

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

Background daemon (monitor every 15 seconds):

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

Stop daemon:

php pt-kill.php --daemon 0

Important Parameters

--kill

Activates query termination. --match-info Can be combined with --match-user for finer filtering. --daemon 1 Runs the tool as a background service; without it you may use crontab instead. --mail Sends email alerts; configure SMTP settings in smtp_config.php. --weixin Sends WeChat alerts; install simplejson-3.8.2.tar.gz and set your WeChat ID.

Configuration Details

After installing the required Python library, edit pt‑kill.php to replace the placeholder WeChat ID (e.g., hcymysql) with your own. The script invokes a Python helper like:

$status1 = system("/usr/bin/python wechat.py 'your_wechat_id' {$row['DB']}库出现卡顿慢SQL！ '{$content1}'");

Logging

The tool creates kill.txt in its directory, appending a record for each detected slow query. Example entry:

2018-11-27 16:41:22
用户名：root
来源IP：localhost
数据库名：hcy
执行时间：18
SQL语句：select sleep(60)

Customization

By default the script issues KILL QUERY {ID}, which terminates only the statement. To kill the entire connection, modify the kill statement in pt‑kill.php:

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

Download

The script can be downloaded from the following link (plain URL):

https://pan.baidu.com/s/1dDJfkRMKn3zwDlVvHqnLdA

After extracting the archive, follow the installation steps above.