MySQLTuner is a Perl script that connects to a MySQL or MariaDB server, reads status and configuration variables, and prints a list of recommendations. The report is useful for a quick check, but part of its advice is calculated from universal formulas that ignore the real load of a specific site. This article breaks down which points of the report are worth trusting and which need a manual check first.
When to run MySQLTuner, and when not to
The server should run under normal load for at least a day, and ideally a week — only then do status variables (Questions, Threads_connected, Created_tmp_disk_tables) reflect the real picture. Right after a MySQL restart most counters are zero or tiny, and the report will give advice built on random numbers.
Running it right after a database migration, a MySQL version change, or on a server where a web server and PHP-FPM also run with limited memory produces a report whose buffer recommendations are calculated without regard for other memory consumers — more on that in the section about advice given for no good reason.
How to get the report
The script runs under a user that has access to SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES; usually that is the MySQL root account or a separate account with only PROCESS and SELECT rights on service tables.
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl --user root --pass PASSWORDThe output is split into two blocks: General recommendations — text-based advice, and Variables to adjust — specific configuration variables with suggested values. Change the second block only after checking the first: advice applied without understanding the cause creates a new problem instead of the old one.
Which recommendations are worth checking by hand
Several points in the report are tied directly to statistics that are worth recalculating from current data rather than taking on faith:
- The ratio of
Created_tmp_disk_tablestoCreated_tmp_tables— a high share of on-disk temporary tables means queries withGROUP BYorORDER BYdo not fit intotmp_table_size, and the advice to raise the limit is worth checking through the slow query log instead of applying it blindly. - The InnoDB buffer pool hit rate (
Innodb_buffer_pool_readsagainstInnodb_buffer_pool_read_requests) — a low value on a fresh server is normal: the cache has not warmed up yet. - The peak connection count (
Max_used_connections) againstmax_connections— advice to lower the limit only makes sense if the peak stays low for several days in a row, not for a single measurement. - The presence of MyISAM tables — advice to convert them to InnoDB is correct almost always, but it is worth checking first whether those tables use an old-format full-text search that behaves differently after conversion.
- The
thread_cache_sizevalue — advice to raise it helps with frequent short connections (PHP without persistent connections), and is pointless with a pool of persistent connections.
The general rule: any advice from the report is worth cross-checking against the plan of specific queries — EXPLAIN helps here, not changing a variable blindly.
Which advice MySQLTuner gives for no good reason
The main source of misleading recommendations is the InnoDB buffer calculation based on the amount of memory the script sees through /proc/meminfo. On a virtual server where Nginx, PHP-FPM, and Redis also run alongside MySQL, all the free memory does not belong to the database alone, and the MySQLTuner formula has no way of knowing that.
| Advice from the report | What the script sees | What to account for |
|---|---|---|
| Raise innodb_buffer_pool_size to 70-80% of RAM | Total server RAM | How much memory PHP-FPM, Nginx, and other services already use |
| Enable query cache | The old query_cache_type variable | In MySQL 8.0 the query cache was removed from the code — the advice applies only to MySQL 5.7 and MariaDB |
| Raise table_open_cache to several thousand | The current number of open tables | The system open file limit (ulimit -n), which needs to be raised at the same time |
| Lower max_connections | A single peak of Max_used_connections | Load during peak hours over several days, not one measurement |
Before changing innodb_buffer_pool_size, it is worth checking the server load plan and the indexes on the tables themselves — an extra buffer will not speed up a query without the right index.
How to check the result after changes
Right after changing a variable in the MySQL configuration, restart the service and watch the error log for the first few minutes: a wrong buffer value most often shows up as a failed start, not as slow performance.
After a day or two, run MySQLTuner again and compare the buffer pool hit ratio and the share of on-disk temporary tables against the numbers before the changes — that is more honest than relying on a single run of the report.
Frequently asked questions
Is it safe to run MySQLTuner on a production server?
Yes, the script itself only reads status variables and changes nothing in the configuration; the risk appears only when an administrator applies the suggested values without checking them. Running the report by itself is safe and creates no load noticeable on a live site.
MySQLTuner advises raising innodb_buffer_pool_size to almost the entire amount of RAM — is that correct?
Only on a dedicated server where nothing besides MySQL runs. On a server with Nginx, PHP-FPM, and other services, such a buffer leaves too little memory for the rest of the processes, and under peak load the system will start swapping, which is slower than any cache miss.
Is it necessary to act on every point marked [--!!] right away?
No, that mark only means high priority inside the script's formula, not a confirmed cause of a problem on a specific server. Each such point is worth cross-checking against statistics over several days and against the slow query log, rather than working through the list from top to bottom.