Essential MySQL Performance Tuning Tools and How to Use Them

Regularly performing a health check on a running MySQL instance is essential for ensuring performance, proper parameter configuration, and security.

Below are four widely used MySQL optimization tools, how to obtain them, install, run, and interpret the generated AWR‑style reports.

1. mysqltuner.pl

mysqltuner.pl is a popular Perl script that examines MySQL/MariaDB/Percona Server configuration, logs, storage engines, and security recommendations, reporting about 300 metrics. It suggests improvements based on detected issues.

Download

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

Run

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

Report analysis

Pay special attention to items marked with [!!] (e.g., “Maximum possible memory usage: 4.8G (244.13% of installed RAM)”), and follow the “Recommendations” section.

2. tuning-primer.sh

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

Download

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

Run ./tuning-primer.sh Report analysis

Focus on red‑flag warnings and apply the suggested changes according to your environment.

3. pt-variable-advisor

Part of the Percona Toolkit, pt-variable-advisor analyses MySQL variables and warns about potential problems.

Installation

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

Run

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

Report analysis

Check entries marked with WARN and adjust configuration accordingly.

4. pt-query-digest

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

Installation

See section 3.1 for Percona Toolkit installation.

Usage examples

pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log
pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > recent_report.log
pt-query-digest --since='2017-01-07 09:30:00' --until='2017-01-07 10:00:00' /var/lib/mysql/slowtest-slow.log > time_range_report.log
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log > select_report.log
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log > root_report.log
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log > fullscan_report.log

Report analysis

The report is divided into three parts: overall statistics, query‑group statistics, and detailed per‑query statistics, including total time, call count, average time per call, and execution plans.