Four MySQL Scripts for Diagnosing and Optimizing Your Queries

Regular health checks of a running MySQL instance are essential for verifying performance, configuration, and security.

MySQLTuner.pl

MySQLTuner.pl is a diagnostic script that evaluates parameter settings, log files, storage engines, security recommendations, and performance metrics for MySQL, MariaDB, and Percona Server. It checks roughly 300 indicators and suggests improvements.

Project address: https://github.com/major/MySQLTuner-perl

Download

[root@localhost ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Run

[root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

Key points in the report:

Items marked with [!!] indicate critical issues, e.g.,

Maximum possible memory usage: 4.8G (244.13% of installed RAM)

.

Review the “Recommendations” section for actionable advice.

tuning-primer.sh

tuning-primer.sh performs a comprehensive health check of MySQL and provides optimization suggestions.

Project address: https://github.com/BMDan/tuning-primer.sh

Download

[root@localhost ~]# wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

Run [root@localhost ~]# ./tuning-primer.sh The report highlights red‑flagged items; each warning should be reviewed and the suggested changes applied according to the environment.

pt-variable-advisor (Percona Toolkit)

pt-variable-advisor analyzes MySQL variables and flags potential problems.

Download & Install

[root@localhost ~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
[root@localhost ~]# yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

Run

[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock

Focus on entries marked with WARN; each entry explains why the current setting may be sub‑optimal.

pt-query-digest (Percona Toolkit)

pt-query-digest parses MySQL slow‑query logs, process lists, or tcpdump captures to produce detailed query statistics.

Typical usage examples

# Analyze a slow‑log file
pt-query-digest /var/lib/mysql/slowtest-slow.log

# Analyze the last 12 hours of queries
pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log

# Analyze queries within a specific time window
pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'

# Filter only SELECT statements
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log

# Filter queries from the root user
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log

# Show only full‑table‑scan or full‑join queries
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log

The generated report is divided into three parts:

Overall statistics – total queries, time range, unique queries, min/max/avg, 95th percentile, median, etc.

Query group statistics – rank, query ID, total response time, percentage of total time, call count, average time per call, variance‑to‑mean ratio, and the query object.

Detailed per‑query statistics – query ID, database name, user distribution, query‑time distribution, involved tables, and the actual SQL statement.