MySQL Memory Trimming on a 1–2 GB VPS: A Budget You Can Actually Defend

Running MySQL on a 1 GB VPS is entirely possible — the default configuration is just built for a machine with 8 GB or more, and the surplus is spent on buffers that a small instance will never use efficiently. This article is a budget exercise: enumerate what MySQL allocates, cut what cannot pay for itself at this scale, and verify the result with numbers. Before any of it, though, confirm you are actually memory-constrained rather than I/O-constrained; the two present identically from the outside.

Where the memory goes

Component	Default (MySQL 8.0)	Per-connection?	Sensible 1 GB value
innodb_buffer_pool_size	128 MB	No	256–384 MB
innodb_log_buffer_size	16 MB	No	8–16 MB
key_buffer_size	8 MB (deprecated MyISAM)	No	16 MB
tmp_table_size / max_heap_table_size	16 MB each	No (per temp table)	16–32 MB, kept equal
sort_buffer_size	256 KB	Yes	256 KB–512 KB
join_buffer_size	256 KB	Yes	256 KB
read_buffer_size / read_rnd_buffer_size	128 KB / 256 KB	Yes	128 KB / 256 KB
binlog_cache_size	32 KB	Yes	32 KB
Table definition cache	~400 entries	No	Largely handles it
Performance Schema	Enabled, ~200 MB+	No	Disable on 1 GB

The per-connection column is the one that surprises people. MySQL does not allocate those buffers per connection continuously — it allocates them on demand for the duration of a query — but the theoretical worst case is a sum across all connections, and that is what determines whether you OOM. Twenty connections multiplied by several megabytes of per-query buffers is how a “tuned” server dies under load.

Step 1: measure what you have before changing anything

-- Current settings that matter
SELECT @@innodb_buffer_pool_size/1024/1024 AS bp_mb,
       @@innodb_log_buffer_size/1024/1024  AS log_buf_mb,
       @@sort_buffer_size/1024/1024        AS sort_mb,
       @@join_buffer_size/1024/1024        AS join_mb,
       @@tmp_table_size/1024/1024          AS tmp_mb,
       @@max_connections                   AS max_conn;

-- Actual peak memory demanded by MySQL
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

-- Who is holding memory, by component
SELECT event_name, current_alloc/1024/1024 AS mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE current_alloc > 10*1024*1024
ORDER BY current_alloc DESC;

And from the shell, observe the real residency:

ps -o rss= -C mysqld | awk '{ printf "mysqld RSS: %.0f MB\n", $1/1024 }'
# Sample over time to catch growth that indicates a leak vs steady state
while true; do
  printf "%s  " "$(date +%H:%M:%S)"
  ps -o rss= -C mysqld | awk '{ printf "%.0f MB\n", $1/1024 }'
  sleep 30
done

Step 2: apply a defensible budget

Here is the arithmetic for a 1 GB VPS that also runs nginx and PHP-FPM. Write it down; the point is to be able to explain why each number is what it is.

# 1 GB VPS budget
OS + systemd + sshd + cron ........  180 MB
nginx ..............................   60 MB
PHP-FPM (8 children x 40 MB) ......  320 MB
Redis (optional, small) ...........   80 MB
MySQL .............................  310 MB   <-- the share we will defend
--------------------------------------------
headroom (unallocated) ............   50 MB

# Inside the 310 MB MySQL share:
innodb_buffer_pool_size ...........  192 MB   (60%)
innodb_log_buffer_size ............    8 MB
performance_schema ................  OFF
tmp_table_size/max_heap_table .....   16 MB   (equal values)
key_buffer_size ...................    8 MB
per-connection buffers ............  ~40 KB per query, worst case capped
max_connections ...................   30

Note that the buffer pool is deliberately not the largest possible number. On a 1 GB instance the buffer pool is competing with the OS page cache for the same physical RAM, and InnoDB reads go through both. Oversizing the pool past roughly 60% of the MySQL share usually produces no hit-rate improvement while increasing the chance that a checkpoint stall coincides with high write load. Sizing it precisely is arithmetic, and the InnoDB buffer pool sizing walkthrough shows the per-table calculation for the specific case of a 1 GB VPS.

Step 3: the actual configuration

[mysqld]
# --- Memory ---
innodb_buffer_pool_size     = 192M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size      = 8M
key_buffer_size             = 8M

# --- Temp tables: keep the pair equal or MySQL picks the smaller ---
tmp_table_size              = 16M
max_heap_table_size         = 16M
internal_tmp_mem_storage_engine = MEMORY

# --- Per-connection buffers: conservative, not generous ---
sort_buffer_size            = 256K
join_buffer_size            = 256K
read_buffer_size            = 128K
read_rnd_buffer_size        = 256K

# --- Connections: bound them, then let a pooler handle the rest ---
max_connections             = 30
thread_cache_size           = 8

# --- Disable what a 1 GB instance cannot afford ---
performance_schema          = OFF
innodb_stats_persistent     = ON
table_open_cache            = 400
table_definition_cache      = 400

# --- I/O behaviour appropriate to shared storage ---
innodb_io_capacity          = 200
innodb_io_capacity_max      = 400
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit = 2   # 1 extra second of loss window; big fsync saving

Three notes on the risky-looking lines. performance_schema = OFF saves a substantial amount of memory (commonly 150–250 MB in MySQL 8.0) but you lose the instrumentation used in Step 1 — do the measurement first, then disable it. innodb_flush_log_at_trx_commit = 2 means a crash can lose up to one second of committed transactions; for a web application that is usually acceptable and it reduces fsync pressure significantly. Do not set it to 2 for anything with a financial or inventory consistency requirement.

If you need more concurrency than 30 connections can provide, do not raise max_connections — put a connection pooler in front. A pooler multiplexes hundreds of application connections onto a small number of database connections, which is exactly the right architecture when the database’s memory is the constraint. PgBouncer’s equivalent for MySQL is ProxySQL; the pooling argument is covered in our connection pooling article and translates directly.

Step 4: verify the reduction is real

# Restart, then confirm RSS settled lower
systemctl restart mysql
sleep 30
ps -o rss= -C mysqld | awk '{ printf "post-tune RSS: %.0f MB\n", $1/1024 }'

# Confirm the buffer pool is being used, not wasted
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A2 'BUFFER POOL AND MEMORY'
mysql -e "SELECT ROUND(1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
  WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') /
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
  WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'), 4) AS hit_ratio;"

# Confirm nothing is spilling to disk temp tables excessively
mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';"

Interpret the results:

• Buffer pool hit ratio above 0.99 — the pool is adequate. Do not increase it; that memory is better spent on PHP or the OS page cache.
• Hit ratio between 0.95 and 0.99 with low disk I/O — usually fine. Chasing the last fraction of a percent costs far more memory than it returns.
• Hit ratio below 0.95 — either the working set genuinely does not fit, or a query is scanning the whole table repeatedly. Check the slow log before adding memory; a missing index looks exactly like an undersized pool.

Step 5: guard against the OOM killer

Even with a good budget, a spike in connections or a runaway query can push the system over. Give MySQL a nudge toward being killed rather than the kernel or your database being the victim:

mkdir -p /etc/systemd/system/mysql.service.d
cat > /etc/systemd/system/mysql.service.d/oom.conf <<'EOF'
[Service]
OOMScoreAdjust=-500
EOF
systemctl daemon-reload && systemctl restart mysql

# And add a monitored swap file so pressure surfaces as latency, not death
fallocate -l 1G /swapfile && chmod 600 /swapfile
mkswap /swapfile && swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab

Note that OOMScoreAdjust=-500 makes MySQL less likely to be killed — negative values lower the score, positive values raise it. The intent is to sacrifice a PHP worker before the database. Verify with cat /proc/$(pgrep -x mysqld)/oom_score_adj.

When trimming stops being the answer

There is a floor below which tuning MySQL is just removing capability. If the buffer pool is at 192 MB and the hit ratio is 0.92, the dataset is bigger than the machine. The options at that point are: reduce the dataset (archive old rows — usually the cheapest and most effective intervention), move the database to its own instance, or move to a plan with more RAM. Compare the cost of the latter against the alternatives honestly, and remember that a database instance needs predictable I/O as much as it needs memory, which is why the VPS versus dedicated server decision is most finely balanced for database hosts.

If you prefer to keep the budget above but stop babysitting it, the practical alternative is a provider whose plan ships with a working low-memory default. InterServer’s VPS plans include memory allocations you can map onto the budget above, and their self-managed model means root access to apply it. If you would rather not own MySQL tuning at all, Cloudways’ managed database stack handles the buffer configuration for you.

Final note on the order of operations: trim the buffer pool only after you have confirmed the hit ratio is acceptable, disable Performance Schema only after you have collected your measurements, and always restart with a monitoring loop running so you can see RSS settle. A memory edit made blind is indistinguishable from a memory leak.