Essential MySQL Performance Tools: mysqltuner.pl, tuning-primer.sh, pt-variable-advisor & pt-query-digest

Why MySQL health checks matter

Regularly inspecting a running MySQL instance verifies that configuration parameters, account privileges, and resource usage stay within safe limits, helping to avoid performance degradation and security risks.

1. mysqltuner.pl

mysqltuner.pl is a widely used Perl script that evaluates MySQL/MariaDB/Percona Server settings, log files, storage engines, and security recommendations, and provides concrete improvement tips.

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

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

Key points when analysing the report:

Items marked with [!!] indicate critical issues, for example “Maximum possible memory usage: 4.8G (244.13% of installed RAM)”.

Pay special attention to the “Recommendations” section for actionable configuration changes.

2. tuning-primer.sh

tuning-primer.sh performs a comprehensive MySQL health check and suggests optimisations based on the detected configuration and usage patterns.

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

wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

./tuning-primer.sh

When reviewing the output, focus on any red‑highlighted warnings and apply the suggested configuration changes that fit your environment.

3. pt-variable-advisor

pt-variable-advisor, part of the Percona Toolkit, analyses MySQL system variables and flags potentially problematic settings.

Installation (example for Percona Toolkit 3.0.13 on RHEL 7):

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
yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock

Report analysis: concentrate on entries marked with WARN , which indicate variables that may need adjustment.

4. pt-query-digest

pt-query-digest parses slow‑query logs, process lists, or tcpdump captures to produce detailed query statistics, helping to identify inefficient queries and workload patterns.

Installation: use the same Percona Toolkit package described in section 3.

Typical usage examples:

pt-query-digest /var/lib/mysql/slowtest-slow.log

pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log

pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00' > slow_report3.log

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

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

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log > slow_report6.log

Report sections to review:

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

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

Detailed per‑query statistics – ID, database, user, query‑time distribution, involved tables, and EXPLAIN output.