Capping the MySQL my.cnf Memory Budget on a 1 GB VPS, Setting by Setting

MySQL’s default configuration is written for a machine with several gigabytes of headroom. On a 1 GB VPS that assumption is fatal: the server starts happily, then dies hours later when real connections arrive and the per-connection buffers multiply. This is not a general tuning guide — it is a memory ledger. Every setting below is chosen against a fixed budget, and the arithmetic is shown so you can redo it when your workload changes.

Start with the ledger, not the config file

MySQL memory splits into two kinds. Global buffers are allocated once at startup and never grow. Per-connection buffers are allocated per session, and — this is the part that surprises people — some of them are allocated per query execution, and max_connections is a ceiling on how many can exist at once. Budget the globals first, then divide the remainder by a realistic connection count.

SELECT
  @@innodb_buffer_pool_size/1024/1024      AS buffer_pool_mb,
  @@innodb_log_buffer_size/1024/1024       AS log_buffer_mb,
  @@key_buffer_size/1024/1024              AS key_buffer_mb,
  @@tmp_table_size/1024/1024               AS tmp_table_mb,
  @@max_connections,
  (@@sort_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size
   + @@join_buffer_size + @@binlog_cache_size)/1024/1024 AS per_conn_mb
\G

The most useful derived number is the worst case: globals plus per_conn_mb * max_connections. On a default 8.0 install that lands around 4 GB on a machine with 1 GB. The server is not over-committed — it is running a plan that only works if few users connect at once. Our VPS comparison page shows how much RAM each tier actually guarantees, which is the input to this calculation.

A 1 GB budget you can defend

Assume Nginx and PHP-FPM take 300 MB, the OS and page cache want at least 150 MB, and MySQL gets the rest — about 550 MB. Here is how to spend it.

Setting	Default	1 GB target	Reason
innodb_buffer_pool_size	128M	256M	The only setting that reliably improves reads; 256M at 4–8 instances is the sweet spot
innodb_log_file_size	48M	64M	Larger files reduce checkpoint pressure without much memory cost
innodb_log_buffer_size	16M	8M	Flushed on every commit for transactional workloads
key_buffer_size	8M	16M	Only if MyISAM tables remain; otherwise leave small
tmp_table_size / max_heap_table_size	16M	32M	Must match each other or the smaller wins silently
max_connections	151	30	Divide the remainder by per-connection cost
sort_buffer_size	256K	512K	Per connection, per sort operation
join_buffer_size	256K	256K	Do not raise; fix the query or the index
read_buffer_size	128K	256K	Sequential scan buffer, per connection
performance_schema	ON	OFF	Frees 100–200 MB immediately; turn on only while measuring

At those values the per-connection cost is roughly 1.6 MB, so 30 connections cost about 48 MB in the worst case and the total sits near 430 MB. That leaves real headroom for the page cache, which matters more than people expect: without free memory the kernel cannot cache InnoDB data files, and every read becomes a physical read.

The config file

[mysqld]
innodb_buffer_pool_size          = 256M
innodb_buffer_pool_instances     = 4
innodb_log_file_size             = 64M
innodb_log_buffer_size           = 8M
innodb_flush_method              = O_DIRECT
innodb_flush_log_at_trx_commit   = 2

key_buffer_size                  = 16M
tmp_table_size                   = 32M
max_heap_table_size              = 32M

max_connections                  = 30
thread_cache_size                = 8
sort_buffer_size                 = 512K
join_buffer_size                 = 256K
read_buffer_size                 = 256K
read_rnd_buffer_size             = 256K

performance_schema               = OFF

[mysqldump]
quick
single-transaction

One warning: innodb_flush_log_at_trx_commit = 2 trades durability for speed — you can lose the last second of committed transactions in a crash. It is appropriate when the box is also doing regular backups and the schema can tolerate it. If it cannot, use 1.

Verifying the result, not assuming it

After a restart, give it five minutes of real traffic, then check that resident memory actually settled where you planned.

# Actual RSS of the mysqld process
ps -o rss= -C mysqld | awk '{printf "mysqld RSS: %.0f MB\n", $1/1024}'

# Is it swapping? Any sustained non-zero si/so means the budget is wrong
vmstat 1 5

# Buffer pool efficiency: hit ratio should stay above 99% for OLTP
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

Calculate the hit ratio as 1 - (reads / read_requests). Below 99% the buffer pool is too small for the working set, and at that point the disk is doing work the RAM should have absorbed — but before raising it, check whether the working set is simply larger than 1 GB allows, which is a plan-size question rather than a configuration one. Constant swapping or an OOM kill in dmesg after these changes means the globals are still too generous; a small swapfile is a safety net, not a substitute for the arithmetic above.

When the ledger stops balancing

Once the buffer pool hit ratio is solid and the box is stable, the remaining lever is not a setting — it is more memory. Cutting max_connections is a real tradeoff: it prevents collapse under load, but the 31st concurrent request gets an error rather than a slow answer. That trade is only acceptable for so long. See the full specs and pricing when the arithmetic tells you the workload has outgrown the plan.