A MySQL or MariaDB server that swaps, gets OOM-killed, or refuses new connections is almost always misconfigured for the memory it has \u2014 not short of memory. On a 1 GB or 2 GB VPS you cannot give the database “enough” RAM, so you have to give it the right RAM. This guide walks through exactly where MySQL’s memory goes, which settings to cut, and how to verify the result.<\/p>\n\n\n\n
Everything here targets MySQL 8.0 and MariaDB 10.6+ on Debian or Ubuntu. The numbers assume a small VPS also running Nginx and PHP-FPM, so the database gets roughly half the machine.<\/p>\n\n\n\n
Do not guess. Two queries give you the real picture \u2014 global buffers (allocated once at startup) and per-connection buffers (multiplied by every open connection and potentially by every thread that runs a query):<\/p>\n\n\n\n
mysql -e \"\nSELECT\n @@innodb_buffer_pool_size\/1024\/1024 AS buffer_pool_mb,\n @@key_buffer_size\/1024\/1024 AS key_buffer_mb,\n @@tmp_table_size\/1024\/1024 AS tmp_table_mb,\n @@max_heap_table_size\/1024\/1024 AS max_heap_mb,\n @@max_connections,\n @@sort_buffer_size\/1024\/1024 AS sort_mb,\n @@read_buffer_size\/1024\/1024 AS read_mb,\n @@join_buffer_size\/1024\/1024 AS join_mb;\"<\/code><\/pre>\n\n\n\nEstimated worst-case memory is:<\/p>\n\n\n\n
total = buffer_pool + key_buffer + tmp_table\n + (max_connections * (sort + read + join + binlog_cache + net + thread_stack))<\/code><\/pre>\n\n\n\nOn a default install, max_connections<\/code> of 151 multiplied by even 1 MB of per-connection buffers is 151 MB of potential overhead \u2014 before the buffer pool. That is often what pushes a small VPS into swap.<\/p>\n\n\n\nStep 2: Set the InnoDB Buffer Pool to Fit Your Machine<\/h2>\n\n\n\n
For a VPS where the database shares RAM with the web tier, 256 MB is the right starting point on a 1 GB machine and 512\u2013768 MB on a 2 GB machine:<\/p>\n\n\n\n
# \/etc\/mysql\/mysql.conf.d\/90-small.cnf (MySQL)\n# \/etc\/mysql\/mariadb.conf.d\/90-small.cnf (MariaDB)\n[mysqld]\ninnodb_buffer_pool_size = 256M\ninnodb_buffer_pool_instances = 1\ninnodb_log_buffer_size = 16M\ninnodb_flush_method = O_DIRECT<\/code><\/pre>\n\n\n\ninnodb_buffer_pool_instances = 1<\/code> is deliberate: splitting a small pool creates contention without benefit. O_DIRECT<\/code> avoids double-caching the same pages in the OS page cache and the buffer pool, which on a small machine effectively doubles your usable database cache.<\/p>\n\n\n\nFor a 1 GB VPS, also shrink the redo log so it does not dominate disk and memory during checkpoints:<\/p>\n\n\n\n
innodb_log_file_size = 64M<\/code><\/pre>\n\n\n\nStep 3: Cut the Per-Connection Buffers Aggressively<\/h2>\n\n\n\n
This is where the biggest wins are on small servers, and where most guides go wrong by leaving defaults. Every connection can allocate these buffers, so keep them tiny and let the buffer pool absorb the load:<\/p>\n\n\n\n
max_connections = 50\nthread_cache_size = 16\nsort_buffer_size = 256K\nread_buffer_size = 128K\nread_rnd_buffer_size = 128K\njoin_buffer_size = 128K\nbinlog_cache_size = 32K\nthread_stack = 192K\nnet_buffer_length = 8K<\/code><\/pre>\n\n\n\nThen eliminate the connection churn that makes max_connections<\/code> matter at all:<\/p>\n\n\n\nwait_timeout = 120\ninteractive_timeout = 120<\/code><\/pre>\n\n\n\nIf your application opens a connection per request, install a pooler rather than raising limits \u2014 a single PHP-FPM pool holding 10 persistent connections will serve far more traffic than 100 connections opened and closed continuously.<\/p>\n\n\n\n
Step 4: Stop Temporary Tables From Eating Disk and RAM<\/h2>\n\n\n\n
Small servers usually have both slow disk and little RAM, so disk-based temporary tables are doubly painful. Keep temp tables in memory, but cap them hard:<\/p>\n\n\n\n
tmp_table_size = 32M\nmax_heap_table_size = 32M\ninternal_tmp_mem_storage_engine = MEMORY\n<\/code><\/pre>\n\n\n\nSet the two size values equal \u2014 MySQL uses the smaller of the two, and mismatched values are a common source of confusion. Then watch whether you are still spilling:<\/p>\n\n\n\n
mysql -e \"SHOW GLOBAL STATUS LIKE 'Created_tmp%';\"<\/code><\/pre>\n\n\n\nA high Created_tmp_disk_tables<\/code> relative to Created_tmp_tables<\/code> means queries are sorting or grouping too much data. The fix is an index, not a bigger limit \u2014 raising tmp_table_size<\/code> further just delays the same spill while consuming RAM you do not have.<\/p>\n\n\n\nStep 5: Disable What You Are Not Using<\/h2>\n\n\n\n
Every feature you are not using costs memory. On a small web server:<\/p>\n\n\n\n
performance_schema = OFF\n# MariaDB only:\n# aria_pagecache_buffer_size = 16M\n# if you use only InnoDB:\n# skip-innodb does not exist in 8.0; instead reduce stats:\ninnodb_stats_persistent_sample_pages = 20<\/code><\/pre>\n\n\n\nperformance_schema = OFF<\/code> alone reclaims 100\u2013200 MB on MySQL 8.0, which is a huge fraction of a 1 GB VPS. Re-enable it temporarily if you need to diagnose a lock issue, then turn it back off. If you use MariaDB, shrink the Aria page cache unless you rely on Aria tables.<\/p>\n\n\n\nStep 6: Verify the Server Is Actually Healthy Under Load<\/h2>\n\n\n\n
Restart and confirm the settings took effect:<\/p>\n\n\n\n
sudo systemctl restart mysql\nmysql -e \"SHOW VARIABLES LIKE 'innodb_buffer_pool_size';\"\nfree -m\nmysqladmin status<\/code><\/pre>\n\n\n\nThen generate load and watch for the failure you were trying to prevent \u2014 swapping and OOM kills:<\/p>\n\n\n\n
# In one terminal\nsysbench \/usr\/share\/sysbench\/oltp_read_write.lua --mysql-user=root \\\n --mysql-db=bench --table-size=100000 --threads=4 --time=120 run<\/p>\n\n\n\n# In another\nwatch -n2 'free -m; echo ---; grep -i \"out of memory\\|oom\" \/var\/log\/syslog | tail -5'<\/code><\/pre><\/p>\n\n\n\n
Success looks like: available memory never drops near zero, swap usage stays flat, and no OOM messages appear. If MySQL still swaps, cut the buffer pool by another 25% \u2014 a smaller cache that fits in RAM always beats a larger one that forces pages out to disk.<\/p>\n\n\n\n
Step 7: Know When to Stop Tuning and Buy More RAM<\/h2>\n\n\n\n
There is a floor below which tuning cannot go. If your working set genuinely exceeds what the buffer pool can hold, you will see persistent disk reads and unresponsive queries no matter how tight the config is. Check the cache hit ratio:<\/p>\n\n\n\n
mysql -e \"\nSELECT\n ROUND(100 * (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status\n WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') \/\n (SELECT VARIABLE_VALUE FROM performance_schema.global_status\n WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')), 2) AS hit_pct;\"<\/code><\/pre>\n\n\n\nAbove 99% is healthy. Below 97% with a working set that cannot shrink, it is time for a larger plan \u2014 or a plan whose storage delivers consistent IOPS so the reads you cannot cache are at least fast. If that is where you have landed, our VPS comparison table<\/a> lists plans with their measured IOPS and memory ceilings so you can size the upgrade against real numbers instead of spec sheets.<\/p>\n\n\n\n