MySQL and MariaDB Performance Tuning for VPS Servers

Most MySQL and MariaDB performance gains on a VPS come from three things: giving the InnoDB buffer pool enough memory to hold your frequently used data, finding slow queries with the slow query log, and adding the right indexes. Exotic configuration changes rarely help as much as fixing the few queries that do most of the damage.

This guide is for VPS and dedicated servers where you control the database configuration. Always change one setting at a time and measure.

Step 1: Measure first

• Enable the slow query log to capture queries above a threshold, for example one second:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

• Watch overall server health: memory, swap, CPU and I/O wait. See Linux load average, CPU and memory explained.
• Note your database size and how quickly it grows.

Tools such as mysqldumpslow or pt-query-digest summarise the slow log so you can see which queries cost the most time in total.

Step 2: Size the InnoDB buffer pool

The buffer pool caches table data and indexes in memory. When the frequently read data fits, most reads never touch the disk.

• On a dedicated database server, a common starting point is around 50–70% of RAM.
• On a server that also runs the web server and PHP, it must be smaller, leaving room for PHP workers, the OS and caches.

innodb_buffer_pool_size = 2G

Never set it so high that the server starts swapping; that is far worse than a smaller buffer pool. Check memory usage after the change under real load.

Step 3: Fix slow queries with indexes

For each expensive query from the slow log:

1. Run it with EXPLAIN to see how the database executes it.
2. Look for full table scans (type: ALL) on large tables and high "rows examined" counts.
3. Add an index on the columns used in WHERE, JOIN and ORDER BY clauses, in the right order for the query.
4. Run EXPLAIN again and measure.

Indexes speed up reads but cost space and slow writes a little, so add the ones your queries actually need. Design principles are in database design basics for web applications.

Step 4: Keep connections reasonable

• max_connections should match what your application can actually use. Each connection consumes memory.
• Many idle connections usually mean the application opens connections it does not close or uses persistent connections carelessly.
• Running out of connections shows up as "Too many connections" errors during traffic spikes.

Step 5: Watch per-connection memory

Settings like sort_buffer_size, join_buffer_size and tmp_table_size are allocated per connection or per operation. Raising them globally multiplies memory use and can cause out-of-memory crashes. Leave them at defaults unless a specific query needs more.

Step 6: Clean up the data

• Archive or delete old logs, sessions and audit rows that nobody reads.
• For WordPress, clean autoloaded options and transients; see WordPress database optimization.
• Large tables of temporary data are better kept in Redis or with a retention policy.

Step 7: Keep the server current

Newer MySQL and MariaDB versions bring optimiser and performance improvements. Plan upgrades within supported versions, test on a copy, and back up first.

Worked example: one missing index

The slow query log on a shop's VPS shows this query taking about 1.8 seconds and running hundreds of times an hour:

SELECT * FROM orders WHERE customer_id = 1043 ORDER BY created_at DESC LIMIT 10;

EXPLAIN shows type: ALL and around 900,000 rows examined: a full table scan of every order, followed by a sort. Adding a composite index that matches the filter and the order:

ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

After the change, EXPLAIN shows the index in use and only a handful of rows examined; the query takes a few milliseconds. On a large table, add indexes during a quiet period, because building them takes time and resources; recent MySQL and MariaDB versions can often build indexes without blocking writes, but test on a copy first.

Example memory budget on an 8 GB VPS

Component	Allocation
Operating system and caches	about 1 GB
Web server	a few hundred MB
PHP-FPM (20 workers × ~80 MB)	about 1.6 GB
Redis	256 MB
InnoDB buffer pool	3–4 GB
Headroom	the rest

The exact numbers depend on your workload; the point is to budget memory explicitly so the buffer pool does not compete with PHP and push the server into swap.

Things that rarely help

• Copying a "high-performance my.cnf" from the internet.
• Enabling features you do not use.
• Tuning the database when the application issues hundreds of queries per page; fix the application instead (or add an object cache; see Redis object caching explained).

Frequently Asked Questions

What is a good buffer pool hit rate?

Aim for most reads being served from memory under normal load. More useful than a single ratio is whether disk reads and I/O wait drop after increasing the buffer pool.

Should the database have its own server?

When the database competes heavily with PHP for memory and CPU, moving it to a separate server lets each be sized and tuned independently.

Do I need to restart to change settings?

Some settings are dynamic and can be changed at runtime; others need a restart. Check the documentation for each variable.

Related reading

For general server tuning, see server performance tuning, and for the fundamentals, what is a VPS. Compare the ServerNeed VPS plans.

Sources

• MySQL Reference Manual: Optimization
• MariaDB documentation: InnoDB buffer pool
• MySQL Reference Manual: The Slow Query Log

Featured image: “database schema” by gnizr, licensed under CC BY 2.0.