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

Regular health checks of a running MySQL instance are essential for ensuring proper parameter settings, security, and overall performance.

mysqltuner.pl

A widely used Perl script that examines MySQL configuration, logs, storage engines, and security recommendations, providing over 300 metrics for MySQL, MariaDB, and Percona Server.

# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

Key report items are marked with [!!] (critical issues) and a list of Recommendations for improvement.

tuning-primer.sh

An alternative shell script that performs a comprehensive MySQL health check and offers optimization suggestions.

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

The script reports on configuration, indexes, query cache, InnoDB settings, and other performance‑related aspects.

pt-variable-advisor

A Percona Toolkit utility that analyses MySQL system variables and flags potential problems.

# wget https://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-3.0.13.tar.gz
# yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm
# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock

Focus on entries marked with WARN to adjust variable settings.

pt-query-digest

Another Percona Toolkit tool that parses slow‑query logs, process lists, or tcpdump captures to produce detailed query performance statistics.

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

The output includes overall statistics, query‑group rankings, and per‑query details such as response time, call count, and execution distribution.

After reviewing the reports, the article invites readers to join the "Top‑Level Architect" community for additional resources and a giveaway.