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

Running MySQL? Do you know whether its performance is optimal, its parameters are properly set, or its accounts pose security risks? Regularly performing a MySQL health check is essential for safe and efficient operation.

MySQLTuner.pl

MySQLTuner is a popular diagnostic tool that checks parameter settings, log files, storage engines, security recommendations, and performance analysis, offering improvement suggestions. It supports MySQL, MariaDB, and Percona Server with about 300 metrics.

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

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

Key points in the report:

Items marked with [!!] indicate critical issues, e.g., memory usage exceeding installed RAM.

Pay attention to the "Recommendations" section for actionable advice.

tuning-primer.sh

tuning‑primer is another MySQL health‑check script that provides overall diagnostics and optimization suggestions.

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

# ./tuning-primer.sh

Focus on red‑alert items in the report and apply the suggested changes according to your system.

Percona Toolkit – pt‑variable‑advisor

pt‑variable‑advisor, part of the Percona Toolkit, analyzes MySQL variables and advises on potential problems.

# 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

Look for entries marked with WARN for variable‑related issues.

Percona Toolkit – pt‑query‑digest

pt‑query‑digest analyzes MySQL queries from logs, process lists, or tcpdump captures, providing detailed statistics.

# 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

The report is divided into three parts:

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

Query group statistics : rank, query ID, response time, time share, call count, R/Call, variance/mean ratio, item.

Detailed per‑query statistics : ID, database, users, query‑time distribution, tables, EXPLAIN output.

By using these tools, you can identify memory over‑use, misconfigured parameters, security risks, and slow queries, then apply the recommended optimizations to keep your MySQL instance running efficiently.