Boost MySQL Performance: Essential Tools and How to Use Them

Regular health checks of a running MySQL instance are essential for ensuring optimal performance, safe configuration, and the absence of security risks. The following tools provide automated diagnostics and actionable recommendations.

MySQLTuner

MySQLTuner is a widely used Perl script that evaluates parameter settings, log files, storage engines, security suggestions, and overall performance. It supports MySQL, MariaDB, and Percona Server, checking roughly 300 metrics.

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)" indicates severe memory over‑commit.

Review the "Recommendations" section for concrete tuning advice.

tuning-primer.sh

tuning‑primer.sh performs a comprehensive MySQL health check and offers 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 entries highlighted in red; the tool provides specific recommendations for each warning.

pt-variable-advisor

Part of the Percona Toolkit, pt‑variable‑advisor analyzes MySQL variables and suggests fixes for potential problems.

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

Install

# 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; they indicate variable settings that may degrade performance or security.

pt-query-digest

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

Installation

Refer to the installation steps for pt‑variable‑advisor (the Percona Toolkit package includes pt‑query‑digest).

Basic usage

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

Common analysis commands

Analyze a slow‑query 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 specific user (e.g., root):

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

Show all 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

Overall statistics – total queries, time range, unique query count, totals, min/max/avg, 95th percentile, median.

Query group statistics – rank, query ID, total response time, time share, call count, average per call, variance‑to‑mean ratio, affected tables.

Detailed per‑query statistics – query ID, database name, user distribution, query‑time distribution, involved tables, and the EXPLAIN output.