Boost MySQL Performance: Essential Tools and How to Use Them

mysqltuner.pl

mysqltuner.pl is a Perl script that inspects MySQL, MariaDB, and Percona Server configurations, logs, storage engines, and security settings, then provides concrete recommendations.

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

Pay special attention to items marked with [!!] (e.g., “Maximum possible memory usage: 4.8G (244.13% of installed RAM)”) and to the “Recommendations” section at the end of the output.

tuning-primer.sh

tuning-primer.sh performs a comprehensive health check of a MySQL instance and suggests optimizations.

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 red‑highlighted warnings and apply the suggested changes according to your environment.

pt-variable-advisor (Percona Toolkit)

pt-variable-advisor examines MySQL system variables and flags potentially problematic settings.

Download from Percona Toolkit: 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 entries marked with WARN and address the indicated configuration issues.

pt-query-digest (Percona Toolkit)

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

Run

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

Common usage patterns

Analyze a slow‑log file directly:

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

Analyze queries from 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 queries run by a particular 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 structure

Part 1: Overall statistics – total queries, time range, unique queries, total/average/max/min times, 95th percentile, median, etc.

Part 2: Query group statistics – rank, query ID, total response time, proportion of total time, call count, average time per call, variance‑to‑mean ratio, affected tables.

Part 3: Detailed per‑query statistics – query ID, database name, user distribution, query‑time distribution, tables involved, and the actual SQL statement (EXPLAIN output).