Build and Deploy a PHP‑Based MySQL Slow Query Analyzer with Automated Alerts

Background

Slowquery is a web‑based MySQL slow‑query analysis tool. It was refactored from a PHP CI application to a standalone interface that visualizes slow queries using the Anemometer layout and integrates the open‑source Soar optimizer to provide one‑click recommendations. The agent component uses Percona pt‑query‑digest to collect and store slow‑query logs.

Repository

Source code:

https://github.com/hcymysql/slowquery

Prerequisites

Percona Toolkit (provides pt‑query‑digest)

Apache/httpd, PHP (with MySQL extension) and MySQL client libraries

A MySQL server that will host the management database (stores configuration and analysis results)

Installation

Install Percona Toolkit: yum install percona-toolkit -y Install Apache, PHP and MySQL client packages: yum install httpd mysql php php-mysql -y Copy the application into the web root, e.g. # mv slowquery /var/www/html/ Create the management database (named sql_db in examples) and import the schema files:

# mysql -uroot -p123456 sql_db < ./dbinfo_table_schema.sql
# mysql -uroot -p123456 sql_db < ./slowquery_table_schema.sql

Insert a record into dbinfo that describes the MySQL instance to monitor, for example:

INSERT INTO sql_db.dbinfo VALUES (1,'192.168.148.101','test','admin','123456',3306);

Adjust the configuration files (see below).

Configuration files

config.php

– set the connection parameters for the management MySQL instance (host, port, user, password, database). soar_con.php – same connection parameters are used by the Soar integration.

Agent script

The script slowquery_analysis.sh runs on the production MySQL host. It performs three tasks: enable slow‑query logging, run pt‑query‑digest on the current slow‑query log and store the parsed events in the management tables, and delete log files older than seven days.

Key variables that must be edited before use:

slowquery_db_host="192.168.148.9"          # management DB host
slowquery_db_user="admin"
slowquery_db_password="123456"
slowquery_db_database="sql_db"

mysql_host="192.168.148.1"               # production MySQL host
mysql_user="admin"
mysql_password="123456"
slowquery_dir="/data/mysql/yourDB/slowlog/"
slowquery_long_time=2                    # threshold in seconds
pt_query_digest="/usr/local/bin/pt-query-digest"
mysql_server_id=270

Example command that feeds the log to pt‑query‑digest (adds a column with the raw query length and restricts to the configured server ID):

$pt_query_digest --user=$slowquery_db_user --password=$slowquery_db_password \
    --review h=$slowquery_db_host,D=$slowquery_db_database,t=mysql_slow_query_review \
    --history h=$slowquery_db_host,D=$slowquery_db_database,t=mysql_slow_query_review_history \
    --no-report --limit=100% \
    --filter="\$event->{add_column}=length(\$event->{arg}) and \$event->{serverid}=$mysql_server_id" \
    $slowquery_file > /tmp/slowquery_analysis.log

Enable logging and set the log file name:

$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password \
    -e "set global slow_query_log=1; set global long_query_time=$slowquery_long_time;"

$tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password \
    -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');" | grep log | sed -n -e '2p'`

$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password \
    -e "set global slow_query_log_file='$tmp_log';"

Delete logs older than seven days:

cd $slowquery_dir
/usr/bin/find ./ -name 'slowquery_*' -mtime +7 | xargs rm -f

Cron scheduling

Run the analysis script every 10 minutes:

*/10 * * * * /bin/bash /usr/local/bin/slowquery_analysis.sh > /dev/null 2>&1

Send email alerts every three hours (see next section).

Email alert configuration

Edit slowquery/alarm_mail/sendmail.php with your SMTP server details. Example:

$smtpserver = "smtp.126.com";
$smtpserverport = 25;
$smtpusermail = "[email protected]";
$smtpemailto = "[email protected]";
$smtpuser = "[email protected]";
$smtppass = "your_smtp_password";

Schedule the mailer:

0 */3 * * * cd /var/www/html/slowquery/alarm_mail; /usr/bin/php sendmail.php > /dev/null 2>&1

Usage

Open the web UI at http://<em>your-host</em>/slowquery/slowquery.php. The UI lists slow queries; clicking the “+” icon invokes Soar, which returns a formatted optimization recommendation and an EXPLAIN plan. The interface also provides links to view detailed query statistics and historical data.

Demo

A public demonstration is available at http://fander.jios.org:8008/slowquery/slowquery.php.