MySQL/MariaDB Tuning on a VPS: my.cnf Settings That Matter

You've already checked the slow query log, added indexes, and the queries themselves look fine — but the site still crawls under any real traffic, and MySQL is pegged at 100% CPU while PHP sits idle waiting on it. Nine times out of ten on a VPS, that's not a query problem. It's a config problem: MySQL or MariaDB is still running with defaults built for a machine with a fraction of your RAM, and it's thrashing instead of caching.

Symptom: MySQL Eats CPU/RAM Even With "Good" Queries

The pattern is easy to spot once you know what to look for. Run top during a busy period and mysqld is sitting at or near 100% CPU on one core. Memory usage on the box is high but swap is also climbing. Meanwhile SHOW FULL PROCESSLIST; shows connections stacking up in Sending data or, worse, Waiting for table level lock — even though each individual query, run on its own with EXPLAIN, finishes in milliseconds.

This is what happens when the InnoDB buffer pool is too small to hold your working set, so every query is going back to disk instead of memory, and each new connection is spinning up its own per-thread buffers on top of that. On a VPS you provisioned yourself — especially a fresh Ubuntu or AlmaLinux box with the default apt/dnf install — this is almost guaranteed, because the stock config ships defaults meant to work on a machine with 512MB of RAM.

Cause: Defaults Assume the Smallest Possible Server

Check your current buffer pool size:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

On a lot of fresh installs this comes back at 128MB — regardless of whether your VPS has 2GB or 32GB of RAM. That single setting is usually the biggest lever you have, because it controls how much of your actual data (tables and indexes) MySQL can keep in memory instead of reading from disk on every query.

The other common offenders:

• max_connections set too high for your available RAM, so a traffic spike lets MySQL try to allocate memory for 150 connections it can't actually afford, and the OOM killer (or swap) steps in.
• tmp_table_size / max_heap_table_size too small, forcing GROUP BY / ORDER BY / DISTINCT queries with large result sets to spill to disk-based temp tables — you'll see Created_tmp_disk_tables climbing in SHOW GLOBAL STATUS.
• query_cache still enabled on older MySQL 5.7 installs — it was removed in MySQL 8 for a reason; on write-heavy sites (any WordPress site with logged-in users, any WooCommerce store) it causes more lock contention than it saves.
• No slow query log or performance schema enabled, so you're tuning blind.

Fix: Size the Buffer Pool to Your RAM, Not the Defaults

Find your my.cnf first — location varies by distro and install method:

# Debian/Ubuntu (MySQL)
/etc/mysql/mysql.conf.d/mysqld.cnf

# Debian/Ubuntu (MariaDB)
/etc/mysql/mariadb.conf.d/50-server.cnf

# RHEL/AlmaLinux/CentOS
/etc/my.cnf or /etc/my.cnf.d/mariadb-server.cnf

# cPanel/WHM servers
/etc/my.cnf

Back it up before touching anything:

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

Then add or update these under [mysqld]. The exact numbers depend on your total RAM and whether MySQL shares the box with PHP-FPM and Nginx/Apache — a rough rule of thumb is 50-60% of total RAM on a dedicated DB VPS, or 25-30% if the same box also runs your web server and PHP.

Example block for a 4GB VPS running WordPress + MySQL on the same server:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
max_connections = 75
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

A couple of gotchas worth knowing before you restart:

• Changing innodb_log_file_size on older MySQL/MariaDB versions requires the old log files to be removed after a clean shutdown — MySQL 5.7.11+ and MariaDB 10.5+ handle resizing automatically, but check your version first with mysql --version.
• Don't set max_connections higher than your RAM can support. Each connection reserves memory for sort buffers, join buffers, and read buffers even when idle — 150 connections on a 2GB VPS is how you end up talking to the OOM killer.
• If you're on a managed Getwebup VPS plan, open a ticket before changing InnoDB log file size in production — we can apply it during a maintenance window with a clean restart so you don't risk a crash-recovery delay on a live site.

Restart and verify the new values actually took effect:

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Measure Before and After — Don't Just Trust the Numbers

Tuning blind is how people end up with a config that looks better on paper but performs worse in practice. Check the buffer pool hit ratio:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

You want Innodb_buffer_pool_reads (disk reads) low relative to Innodb_buffer_pool_read_requests (total reads, including from memory). A healthy ratio is 99%+ served from memory. If it's still low after resizing the buffer pool, your working set genuinely doesn't fit in RAM yet — that's a signal to either upsize the VPS or trim what's cached (unused plugins, bloated post revisions, orphaned transients in a WordPress wp_options table are common culprits).

Also watch Threads_connected vs max_connections over a few days with:

mysqladmin -u root -p extended-status | grep Threads_connected

If it's consistently near your ceiling, that's your traffic outgrowing the config — a scaling conversation, not a tuning one.

Prevention: Re-Check After Every RAM Upgrade or Migration

The most common way this problem reappears: someone resizes the VPS to more RAM (or migrates to a bigger plan) and never revisits my.cnf. The buffer pool stays at the old size, so the extra RAM just sits unused while the database keeps hitting disk the same way it always did. Make a habit of re-checking innodb_buffer_pool_size every time you resize, and keep the pre-change my.cnf backup around so you can diff what changed if something regresses.

If you'd rather not hand-tune this every time, Getwebup's managed VPS plans include a database tuning pass as part of provisioning and after any resize — it's one less config file to babysit.