Yesterday a teammate messaged me: “MySQL server is running slow, CPU at 80%, do you know where to start optimizing?” — A familiar question I’ve run into more than a few times. After trying various tools, I keep coming back to MySQLTuner because it’s fast, cuts straight to the point, and doesn’t require deep DBA experience.
The Real Problem: MySQL Defaults Aren’t Built for Production
I remember the first time I hit this situation clearly: once the users table crossed 10 million rows, slow queries started appearing constantly and server load climbed high even though the hardware still had plenty of headroom. I had to optimize indexes, but at the same time I realized that MySQL’s default configuration is completely unsuited for large data volumes.
The default my.cnf is designed by MySQL to run on any machine — including ones with only 512MB of RAM. Your server has 16GB of RAM but MySQL is only allowed to use 128MB for the InnoDB buffer pool? That’s an enormous waste of resources.
Instead of reading through documentation and manually calculating every parameter, MySQLTuner does that hard work for you.
What Is MySQLTuner and How Does It Work?
At its core, MySQLTuner is an open-source Perl script. It connects directly to a running MySQL instance, reads actual runtime statistics, and returns a list of parameters that have issues — along with the specific values you should set.
What keeps me coming back to it:
- No complex packages to install — Perl is available on most Linux systems out of the box
- Runs directly against a live server, analyzing real uptime data
- Color-coded output:
[OK]in green means good,[!!]in red means needs attention - Gives you specific values to set, not just vague advice like “needs to be increased”
It’s not a replacement for a professional DBA. But 10–15 minutes with MySQLTuner will typically uncover the 2–3 biggest bottlenecks that you can fix the same day.
Installing and Running MySQLTuner
Option 1: Download the Script Directly from GitHub
# Download MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# Make it executable
chmod +x mysqltuner.pl
# Run with MySQL credentials
perl mysqltuner.pl --user root --pass yourpasswordOption 2: Install via Package Manager (Ubuntu/Debian)
sudo apt install mysqltuner -y
# Run directly
mysqltunerFor more detailed output, add the --verbose flag:
perl mysqltuner.pl --user root --pass yourpassword --verboseImportant note: MySQLTuner analyzes data from real uptime statistics. If MySQL was just restarted, the results won’t be accurate because there isn’t enough data yet. It’s best to run it after the server has been running under real load for at least 24–48 hours.
Understanding MySQLTuner Output
General Statistics Section
-------- General Statistics ------------------------------------------
[OK] Currently running supported MySQL version 8.0.32
[OK] Operating on 64-bit architecture
[!!] Uptime = 2d 3h 14m (should be > 7 days for accurate data)[OK] lines in green are fine. [!!] lines in red need attention. If uptime is under 7 days, treat the results as a rough reference only.
Performance Metrics Section
-------- Performance Metrics -----------------------------------------
[--] Reads / Writes: 84% / 16%
[!!] Thread cache is disabled
[!!] Table cache hit rate: 11% (400 open / 3K opened)
[OK] Temporary tables created on disk: 12% (2K on disk / 20K total)
[OK] Open file limit used: 0% (36/65K)This is the most important section. Looking at the example above, two problems stand out immediately:
- Thread cache is disabled — every new connection will spin up a new thread, which is very resource-intensive when there are many short-lived connections
- Table cache hit rate is only 11% — MySQL has to constantly open and close file descriptors because
table_open_cacheis set too low
Recommendations Section — Read This Most Carefully
-------- Recommendations ---------------------------------------------
General recommendations:
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting point
Variables to adjust:
thread_cache_size (> 0)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 5G) if possible.
innodb_log_file_size should be (= 512M)Specific numbers are always provided — this is the section you’ll copy directly into my.cnf.
Applying the Optimization Recommendations to Your Configuration
Before changing anything, I always back up the config first:
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf \
/etc/mysql/mysql.conf.d/mysqld.cnf.bak.$(date +%Y%m%d)
echo "Backup complete, starting edits..."Optimizing InnoDB Buffer Pool — The Most Important Setting
[mysqld]
# Set to around 70–80% of RAM if MySQL is the primary application on the server
# 16GB RAM server → set to 12G
innodb_buffer_pool_size = 12G
# If buffer pool > 1G, use multiple instances to reduce contention
innodb_buffer_pool_instances = 4
# Increase log file size to reduce I/O checkpoints
innodb_log_file_size = 512MOptimizing Connections and Cache
[mysqld]
# Thread cache — reuse threads instead of creating a new one per connection
thread_cache_size = 16
# Increase table cache to reduce constant file open/close operations
table_open_cache = 2000
table_definition_cache = 1400
# In-memory temp table size — reduces disk I/O during sort/group operations
tmp_table_size = 128M
max_heap_table_size = 128MAfter saving the file, restart MySQL and verify the service comes back up:
sudo systemctl restart mysql
sudo systemctl status mysql
# Verify the variables have been applied
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"Wait at least 48 hours to collect sufficient statistical data, then run MySQLTuner again to see how much the [!!] count has dropped.
A Few Things to Keep in Mind
- Don’t apply blindly: MySQLTuner makes suggestions based on current statistics. If traffic is unusual or the server was recently restarted, the results may not reflect your actual workload.
- Change things incrementally: Don’t adjust 10 parameters at once. Prioritize the InnoDB buffer pool first, test for a few days, then move on to the rest.
- Monitor after changes: Use
SHOW STATUS LIKE 'Innodb_buffer_pool%'to check real-world effectiveness — pay particular attention toInnodb_buffer_pool_reads(lower is better). - innodb_buffer_pool_size is the single most impactful setting: If you can only change one thing, make it this one. The majority of MySQL performance comes from keeping data in RAM rather than reading it from disk.
Conclusion
The workflow I typically follow: run MySQLTuner → note down every [!!] line → look up what each one means → apply changes in priority order (InnoDB buffer pool first, then connection settings) → wait 48 hours → run it again to verify progress.
MySQLTuner isn’t a cure-all — once your config is well-tuned but slow queries persist, the next step is diving into individual SQL statements with EXPLAIN and query profiling. But getting the configuration right is the essential foundation; without it, query optimization will only take you halfway.
