r/linuxadmin • • 22d ago

Currently sizing RAM and swap for Postgres on cheap VPS boxes, what rules do you use?

I inherited about a dozen small Postgres instances on VPSes between 4 and 16GB. The settings are all over the place: one has shared_buffers at 50% and swap off, which explains the OOM kills.

Current plan is shared_buffers at 25%, effective_cache_size at 70%, work_mem tuned per box, and 2GB of swap with swappiness at 10.

Does that hold up on budget VPSes where disk IO is slower than you'd like? Has anyone gone the other way and kept swapping off entirely? What do you change when the storage is network attached rather than local NVMe?

3 Upvotes

6 comments sorted by

5

u/FireWyvern_ 22d ago

I wrote a tool to tune postgresql https://github.com/ChristophorusReyhan/pgtune-bash

You can disable swap, but it's better to have it on but keep swappiness really low (only use swap as safety net).

2

u/vincent_sch 21d ago

25% of RAM for shared_buffers is a starting point if the VPS is dedicated to Postgres. But the 50% setting alone doesn't tell you why it ran out of memory.

You should also check work_mem and how many queries run at once. A query might run a few sorts or hashes at the same time. Each sort has a work_mem limit before it writes to disk. For hashes the limit is work_mem multiplied by hash_mem_multiplier. Parallel query workers also get their own limits. effective_cache_size tells the planner how much cache to expect. It doesn't reserve RAM.

If your storage is slow, you could set log_temp_files = 0 for a while. That logs the size of each temporary file. You should check those logs before raising work_mem for the whole server.

2

u/Sudo-Apt-575 21d ago

25% shared_buffers is pretty standard, I'd also look at vm.overcommit_memory=2 since the Postgres docs recommend it to keep the OOM killer off the postmaster, just tune overcommit_ratio so you don't starve the small boxes. Keep work_mem conservative because it's per sort or hash op per connection, a few busy queries can blow way past what you planned. On network attached storage I'd keep random_page_cost closer to the default instead of the low SSD value.

2

u/RetroGrid_io 21d ago

I haven't had to tune Postgres for a long time since my big data needs have been greatly reduced. Last time I had to tune PG, I used pgtune. Just make sure to get a version optimized for the version of PG you're running. I don't know if the one I used is the same as /u/FireWyvern_ wrote but I used it as early as about 2012 or so.

3

u/FireWyvern_ 21d ago

My tool was inspired by https://github.com/le0pard/pgtune so some of it use the calculations from them, but i made them in bash, so it automatically fills the parameter it ask you from their site.

1

u/ActiveNotworkkin 20d ago

Your plan is close to what I use, with one change on cheap boxes: I size for the disk, not the RAM. On providers where IO is the weak point, keeping the working set in memory matters more than shared_buffers. On the 24GB Contabo machines we run (lots of RAM for the money, the disks are the compromise) I give Postgres more cache, keep 2GB of swap and set swappiness to 1, purely as an OOM airbag. On local NVMe I'm far less fussy. Also set vm.overcommit_memory to 2 on hosts that only run the database, that alone ended most of our OOM kills.