Sizing a database server: the working set, memory and disks

A 180 GB database can feel instant on a server with 64 GB of memory and sluggish on one with 32 GB, while the processor on the slow machine sits mostly idle. Nothing about the queries changed. On the first machine the pages they need are already in memory. On the second, a slice of them has to come from disk again and again, and the queries spend their time waiting for it.

That is most of what sizing a database server comes down to. You need memory for the part of your data the queries touch, not all of it, so the work is estimating that part, knowing where each database keeps it and checking that it fits. Settings, connections, disks and cores follow.

The working set, not the database, has to fit

In 1968 Peter Denning defined a program's working set as the information it referenced in a recent window of time, usually counted in memory pages, and proposed it as an approximation of the pages it would need next.¹ Databases fit the idea well: they store tables and indexes as fixed-size pages, typically 8 kB in PostgreSQL,² and only some of those pages are in play at any moment.

Think of an online shop with 180 GB on disk. Most of it is history: old orders, an audit log nobody reads unless something has gone wrong, index entries for rows nobody looks up. A normal busy hour touches far less: recent orders, the index pages that lead to them, the users and sessions tables. Add those up and you might get 30 GB. That is the working set, and it, not the 180 GB, is the number to size memory against. It follows the application: a feature that shows customers their full history makes the history hot.

A page in memory costs a memory access. A page that is not makes the query wait for the disk, slower by a wide margin even on NVMe (NVMe vs SATA SSD vs hard disk explains how wide). A query that touches a hundred pages with three missing spends most of its time on those three, so a working set that does not fit shows up as an idle processor, waiting.

Two caches between a query and the disk

When a PostgreSQL query needs a page, it looks first in shared_buffers, the database's own cache in shared memory. On a miss it asks the kernel for the page, from the query's own process or, for reads PostgreSQL 18 runs asynchronously, from a pool of I/O worker processes (io_method defaults to worker).² Either way the kernel's page cache may still hold a copy, and only if it does not does the disk do any work. The manual says the same of its read counters: they count read() calls to the kernel, and fewer of those reach the disk because the kernel caches too.⁹

MySQL's InnoDB takes a different route. Its buffer pool is the area of main memory where it caches table and index data as it is accessed.¹⁶ On Unix-like systems innodb_flush_method defaults to O_DIRECT where the system supports it, and the manual names avoiding double buffering between the buffer pool and the file system cache as one reason to use it.¹⁹ For sizing purposes, a miss in the buffer pool is a disk read.

That difference is why the two manuals recommend such different memory settings. PostgreSQL relies on the operating system cache and says so,² so it keeps its own cache moderate and leaves most of the remaining memory to the kernel. InnoDB skips that cache, so its pool has to be most of the machine. How the page cache behaves in general, and why little "free" memory is usually healthy, is in How much RAM does a server need?

What a cache throws out matters as much as its size. In InnoDB's buffer pool a newly read page goes in at the midpoint, the head of an old sublist that takes 3/8 of the pool by default, and pages that drift to the tail of that sublist are the ones evicted.¹⁶ A page moves to the young end only if it is touched again after innodb_old_blocks_time, 1000 ms by default, has passed since its first access. A table scan touches each page a few times in quick succession and then never again, so its pages stay in the old sublist and are evicted first.¹⁷ The working set survives the nightly report.

Memory settings: the starting points in the manuals

PostgreSQL: shared_buffers and effective_cache_size

shared_buffers is typically 128 MB out of the box. On a dedicated server with 1 GB of memory or more, the manual suggests starting at 25% of RAM. Some workloads gain from more, but past about 40% the manual doubts it helps, because PostgreSQL leans on the operating system cache as well. A bigger setting usually wants a bigger max_wal_size too, to spread out the writing of changed data.²

effective_cache_size sounds like an allocation and is not one. It tells the planner how much cache one query can expect, counting shared_buffers and the kernel's cache of PostgreSQL's files, and a higher value makes index scans look cheaper. It allocates nothing, reserves nothing in the kernel and only feeds estimates; the default is 4 GB.³ Set it to roughly what the two caches hold together.

work_mem and maintenance_work_mem

work_mem is what one sort or hash table may use before it spills to temporary files on disk; the default is 4 MB. The key word is one. A complex query can have several sorts and hashes running at once, each entitled to that much, and many sessions can do the same at the same moment, so the total can reach many times work_mem. Hash tables get more again: work_mem times hash_mem_multiplier, which defaults to 2.0.²

maintenance_work_mem serves VACUUM, CREATE INDEX and adding foreign keys. It defaults to 64 MB, and since a session runs one of these at a time it can be set far higher than work_mem. Autovacuum is the exception: each of its workers may take that much,² and autovacuum_max_workers defaults to 3.⁴

MySQL: innodb_buffer_pool_size

On MySQL the buffer pool is the number that matters, and on dedicated servers up to 80% of physical memory is often given to it.¹⁶ If MySQL has the machine to itself, the --innodb-dedicated-server option sizes the pool from the memory it detects: the 128 MB default below 1 GB, half of memory from 1 to 4 GB, and 75% of memory above 4 GB. The manual limits the option to a server where MySQL can use all the resources, such as a dedicated VM that runs nothing else.¹⁸

Starting value for shared_buffers on a dedicated PostgreSQL server

25%

Above this, the PostgreSQL manual doubts a bigger shared_buffers helps

40%

InnoDB buffer pool set by innodb_dedicated_server above 4 GB

75%

Default work_mem, for each sort or hash, not each connection

4MB

Connections cost memory, even the idle ones

PostgreSQL starts, or forks, a new server process for every client connection.⁶ max_connections caps how many, typically 100 by default, and some resources, shared memory among them, are sized from it at startup.⁵ Each of those processes can run queries that use work_mem, so max_connections quietly sets the worst case for query memory. MySQL uses a thread per connection, but each still has its own stack and buffers, and a sort buffer when it sorts.²¹

Applications push that number up without anyone deciding to. Each app process keeps its own pool of open connections, so ten processes holding forty each make 400. Almost all of them are idle at any moment. They still count.

Put numbers on it. With work_mem at 32 MB, a report query with one sort and one hash join may use 32 MB for the sort and 64 MB for the hash, 96 MB in all. 400 connections running it at once is 400 × 96 MB = 38,400 MB, or 37.5 GB. Through a pool of 32 server connections it is 32 × 96 MB = 3,072 MB, or 3 GB. Nothing reserves that memory in advance; it is the ceiling the settings allow, and on a busy day the page cache is what gets squeezed.

PostgreSQL's chapter on kernel resources agrees: when the Linux out-of-memory killer ends the database, too many connections may be why, and the fix it suggests is fewer max_connections behind a connection pooler.⁷

Pooling with PgBouncer

PgBouncer sits between the application and PostgreSQL and shares a small set of server connections among many clients. In session pooling a client keeps a server connection for as long as it stays connected, and every PostgreSQL feature works. In transaction pooling it holds one only for the length of a transaction, which is what lets a small pool serve many clients, at the cost of a few session-based features the application must avoid. PgBouncer itself needs about 2 kB per client connection by default.²²

Two of its defaults catch people out: max_client_conn is 100, and default_pool_size, the server connections for each user and database pair, is 20.²³ For 400 clients and a pool of 32, raise both, and set PostgreSQL's max_connections a little above the pool for maintenance jobs and admin sessions.

The pool size is a judgement, not a formula. A running query holds a core; one waiting on a disk read or a lock does not. A pool somewhat larger than the core count, here 32 for 16 cores, keeps the cores busy while some queries wait. A much larger one moves the queue from PgBouncer, where waiting is cheap, into PostgreSQL, where each waiting query holds a process and its memory.

Cores: one query, one core, unless it goes parallel

A query runs inside its connection's process, so an ordinary query uses one core. When a parallel plan is fastest, the planner adds a Gather or Gather Merge node and the session's process asks for background workers to share the work.⁸ max_parallel_workers_per_gather allows 2 per node by default, and work_mem applies to each worker: in the manual's example, a query with 4 workers uses up to five times the processor time, memory and I/O of the same query without them.²

So cores follow the queries running at once, which the pool caps, plus their workers. A processor saturated at the busiest hour while the cache numbers look healthy needs more cores or cheaper queries. One that is mostly idle while queries are slow points at memory and disk: more cores do nothing for a query waiting for a page. On a VPS, shared cores can add one more wait, which vCPU vs CPU core explains.

Disks: the time per operation, not megabytes per second

A database serving traffic asks two things of its disk. One is reading pages that missed both caches: small reads, one page each, scattered across the files, each stalling a query until it arrives. The other is the commit. By default PostgreSQL reports a commit as done only after the transaction's write-ahead log records are flushed to permanent storage, and for short transactions that wait is a major part of the total time.¹³

Both are latency problems, decided by how long one small read or one flush takes and how many the disk serves at once, not by megabytes per second. A working set that misses 500 pages a second asks the disk for 500 × 8 kB = 4,000 kB, about 4 MB a second: trivial throughput, and still 500 times a second that a query stops and waits. Sequential speed matters for backups, restores and bulk loads. NVMe vs SATA SSD vs hard disk compares drives on both counts, and Server backups that actually restore covers backups.

To measure your own disk's flush time, point pg_test_fsync, which ships with PostgreSQL, at a file on the same file system as pg_wal with -f. It reports the average sync time in microseconds for each wal_sync_method. The manual adds that many database servers are not held back by their write-ahead log at all.¹⁴

Measuring whether it fits

PostgreSQL keeps the numbers you need in the pg_stat_database view. blks_hit counts pages PostgreSQL already had in shared_buffers; blks_read counts pages it had to ask the kernel for. The manual is explicit that hits in the operating system's file cache are not part of the first.⁹

$ psql -d shop -c "SELECT blks_hit, blks_read, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct FROM pg_stat_database WHERE datname = 'shop';"  blks_hit   | blks_read | hit_pct-------------+-----------+--------- 48213559810 | 402118344 |   99.17(1 row)

The ratio in the last column is the number everyone quotes, and it needs two corrections. A blks_read is a miss in shared_buffers, not necessarily a disk read: PostgreSQL's I/O statistics cannot tell data fetched from disk from data already in the page cache, and the manual advises reading them alongside the operating system's own tools.⁹ And a ratio hides volume: the counters run from the last statistics reset, possibly months ago, and 99.17% of 48.6 billion look-ups still leaves 402 million reads.

The more useful number is misses per second at your busiest hour. Read blks_read at the start and end of that hour and subtract: a rise of 1,800,000 is 1,800,000 ÷ 3,600 s = 500 reads a second, or 500 × 8 kB, about 4 MB/s. Set that against iostat's rkB/s for the database disk over the same hour. Compare bytes, because the kernel merges and enlarges reads, so request counts do not line up. If the disk read far less, the page cache absorbed the misses: the working set fits in memory, just not in shared_buffers alone. If it read as much or more, the working set is bigger than both caches together.

InnoDB keeps the same pair of counters. Innodb_buffer_pool_read_requests counts logical read requests, and Innodb_buffer_pool_reads counts the requests that missed the pool and went to disk.²⁰ Divide the second by the first for the miss ratio, or subtract two readings an hour apart for misses per second, exactly as above.

$ mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads');"+----------------------------------+-------------+| Variable_name                    | Value       |+----------------------------------+-------------+| Innodb_buffer_pool_read_requests | 91284475012 || Innodb_buffer_pool_reads         | 58112035    |+----------------------------------+-------------+

Here 58,112,035 ÷ 91,284,475,012 is a miss ratio of about 0.064%, which says little on its own. The same counters read an hour apart say whether the disk is doing tens of reads a second or thousands.

Seeing what is in the cache

The pg_buffercache extension shows what is in shared buffers in real time.¹¹ Grouped by table, it shows which relations hold shared_buffers, the part of the working set PostgreSQL caches itself. If an archive table you believed was cold holds a quarter of it, some query is reading that table.

$ psql -d shop -c "CREATE EXTENSION IF NOT EXISTS pg_buffercache;"CREATE EXTENSION$ psql -d shop -c "SELECT c.relname, pg_size_pretty(count(*) * current_setting('block_size')::bigint) AS cached FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database()) GROUP BY c.relname ORDER BY count(*) DESC LIMIT 5;"        relname         | cached------------------------+--------- orders                 | 8192 MB orders_customer_id_idx | 3328 MB sessions               | 2560 MB users                  | 1004 MB orders_pkey            | 704 MB(5 rows)

A worked example: 180 GB of data, 30 GB that matters

Here is the whole method on the online shop from the start of this guide. The facts about the shop are assumptions; the rest is arithmetic on the manuals' rules.

1. 1

   Measure what is on disk

   The size of the whole database, then the biggest tables with their indexes.

2. 2

   Mark what is hot

   The recent slice of tables that grow, measured as a share of their rows; the small tables every request reads; the index pages that lead to both.

3. 3

   Add growth

   Plan for the working set you will have in a year, not the one you have today.

4. 4

   Budget the rest of memory

   Query memory for the pool, maintenance, the operating system. Whatever is left is page cache.

5. 5

   Compare cache with working set

   shared_buffers plus page cache on PostgreSQL, or the buffer pool on InnoDB, has to hold the working set with room to spare.

6. 6

   Measure at the busiest hour

   Misses per second from the database counters, checked against the disk's own read rate.

pg_database_size gives a database's total disk use, and pg_total_relation_size a table's, with its indexes and TOAST data (out-of-line storage for large values).¹⁰

$ psql -d shop -c "SELECT pg_size_pretty(pg_database_size('shop'));" pg_size_pretty---------------- 180 GB(1 row)$ psql -d shop -c "SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 4;"  relname  |  total-----------+--------- orders    | 130 GB audit_log | 46 GB sessions  | 3072 MB users     | 1024 MB(4 rows)

The orders table is 130 GB with its indexes: pg_table_size and pg_indexes_size split it into 110 GB of rows and 20 GB of index.¹⁰ The shop's everyday queries read the last 90 days of orders, so measure what share of the rows fall in that range (outside busy hours: it reads the whole table) and apply it to the table's size.

$ psql -d shop -c "SELECT round(count(*) FILTER (WHERE created_at > now() - interval '90 days')::numeric / count(*), 3) AS hot_share FROM orders;" hot_share-----------     0.164(1 row)

0.164 × 110 GB is about 18 GB of rows, if recent rows are about as wide as old ones. Indexes need more care, because the key decides where recent entries sit. The primary key and the created_at index, 5 GB each, are in time order, so recent orders sit together at one end: 0.164 × 10 GB ≈ 1.6 GB. The 6 GB customer_id index is sorted by customer, so recent orders are scattered through nearly all of it, and the "your orders" page keeps all of it hot. A 4 GB status index serves only back-office reports. That is 1.6 + 6 = 7.6 GB, call it 8 GB: 40% of the index for 16% of the rows. pg_relation_size on each index gives the split for your own tables.

Sessions and users, 4 GB together, are read on nearly every request. The audit log is written all day and read only during investigations. That gives a working set of 18 + 8 + 4 = 30 GB. Allow 25% for a year of growth and the plan is for 30 × 1.25 = 37.5 GB.

Three candidate machines

Budget each the same way: shared_buffers at 25%, query memory for a PgBouncer pool of 32 (3 GB, worked out above), maintenance at 2 GB (512 MB for each of three autovacuum workers and one manual job), and 3 GB assumed for the operating system, PgBouncer and monitoring. What is left is page cache.

The total is an upper bound. A page in shared_buffers usually sits in the page cache as well, because PostgreSQL read it through the kernel, and the manual notes that some data may exist in both places,³ so the room for distinct pages is somewhat lower than the sum. That is one more reason to leave margin.

The 32 GB machine is short before the shop grows at all: 6 GB of the working set, a fifth of it, has nowhere to live and is read from disk, pushed out and read again. Raising shared_buffers would not help, since every gigabyte moved into it comes out of the page cache. The 48 GB machine is the smallest that fits, with 2.5 GB to spare after a year, and the overlap between the caches eats into even that. The 64 GB machine leaves 18.5 GB, which pays for the overlap, growth past the first year and an estimate that turns out low. For a shop that keeps growing that margin is worth buying, and the rest of the example uses 64 GB.

The settings for the 64 GB machine

With shared_buffers at 16 GB, raise max_wal_size from its default too, as the manual advises for larger shared_buffers settings.²

The same shop on MySQL

With --innodb-dedicated-server, the 64 GB machine gets a buffer pool of 64 × 0.75 = 48 GB, comfortably above 37.5 GB. The 48 GB machine gets 48 × 0.75 = 36 GB, enough today and just short in a year, and the 32 GB machine 32 × 0.75 = 24 GB, the same shortfall as PostgreSQL's 24 GB of cache. The option also sets innodb_redo_log_capacity to half the number of logical processors in gigabytes, up to 16 GB, so 16 of them give 16 ÷ 2 = 8 GB.¹⁸

A week later: checking the estimate

After a week in service, run the busiest-hour check. Suppose blks_hit rose by 612 million in that hour and blks_read by 1.8 million: a hit ratio of 612 ÷ 613.8 = 99.7%, and 500 misses a second, about 4 MB/s. If the disk read far less, the page cache is catching those misses and the estimate held. If not, read pg_statio_user_tables at the start and end of the hour and rank the tables by how much heap_blks_read plus idx_blks_read grew: the top of the list is where the misses come from.⁹

Mistakes that cost the most

The habit worth keeping is the measurement: blks_read or Innodb_buffer_pool_reads at the start and end of your busiest hour, the difference divided by 3,600, set against what the disk is actually doing. Watch that number month by month and it tells you the working set has outgrown memory long before anyone complains about slow queries.