How to Configure MySQL for High Traffic: Tuning, Caching, and Scaling

Overview

MySQL high traffic problems don’t usually look like “MySQL is broken.” They look like slow page loads, timeout errors, or a PHP application throwing Too many connections at 2am on a Tuesday. The database is almost always the bottleneck before your web server is.

Out of the box, MySQL ships with conservative defaults designed to run on a generic server. Those defaults are fine for a dev environment or a low-traffic site, but they’ll fall apart fast under real load. This guide covers the key configuration changes you should make on any production server handling significant traffic — whether that’s a VPS SSD Hosting instance or a dedicated server.

Most of what’s here applies to MySQL 8.0+ and MariaDB 10.6+. I’ll flag version-specific differences where they matter.

Prerequisites

• Root or sudo SSH access to your server
• MySQL 8.0+ or MariaDB 10.6+ installed
• At least basic familiarity with editing Linux config files (nano or vim)
• A backup of your current /etc/mysql/my.cnf or /etc/my.cnf before making any changes
• Enough free RAM to actually allocate — don’t tune for 4GB of InnoDB buffer pool on a 2GB VPS

Step-by-Step Configuration

Step 1: Locate and Back Up Your MySQL Config File

The main config file location varies by distro and install method. Check these in order:

ls /etc/mysql/my.cnf
ls /etc/my.cnf
ls /etc/mysql/mysql.conf.d/mysqld.cnf

Once you find it, back it up before touching anything:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

📝 Note: On Ubuntu 20.04+ with standard apt installs, the active config is usually split across /etc/mysql/mysql.conf.d/mysqld.cnf. Changes to /etc/mysql/my.cnf alone may be ignored.

Step 2: Tune the InnoDB Buffer Pool

This is the single most impactful change you can make. The InnoDB buffer pool is where MySQL caches table data and indexes in memory. If it’s too small, MySQL hits disk constantly — and disk I/O is where performance dies.

The standard recommendation is to set it to 70-80% of available RAM, but that’s for a dedicated database server. On a shared VPS running a web stack, be more conservative — 40-50% is safer.

Open your config file and add or update these values under [mysqld]:

# For a 4GB VPS running web + DB on the same server
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 2

# For an 8GB dedicated DB server
# innodb_buffer_pool_size = 5G
# innodb_buffer_pool_instances = 4

⚠ Warning: Setting innodb_buffer_pool_size higher than your available RAM will cause the kernel to swap, which is far worse than a small buffer pool. Check your actual free memory with free -h first.

The innodb_buffer_pool_instances value splits the pool into multiple chunks to reduce contention under concurrent writes. Set it to 1 per GB of buffer pool, up to 8.

Step 3: Set Connection Limits and Thread Handling

Each MySQL connection consumes RAM. Under high traffic, connection storms are common — especially with PHP applications using persistent connections incorrectly.

max_connections = 300
thread_cache_size = 50
wait_timeout = 60
interactive_timeout = 60

Here’s the non-obvious part: wait_timeout is probably more important than max_connections for most traffic spikes. If your app opens connections and doesn’t close them properly, they’ll pile up as “sleeping” connections eating your limit. Setting wait_timeout = 60 kills idle connections after 60 seconds instead of the default 8 hours.

📝 Note: If you’re running a connection pooler like ProxySQL or PgBouncer in front of MySQL, you can keep max_connections lower — the pooler handles the burst from the app side and MySQL only sees pooled connections.

Step 4: Tune the Query Cache (MySQL 5.7) or Use ProxySQL / Application-Level Cache (MySQL 8.0+)

If you’re on MySQL 5.7, the query cache can help read-heavy workloads:

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

⚠ Warning: MySQL 8.0 removed the query cache entirely. It was a global mutex nightmare under concurrent write load — it actually hurt performance on busy servers. Don’t go looking for it in 8.0; it’s gone by design.

On MySQL 8.0+, use application-level caching instead. Redis or Memcached in front of your queries is the right approach. For WordPress specifically, Host & Tech’s managed WordPress hosting includes Redis object caching configured out of the box.

Step 5: Tune InnoDB I/O Settings

innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2 is a trade-off. The default value of 1 is fully ACID-compliant — every transaction flushes to disk. Setting it to 2 flushes to the OS cache every second instead, which significantly improves write throughput at the cost of potentially losing up to 1 second of transactions if the server crashes hard. For most web apps, that’s an acceptable trade.

O_DIRECT bypasses the OS page cache for InnoDB data files, avoiding double-buffering (data cached by both MySQL and the kernel). On Linux with SSDs, this almost always helps.

Step 6: Apply the Changes

sudo systemctl restart mysql
# or on some systems:
sudo systemctl restart mysqld
sudo systemctl restart mariadb

After restarting, verify MySQL came back up cleanly and check the error log if anything looks off:

sudo journalctl -u mysql -n 50
# or check the log directly:
sudo tail -n 50 /var/log/mysql/error.log

Step 7: Validate Your Tuning with MySQLTuner

MySQLTuner is a Perl script that analyses a running MySQL instance and gives specific recommendations. It’s not a replacement for understanding what you’re doing, but it’ll catch obvious problems.

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass yourpassword

Run it after MySQL has been under real load for at least 24 hours — the recommendations are based on runtime statistics and are meaningless on a fresh restart.

Common Issues and Troubleshooting

“Too many connections” error

This means active connections hit your max_connections limit. Check what’s actually connected before blindly raising the limit:

SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

If you see hundreds of “Sleep” threads, the problem is connections not being released — fix your app’s connection handling or lower wait_timeout. If you see active queries piling up, that’s a slow query problem, not a connection limit problem.

MySQL restart fails after editing my.cnf

A typo in the config file will prevent MySQL from starting. Check the error log immediately:

sudo journalctl -u mysql -n 100

MySQL is annoyingly unhelpful with config errors sometimes — it’ll report something like “unknown variable” with the line number. Fix it, or restore your backup: sudo cp /etc/mysql/my.cnf.bak /etc/mysql/my.cnf.

InnoDB buffer pool size change doesn’t seem to have any effect

This usually means MySQL is reading a different config file than the one you edited. Run this to see which files MySQL actually loads:

mysql --help --verbose | grep -A 1 "Default options"

It’ll list every config file MySQL reads, in order. Edit the correct one.

Slow queries persist after tuning

Config tuning won’t fix badly written queries or missing indexes. Enable the slow query log to find the real culprits:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Add those to [mysqld], restart MySQL, then use mysqldumpslow or pt-query-digest from Percona Toolkit to analyse the output. In my experience, 80% of MySQL performance problems are one or two unindexed queries running thousands of times per hour.

mysql_secure_installation fails on Ubuntu/Debian

Some distros configure the root account to use socket authentication by default, which causes mysql_secure_installation to fail with an access denied error even when you type the right password (or no password). This is genuinely confusing the first time you hit it.

Fix it by logging in via socket first and switching the auth plugin:

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongPasswordHere';
FLUSH PRIVILEGES;
EXIT;

Then rerun mysql_secure_installation. Replace YourStrongPasswordHere with an actual strong password — never leave a placeholder in production.

FAQ