Database Memory Tuning Calculator

Where a database's memory goes, and the parameter that quietly multiplies

Managed databases ship with memory parameters derived from the instance size, and most of them are sensible defaults you should leave alone. One is not, because it does not behave like the others: work_mem is granted per sort or hash node, so its real cost is multiplied by the number of connections and by the number of such nodes in each query. This tool derives the standard parameters from your instance memory and then shows that product explicitly.

How it works

• Derives shared_buffers, effective_cache_size and maintenance_work_mem from instance memory, using the ratios RDS itself uses.
• Shows the MySQL equivalents — innodb_buffer_pool_size and the per-connection buffers — when you switch engines.
• Multiplies work_mem by your connection count and by sorts per query, which is the number that actually has to fit.
• Warns when the base allocation plus that worst case exceeds the memory on the instance.

PostgreSQL, as RDS sets the defaults
  shared_buffers        = {DBInstanceClassMemory/32768}  -> 25% of RAM
  effective_cache_size  ~ 75% of RAM   (a planner hint, not an allocation)
  maintenance_work_mem  ~ 5%, capped around 2 GiB

MySQL
  innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} -> 75% of RAM

the one that multiplies
  worst case = work_mem x connections x sorts_per_query

Worked example

A 32 GiB PostgreSQL instance at the defaults, and what happens when one setting is raised.

1. shared_buffers = 25% of 32 GiB = 8 GiB
2. effective_cache_size = 75% = 24 GiB, but this reserves nothing — it only tells the planner what the OS is likely caching
3. maintenance_work_mem = 5% = 1.6 GiB, under the 2 GiB cap
4. work_mem = 16 MiB, with 200 connections and 2 sort nodes per query
5. worst case = 16 MiB x 200 x 2 = 6.25 GiB
6. 8 GiB + 6.25 GiB = 14.25 GiB of 32 — comfortable
7. now raise work_mem to 64 MiB to fix one slow report, and grow to 1,000 connections with 4 sorts
8. worst case = 64 MiB x 1,000 x 4 = 250 GiB on a 32 GiB instance

The last two steps are the usual route to an out-of-memory kill. Nothing looked reckless: someone raised work_mem by a factor of four to make one query faster, and traffic grew. The multiplication did the rest, and it happens under load rather than when the parameter is changed — which is why it is rarely caught in review.

Reading the result

• effective_cache_size allocates nothing. It is an estimate you give the planner of how much memory the operating system is likely using to cache your data, and it changes which plans look cheap. Setting it high does not consume memory; setting it wrongly produces bad plans.
• shared_buffers at 25% is the conventional starting point and RDS's default. Raising it is not free: PostgreSQL relies on the operating system's page cache as a second tier, so memory moved into shared_buffers is memory taken away from that cache. Very large values can make things slower.
• MySQL is the opposite shape. innodb_buffer_pool_size defaults to 75% because InnoDB does not lean on the OS cache the way PostgreSQL does — it wants the data in its own pool. Do not carry a PostgreSQL intuition across.
• Raise work_mem for a session rather than the server when one report needs it. SET work_mem in that connection gets the plan you want without granting the same allowance to every other connection on the instance.
• A query with several sort or hash nodes can take work_mem several times over, in a single connection. Parallel workers multiply it again. The tool's sorts-per-query slider is there because assuming one is the most common way this estimate comes out too low.

Common questions

Is the worst case realistic, or just arithmetic?

It is a ceiling, not a forecast — every connection would have to be running a memory-hungry query simultaneously. But it is the ceiling that matters, because databases do not fail gracefully at it: they get killed by the OOM killer, and traffic spikes are exactly when every connection is busy at once.

My managed database will not let me change shared_buffers. Why?

On RDS and Azure it is set through a parameter group as a formula over DBInstanceClassMemory, not as a fixed number, and changing it needs a reboot because the shared memory segment is allocated at startup. Cloud SQL restricts some flags entirely. The default is usually right; the parameter worth your attention is work_mem.

How do I know what work_mem a query actually needs?

Run EXPLAIN (ANALYZE, BUFFERS) and look for external merge or disk-based hashes. If a sort spilled to disk, the plan says so and gives the size. That figure — a little more than the spill — is the value that query wants, and it tells you whether raising the setting globally is worth it or whether the query should be fixed instead.

What about MySQL's per-connection buffers?

They multiply the same way. sort_buffer_size, join_buffer_size and read_rnd_buffer_size are allocated per connection when needed, on top of the buffer pool. The same discipline applies: keep them modest, and raise them for the session that genuinely needs them.

Sources

• PostgreSQL — resource consumption parameters
• Amazon RDS — best practices
• MySQL — InnoDB buffer pool configuration