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 to verify performance, configuration correctness, and security. The article presents a set of open‑source tools that generate AWR‑style reports, highlight critical warnings, and suggest improvements.

mysqltuner.pl

mysqltuner.pl is a widely used diagnostic script that evaluates MySQL/MariaDB/Percona Server settings, logs, storage engines, and security recommendations. It reports around 300 metrics and provides actionable advice.

1.1 Download

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

1.2 Usage

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

The script interacts with the user for credentials, then displays a summary of version, architecture, and key configuration checks.

1.3 Report Analysis

Focus on items marked with [!!] (critical warnings), e.g., “Maximum possible memory usage: 4.8G (244.13% of installed RAM)”. Also review the “Recommendations” section for specific tuning steps.

tuning-primer.sh

tuning‑primer.sh performs a comprehensive MySQL health check and offers optimization suggestions.

2.1 Download

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

2.2 Usage

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

The output highlights red‑flag warnings; users should adjust settings based on their environment.

pt-variable-advisor

Part of the Percona Toolkit, pt‑variable‑advisor examines MySQL variables and flags potential problems.

3.1 Installation

[root@localhost ~]# 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
[root@localhost ~]# yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

3.2 Usage

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

Inspect the report for entries marked with WARN and address the suggested changes.

pt-query-digest

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

4.1 Installation

Refer to the installation steps in section 3.1 (Percona Toolkit).

4.2 Usage

[root@localhost ~]# pt-query-digest /var/lib/mysql/slowtest-slow.log

Common usage patterns include:

Analyzing a slow‑query file: pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log

Limiting analysis to the last 12 hours: pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log

Filtering by time range: 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

Filtering only SELECT statements: pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log > slow_report4.log

Filtering by user (e.g., root): pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log > slow_report5.log

Finding full table scans or full joins: pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log > slow_report6.log

The generated report is divided into three sections: overall statistics, query ranking, and detailed per‑query metrics (including execution time distribution, affected databases, users, tables, and the actual SQL statements).

By regularly running these tools, database administrators can proactively detect misconfigurations, memory over‑commitment, inefficient queries, and security gaps, thereby improving MySQL reliability and performance.