Using MySQLTuner to optimize MySQL performance

MySQLTuner is a single-file Perl script that connects to a running MySQL or MariaDB server, reads its global status counters, configuration variables, schema information, and security posture, and prints a prioritized list of recommendations. It is not a benchmark, not a profiler, and not a magic "make MySQL faster" button — what it actually does is encode about a decade of "this counter is unusually high, here is the variable that controls it" rules so you don't have to remember them.

Used correctly it is one of the quickest ways to spot a misconfigured buffer pool, missing indexes that are pushing temp tables to disk, or a query cache (on MySQL 5.x) that is doing more harm than good. Used badly — copy-pasting every suggestion straight into my.cnf — it is a great way to make a working server worse.

This guide walks through installing and running MySQLTuner on a modern server, what each section of its output actually means, and the small set of common findings that almost always justify a change versus the ones that are red herrings.

Run it on a server with real traffic. MySQLTuner's recommendations are derived from runtime counters. On a freshly started instance with no load, the script will tell you many useful things are "below the recommended threshold" purely because the server has not been queried yet. The rule of thumb in the script's own output: at least 24 hours of uptime and a representative workload before its advice means anything.

What MySQLTuner actually checks

The script runs roughly four passes over the server:

General info — version, uptime, threads, total memory.
Security audit — empty passwords, anonymous users, test database left behind, users without host restrictions.
Performance metrics — InnoDB buffer pool hit rate, key buffer (MyISAM) usage, query cache hit rate (5.x only), temporary tables on disk, table cache, open files, slow queries, joins without indexes, sorts that overflow, aborted connections, thread cache hit rate, table locking, schema engine balance.
Schema audit (optional, --dbstat/--idxstat) — table fragmentation, missing primary keys, redundant indexes.

It ends with two sections you actually act on:

General recommendations — non-config changes, usually "add indexes", "use InnoDB instead of MyISAM", "set a root password".
Variables to adjust — concrete my.cnf directives with suggested values.

Installation

Debian / Ubuntu

sudo apt update
sudo apt install -y mysqltuner
mysqltuner --version

The distro package is convenient but can lag the upstream script by months. For the latest checks, pull from GitHub.

RHEL / AlmaLinux / Rocky / CentOS / Fedora

The script is not in the default repos. Install via EPEL or grab the script directly:

# EPEL (if you use it)
sudo dnf install -y epel-release
sudo dnf install -y mysqltuner

# Or upstream, single-file
sudo curl -L https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl \
  -o /usr/local/bin/mysqltuner
sudo chmod +x /usr/local/bin/mysqltuner

The script depends only on a Perl interpreter (perl-core or perl package), which is preinstalled almost everywhere.

Verify

mysqltuner --help | head

You should see the usage and a long flag list — --host, --user, --pass, --dbstat, --idxstat, --cvefile, --forcemem, etc.

Running it safely

MySQLTuner is read-only against the server — it issues SHOW VARIABLES, SHOW GLOBAL STATUS, and reads information_schema / mysql.user. It does not modify configuration, restart anything, or run heavy queries. That said, it still needs a privileged user and on a very busy server even read-only information_schema scans (with --dbstat) can take a few seconds.

Create a dedicated user (recommended)

Logging in as root works but is bad hygiene. Create a low-privilege account:

CREATE USER 'tuner'@'localhost' IDENTIFIED BY '{strong_password}';

GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'tuner'@'localhost';
GRANT SELECT ON mysql.* TO 'tuner'@'localhost';
GRANT SELECT ON performance_schema.* TO 'tuner'@'localhost';
FLUSH PRIVILEGES;

These grants are sufficient for the standard checks. --dbstat and --idxstat need read access to user schemas — for a thorough audit, grant SELECT ON *.* to tuner.

Use a credentials file

Putting the password on the command line is visible in ps. Use ~/.my.cnf instead:

[client]
user=tuner
password={strong_password}
host=127.0.0.1

chmod 600 ~/.my.cnf and then:

mysqltuner

…will pick it up automatically. Otherwise:

mysqltuner --user tuner --pass '{strong_password}' --host 127.0.0.1

Useful runtime flags

Flag	Effect
`--host`, `--port`, `--socket`	Target a remote / non-default server
`--user`, `--pass`, `--defaults-file`	Auth
`--dbstat`	Per-database fragmentation/size report
`--idxstat`	Index efficiency report
`--cvefile` / `--nocheckversion`	Security CVE check (online) or disable it
`--forcemem N`	Tell the script the server has N MB RAM (use when running remotely)
`--forceswap N`	Same for swap
`--silent`	Only print the recommendations, hide the banner/details
`--json` / `--prettyjson`	Machine-readable output (handy for CI)
`--buffers`	Print the current buffer settings, useful for diffing

For a "tell me everything" report on the box itself:

mysqltuner --dbstat --idxstat

For an automated check from a monitoring host:

mysqltuner --host db.example.internal --user tuner --pass '...' \
  --forcemem 16384 --json > /tmp/mysqltuner.json

Reading the output

A real run produces output in roughly this shape (abridged):

>>  MySQLTuner 2.x.x
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 8.0.36
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ------------------------
[--] Data in InnoDB tables: 12.4G (Tables: 142)
[OK] Total fragmented tables: 0

-------- Performance Metrics ------------------------------
[--] Up for: 3d 4h (482M q [1.7K qps], 38K conn, TX: 612G, RX: 84G)
[OK] Reads / Writes: 78% / 22%
[OK] Binary log enabled
[!!] Maximum reached memory usage: 18.2G (113.75% of installed RAM)
[!!] Maximum possible memory usage: 26.4G (165% of installed RAM)
[!!] Joins performed without indexes: 142000
[!!] Temporary tables created on disk: 32% (sort table size limit)
[OK] Thread cache hit rate: 99%
[OK] Table cache hit rate: 95%
[!!] InnoDB buffer pool / data size: 8G / 12.4G

-------- Recommendations ----------------------------------
General recommendations:
    Add indexes on queries with JOIN clauses (see 'Joins without indexes')
    Consider raising innodb_buffer_pool_size — current 8G, dataset 12.4G
    Reduce tmp_table_size or fix queries creating large temp tables
Variables to adjust:
    join_buffer_size (> 256K, or always use indexes for joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    innodb_buffer_pool_size (>= 12G if possible)

Status tags to read:

[OK] — counter is healthy. Ignore.
[--] — informational. Ignore unless surprising.
[!!] — flagged. Investigate, but don't blindly apply the fix.

The script's bias is conservative-on-symptoms, generous-on-resources. That is, it will flag any sign of disk-spill, lock contention, or saturation — even mild — but its suggested values often assume you have headroom you don't. The "Maximum possible memory usage" line is the canonical example: it's the sum of per-thread buffers × max_connections plus the global buffers, which is a worst case that rarely materialises. If it exceeds RAM, that is a planning warning, not necessarily a misconfiguration.

Findings that almost always justify a change

These are the recommendations where, in practice, the fix is correct on a busy server:

InnoDB buffer pool smaller than the working set. innodb_buffer_pool_size is the single most important variable on any InnoDB-heavy server. Aim for ~70–75% of RAM on a dedicated DB host (smaller on a shared box). If the buffer pool is smaller than your hot data, reads hit disk and everything suffers.
Slow query log disabled or threshold too high. Set slow_query_log = ON, long_query_time = 1 (one second), and check the log. Most "MySQL is slow" problems are application queries, not server config.
tmp_table_size / max_heap_table_size mismatch causing on-disk temp tables. These two should be set to the same value. If many temp tables are spilling to disk because of BLOB/TEXT columns in the result, the variables won't help — fix the query.
Joins without indexes. Not a config problem — add indexes. MySQLTuner pointing this out is doing you a favour; bumping join_buffer_size is treating a symptom.
MyISAM tables in a 2026 schema. Convert to InnoDB. ALTER TABLE … ENGINE=InnoDB; (offline, ideally during a maintenance window for large tables).
Security findings. Empty passwords, anonymous users, test database remaining — fix immediately, these are real exposures.

Findings that need scepticism

These flags often look alarming but are not necessarily problems:

"Maximum possible memory usage > installed RAM." This is a worst-case sum, not actual usage. Real RSS is what ps or SHOW STATUS LIKE 'Threads_connected' × buffers tells you. If your real usage is fine, leave it alone — but use it as a planning signal.
"Query cache hit rate low." On MySQL 5.x, the query cache is often a bottleneck under concurrent writes; disabling it (query_cache_type = OFF, query_cache_size = 0) is frequently the right move. MySQL 8 removed the query cache entirely, so this section is moot.
"Read buffer / sort buffer too small." Per-thread buffers — raising them multiplies by max_connections. Touch with caution; an index is almost always cheaper than a bigger sort buffer.
"table_open_cache too small." Often a side-effect of an application opening too many tables per request (ORM with eager joins). Raising the cache hides the symptom; fix the access pattern when you can.
"innodb_log_file_size too small." A valid finding, but resizing redo logs in MySQL 5.7 needs a clean shutdown. In MySQL 8, innodb_redo_log_capacity replaces this and is dynamic — apply at runtime.

Applying changes safely

One change at a time. Apply a single recommendation, restart (or SET GLOBAL if dynamic), wait a representative workload window, re-run MySQLTuner, compare.
Dynamic vs static. Many variables can be changed at runtime with SET GLOBAL var = value; — useful for testing without a restart. Make the change permanent by writing it into /etc/my.cnf.d/tuning.cnf afterwards.
Keep changes in version control. Drop tuning into a separate include file (e.g. /etc/my.cnf.d/99-tuning.cnf) instead of editing the distro's main my.cnf. Commit it.

Reload and verify.

sudo systemctl restart mysqld
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Measure. "Did this help?" is answered by query latency and throughput, not by MySQLTuner running again. Use pt-query-digest on the slow log, your APM, or even a simple SHOW GLOBAL STATUS diff before/after.

Cron-ing it

A nightly run that emails the report is a cheap continuous-tuning signal:

sudo tee /etc/cron.daily/mysqltuner >/dev/null <<'EOF'
#!/bin/bash
/usr/bin/mysqltuner --silent --nocheckversion --json \
  > /var/log/mysqltuner.json 2>/dev/null
EOF
sudo chmod +x /etc/cron.daily/mysqltuner

Pipe the JSON into your monitoring stack and alert when new [!!] findings appear or when innodb_buffer_pool / data size drops below a threshold.

What MySQLTuner cannot tell you

Which queries are slow. Use the slow query log + pt-query-digest or Performance Schema.
Whether your schema is the problem. Use EXPLAIN and look at index usage on real queries.
Whether your replicas are behind. Use SHOW REPLICA STATUS (or SHOW SLAVE STATUS on older versions) and Seconds_Behind_Source.
Whether your application is opening too many connections. That's a connection-pool problem at the app layer, not a server tuning one — raising max_connections past a few hundred is usually a band-aid.
Whether the disk is your bottleneck. iostat -x 1, vmstat 1, pidstat -d 1 will tell you that. MySQLTuner cannot.

Troubleshooting

Can't connect to local MySQL server through socket. Pass --host 127.0.0.1 to force TCP, or --socket /path/to/mysql.sock.
Access denied for user 'tuner'@'localhost'. Re-check the GRANT statements. PROCESS is the privilege most often missing.
--dbstat runs forever. On schemas with thousands of tables, the script's information_schema queries are slow. Either accept the wait, or run it on a replica.
Output says "uptime too short". Wait at least 24 hours after a restart before trusting any of the per-counter recommendations.
JSON output is partial. Older packaged versions had buggy JSON serialization — install the upstream single-file script if you depend on machine-readable output.

Summary

MySQLTuner is a fast, deterministic second opinion on a running MySQL/MariaDB instance. The right way to use it is:

Give it at least 24 hours of representative traffic.
Run it with a dedicated read-only user via ~/.my.cnf.
Focus on the small set of high-leverage findings: InnoDB buffer pool, slow log, missing indexes, MyISAM holdouts, security.
Treat per-thread buffer suggestions with suspicion — they multiply by max_connections.
Change one variable at a time, verify with real query latency, and keep your tuning file under version control.

Used like that, it pays for itself within an hour the first time you run it on a server nobody has touched in two years. Used as "apply all variables to adjust", it will make Tuesday morning very memorable.