Boost MySQL Performance: Essential Tools, Installation & Report Analysis

mysqltuner.pl

mysqltuner.pl is a widely used Perl script that inspects MySQL/MariaDB/Percona Server settings, logs, storage engines, and security recommendations, then suggests concrete improvements. It evaluates around 300 metrics in recent releases.

Download

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

Usage

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

Report analysis

Pay special attention to items marked with [!!] (e.g., "Maximum possible memory usage" exceeding installed RAM) and the final "Recommendations" section, which lists actionable configuration changes.

tuning-primer.sh

tuning-primer.sh performs a comprehensive health check of a MySQL instance and offers optimization advice.

Download

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

Usage

./tuning-primer.sh

Report analysis

Focus on red‑flagged warnings and the "Recommendations" section; adjust settings according to your environment.

pt-variable-advisor

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

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

Usage

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

Report analysis

Look 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 query statistics.

Installation

Refer to the same Percona Toolkit package used for pt-variable-advisor.

Usage

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

Common analysis patterns

Analyze the entire slow‑log file.

Analyze queries from the last 12 hours:

pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > report.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' > report.log

Filter only SELECT statements:

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

Filter by user (e.g., root):

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

Report sections

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

Query group statistics : rank, query ID, response time, time share, call count, R/Call, variance‑to‑mean ratio, affected tables.

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

By regularly running these tools and reviewing their reports, database administrators can detect misconfigurations, memory over‑commitments, insecure accounts, and inefficient queries, then apply the recommended changes to keep MySQL instances secure and performant.