Essential MySQL Tuning Tools: Diagnose and Optimize Your Database

mysqltuner.pl

mysqltuner.pl is a widely used Perl script that inspects a running MySQL instance. It evaluates configuration parameters, log settings, storage‑engine choices, security options, and basic performance metrics, then prints concrete tuning suggestions.

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

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 entries marked [!!] , which flag critical problems such as memory over‑commit (e.g., "Maximum possible memory usage: 4.8G (244.13% of installed RAM)"). After the diagnostic section, review the "Recommendations" block for specific configuration changes.

tuning-primer.sh

tuning-primer.sh performs a comprehensive health check of a MySQL server, covering kernel parameters, InnoDB settings, query cache, table statistics, and security aspects. It outputs warnings and suggested adjustments.

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

Download

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

Usage

# ./tuning-primer.sh

Report analysis

The script highlights problematic items in red. Examine each red warning and apply the corresponding recommendation, adjusting kernel limits, buffer sizes, or security settings as appropriate for your environment.

pt-variable-advisor

pt-variable-advisor is a Percona Toolkit utility that audits MySQL system variables, compares them against best‑practice thresholds, and reports potential misconfigurations.

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

Entries marked with WARN indicate variables that fall outside recommended ranges. Typical warnings involve innodb_buffer_pool_size, query_cache_size, or max_connections. Adjust the flagged variables in my.cnf and restart MySQL to apply changes.

pt-query-digest

pt-query-digest, also part of Percona Toolkit, parses MySQL slow‑query logs, general logs, or tcpdump captures and produces detailed performance reports, including query frequency, execution time distribution, and EXPLAIN output.

Installation

Install the Percona Toolkit as described in the pt-variable-advisor section; the same package provides pt-query-digest.

Basic usage

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

Common usage examples

# pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log
# pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report_12h.log
# 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_range.log
# pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log > select_report.log
# pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log > root_user_report.log
# pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log > fullscan_join_report.log

Report analysis

Part 1: Overall statistics – total query count, time range, number of unique fingerprints, min/avg/max execution times, 95th percentile, median, etc.

Part 2: Query grouping statistics – rank, query ID (hash of normalized query), total response time, share of total time, call count, average time per call, variance‑to‑mean ratio, and the query text.

Part 3: Detailed per‑query statistics – database name, user distribution, query‑time distribution, tables involved, and the EXPLAIN plan for each fingerprint.