Boost MySQL Performance: Essential Tools and How to Use Them

Regular health checks of a running MySQL instance are essential for ensuring optimal performance, proper configuration, and security. This guide introduces four widely used open‑source utilities that generate comprehensive AWR‑style reports and actionable recommendations.

mysqltuner.pl

tuning-primer.sh

pt-variable-advisor

pt-query-digest

mysqltuner.pl

A Perl script that evaluates MySQL configuration, storage engines, log settings, and security recommendations. It checks roughly 300 metrics for MySQL, MariaDB, and Percona Server and suggests improvements.

Project URL: 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

Key items marked with [!!] indicate critical issues, e.g., "Maximum possible memory usage: 4.8G (244.13% of installed RAM)". Pay special attention to the "Recommendations" section for concrete tuning steps.

tuning-primer.sh

An alternative MySQL health‑check script that performs a full system inspection and provides optimization advice.

Project URL: 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 entries highlighted in red; they correspond to warnings that should be addressed based on your environment.

pt-variable-advisor

A Percona Toolkit sub‑tool that inspects MySQL variables and flags potential misconfigurations.

Download page: https://www.percona.com/downloads/percona-toolkit/LATEST/

Installation

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

Run

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

Report analysis

Look for lines marked with WARN ; they highlight variable settings that may need adjustment.

pt-query-digest

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

Installation

Same package as pt-variable-advisor (Percona Toolkit).

Run

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

Common usage patterns

Analyze the entire slow‑log:

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

Analyze the last 12 hours:

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

Analyze a specific time window:

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 by user (e.g., root):

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

Show only full‑table‑scan or full‑join queries:

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

Report sections

The output is divided into three parts:

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

Query group statistics : ranking, query ID, total response time, percentage of total time, call count, average time per call, variance‑to‑mean ratio, and affected tables.

Detailed per‑query statistics : query ID, database, user distribution, query‑time distribution, tables involved, and the actual SQL statement with EXPLAIN output.