All articles
TutorialsJun 01, 2026 · 25 min read

MariaDB and MySQL Tuning for a Small VPS

MariaDB and MySQL Tuning for a Small VPS

MySQL and MariaDB both ship with a configuration designed to start on almost anything, including a laptop from 2009. The default innodb_buffer_pool_size is 128 MB. That single number means a database on a 2 GB VPS is using a sixteenth of the RAM it could be, reading pages from disk that should be sitting in memory. The result is a database that feels sluggish on a workload that should fit comfortably in cache.

The good news is that you do not need a DBA to fix this. A handful of well-chosen settings get you most of the way, and the rest is observability. This walkthrough covers what to change, why, how to apply it safely, and how to find the slow queries that no amount of tuning will save you from. MariaDB is a fork of MySQL, so the knobs are almost identical - the few places they differ are flagged as we go.

TL;DR

  • Raise innodb_buffer_pool_size to 60-70% of RAM on a dedicated DB box, or 25-40% if the app shares the machine.
  • Set innodb_flush_log_at_trx_commit = 2 for a big write speedup if you can tolerate losing ~1 second of data on a power loss.
  • Use innodb_flush_method = O_DIRECT and, on NVMe, raise innodb_io_capacity.
  • Keep max_connections modest and the per-connection buffers small - they multiply.
  • Leave the query cache off. Turn on the slow query log and run mysqltuner after a day of real traffic.

Total time: around 30 minutes, plus one restart.

What You Need

  • A VPS running MySQL 8.0 or MariaDB 10.5+ (the values apply to the 10.6 and 10.11 LTS releases and to 11.x too).
  • Root or sudo access on the host.
  • An idea of how much RAM you have. free -h will tell you.
  • To know whether the box is database-only or shares RAM with your application.
  • A recent backup or a snapshot before you change anything. Our encrypted Restic backups guide covers off-site dumps you can roll back to.

We assume Ubuntu 22.04 or 24.04. Only the file paths are distro-specific; the settings themselves are the same everywhere.

The fastest way to take a small database box down is to size the buffer pool plus the per-connection buffers larger than the RAM you actually have. `innodb_buffer_pool_size` is allocated up front; the per-connection buffers are allocated on top of it, once per connection. Add them up before you restart, not after the OOM killer does it for you.

Step 1: Find the Config File

MySQL and MariaDB read a chain of files. Do not edit the packaged ones - drop your own file in so a package upgrade never clobbers your work. The include directory depends on the distro and the engine:

  • Debian and Ubuntu MariaDB: /etc/mysql/mariadb.conf.d/
  • Debian and Ubuntu MySQL: /etc/mysql/mysql.conf.d/
  • RHEL, Rocky, Alma: /etc/my.cnf.d/

If you are not sure what your server reads, ask it for the search order:

mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"

Create a dedicated drop-in. The 99- prefix makes it sort last, so it wins any conflict:

# MariaDB sudo nano /etc/mysql/mariadb.conf.d/99-tuning.cnf # MySQL sudo nano /etc/mysql/mysql.conf.d/99-tuning.cnf

Everything below goes under a [mysqld] header in that file.

Step 2: Know Your Version and Engine

SELECT VERSION();

Then confirm your tables are actually InnoDB. Everything in this guide tunes InnoDB; it does nothing for the old MyISAM engine, which uses key_buffer_size instead.

SELECT table_schema, engine, COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') GROUP BY table_schema, engine;

If you see MyISAM on real application tables, plan a migration to InnoDB (covered in Going Further). InnoDB has been the default since MySQL 5.5, so most databases are already there.

Step 3: The InnoDB Buffer Pool

This is 80% of the win. The buffer pool caches table and index pages in RAM, and the default 128 MB is the first thing to fix.

[mysqld] innodb_buffer_pool_size = 1G

How to size it:

  • Dedicated DB box: 60-70% of RAM. The operating system and the rest of MySQL need the remainder.
  • Shared box (app + DB together): 25-40%. Leave room for PHP or Node, your application, and the kernel page cache.

On a 2 GB VPS running both an app and the database, 512M is a sane start. On a 4 GB database-only box, 2560M (around 62%) is reasonable. One caveat: do not exceed your data size. If the whole database is 400 MB, a 4 GB buffer pool just wastes RAM - size it to the data plus headroom for growth.

You can skip innodb_buffer_pool_instances. Splitting the pool only reduces contention above ~1 GB, and MariaDB 10.5+ ignores the setting entirely. On a small VPS, leave it at the default.

The buffer pool is resizable online (since MySQL 5.7 and MariaDB 10.2), so you can test a value before committing to a restart:

SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1 GiB, in bytes SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Once the resize finishes, write the value into your .cnf so it survives a restart.

Step 4: Redo Log and Flushing

The redo log absorbs writes before they are checkpointed into the tablespace. The default is small, which forces frequent checkpoints and caps write throughput. MariaDB and recent MySQL name this setting differently:

# MariaDB 10.5+ (single redo log) innodb_log_file_size = 512M # MySQL 8.0.30+ innodb_redo_log_capacity = 536870912 # 512 MiB in bytes

A larger redo log means fewer checkpoint flushes and smoother writes; the cost is a slightly longer crash recovery. 256-512 MB is a good range for a small box.

Then the durability dial, the biggest single write speedup here:

innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT
  • innodb_flush_log_at_trx_commit controls how aggressively each commit is persisted. 1 (the default) is full ACID: flush and fsync on every commit. 2 flushes to the OS on every commit but only fsyncs about once a second - far less disk pressure, at the cost of losing up to a second of committed transactions if the whole machine loses power. 0 is similar but loses that second even on a plain mysqld crash. For anything that is not a payment ledger, 2 is the right trade.
  • innodb_flush_method = O_DIRECT stops InnoDB pages being cached twice (once in the buffer pool, once in the OS page cache). On Linux with a normal filesystem this is almost always correct.
`innodb_flush_log_at_trx_commit = 2` is safe against a MySQL crash, but it trades a one-second window against a full power loss or kernel panic. If your data is financial, or you genuinely cannot lose a single committed row, leave it at `1` and find your speed elsewhere.

Step 5: Tell InnoDB It Is on NVMe

The I/O defaults assume a single slow spinning disk. Any modern VPS is on NVMe, so raise the ceiling:

innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_flush_neighbors = 0
  • innodb_io_capacity is the steady-state budget for background flushing. The default 200 suits one rotational disk; NVMe handles thousands.
  • innodb_flush_neighbors = 0 disables the optimisation that flushes adjacent pages together. That batching only helps on rotational disks where seeking is expensive. On flash it just writes pages you did not need to.

Step 6: Connections and Per-Connection Buffers

The default max_connections = 151 is rarely right for a small box. Each connection costs memory before it runs a single query, and several buffers are allocated per connection.

max_connections = 80 thread_cache_size = 16

Then the dangerous ones - keep them small:

sort_buffer_size = 2M join_buffer_size = 2M read_buffer_size = 1M read_rnd_buffer_size = 1M

These are allocated per connection, and sort_buffer_size can be allocated more than once inside a single complex query. Raising them globally to "go faster" is exactly how a box with plenty of idle RAM suddenly OOMs under load. The worst-case memory is roughly:

innodb_buffer_pool_size + max_connections * (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + thread_stack)

If one report genuinely needs a big sort, raise the buffer for that session only, never globally:

SET SESSION sort_buffer_size = 16 * 1024 * 1024;

Step 7: Leave the Query Cache Off

This one is a "do not". The old MySQL query cache was removed entirely in MySQL 8.0. MariaDB still has it, but it is off by default and should stay that way on any write-heavy or concurrent workload - its single global mutex becomes a bottleneck that is usually slower than no cache at all.

# MariaDB only; this is already the default - do not turn it on query_cache_type = 0 query_cache_size = 0

If you want a query cache, put it in your application (Redis, or your framework's cache layer), not in the database.

Step 8: Temp Tables and the Table Cache

Queries that group, sort, or use UNION may build temporary tables. If they outgrow the in-memory limit they spill to disk, which is slow.

tmp_table_size = 64M max_heap_table_size = 64M table_open_cache = 4000 open_files_limit = 8192

tmp_table_size and max_heap_table_size must be raised together - the effective limit is the lower of the two. Watch Created_tmp_disk_tables against Created_tmp_tables to see whether you are spilling to disk.

Step 9: Apply the Changes

Save the drop-in, then restart:

sudo systemctl restart mariadb # MariaDB sudo systemctl restart mysql # MySQL

Many of these settings are dynamic, so you can apply them at runtime to test before persisting. Set the live value, confirm it, then write it into your .cnf:

SET GLOBAL innodb_flush_log_at_trx_commit = 2; SET GLOBAL max_connections = 80;

On MySQL 8 you can make a runtime change durable in one step - it writes to mysqld-auto.cnf:

SET PERSIST innodb_io_capacity = 2000;

MariaDB has no SET PERSIST; use SET GLOBAL for the live value and edit your .cnf for the permanent one. The buffer pool, IO capacity, flush-at-commit, and connection limits are all dynamic. The redo log size and innodb_flush_method are applied on restart.

If a setting refuses to take effect, another file in the include chain is overriding yours. Confirm the value the server is actually using with `SHOW VARIABLES LIKE 'innodb_buffer_pool_size';`, make sure your drop-in sorts last (the `99-` prefix), and check that your settings sit under a `[mysqld]` header and not `[mysql]` or `[client]`.

Step 10: Turn On the Slow Query Log

Tuning settings is half the job. The other half is finding the queries that are slow.

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

long_query_time = 1 logs anything over a second. Turn log_queries_not_using_indexes on only briefly - on a busy server it floods the log. Make sure the file is writable by the server:

sudo touch /var/log/mysql/slow.log sudo chown mysql:mysql /var/log/mysql/slow.log

You can enable it without a restart:

SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1;

After a day of real traffic, find the worst offenders. mysqldumpslow ships with the server:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

For a far better report, Percona's pt-query-digest groups queries by fingerprint and shows where the time actually goes:

sudo apt install -y percona-toolkit sudo pt-query-digest /var/log/mysql/slow.log | less

Step 11: Read a Plan With EXPLAIN

Once you have a suspect query, see how the engine runs it:

EXPLAIN SELECT u.id, u.email, COUNT(o.id) AS orders FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.created_at > NOW() - INTERVAL 30 DAY GROUP BY u.id, u.email ORDER BY orders DESC LIMIT 50;

Sample output (trimmed):

+----+-------+------+---------------+------+-------+----------------------------------+ | id | table | type | possible_keys | key | rows | Extra | +----+-------+------+---------------+------+-------+----------------------------------+ | 1 | u | ALL | NULL | NULL | 48211 | Using where; Using temporary; | | | | | | | | Using filesort | | 1 | o | ref | user_id_idx | ... | 4 | | +----+-------+------+---------------+------+-------+----------------------------------+

Things to look for:

  • type: ALL is a full table scan. On a large table that wants a filter, add the missing index - here, on users.created_at.
  • Using filesort and Using temporary mean the sort or grouping could not use an index. Often a single composite index removes both.
  • A rows estimate wildly off the real count means stale statistics. Run ANALYZE TABLE users;.

For the actual execution rather than an estimate, MySQL 8.0.18+ has EXPLAIN ANALYZE, and MariaDB has the ANALYZE statement plus ANALYZE FORMAT=JSON.

Step 12: Verify With mysqltuner

After the server has been up under real load for a day, run MySQLTuner. It reads your live status counters and flags what is still wrong:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl sudo perl mysqltuner.pl

Pay attention to its "Maximum possible memory usage" line - that is the OOM ceiling from Step 6. If it exceeds your RAM, lower max_connections or the per-connection buffers.

The single most useful health number is the buffer pool hit ratio:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

Divide Innodb_buffer_pool_reads (pages fetched from disk) by Innodb_buffer_pool_read_requests (total page reads). A healthy small box reads from disk well under 1% of the time. If that ratio stays high after the cache has warmed, the buffer pool is too small for your working set.

Troubleshooting

MySQL will not start after the change. Almost always the buffer pool plus buffers exceed RAM, or there is a typo in the .cnf. Read the error log with sudo journalctl -u mariadb -n 50 (or -u mysql). Comment out your drop-in, start the server, and re-add settings one block at a time.

The OOM killer is killing mysqld under load. Total memory is over budget. Lower innodb_buffer_pool_size, drop max_connections, and shrink the per-connection buffers, then re-run mysqltuner's memory line.

Changes seem ignored. Another file in the include chain overrides yours. Confirm the effective value with SHOW VARIABLES LIKE '...';, make sure your drop-in sorts last, and check it sits under [mysqld].

The first queries after a restart are slow, then fast. Cold buffer pool. It refills as pages are read. To warm it automatically, keep innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup on (both default to ON in modern versions).

Too many connections errors. The app is opening more connections than max_connections allows. Either it is leaking connections (fix the pool), or raise the limit - but mind the memory math. A connection pooler is the real fix.

Going Further

  • Put a connection pooler (ProxySQL, or your framework's persistent pool) in front of the database so a small max_connections can serve a busy app without exhausting RAM. This often beats any single .cnf tweak.
  • Migrate leftover MyISAM tables to InnoDB with ALTER TABLE mytable ENGINE=InnoDB;. You get row-level locking, crash recovery, and the buffer pool you just sized.
  • Dashboard the metrics so you spot regressions before users do. Our guide to Grafana and Prometheus on a VPS plus mysqld_exporter gives you buffer pool, connection, and slow-query graphs.
  • On Postgres instead? Same exercise, different knobs - see PostgreSQL Tuning for a Small VPS.
  • Once the box is tuned, make sure it is also locked down. Our SSH hardening with Fail2Ban walkthrough closes the front door.

A well-tuned MariaDB on a $5 NVMe VPS will comfortably outrun a default install on far bigger hardware. The buffer pool alone gets you most of the way; the slow query log gets you the rest.

Need a fast box to run MariaDB or MySQL on? Our Linux VPS plans ship with NVMe storage, generous RAM, and snapshots so you can resize the buffer pool without fear. See the options.