Memory Pressure

Memory problems come in two distinct forms, and they need different responses.

Cache pressure — the working set no longer fits, so reads go to storage. Performance degrades gradually and predictably.

Allocation failure — a query or the process tries to allocate more than is available. The outcome is an error, or the kernel killing the process.

Cache pressure

The signal is a falling cache hit ratio and rising read I/O with unchanged query patterns.

-- PostgreSQL
SELECT sum(heap_blks_hit) * 100.0 / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS hit_pct
FROM pg_statio_user_tables;

-- MySQL: reads that missed the buffer pool.
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

INFO memory     -- Redis: used_memory against maxmemory, and evicted_keys

Responses, in order of cost:

1. Reduce the working set. Archive cold data, add partitioning so queries touch less, drop unused indexes that compete for cache. See Data Retention.
2. Improve access patterns. A query that scans is a query that evicts the cache for everyone else.
3. Rebalance the memory split between the engine's own cache and the OS page cache — the right split differs by engine.
4. Add memory.

See Cache Hit Ratio for how to read the numbers correctly.

Allocation failure

The usual causes:

• Per-operation memory multiplied by concurrency. PostgreSQL's work_mem is per sort or hash node, per parallel worker, per query. See Memory Settings.
• A single large aggregation or join. ClickHouse and Elasticsearch both have explicit limits for this reason; use them.
• Unbounded result sets. A SELECT * returning ten million rows consumes memory in the database, the driver and the application.
• Connection count. Each connection has a baseline cost before it does anything.

Set the limits that turn this into an error:

SET max_memory_usage = 10000000000;              -- ClickHouse, per query
SET max_bytes_before_external_group_by = '8G';   -- spill instead of failing

indices.breaker.request.limit: 60%               # Elasticsearch circuit breaker

Swap

In containers

The cgroup memory limit is what the kernel enforces, and page cache counts towards it. Two consequences:

• The engine must be configured from the cgroup limit, not the host's total RAM. Several engines read the host value unless told otherwise.
• Leave headroom for page cache, or file I/O will trigger reclaim and, eventually, the OOM killer.