MySQL vs MariaDB on a 2GB RAM VPS: Tuning Profiles That Don’t Crash

Pick MySQL or MariaDB on 2GB: the real decision points

On a 2GB VPS, “MySQL vs MariaDB” is less about ideology and more about operational ergonomics: defaults, packaging, and which landmines you’d rather manage.

My recommendation (opinionated, with caveats)

• If you need predictable behavior across clouds and managed services: pick MySQL 8.0. It matches what most hosted providers run, and the operational lore is deep.
• If you’re self-hosting and want knobs that help small boxes: MariaDB can be friendlier, especially with thread pool (on some builds) and sane packaging on many distros.
• If you rely on MySQL 8 features (certain JSON and replication behaviors, newer optimizer work, official tooling expectations): pick MySQL, tune it conservatively, and move on with your life.
• If you need to minimize surprise during upgrades: pick whichever your distro supports best and keep it pinned. On 2GB, surprise is expensive.

Performance-wise, either engine will happily saturate your tiny CPU and slow disk before it wins any awards. The real risk is memory: the database is a professional at spending whatever you give it, plus whatever you forgot it could spend.

Joke #1: A 2GB VPS running an untuned database is like a carry-on bag: you can fit everything in, right up until the zipper decides you’ve offended it.

Interesting facts & context that actually matter

These are not trivia-night facts. They’re the kind that explain why settings differ, why defaults exist, and why “just copy this my.cnf” is a trap.

1. MariaDB forked from MySQL after Oracle acquired Sun (2010): that fork wasn’t just politics—it led to diverging defaults, features, and sometimes different interpretations of “compatible.”
2. MySQL 5.6 made InnoDB the clear default for serious workloads: the shift from MyISAM-era tuning lore to InnoDB-centric tuning changed what “safe” means.
3. MySQL 8.0 removed the query cache entirely: old “performance guides” still recommend query cache tweaks; on MySQL 8 those knobs don’t exist, and on MariaDB they can still hurt you.
4. InnoDB’s buffer pool became the main performance lever: the “give it 80% RAM” rule got popular on dedicated DB hosts—on a 2GB VPS with other processes, it’s reckless.
5. Thread pooling diverged: MariaDB has widely used thread pool implementations; MySQL’s story differs depending on edition and version. On small CPU counts, thread handling affects tail latency more than you’d think.
6. InnoDB redo logging evolved: tuning redo capacity and flush behavior matters because small VPS disks are often network-backed and bursty.
7. Ubuntu/Debian packaging defaults changed over time: you’ll see different baseline configs across major versions, which means “stock MySQL” is not a single thing.
8. Linux’s OOM killer doesn’t care about your SLA: it kills something. Often mysqld. Sometimes your app. Either way, your incident channel lights up.

The 2GB VPS mental model: where memory really goes

On small hosts, the question isn’t “what’s the fastest setting?” It’s “what’s the maximum damage this setting can do at peak concurrency?” That’s the mental shift that prevents crashes.

Budget your RAM like you budget on-call sleep

With 2GB RAM, you don’t have 2GB for MySQL/MariaDB. You have:

• Kernel + page cache + slab: typically a few hundred MB, more under I/O load.
• System services (sshd, journald, cron, monitoring agent): 50–200MB.
• Your application runtime (PHP-FPM, Node, Java, Python workers): anywhere from “manageable” to “why is it 900MB.”
• Database: whatever is left, and it will try to take it all.

On a dedicated DB-only 2GB VPS (no app), you can push the DB harder. On a shared app+DB box (common in cheap stacks), you must reserve space so the kernel doesn’t panic and start murdering processes.

The two kinds of database memory: global and per-connection

Most tuning guides talk about global buffers (buffer pool, log buffers). The hidden assassin is per-connection memory:

• sort buffers
• join buffers
• read buffers
• tmp tables (in-memory until they spill)
• thread stacks

Each one might look small. Multiply by 200 connections and you’ve built a RAM bonfire. This is why “just increase max_connections” is one of the most expensive clicks you can make.

Disk is the other axis: tiny VPS storage is often “I/O theater”

Your VPS disk might be network-attached, burstable, throttled, or backed by overcommitted storage. Databases expose storage lies brutally. If you tune for memory but ignore I/O patterns (fsync rate, dirty page flushing), you’ll get the classic symptoms:

• fast until it isn’t
• random latency spikes
• stalls during checkpoints
• replication lag that appears “for no reason”

A single reliability quote (paraphrased idea)

“Hope isn’t a strategy.” — General H. Norman Schwarzkopf (paraphrased idea often used in engineering and operations)

It applies here: you can’t hope the VPS won’t hit peak concurrency; you have to set limits so peak doesn’t kill you.

Safe tuning profiles (copy/paste) for 2GB RAM

These profiles aim for “doesn’t crash” first, “fast enough” second. They assume InnoDB (you should be on InnoDB unless you have a very specific reason not to).

Before you copy/paste: decide your deployment type

• DB-only VPS: the database can use most memory, but still leave breathing room for the OS.
• App + DB on same VPS: the database must be modest, and connection limits must be strict.

Also decide storage characteristics:

• SSD-ish with decent fsync: you can run more aggressive flushing.
• Network/cheap block storage: expect fsync pain; tune to avoid burst stalls.

Profile A: “App + DB together” (most common cheap VPS stack)

Target: MySQL 8.0 or MariaDB 10.6+ on a 2GB VPS running an app server too. This is the profile you use when you’d rather degrade performance than page the whole team.

cr0x@server:~$ sudo tee /etc/mysql/conf.d/99-vps-2gb-safe.cnf >/dev/null <<'EOF'
[mysqld]
# Core safety limits
max_connections                 = 60
skip_name_resolve               = ON
wait_timeout                    = 60
interactive_timeout             = 300

# InnoDB: keep it small and stable
innodb_buffer_pool_size         = 512M
innodb_buffer_pool_instances    = 1
innodb_log_buffer_size          = 16M
innodb_redo_log_capacity        = 256M
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 1
innodb_io_capacity              = 200
innodb_io_capacity_max          = 400

# Reduce per-connection memory blowups
tmp_table_size                  = 32M
max_heap_table_size             = 32M
sort_buffer_size                = 2M
join_buffer_size                = 2M
read_buffer_size                = 1M
read_rnd_buffer_size            = 1M
thread_stack                    = 256K

# Keep table cache reasonable
table_open_cache                = 800
open_files_limit                = 65535

# Observability that pays rent
slow_query_log                  = ON
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 1
log_queries_not_using_indexes   = OFF
EOF

Why this works: 512M buffer pool won’t win benchmarks, but it won’t starve your app. Tight timeouts reduce idle connection hoarding. Modest tmp/sort/join buffers cap per-connection blowups. You’re choosing bounded damage.

Profile B: “DB-only 2GB VPS” (more headroom, still not a toy)

If the box runs only the database (plus monitoring), you can feed InnoDB more.

cr0x@server:~$ sudo tee /etc/mysql/conf.d/99-vps-2gb-dbonly.cnf >/dev/null <<'EOF'
[mysqld]
max_connections                 = 120
skip_name_resolve               = ON
wait_timeout                    = 120

innodb_buffer_pool_size         = 1G
innodb_buffer_pool_instances    = 1
innodb_log_buffer_size          = 32M
innodb_redo_log_capacity        = 512M
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 1
innodb_io_capacity              = 300
innodb_io_capacity_max          = 600

tmp_table_size                  = 64M
max_heap_table_size             = 64M
sort_buffer_size                = 2M
join_buffer_size                = 2M
read_buffer_size                = 1M
read_rnd_buffer_size            = 1M
thread_stack                    = 256K

table_open_cache                = 1200
open_files_limit                = 65535

slow_query_log                  = ON
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 0.5
EOF

Hard rule: don’t set a 1.4G buffer pool on a 2GB VPS and then act shocked when it swaps. You’ll be right on the edge, and the edge is where incidents live.

MariaDB-specific knobs worth considering on small boxes

MariaDB often gives you extra operational levers. Use them carefully; “more knobs” is not automatically “more better.”

• Thread pool can reduce thread thrash under high connection counts, improving tail latency. If you enable it, keep max_connections realistic.
• Aria and legacy engines exist; don’t accidentally use them for high-write tables unless you know the failure modes.

MySQL-specific realities on small boxes

• MySQL 8’s defaults are generally sane, but “sane” assumes you’re not running 200 connections on 2GB.
• Don’t chase removed features like query cache. If you find a guide telling you to tune it on MySQL 8, close the tab.

About durability settings (the fsync argument)

innodb_flush_log_at_trx_commit=1 is the durable default. On garbage storage, it can be painful. Setting it to 2 reduces fsync frequency and improves throughput, but you accept losing up to ~1 second of transactions on crash. If you’re on a single VPS with no replication and you care about data, don’t “optimize” durability away because a blog told you to. If your workload can tolerate it (sessions, caches, derived data), that’s a business decision. Write it down.

Joke #2: Turning off durability to “fix performance” is like removing the smoke alarm because the beeping is annoying.

Hands-on tasks: commands, outputs, and what you decide

These tasks are designed for the exact moment you’re staring at a 2GB VPS and wondering why it’s slow, stuck, or rebooting. Every task includes: a command, what realistic output looks like, what it means, and the decision you make.

Task 1: Confirm what you actually installed (and avoid tuning the wrong daemon)

cr0x@server:~$ mysql --version
mysql  Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

Meaning: Version tells you which features exist and which tuning myths to ignore. MySQL 8 has no query cache; MariaDB has different defaults in several areas.

Decision: Pin your tuning advice to your version. If you’re on MariaDB, also check the exact minor version because defaults move.

Task 2: Verify the server flavor from inside SQL

cr0x@server:~$ mysql -e "SELECT VERSION(), @@version_comment;"
+-----------+------------------------------+
| VERSION() | @@version_comment            |
+-----------+------------------------------+
| 8.0.36    | (Ubuntu)                     |
+-----------+------------------------------+

Meaning: This confirms you’re connected to the server you think you are. Sounds obvious. It stops being obvious during migrations, failovers, and “temporary” debug containers.

Decision: If version_comment doesn’t match expectations, stop and locate the real endpoint. Tuning the wrong instance is a special kind of wasted day.

Task 3: Check RAM, swap, and whether you’re already living dangerously

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:           1.9Gi       1.2Gi       140Mi        42Mi       620Mi       420Mi
Swap:          1.0Gi       180Mi       820Mi

Meaning: Low “available” memory plus swap usage means pressure is real. Swapping on a DB host often correlates with latency spikes and stalls.

Decision: If swap is non-zero and DB latency is bad, reduce DB memory footprint (buffer pool, connection count, per-connection buffers) before you “optimize queries.”

Task 4: Prove the OOM killer is involved (or rule it out)

cr0x@server:~$ sudo dmesg -T | tail -n 20
[Mon Dec 30 09:11:02 2025] Out of memory: Killed process 1421 (mysqld) total-vm:3074824kB, anon-rss:1320440kB, file-rss:0kB, shmem-rss:0kB, UID:110 pgtables:3140kB oom_score_adj:0
[Mon Dec 30 09:11:02 2025] oom_reaper: reaped process 1421 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

Meaning: That’s not “MySQL crashed.” That’s “Linux killed it.” The fix is capacity or memory caps, not reindexing.

Decision: Set hard boundaries: lower max_connections, lower per-connection buffers, and right-size innodb_buffer_pool_size. Consider adding swap only as a last-resort cushion, not a plan.

Task 5: Identify who’s hoarding memory right now

cr0x@server:~$ ps -eo pid,comm,rss,pmem --sort=-rss | head
  PID COMMAND           RSS %MEM
 1421 mysqld        1184500 60.2
  911 php-fpm8.1     210300 10.7
  947 php-fpm8.1     185120  9.4
  612 node            121800  6.1

Meaning: RSS shows current resident memory. It’s a snapshot, but it tells you whether the DB is genuinely the pressure source.

Decision: If mysqld is the biggest by far, tune DB memory. If the app is bigger, fix the app’s process model first (worker counts, leak, caching).

Task 6: Confirm InnoDB buffer pool size and key memory variables

cr0x@server:~$ mysql -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','tmp_table_size','max_heap_table_size','sort_buffer_size','join_buffer_size');"
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
| join_buffer_size        | 2097152   |
| max_connections         | 60        |
| max_heap_table_size     | 33554432  |
| sort_buffer_size        | 2097152   |
| tmp_table_size          | 33554432  |
+-------------------------+-----------+

Meaning: Values are bytes for many variables. Buffer pool at 512MB matches Profile A.

Decision: If these don’t match your intended profile, you’re debugging fiction. Fix config loading order, remove conflicting fragments, restart cleanly.

Task 7: Estimate real connection concurrency and whether max_connections is fantasy

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW GLOBAL STATUS LIKE 'Max_used_connections';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 18    |
+-------------------+-------+
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Max_used_connections| 54    |
+---------------------+-------+

Meaning: You’ve hit 54 concurrent connections historically. With max_connections=60, you’re close to the ceiling.

Decision: If max_used is near the cap, either (a) raise it slightly and reduce per-connection buffers, or (b) fix pooling in the app so you don’t need 60 real connections.

Task 8: Check for temp table spills (a classic silent performance killer)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1842  |
| Created_tmp_files       | 211   |
| Created_tmp_tables      | 9321  |
+-------------------------+-------+

Meaning: Disk temp tables are happening. Some is normal; lots indicates sorts/group-bys that don’t fit in memory or lack indexes.

Decision: If disk tmp tables are high relative to tmp tables, don’t just raise tmp_table_size on 2GB. Fix the query, add indexes, or accept disk spills and tune I/O instead.

Task 9: Check whether the buffer pool is too small (or just right)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Innodb_buffer_pool_read_requests      | 9284432 |
| Innodb_buffer_pool_reads              | 188322  |
+---------------------------------------+---------+

Meaning: Reads vs read_requests gives you a sense of cache misses. Some misses are expected. A very high miss ratio suggests a too-small buffer pool or a workload that doesn’t cache well.

Decision: If misses are huge and you have memory headroom (and you’re not swapping), increase buffer pool cautiously. If you’re memory-bound, fix queries and indexes instead.

Task 10: Find the top wait classes fast (InnoDB status snapshot)

cr0x@server:~$ mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2025-12-30 09:22:11 0x7f2d2c1ff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2199 srv_active, 0 srv_shutdown, 12195 srv_idle
srv_master_thread log flush and writes: 14394
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 31212
OS WAIT ARRAY INFO: signal count 30009
RW-shared spins 0, rounds 0, OS waits 12
RW-excl spins 0, rounds 0, OS waits 48
------------
TRANSACTIONS
------------
Trx id counter 1149091
History list length 188

Meaning: This is a quick reality check. High OS waits in semaphores can indicate contention; large history list length suggests purge lag (often from long transactions).

Decision: If history list length grows and stays high, hunt long-running transactions. If semaphore waits spike under load, reduce concurrency, tune queries, and consider thread pooling (especially on MariaDB).

Task 11: Spot long transactions that keep undo/purge busy

cr0x@server:~$ mysql -e "SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;"
+--------+---------------------+---------------------+----------------------------------+
| trx_id | trx_started         | trx_mysql_thread_id | trx_query                        |
+--------+---------------------+---------------------+----------------------------------+
| 1148802| 2025-12-30 09:04:01 | 312                 | SELECT * FROM orders WHERE ...   |
+--------+---------------------+---------------------+----------------------------------+

Meaning: One transaction running since 09:04 can keep undo segments hot and purge behind, impacting writes and bloat.

Decision: Fix the app behavior (avoid interactive transactions), add indexes, and set sane timeouts. Killing it is sometimes necessary, but prevention is cheaper.

Task 12: Verify slow query logging is on and useful

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time';"
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

Meaning: Slow logs are your low-budget profiler. On a 2GB VPS, you don’t get to guess.

Decision: If slow log is off, turn it on. If it’s too noisy, raise long_query_time temporarily—don’t disable the log.

Task 13: Read the slow log like an operator, not a tourist

cr0x@server:~$ sudo tail -n 25 /var/log/mysql/slow.log
# Time: 2025-12-30T09:27:14.123456Z
# User@Host: app[app] @ 10.0.0.15 []
# Query_time: 3.214  Lock_time: 0.002 Rows_sent: 50  Rows_examined: 982341
SET timestamp=1735550834;
SELECT * FROM events WHERE user_id=123 ORDER BY created_at DESC LIMIT 50;

Meaning: Rows_examined ~ 1M for a LIMIT 50 is a classic missing/poor index. Lock_time is tiny; this isn’t “locking,” it’s “work.”

Decision: Add/adjust an index (likely (user_id, created_at)), or rewrite query to avoid scanning. Don’t touch buffer sizes to “fix” this.

Task 14: Confirm you aren’t accidentally on double caching (bad with O_DIRECT mismatches)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'innodb_flush_method';"
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+

Meaning: O_DIRECT reduces double-buffering between InnoDB buffer pool and OS page cache. On small RAM, that helps keep memory predictable.

Decision: If it’s not O_DIRECT and you’re memory pressured, consider switching (test first; some storage stacks behave oddly).

Task 15: Inspect open tables and file descriptor pressure

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Open_tables'; SHOW VARIABLES LIKE 'table_open_cache';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 790   |
+---------------+-------+
+------------------+------+
| Variable_name    | Value|
+------------------+------+
| table_open_cache | 800  |
+------------------+------+

Meaning: If Open_tables keeps hitting the cache limit, MySQL will open/close tables frequently, causing overhead and metadata contention.

Decision: If you have FD headroom and memory headroom, raise table_open_cache moderately. On 2GB, don’t set it to 10,000 like it’s a free buffet.

Task 16: Check I/O latency symptoms from the OS side

cr0x@server:~$ iostat -xz 1 3
Linux 5.15.0-91-generic (server) 	12/30/2025 	_x86_64_	(2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.21    0.00    6.44   22.12    0.00   53.23

Device            r/s     w/s   rkB/s   wkB/s  await  %util
vda              42.0    58.0   912.0  2310.0  48.30  92.10

Meaning: await ~48ms and %util ~92% indicates the disk is the bottleneck. High iowait backs that up.

Decision: If disk is pegged, stop tuning CPU knobs. Reduce write amplification (indexes, transaction size), adjust redo capacity sensibly, and consider upgrading storage or moving DB off the box.

Fast diagnosis playbook

This is the “you have 15 minutes to look competent” workflow. It’s ordered to find the bottleneck quickly on a 2GB VPS.

First: is it dying (OOM, restarts, or swaps)?

• Check dmesg for OOM kills.
• Check free -h for swap activity and low available memory.
• Check whether mysqld is restarting (systemd logs).

cr0x@server:~$ sudo journalctl -u mysql --since "1 hour ago" | tail -n 30
Dec 30 09:11:04 server systemd[1]: mysql.service: Main process exited, code=killed, status=9/KILL
Dec 30 09:11:04 server systemd[1]: mysql.service: Failed with result 'signal'.
Dec 30 09:11:06 server systemd[1]: mysql.service: Scheduled restart job, restart counter is at 3.

Decision: If there’s OOM/restarts, prioritize memory caps and connection limits before any micro-optimization.

Second: is it disk (iowait, fsync stalls, temp table spills)?

• iostat -xz for await/util.
• Check temp table disk spills.
• Check redo/log flush pressure.

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs'; SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';"
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_os_log_fsyncs | 192113 |
+----------------------+--------+
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Innodb_os_log_written | 987654321 |
+-----------------------+-----------+

Decision: If disk is the bottleneck, focus on query/index fixes and write reduction. Don’t crank buffer pool in a way that forces swapping; you’ll trade one outage for another.

Third: is it CPU/locking (too many threads, contention)?

• top / pidstat to see CPU saturation.
• InnoDB status for semaphore waits and row lock contention.
• Processlist for many simultaneous heavy queries.

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;" | head -n 15
Id	User	Host	db	Command	Time	State	Info
311	app	10.0.0.15:41012	prod	Query	12	Sending data	SELECT * FROM events WHERE user_id=...
312	app	10.0.0.15:41014	prod	Query	520	Updating	UPDATE inventory SET ...
313	app	10.0.0.15:41015	prod	Sleep	58		NULL

Decision: If you see long-running updates and many readers stuck “Sending data,” you probably have an indexing problem or transaction design issue, not a magical config problem.

Three corporate mini-stories (because failure is a teacher)

1) Incident caused by a wrong assumption: “max_connections is just a cap, not a memory setting”

They ran a small customer portal on a 2GB VPS: web app, database, everything in one place. During a seasonal spike, the portal didn’t just slow down. It started rebooting. The team assumed it was a bad kernel or “noisy neighbor” on the VPS host.

The fix they tried first was the classic: bump max_connections from 100 to 400 because clients were getting “too many connections.” The outage got worse. Now the site didn’t just error; it entered a loop of database restarts and half-completed deploys.

Post-incident, we walked through memory accounting. Each connection was allowed to allocate multiple buffers. In a spike, connections would fan out, some running sorts and joins, each quietly grabbing megabytes. Multiply by hundreds and you’ve built a memory cliff. The kernel did what the kernel does: it killed mysqld.

The boring fix was the correct fix: cap connections tightly, enforce application pooling, reduce per-connection buffers, and keep InnoDB’s buffer pool modest. Then, because humans forget, add alerting on Max_used_connections and swap usage.

The result wasn’t “faster” in the benchmark sense. It was stable. The portal degraded gracefully under spikes instead of detonating. In production, graceful degradation is a feature.

2) Optimization that backfired: “Let’s make tmp_table_size huge to stop disk spills”

A reporting-heavy service suffered from slow group-by queries. Someone noticed lots of Created_tmp_disk_tables and decided the fix was to raise tmp_table_size and max_heap_table_size dramatically. On paper, fewer disk temp tables means less I/O, which means faster queries. That’s the seduction.

The change worked in testing. Then it hit production. Memory usage climbed under concurrent reporting. Not linearly. Suddenly. The service didn’t slow down; it fell over. The OOM killer became a routine participant in daily standups.

The subtle part: in-memory temp tables consume memory per session, and complex queries can create large temporary structures. Under concurrency, that “helpful” memory becomes an unbounded liability. On a 2GB VPS, unbounded is another word for “soon.”

The correct fix was multi-part and slightly annoying: add proper indexes to reduce temp table needs, rewrite the worst reports, and accept that some disk temp tables are normal. Keep tmp_table_size modest so the failure mode is “slow report,” not “dead database.”

They also moved reporting workloads to off-peak and introduced a replica later. The big win wasn’t the knob. It was deciding the workload didn’t belong on the primary during business hours.

3) Boring but correct practice that saved the day: “Slow query log + one index per incident”

Another team ran a small SaaS on MariaDB with a strict 2GB budget. Nothing fancy. They had one practice that looked almost too basic: slow query logging was always enabled, rotated, and reviewed after any incident.

When a new feature launched, p95 latency doubled. They didn’t start by changing buffer pools or log flushing. They pulled the slow log and found a query that examined far too many rows for a simple user dashboard. It wasn’t malicious. It was just missing the right composite index.

They added the index, deployed, and latency returned to normal. No scary config changes. No “temporary” durability compromises. No week-long performance project. Just disciplined feedback loops.

Later, when they did tune configuration, it was informed by observed bottlenecks—connection spikes, temp table ratios, disk await. The team’s secret wasn’t brilliance. It was refusing to operate blind.

If you’re running on small hardware, boring observability is not overhead. It’s rent.

Common mistakes: symptom → root cause → fix

1) Symptom: mysqld gets killed, restarts, or disappears

Root cause: OOM killer due to unbounded per-connection memory, oversized buffer pool, or app and DB competing for RAM.

Fix: Reduce max_connections, reduce per-connection buffers (sort/join/read), right-size innodb_buffer_pool_size, and ensure the app uses pooling. Confirm with dmesg.

2) Symptom: “Too many connections” errors during spikes

Root cause: Connection churn and lack of pooling; or max_connections too low for peak concurrency.

Fix: Implement pooling (app-level or proxy), shorten wait_timeout to clear idle connections, and raise max_connections only if you also cap per-connection memory.

3) Symptom: random latency spikes, especially on writes

Root cause: Disk stalls (fsync, checkpointing, cheap storage), or background flushing fighting foreground queries.

Fix: Verify with iostat. Tune redo capacity modestly, keep durability unless you explicitly accept data loss, and reduce write amplification (indexes, batch size).

4) Symptom: CPU is high, load average climbs, but queries aren’t “that big”

Root cause: Too many runnable threads due to excessive concurrency; inefficient queries; missing indexes causing scans.

Fix: Lower concurrency (connections), use slow logs, add indexes, and consider thread pool (especially on MariaDB) to reduce scheduler thrash.

5) Symptom: replication lag (if you run a replica)

Root cause: Replica I/O can’t keep up (disk), or SQL thread is blocked by long transactions or heavy queries.

Fix: Check replica status, reduce write load, and optimize the slowest queries. On 2GB, running replication plus app plus DB is ambitious; be honest about resources.

6) Symptom: temporary tables “mysteriously” fill disk

Root cause: Large sorts/group-bys spilling to disk; tmpdir on small filesystem; insufficient indexes.

Fix: Put tmpdir on a filesystem with space, but primarily fix queries/indexes. Don’t just increase tmp_table_size on 2GB.

7) Symptom: “Sending data” state dominates processlist

Root cause: Table/index scans and heavy row evaluation; sometimes poor join order.

Fix: Use slow log + EXPLAIN, add proper indexes, reduce selected columns, and paginate properly. Config won’t save you from scanning millions of rows for fun.

Checklists / step-by-step plan

Step-by-step: stabilize first (avoid crashes), then optimize

1. Confirm the version and engine: MySQL vs MariaDB, exact version, and that tables are InnoDB.
2. Set connection boundaries: pick a realistic max_connections (60–120 on 2GB depending on app and query cost).
3. Cap per-connection memory: keep sort/join buffers modest; don’t “help” each query with huge buffers.
4. Right-size buffer pool: 512M for app+DB, ~1G for DB-only as a starting point.
5. Enable slow query logging: always, with log rotation in place.
6. Verify you aren’t swapping: swap is not a database cache, it’s a performance tax.
7. Measure disk latency: use iostat; if await is ugly, accept that you’re storage-bound.
8. Fix top 3 queries: add indexes, rewrite, reduce row scans. Do not start by changing 40 knobs.
9. Set alerts: swap usage, Max_used_connections near cap, disk util, and slow query volume changes.
10. Re-test under load: staging load test or a controlled production ramp. Watch memory and disk.

Sanity limits for a 2GB VPS (rules of thumb)

• max_connections: 40–80 if app+DB; 80–150 if DB-only and workload is light per connection.
• innodb_buffer_pool_size: 512M (shared) to 1G (DB-only). Rarely more.
• tmp_table_size / max_heap_table_size: 32M–64M. Larger is a concurrency trap.
• sort/join buffers: 1M–4M max in most cases on small RAM. Big values are for specialized workloads with low concurrency.
• Durability: keep innodb_flush_log_at_trx_commit=1 unless you have explicit data-loss tolerance.

Operational hygiene that prevents “mystery slowness”

• Rotate slow logs and error logs so disk doesn’t fill.
• Keep tables and indexes trimmed; avoid unused indexes that amplify writes.
• Run ANALYZE TABLE when query plans go weird after big data changes (sparingly, off-peak).
• Keep upgrades planned; last-minute major version jumps on a 2GB VPS are how you meet the worst parts of the optimizer.

FAQ

1) Is MariaDB faster than MySQL on a 2GB VPS?

Sometimes, for specific workloads, but the dominant factor on 2GB is usually memory pressure and disk latency. Pick the engine you can operate cleanly, then fix queries.

2) What’s the single most important knob to avoid crashes?

max_connections, because it indirectly caps per-connection memory explosion. Right behind it: keeping per-connection buffers modest.

3) How big should innodb_buffer_pool_size be on 2GB?

Start at 512M if the box runs your app too. If it’s DB-only, start around 1G. Increase only if you’re not swapping and cache misses are genuinely hurting performance.

4) Should I enable swap on a database VPS?

Swap can prevent immediate OOM death, but it can also create long latency stalls. If you use swap, treat it as an emergency buffer and keep DB memory conservative. If swapping becomes routine, you’re underprovisioned or misconfigured.

5) Is innodb_flush_log_at_trx_commit=2 acceptable?

It’s acceptable only if the business accepts losing up to about a second of committed transactions during a crash. On a single VPS holding primary data, default durability is usually the right call.

6) Why not just make tmp_table_size huge to avoid disk temp tables?

Because on a small server, that’s a concurrency grenade. A few concurrent queries can consume hundreds of MB each in the worst case. Fix the query and indexes first.

7) Should I run app and database together on 2GB?

You can, but you must be strict: limit DB memory, cap connections, and reduce worker counts in the app. If the app grows, split them early—vertical scaling on one tiny node has sharp edges.

8) What’s the fastest way to tell whether I’m CPU-bound or disk-bound?

Use iostat -xz. High %iowait, high await, and high %util point to disk. If iowait is low but CPUs are pegged, you’re CPU/query/concurrency bound.

9) Do I need to tune table_open_cache on 2GB?

Only if you see churn (Open_tables near the cache limit and performance issues tied to metadata operations). Keep it moderate; huge caches consume memory and file descriptors.

10) If I can only fix one thing in the application, what is it?

Connection pooling and sane timeouts. It reduces the need for high max_connections and cuts memory volatility dramatically.

Conclusion: practical next steps

If you want a 2GB VPS database that doesn’t crash, stop thinking in “best performance” and start thinking in “bounded failure.” Cap connections. Keep per-connection buffers small. Give InnoDB a buffer pool that fits reality, not ego. Then use the slow query log to earn real performance improvements the honest way: by doing less work.

Do this next, in order

1. Pick Profile A (shared) or Profile B (DB-only) and apply it cleanly; restart the service.
2. Run the fast diagnosis checks: free, dmesg, iostat, Threads_connected/Max_used_connections.
3. Review slow log for the top offenders; fix the worst query with an index or rewrite.
4. Set alerts for swap usage and Max_used_connections nearing your cap.
5. If you still hit limits: split app and DB, or upgrade RAM/storage. On 2GB, “scale up” is often the cheapest optimization you’ll ever buy.

MySQL and MariaDB can both behave well on a 2GB VPS. The trick is not finding the perfect settings. It’s refusing to let any one setting make failure unbounded.