MySQL Memory Budget on a 1 GB VPS: Measuring Every Buffer Instead of Guessing

Attribute the memory before you tune it. On a 1 GB VPS the question is never “is MySQL using memory” — it is which buffer holds the RSS and whether that buffer buys measurable throughput. Let MySQL’s own accounting answer that, rather than top.

Start with the server’s own numbers

MySQL exposes its allocation decisions through performance schema and status variables. The buffer pool is the single largest line item on most installs, so measure it first — allocated versus actually used:

mysql -e "SELECT FORMAT(sum(current_alloc)/1024/1024,2) AS alloc_mb,
                  FORMAT(sum(current_alloc-scale(1,0))/*1,0)
             FROM sys.memory_global_total;"
mysql -e "SELECT variable_name, variable_value
             FROM performance_schema.global_status
            WHERE variable_name IN ('Innodb_buffer_pool_pages_data',
                                    'Innodb_buffer_pool_pages_free',
                                    'Innodb_buffer_pool_pages_total');"

If pages_free is a large fraction of pages_total, you have over-provisioned the pool and are paying for RAM you never touch. On a 1 GB box that waste is often 150–250 MB — enough to end a swap storm.

The components that actually consume RSS

A MySQL RSS figure is the sum of several independent regions. Knowing each one tells you which to cut:

• InnoDB buffer pool — innodb_buffer_pool_size. Cache of data and index pages. The one knob worth real money.
• Per-connection buffers — sort, join and read buffers are allocated per session on demand. max_connections multiplied by a few buffers is a worst-case figure that rarely materialises, but the ceiling still matters for OOM planning.
• Performance schema tables — performance_schema can quietly consume tens of MB. Disable it if you are not querying it.
• InnoDB log and dictionary caches — smaller, but non-zero on tiny hosts.

Enumerate the per-thread worst case with the maximum-memory view, which multiplies every session-level buffer by the current connection count:

mysql -e "SELECT ( @@read_buffer_size
              + @@read_rnd_buffer_size
              + @@sort_buffer_size
              + @@join_buffer_size
              + @@binlog_cache_size
              + @@tmp_table_size )
            / (1024*1024) AS per_conn_mb,
            @@max_connections AS max_conn;
"

Budgeting for 1 GB, step by step

Leave room for the OS, the page cache the database relies on, and your web tier. A workable split on a 1 GB instance running a small application:

• ~256–384 MB — InnoDB buffer pool (start at 256 MB and grow only if pages_free stays low)
• ~64 MB — InnoDB log buffer + redo logs sized to disk, not RAM
• ~150 MB — worst-case session buffers at a sane max_connections of 30–50
• remainder — OS, page cache, PHP-FPM or your app runtime

Apply the pool change dynamically where the server allows it, then prove the effect rather than trusting the config file:

SET GLOBAL innodb_buffer_pool_size = 268435456;
-- verify
SELECT @@innodb_buffer_pool_size/1024/1024 AS pool_mb;

Note that some versions only apply certain buffer changes after restart, and the value in the running server may differ from the one on disk. Always read it back from the live server.

Watch it under load, not at idle

A budget verified at idle is a guess. Drive representative queries and watch the states change; you can do this password-free with the extended-status counter that counts bytes read from disk versus from the pool:

-- two samples, a few seconds apart, under load
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- read_requests = logical reads, reads = physical disk reads
-- ratio reveals true cache hit rate

A hit rate below roughly 99% on a read-heavy workload means the pool is too small for the working set — but on a 1 GB host you often cannot grow it, so the honest alternative is to shrink the working set (indexes, archiving) rather than the pool. This is the same memory-accounting discipline that separates a stable instance from one that a well-run virtual server can host comfortably from one that thrashes under the first traffic spike.

Guardrails beyond MySQL itself

Set a floor for the OS and let the database fail loudly rather than be killed silently. Tune the kernel’s overcommit and swap behaviour so pressure is visible, and cap the MySQL process group so a runaway query cannot take the whole host down. On constrained instances this combination — a measured budget plus an external cap — is what makes memory behaviour predictable, which is fundamentally the point of choosing premium cloud solutions over an unmanaged slice that gives you no accounting at all.

Measure, budget, verify under load, then cap. The arithmetic is boring and repeatable; the swap storms it prevents are neither.