MySQL Tuning on a 4GB VPS: Buffer Pools, Slow Queries and Swap Death

We took down three client production environments in 2022 before we finally banned default MySQL 8.0 configurations from our 4GB VPS base images.

The failure mode was always identical. A small instance running Linux, Nginx, PHP-FPM, and MySQL would run smoothly for weeks. Then, a minor traffic spike or an unindexed query would execute. Disk IOPS would max out, the system load average would climb from 0.2 to 85.0 within ninety seconds, SSH would become completely unresponsive, and the Linux kernel OOM (Out Of Memory) killer would executioner-style terminate the MySQL daemon.

In low-resource environments, MySQL does not crash quietly. It degrades into swap death—a catastrophic state where the OS spends all its CPU cycles shuffling pages between physical RAM and slow virtual disk swap until the kernel panics or kills the process.

Here is how we tune MySQL on 4GB instances to keep memory tightly bounded, eliminate swap thrashing, and maintain sub-millisecond query performance.

class="text-code-comment"># Check if your MySQL instance was previously murdered by the kernel OOM killer
dmesg -T | grep -i -E class="text-code-string">'oom[-_]killer|killed process'
class="text-code-comment"># Output sample:
class="text-code-comment"># [Out of Memory] Killed process 18423 (mysqld) total-vm:4281904kB, anon-rss:2812400kB

The False Premise of the 80% Buffer Pool Rule

DBA tutorials universally suggest setting innodb_buffer_pool_size to 70% or 80% of total system RAM. On a 128GB bare-metal server, that leaves 25GB+ for the OS and connection buffers, which is plenty. On a 4GB VPS, applying an 80% allocation gives MySQL a 3.2GB buffer pool.

That calculation is dead on arrival.

A 4GB virtual machine does not actually give you 4096MB of usable headroom for databases. The Linux kernel, systemd, journald, SSHD, and metrics agents consume roughly 400MB to 600MB. If you run a local web server or application worker on the same node, deduct another 1GB. Even on a dedicated database node, MySQL's global memory allocation is only one part of the equation.

Total MySQL Max Memory = 
    innodb_buffer_pool_size
  + innodb_log_buffer_size
  + key_buffer_size
  + (max_connections * (
        read_buffer_size 
      + read_rnd_buffer_size 
      + sort_buffer_size 
      + join_buffer_size 
      + thread_stack
    ))

The dangerous term in this equation is the second half: per-thread buffers. Global memory allocated for innodb_buffer_pool_size is allocated on startup. Per-thread memory is allocated on demand when a connection opens and executes a query requiring operations like explicit sorts or unindexed joins.

If you leave max_connections at the default 151 and allow bloated per-thread buffers, your worst-case memory footprint quickly breaks the machine.

| Configuration Parameter | MySQL 8.0 Default | Our 4GB VPS Standard | Purpose | | :--- | :--- | :--- | :--- | | innodb_buffer_pool_size | 128MB (or dynamic) | 2048MB (Dedicated) / 1200MB (Co-located) | In-memory cache for data and indexes | | max_connections | 151 | 40 | Upper bound on concurrent client threads | | sort_buffer_size | 256KB | 256KB | Memory for ORDER BY / GROUP BY per thread | | join_buffer_size | 256KB | 256KB | Memory for plain index scans / full joins | | read_rnd_buffer_size | 262144 (256KB) | 256KB | Memory for sorted key read buffers | | innodb_log_buffer_size | 16MB | 16MB | Transaction log buffer before flush |

To inspect your instance's actual dynamic memory allocation breakdown in real time, run this query against the sys schema:

SELECT 
    event_name,
    current_alloc,
    high_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE class="text-code-string">'memory/innodb/%' 
   OR event_name LIKE class="text-code-string">'memory/sql/%'
ORDER BY current_alloc DESC 
LIMIT 10;

Eliminating Swap Death at the OS Layer

Before touching my.cnf, you must control how the Linux kernel handles memory pressure. By default, Linux uses swap aggressively to preserve file cache space. When MySQL requests memory that exceeds available RAM, the kernel attempts to push infrequently accessed InnoDB buffer pool pages onto swap disk.

Because disk I/O is orders of magnitude slower than RAM, MySQL queries stall waiting for disk reads. Client connections pile up, spawning more threads, demanding more memory, causing more swapping. The system enters a lockup loop.

We apply three kernel-level changes on every 4GB node running MySQL:

class="text-code-comment"># /etc/sysctl.d/99-mysql-vps.conf
class="text-code-comment">
# Reduce aggressive swapping. Default is usually 60.
vm.swappiness = 10
class="text-code-comment">
# Prevent dirty pages from filling up cache and causing long I/O stalls
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
class="text-code-comment">
# Prevent panic on out-of-memory allocations
vm.overcommit_memory = 0

Apply these settings without rebooting:

sysctl --system

vm.swappiness = 10 instructs the kernel to prefer dropping page cache over swapping out process memory, giving MySQL's buffer pool protection against being written to disk.

Next, we configure systemd to kill MySQL immediately rather than letting it drag the entire OS into an unresponsive state. A quick systemd crash and auto-restart takes 2 seconds; swap death takes 20 minutes and manual intervention.

class="text-code-comment"># /etc/systemd/system/mysql.service.d/override.conf
[Service]
class="text-code-comment"># Automatically restart MySQL if it crashes or gets killed by OOM
Restart=always
RestartSec=3s
class="text-code-comment">
# Tell the kernel OOM killer to prefer terminating mysqld over system critical processes
class="text-code-comment"># Values range from -1000 (never kill) to 1000 (always kill first). 
OOMScoreAdjust=500

Slow Queries and Per-Thread Memory Inflation

A common mistake engineers make when tuning slow queries is bumping up sort_buffer_size or join_buffer_size globally to suppress warnings or speed up a legacy report query.

Setting sort_buffer_size = 8M on a 4GB server sounds harmless. However, sort_buffer_size is allocated per thread whenever a connection performs a sort that cannot utilize an index. If 30 web application workers simultaneously fire queries that execute an unindexed sort, MySQL suddenly attempts to allocate 30 * 8MB = 240MB on top of all existing memory.

If a query requires a large sort buffer, fix the schema or the query index structure. Do not increase global thread buffers to compensate for missing composite indexes.

We log every query that executes without using an index or takes longer than 500 milliseconds:

-- Enable slow query logging dynamically to audit bad queries
SET GLOBAL slow_query_log = class="text-code-string">'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = class="text-code-string">'ON';
SET GLOBAL min_examined_row_limit = 100;

To see if bad queries are forcing MySQL to create temporary tables on disk (which spikes disk I/O on cheap VPS storage), monitor the global status counters:

SHOW GLOBAL STATUS LIKE class="text-code-string">'Created_tmp%tables';

If Created_tmp_disk_tables is high relative to Created_tmp_tables, queries are spilling to disk because internal memory limits for temporary tables were breached. On MySQL 8.0, this is controlled by the TempTable storage engine.

class="text-code-comment"># Limit maximum RAM used by the TempTable engine before spilling to disk
internal_tmp_mem_storage_engine = TempTable
temptable_max_ram = 64M
temptable_use_mmap = ON

Setting temptable_max_ram = 64M prevents a single massive query from eating half a gigabyte of RAM to build an in-memory temporary table. If the result set exceeds 64MB, MySQL maps it to a temporary space file on disk, isolating the RAM footprint.

Production-Tested `my.cnf` for a 4GB VPS

Below is our exact, production-hardened configuration for MySQL 8.0 running on a 4GB VPS. This profile assumes a dedicated or semi-dedicated instance where MySQL is the primary workload alongside an application worker or lightweight web server.

Save this file to /etc/mysql/conf.d/vps-4gb.cnf:

[mysqld]
class="text-code-comment"># ==============================================================================
class="text-code-comment"># Connection & Thread Tuning
class="text-code-comment"># ==============================================================================
class="text-code-comment"># Web applications using connection pooling rarely need more than 30-50 connections.
class="text-code-comment"># Lowering this strictly bounds max potential per-thread memory allocations.
max_connections = 40
max_connect_errors = 1000
thread_cache_size = 8
class="text-code-comment">
# Per-Thread Buffer Allocations (Keep these LOW)
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
thread_stack = 256K
class="text-code-comment">
# ==============================================================================
class="text-code-comment"># InnoDB Engine Configuration
class="text-code-comment"># ==============================================================================
class="text-code-comment"># 2GB leaves ~1.5GB to 2GB for OS, application code, and thread buffers.
innodb_buffer_pool_size = 2G
class="text-code-comment">
# Set instances to 1 when buffer pool is <= 1GB, set to 2 for a 2GB pool.
innodb_buffer_pool_instances = 2
class="text-code-comment">
# Transaction log size and buffer memory
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
class="text-code-comment">
# Flushing behavior: 1 = strict ACID (safe), 2 = flush every second (faster, minor loss risk)
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
class="text-code-comment">
# Prevent page locks from starving memory resources
innodb_lock_wait_timeout = 50
class="text-code-comment">
# ==============================================================================
class="text-code-comment"># Memory Limits & Temporary Tables
class="text-code-comment"># ==============================================================================
class="text-code-comment"># Prevents memory starvation from uncontrolled internal temp tables
temptable_max_ram = 64M
max_heap_table_size = 64M
tmp_table_size = 64M
class="text-code-comment">
# Traditional MyISAM key buffer (keep minimal, MySQL 8 system tables use InnoDB)
key_buffer_size = 16M
class="text-code-comment">
# Disable Performance Schema if you need to reclaim an extra ~300MB of RAM.
class="text-code-comment"># CAUTION: Disables sys schema performance monitoring.
performance_schema = OFF
class="text-code-comment">
# ==============================================================================
class="text-code-comment"># Logging and Slow Query Identification
class="text-code-comment"># ==============================================================================
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0

Restart MySQL after writing this config:

systemctl restart mysql

If performance_schema is turned off as shown above, MySQL will immediately drop its memory footprint by 250MB to 400MB on startup. On a 4GB instance where memory is extremely tight, this tradeoff is often worth losing deep tracing metrics.

Validating Memory Boundaries

Once applied, run a quick stress test or monitor the instance over a 24-hour cycle. You can verify total process resident memory directly via ps:

ps -o pid,user,%cpu,%mem,vsz,rss,comm -p $(pgrep mysqld)

Look at the RSS (Resident Set Size) column, which reflects actual physical memory consumed in kilobytes. On our 2GB buffer pool configuration, mysqld should stabilize between 2.1GB and 2.4GB RSS under active load, leaving at least 1.5GB of headroom on your 4GB node for the operating system, shell access, and application components.

The Verdict

Small virtual servers fail not because MySQL is inefficient, but because default configurations expect abundance.

On a 4GB VPS:

• Never set innodb_buffer_pool_size above 2GB.
• Never leave max_connections at default values; cap it based on your application thread pool.
• Never increase sort_buffer_size to fix slow queries—add composite indexes instead.
• Disable performance_schema if you need to reclaim 300MB of memory instantly.
• Set vm.swappiness = 10 and configure systemd to auto-restart MySQL instead of allowing swap death to lock your server.