Essential MySQL Performance Tuning Tools: MySQLTuner, Tuning‑Primer, and Percona Toolkit

MySQLTuner.pl

MySQLTuner is a Perl script that examines MySQL/MariaDB/Percona Server configuration, logs, storage engines and security settings, then outputs recommendations. Repository: https://github.com/major/MySQLTuner-perl

Download

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

Run

chmod +x mysqltuner.pl
./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

The script prompts for administrative credentials, then prints a report. Items marked [!!] indicate critical issues such as memory usage exceeding RAM. Review the “Recommendations” section to adjust my.cnf parameters.

tuning‑primer.sh

tuning‑primer is a Bash script that performs a health‑check of a MySQL instance and suggests configuration changes. Repository: https://github.com/BMDan/tuning-primer.sh

Download

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

Run

chmod +x tuning-primer.sh
./tuning-primer.sh

The script prints a header “MYSQL PERFORMANCE TUNING PRIMER” and then a series of sections: overall statistics, query‑group statistics, and per‑query metrics. Red‑flag warnings and the final “Recommendations” block guide adjustments to variables, indexes, and my.cnf.

pt‑variable‑advisor

Part of Percona Toolkit, pt-variable-advisor inspects MySQL system variables and flags values that may degrade performance or security.

Installation

# Example for RHEL/CentOS 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
tar -xf percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
yum install percona-toolkit-3.0.13-re85ce15.el7.x86_64.rpm

Run

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

The output lists each variable, the recommended value, and a WARN tag for out‑of‑range settings. Adjust the flagged variables in my.cnf and restart MySQL.

pt‑query‑digest

pt-query-digest

parses MySQL slow‑query logs, process lists, or tcpdump captures and produces a statistical summary.

Typical usage examples

Analyze a slow‑query log file:

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

Analyze the last 12 hours:

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

Analyze a specific time window:

pt-query-digest --since='2023-01-07 09:30:00' --until='2023-01-07 10:00:00' /var/lib/mysql/slow.log > slow_report_window.log

Filter only SELECT statements:

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

Filter queries run by the root user:

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

Find full table scans or full joins:

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

The generated report consists of three parts:

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

Query‑group statistics (rank, total response time, call count, average per call, variance‑to‑mean ratio).

Per‑query details (query ID, databases, user distribution, time distribution, tables involved, EXPLAIN output).

Use the WARN entries to identify slow or inefficient queries and adjust indexes or query patterns accordingly.