MySQL Performance Tuning Tools: mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest

Regular health checks of a running MySQL instance are essential for ensuring optimal performance, proper parameter settings, and secure account configurations. This guide shares several open‑source tools that generate AWR‑style reports, allowing administrators to assess overall database health.

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, offering concrete improvement suggestions.

Project URL: https://github.com/major/MySQLTuner-perl

1.1 Download

[root@localhost ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

1.2 Usage

[root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

1.3 Report Analysis

Key points to watch:

Items marked with [!!] indicate critical issues, e.g., memory usage exceeding installed RAM.

Pay attention to the “Recommendations” section for actionable advice.

tuning-primer.sh

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

Project URL: https://github.com/BMDan/tuning-primer.sh

2.1 Download

[root@localhost ~]# wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

2.2 Usage

[root@localhost ~]# ./tuning-primer.sh

2.3 Report Analysis

Focus on entries highlighted in red and follow the suggested changes according to your environment.

pt-variable-advisor

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

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

3.1 Installation

[root@localhost ~]# 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
[root@localhost ~]# yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

3.2 Usage

[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock

3.3 Report Analysis

Look for entries marked with WARN to identify mis‑configured variables.

pt-query-digest

pt-query-digest analyzes MySQL query logs, process lists, and tcpdump captures to produce detailed query performance reports.

4.1 Installation

Refer to the installation steps in section 3.1.

4.2 Usage

Typical usage examples:

# Analyze a slow‑log file
pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log

# Analyze the last 12 hours of queries
pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report12h.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 from the root user
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

4.3 Report Analysis

The report is divided into three main sections:

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 the query object.

Detailed Statistics per Query : query ID, database name, user distribution, query‑time distribution, involved tables, and EXPLAIN output.

By reviewing these sections, you can pinpoint the most expensive queries, understand their execution patterns, and apply targeted optimizations.