How to Tune MySQL Configuration for Low-Memory Servers

Why MySQL Struggles on Low-RAM Servers

MySQL ships with a default configuration sized for dedicated database servers with several gigabytes of RAM. On a 512 MB or 1 GB VPS running a web stack — PHP-FPM, Nginx, and MySQL all sharing the same memory — those defaults cause the kernel's OOM killer to terminate MySQL processes under moderate load. The symptoms are abrupt: the site returns a database connection error, the MySQL service is down, and journalctl -xe shows an out-of-memory kill.

The core problem is that MySQL allocates memory in two broad categories: global buffers shared across all connections, and per-connection buffers allocated for each active session. On a stock installation, the InnoDB buffer pool alone defaults to 128 MB, and if you add per-connection buffers for 150 simultaneous connections, the total resident memory easily exceeds 600 MB before the OS has allocated anything for PHP or Nginx. Understanding which variables control these two categories is the first step toward a stable low-memory configuration.

Essential my.cnf Settings for 512 MB and 1 GB VPS

The fastest path to a stable low-memory MySQL is a targeted override file that reduces global buffers and per-connection allocations simultaneously. Rather than editing the main configuration file directly, create a drop-in override so you can diff your changes at a glance and roll back cleanly.

Open a new override file:

Paste the following block, then adjust the values based on the RAM tier table below:

Each group of settings has a distinct role. Global buffers are allocated once when MySQL starts and stay resident until the process exits. Per-connection buffers are allocated per active query — so 50 connections each with a 256 KB sort_buffer_size consume up to 12.5 MB only when those sort operations are active, not continuously.

InnoDB Buffer Pool: The Single Biggest Lever

innodb_buffer_pool_size is the most impactful variable in the entire configuration file. It controls how much data and index pages InnoDB holds in memory before going to disk. On a dedicated database server you would target 70–80% of total RAM. On a shared VPS running a full web stack, the realistic ceiling is 25–35% of total RAM, leaving headroom for the OS page cache, PHP-FPM workers, and Nginx.

A practical formula for shared VPS servers:

On MySQL 8.0 you can also split the buffer pool into multiple instances with innodb_buffer_pool_instances. For total pool sizes below 1 GB, leave this at the default of 1 — splitting a small pool adds locking overhead without benefit.

The innodb_flush_log_at_trx_commit = 2 setting trades a small window of potential data loss (up to one second of committed transactions on a hard crash) for a significant reduction in I/O pressure. For most WordPress and application workloads this is an acceptable trade. If you are running financial or e-commerce data where every transaction must be durable even on a power cut, keep this at 1.

Setting innodb_flush_method = O_DIRECT bypasses the OS page cache for InnoDB data files, which prevents the buffer pool data from being double-buffered in both InnoDB's own cache and the OS page cache. On a low-memory server this reclaims meaningful RAM that would otherwise be silently consumed.

Connection and Thread Limits: Stop Over-Allocating

MySQL's default max_connections = 151 sounds modest, but each connection can hold several per-connection buffers simultaneously. On a 512 MB VPS with aggressive per-connection defaults, 151 simultaneous connections could theoretically require over 300 MB just for thread-level memory before any query work begins.

For a single-site VPS running WordPress with PHP-FPM, a max_connections of 25–50 is realistic and safe. PHP-FPM typically opens a persistent connection per worker, so if you have 10 PHP-FPM workers configured, MySQL will never see more than 10 simultaneous connections from the web application. The remaining headroom covers CLI tools, monitoring agents, and occasional admin connections.

Set max_connections to roughly double the Max_used_connections value observed after 24 hours of normal traffic. That headroom handles traffic spikes while preventing runaway memory consumption.

The thread_cache_size variable controls how many threads MySQL keeps in a cache pool after a connection closes, so they can be reused by incoming connections without the overhead of spawning a new OS thread. On a low-traffic server a cache of 8 is generous. On high-traffic servers you would check Threads_created and increase the cache if this grows rapidly after restart.

Slow Query Log: Find the Queries Burning Your RAM

Reducing buffer sizes helps, but the deeper fix is eliminating queries that require large in-memory sort or join operations in the first place. The slow query log surfaces exactly those queries. Enable it in your override file:

After a few hours of traffic, summarise the worst offenders with pt-query-digest from the Percona Toolkit, or use the built-in mysqldumpslow:

Queries logged as not using indexes are the highest-value targets. A full table scan on a 500k-row table forces MySQL to read every row into the sort buffer, rapidly exhausting available memory. Adding the right index converts that operation into a tiny key lookup that fits comfortably in the buffer pool.

For WordPress installations, the most common offenders are wp_options autoloaded rows, unindexed post meta queries from plugins, and WooCommerce order meta queries. Addressing those three categories alone often reduces peak MySQL memory usage by 20–30% without touching a single configuration variable.

Applying Changes and Verifying the Result

After editing the configuration file, validate syntax before restarting to avoid a failed restart that takes your database offline:

Give the server 10–15 minutes under live traffic, then run free -m again. You want to see at least 150–200 MB of free plus cached memory on a 1 GB VPS. If the available column is below 50 MB consistently, reduce either innodb_buffer_pool_size or the number of PHP-FPM workers.

If you manage multiple servers through CloudStick, the server metrics panel shows per-server RAM usage in real time so you can spot a server trending toward OOM before the kill happens — no separate monitoring stack required. Once your baseline is stable, you can create a CloudStick cron job to dump SHOW ENGINE INNODB STATUS to a log file nightly and track buffer pool hit ratios over time.

The buffer pool hit ratio is the most important long-term health metric. Query it directly:

A hit ratio above 95% means the buffer pool is large enough to serve the working data set from RAM. A ratio below 90% indicates the pool is too small and MySQL is making frequent disk reads — at that point, consider upgrading to a 2 GB VPS or reducing the total data footprint through table archival or query optimisation before allocating more memory to MySQL.