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

Running MySQL without regular health checks can hide performance bottlenecks, mis‑configured parameters, and security risks. Periodic MySQL diagnostics are essential for safe and efficient operation.

mysqltuner.pl

mysqltuner.pl is a widely used MySQL performance‑diagnosis script that checks parameter settings, log files, storage engines, security recommendations, and overall performance, providing improvement suggestions. It supports MySQL, MariaDB, and Percona Server with about 300 metrics.

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 serious issues, e.g., memory usage exceeding installed RAM.

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

tuning-primer.sh

tuning-primer.sh is another MySQL health‑check script that provides overall diagnostics and 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 dba]# ./tuning-primer.sh

2.3 Report Analysis

Focus on red‑flagged items and follow the suggested changes according to your environment.

pt-variable-advisor

Part of Percona Toolkit, pt-variable-advisor analyzes MySQL variables and offers recommendations.

3.1 Installation

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

[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 and address the highlighted configuration problems.

pt-query-digest

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

4.1 Installation

Same installation steps as in section 3.1.

4.2 Usage

Typical command to analyze a slow‑log file:

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

4.3 Common Usage Examples

Analyze the entire slow‑log:

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

Analyze the last 12 hours:

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

Analyze a specific time range:

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 from 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 only 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 > slow_report6.log

4.4 Report Analysis

The report is divided into three main parts:

Overall Statistics : total queries, time range, unique queries, min/avg/max times, 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, involved tables, etc.

Detailed Statistics per Query : query ID, database name, user distribution, query‑time distribution, tables used, and the EXPLAIN output for each statement.

By regularly running these tools and reviewing their reports, you can keep MySQL configurations optimal, improve performance, and reduce security risks.