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

Running MySQL in production requires regular health checks to ensure optimal configuration, safe account settings, and acceptable performance. The article presents four widely used MySQL diagnostic tools that generate AWR‑style reports and provide actionable recommendations.

1. mysqltuner.pl

mysqltuner.pl is a Perl script that evaluates MySQL/MariaDB/Percona Server settings, logs, storage engines, and security recommendations, covering roughly 300 metrics.

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

Report analysis

Items marked with [!!] indicate critical warnings (e.g., memory usage exceeding installed RAM). The "Recommendations" section lists suggested configuration changes.

2. tuning-primer.sh

tuning-primer.sh performs a comprehensive MySQL health check and offers optimization advice.

Download

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

Run

[root@localhost ~]# ./tuning-primer.sh

Report analysis

Focus on items highlighted in red; apply the suggested changes according to your environment.

3. pt-variable-advisor

Part of Percona Toolkit, pt-variable-advisor inspects MySQL variables and flags potential problems.

Installation

[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<br/>[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

Report analysis

Pay attention to entries marked with WARN , which indicate sub‑optimal variable settings.

4. pt-query-digest

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

Installation

Refer to the Percona Toolkit installation steps (section 3.1).

Run

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

Common usage examples

Analyze a slow‑query file:

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

Analyze the last 12 hours:

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

Filter by time range:

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

Show only SELECT statements:

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

Show queries from a specific user (e.g., root):

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

Show 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 > slow_report6.log

Report structure

The output is divided into three parts: overall statistics (total queries, time range, min/avg/max, 95th percentile, median), query‑group statistics (rank, query ID, response time, calls, R/Call, V/M, affected tables), and detailed per‑query metrics (ID, databases, users, time distribution, tables, EXPLAIN output).

By regularly running these tools, DBAs can quickly spot misconfigurations, memory over‑commit, inefficient queries, and security risks, thereby keeping MySQL instances healthy and performant.