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 and security. The author shares four popular MySQL optimization tools that can generate AWR‑style reports and provide actionable recommendations.

1. mysqltuner.pl

mysqltuner.pl is a widely used Perl script that evaluates MySQL/MariaDB/Percona configuration, logs, storage engines, and security settings, offering improvement suggestions.

Download

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

Usage

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

The report highlights critical items marked with [!!] (e.g., memory usage exceeding RAM) and provides a “Recommendations” section for tuning.

2. tuning-primer.sh

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

Download

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

Usage

# ./tuning-primer.sh

The generated report flags warnings (shown in red) that should be reviewed and adjusted according to the specific environment.

3. pt-variable-advisor

Part of the Percona Toolkit, pt-variable-advisor analyzes MySQL variables and warns about potentially problematic settings.

Installation

# wget https://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-3.0.13.tar
# yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

Usage

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

The output emphasizes entries with WARN flags for further investigation.

4. pt-query-digest

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

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

Usage examples

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

# pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log

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

The report is divided into overall statistics, query‑group rankings, and detailed per‑query analysis (including databases, users, execution time distribution, tables, and EXPLAIN output).

In addition to the technical content, the article contains extensive promotional sections for ChatGPT services, private “knowledge‑planet” communities, and various paid offers, which are unrelated to the MySQL tutorial.