Essential MySQL Performance Tuning Tools and How to Use Them

MySQL Performance Tuning Tools Overview

Regular health checks of a running MySQL instance are essential for security and performance. The following open‑source tools generate AWR‑style reports and provide tuning recommendations.

1. mysqltuner.pl

Perl script that examines MySQL/MariaDB/Percona Server configuration, logs, storage engines, and security settings, then suggests improvements.

Project address: https://github.com/major/MySQLTuner-perl

Download

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

Run

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

Report analysis

Items marked [!!] indicate serious issues (e.g., memory usage exceeding RAM). Review the “Recommendations” section for actionable changes.

2. tuning‑primer.sh

Shell script that produces a comprehensive health‑check report with optimization suggestions.

Project address: https://github.com/BMDan/tuning-primer.sh

Download

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

Run ./tuning-primer.sh Report analysis

Focus on items highlighted in red; the tool provides specific configuration changes based on detected problems.

3. pt‑variable‑advisor

Part of Percona Toolkit; analyses MySQL system variables and warns about potentially unsafe settings.

Installation (example for Percona Toolkit 3.0.13)

wget https://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

Usage

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

Report analysis

Pay attention to entries marked WARN, especially memory‑related variables that may need adjustment.

4. pt‑query‑digest

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

Installation – same as in section 3 (install Percona Toolkit).

Common usage examples

Analyze a slow‑query log file:

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

Analyze the last 12 hours of queries:

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

Analyze queries in a specific 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

Filter only SELECT statements:

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

Filter queries from the root user:

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log > slow_report5.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/slowtest-slow.log > slow_report6.log

Report analysis

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

Query‑group statistics: rank, query ID, total response time, percentage of total time, call count, average time per call, variance‑to‑mean ratio, and the query text.

Per‑query details: ID, database, user distribution, query‑time distribution, involved tables, and EXPLAIN output.

These tools together provide a systematic way to audit MySQL configuration, identify performance bottlenecks, and apply targeted optimizations.