Master MySQL Performance: Essential Tools and How to Use Them

MySQLTuner.pl

MySQLTuner.pl is a widely used diagnostic script that evaluates MySQL/MariaDB/Percona Server settings, logs, storage engines, security recommendations, and performance metrics, then suggests concrete improvements.

Download

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

Usage

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

Report analysis

Key items are marked with [!!] (e.g., "Maximum possible memory usage: 4.8G (244.13% of installed RAM)" indicates severe memory over‑allocation). Review the "Recommendations" section for actionable changes.

tuning-primer.sh

tuning-primer.sh performs a comprehensive health check of a MySQL instance and provides optimization suggestions.

Download

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

Usage

# ./tuning-primer.sh

Report analysis

Focus on entries highlighted in red; the tool lists potential problems and corresponding remediation steps.

pt-variable-advisor

Part of the Percona Toolkit, pt-variable-advisor examines MySQL system variables and flags configurations that may cause issues.

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

Pay attention to entries marked with WARN; they indicate variable settings that deserve review.

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 pt-variable-advisor installation steps (the same Percona Toolkit package provides pt-query-digest).

Usage

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

Common usage patterns

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_report_12h.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_report_window.log

Filter only SELECT statements:

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

Filter queries executed by the root user:

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

Show queries that performed 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 > fullscan_report.log

Report structure

Part 1: Overall statistics

Overall – total number of queries

Time range – execution time span

Unique – number of distinct queries after parameterization

Total – aggregate metrics

Min / Max / Avg – minimum, maximum, and average values

95% – value at the 95th percentile

Median – middle value

Part 2: Query group statistics

Rank – ranking by query time (default descending)

Query ID – hash of the normalized query text

Response – total response time for the group

Time – proportion of total time taken by the group

Calls – number of executions

R/Call – average response time per call

V/M – variance‑to‑mean ratio

Item – the query fingerprint

Part 3: Detailed statistics for each query

ID – matches the Query ID

Databases – database name(s) involved

Users – distribution of users executing the query

Query_time distribution – breakdown of execution times

Tables – tables referenced

Explain – the SQL statement itself