MySQL Performance Tuning Tools: mysqltuner.pl, tuning-primer.sh, and Percona Toolkit

Running MySQL in production raises questions about performance, configuration, and security; regular health checks using dedicated tools can help ensure safe and efficient operation.

mysqltuner.pl is a widely used Perl script that evaluates MySQL parameters, log settings, storage engines, and security recommendations, offering improvement suggestions. It supports MySQL, MariaDB, and Percona Server with around 300 metrics.

Download and run:

[root@localhost ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

[root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

The generated report highlights critical alerts (e.g., memory usage exceeding RAM) and provides a “Recommendations” section for tuning.

tuning-primer.sh is another script that performs a comprehensive MySQL health check and offers optimization advice.

Download and run:

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

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

After execution, focus on red‑flagged items and follow the suggested changes according to your environment.

pt-variable-advisor (part of Percona Toolkit) analyzes MySQL variables and warns about potential problems.

Installation:

[root@localhost ~]# wget https://www.percona.com/downloads/percona-toolkit/LATEST/

[root@localhost ~]# yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

Usage:

[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock

Review WARN entries in the output for mis‑configured variables.

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

Typical 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 > 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, averages, percentiles), query‑group statistics (rank, query ID, response time, calls, R/Call, variance/mean, item), and detailed per‑query metrics (ID, databases, users, time distribution, tables, EXPLAIN output).

By regularly running these tools and analyzing their output, database administrators can detect configuration bottlenecks, unsafe settings, and inefficient queries, then apply targeted optimizations to improve MySQL performance and reliability.