MySQL and MariaDB Performance Tuning for Low-Memory VPS: A 1-2 GB RAM Configuration Walkthrough

Running MySQL or MariaDB on a 1-2 GB VPS is entirely practical, but the default configuration is written for a machine with far more RAM than you have. Left alone, the server will happily size the InnoDB buffer pool at 128 MB, allocate per-connection buffers that multiply into hundreds of megabytes, and get your database killed by the OOM killer under a modest connection spike. This walkthrough builds a configuration that fits a small VPS, measures it, and explains every number.

This is the companion to the broader database tuning material in our VPS performance tuning guides. The principles are the same: know your budget, size the big allocations first, and verify with real metrics rather than guesses.

Calculate the Memory Budget Before Touching Config

Start with the total RAM and subtract everything that is not the database server. On a 1 GB box running a web stack, the OS, the web server and PHP-FPM may consume 400-600 MB. That leaves 400-600 MB for MySQL. This is the number you must not exceed, and the buffer pool is where most of it should go.

free -m
ps_mem 2>/dev/null || for p in $(pgrep -f 'httpd|php-fpm|nginx|mysqld|mariadbd'); do echo -n "$p "; awk '/VmRSS/{print $2/1024" MB"}' /proc/$p/status; done

Write the budget down. If your database has 300 MB of hot data and you can spare 400 MB, an InnoDB buffer pool of 256-320 MB is right. Oversizing the pool to 700 MB on a 1 GB VPS just moves the OOM kill from the database to a random victim later.

Size InnoDB Buffer Pool and Log Files

The buffer pool is the single most important setting. It caches data and index pages. MyISAM also has a key buffer, but you should be on InnoDB for anything transactional, so keep the MyISAM key buffer small and let InnoDB take the memory.

[mysqld]
innodb_buffer_pool_size = 320M
innodb_buffer_pool_instances = 1
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size: 40-50% of the memory you budgeted for MySQL
• innodb_buffer_pool_instances: keep at 1 on small VPS to avoid overhead
• innodb_log_file_size: larger reduces checkpoint churn; 128M is a good small-box value
• innodb_flush_log_at_trx_commit = 2: trades a one-second durability window for far fewer fsyncs
• innodb_flush_method = O_DIRECT: avoids double buffering through the page cache

That last setting matters more than people expect on a small VPS with limited RAM. Without O_DIRECT, InnoDB pages can live in both the buffer pool and the OS page cache, silently halving your effective cache and pushing you toward swap.

Cap Per-Connection Buffers — They Multiply

Memory that looks harmless per connection explodes across hundreds of them. Every active session allocates sort buffers, join buffers and read buffers. The max_connections limit times those per-thread buffers is your worst-case RAM, and it is almost always larger than the buffer pool.

sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 128K
tmp_table_size = 16M
max_heap_table_size = 16M
max_connections = 50

On a 1 GB VPS, 50 connections times roughly 2 MB of worst-case per-thread buffers is about 100 MB, which fits alongside the pool. Do not set max_connections to 500 because you read a tutorial for an 8 GB server. Use a connection pooler in front, or set thread_cache_size to a modest value and let connections queue.

Watch the actual usage rather than trusting the theoretical worst case: SHOW STATUS LIKE 'Threads_connected' and SHOW STATUS LIKE 'Threads_running'. If you never exceed twenty concurrent connections, the theoretical 50-connection ceiling gives you generous headroom.

Reduce Temporary Table and Sort Pressure

Small tmp_table_size is a common cause of mysterious disk usage: when an in-memory temporary table grows beyond the limit, MySQL spills it to disk, which then needs a file sort. On a VPS with fast NVMe this is survivable, but it still multiplies I/O. Raise the in-memory limits modestly and fix the queries that create giant temp tables in the first place.

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW FULL PROCESSLIST;

If Created_tmp_disk_tables is a large fraction of Created_tmp_tables, your queries are sorting large result sets. Index the ORDER BY and GROUP BY columns, and check with EXPLAIN before adding more memory. On a small VPS, query fixes are almost always cheaper than RAM.

Turn On the Slow Query Log and Measure

Tuning without measurement is superstition. Enable the slow query log with a low threshold, let it run for a day, and read the top offenders. This is the same evidence-based approach we recommend throughout our VPS tuning guides.

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

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

Leave log_queries_not_using_indexes off in production — it floods the log on busy apps. Once you have the top ten slow queries, index or rewrite them, then re-measure. Expect the buffer pool hit ratio to rise and disk reads to fall.

Watch the OOM Killer and Set a Safety Net

Even a well-tuned small database can spike. Protect the system by giving the database process a lower OOM score adjustment and by monitoring memory pressure, so you see trouble before the kernel acts.

• systemctl edit mariadb and add OOMScoreAdjust=-500 so the kernel prefers other victims
• Monitor /proc/pressure/memory for rising stall time
• Alert on swap-in rate with vmstat 1 before the kernel reaches for the OOM killer
• Keep backups verified so a crash never becomes data loss

Run mysqltuner.pl and tuning-primer.sh as sanity checks, but treat their advice as suggestions, not commands. Their recommendations assume steady load and can push a small VPS toward swap if applied blindly.

A Fitting Configuration Baseline

For a 1 GB VPS with a typical WordPress or small application database, the baseline above — a 320 MB buffer pool, one instance, modest per-connection buffers, 50 connections and a 0.5-second slow query threshold — runs comfortably and leaves the rest of the system room to work. For a 2 GB box, raise the pool to about 768 MB and max_connections to 100, and keep everything else proportional.

The discipline is simple: budget RAM, allocate most of it to InnoDB, cap per-connection memory, and verify with the slow query log and status counters. Ten minutes of sizing beats an afternoon of guessing, and it keeps your small VPS serving traffic instead of swapping.