# 内存预算 LLMS 索引： [llms.txt](/llms.txt) --- PostgreSQL 没有一个叫 `total_memory_limit` 的总开关。 内存来自多个 scope： ```text postmaster shared memory backend private memory contexts sort/hash/memoize operations parallel workers maintenance/autovacuum logical decoding temporary table buffers extension/foreign data wrapper kernel page cache monitor/backup/agent ``` 有的是启动时固定，有的是按需分配，有的是 per operation，有的是 per worker。内存 调优首先是预算模型，其次才是参数值。 ## 27.2.1 shared buffers、操作系统页缓存与双重缓存 {#item-27-2-1} ### PostgreSQL 不是绕过 OS 的单一 cache 普通 buffered I/O 路径近似： ```text query -> PostgreSQL shared_buffers -> read()/write() -> kernel page cache -> filesystem/device ``` 同一个 relation block 可能同时存在 shared buffers 和 OS page cache。它不是简单的 “浪费两份”： - shared buffers 提供 PostgreSQL page、pin、lock、dirty 与 WAL 协调； - OS cache 服务 filesystem read/write、readahead 和其他进程； - checkpoint/background writer 把 dirty shared buffer 写给 kernel； - kernel 决定何时 writeback 到设备； - WAL 有自己的 buffer 与 flush 路径。 所以： ```text RAM - shared_buffers = OS cache ``` 只是预算近似，不是内核保证。 ### `shared_buffers` 是启动参数 ```sql SELECT name, setting, unit, context, source, pending_restart FROM pg_settings WHERE name = 'shared_buffers'; ``` 参考沙箱： ```text setting 62592 unit 8kB bytes 512,753,664 server RAM 2,048,679,936 ratio about 25% context postmaster source configuration file ``` `context=postmaster` 表示需要 server restart 才能改变 effective value。reload 只能让 `pending_restart=true`，不能扩大正在运行的 shared memory。 PostgreSQL 官方 [`Resource Consumption`](https://www.postgresql.org/docs/18/runtime-config-resource.html) 把 dedicated server 的约 25% 视为常见起点，并指出通常超过 RAM 40% 不太可能优于 较小值，因为系统仍依赖 OS cache。这是 starting heuristic，不是 capacity theorem。 ### 为什么不是越大越好 增加 shared buffers 可能： - 提高某些 working set 的 PostgreSQL cache residency； - 减少 shared buffer miss； - 增加启动 shared memory 与 page table； - 压缩 kernel cache、backend private memory 和 agent headroom； - 增加 checkpoint 要管理的 dirty buffer； - 改变 writeback burst； - 延长重启/预热行为； - 在容器/cgroup limit 下增加 OOM 风险。 更大的 shared buffers 通常还要重新评估 `max_wal_size` 与 checkpoint write smoothing。只改 cache、不看 WAL/checkpoint，不是一个完整 factor。 ### cache hit 不等于不读磁盘 `pg_stat_database.blks_hit` 只表示 requested block 已在 PostgreSQL shared buffers； `blks_read` 表示 PostgreSQL 发起读取，不代表物理设备一定读了，因为 OS cache 可能 命中。 ```text PostgreSQL hit -> no filesystem read for that access PostgreSQL read -> may be OS-cache hit or device read ``` 要组合： ```sql SELECT * FROM pg_stat_io ORDER BY backend_type, object, context; ``` 以及： ```text OS read bytes device latency/queue filesystem/cache state query BUFFERS + I/O timing ``` aggregate hit ratio 99.9% 也可能掩盖一条关键 query 每次读取 10 GiB。 ### `effective_cache_size` 不分配内存 参考沙箱： ```text effective_cache_size = 187520 * 8KiB = 1,536,163,840 bytes ≈ 1.43 GiB ``` 它是 planner 对“单条 query 可利用的 shared + kernel cache”的估计，不是： - shared memory allocation； - OS cache reservation； - memory limit； - 当前 cache occupancy。 官方 [`Query Planning`](https://www.postgresql.org/docs/18/runtime-config-query.html) 明确说明它只影响 estimate。把它从 4GB 改到 64GB 不会获得 60GB RAM，只可能让 planner 更相信 index access 的 cache 命中。 ### huge page 与 transparent huge page PostgreSQL 主 shared memory 可以用 explicit huge pages： ```sql SHOW huge_pages; SHOW huge_pages_status; SHOW huge_page_size; ``` `huge_pages=try` 会尝试，失败时回退；`on` 失败会阻止 server 启动。它是 restart parameter，需要同时验证 OS reservation、启动和 failover member。 Linux Transparent Huge Pages 与 explicit huge pages 不同。PostgreSQL 官方当前仍 提示 THP 曾对一些版本/环境造成性能退化，不应把“huge page 有益”推导为“THP 永远 开启”。 ### shared memory inventory ```sql SELECT name, pg_size_pretty(allocated_size) AS allocated, pg_size_pretty(coalesce(free_size, 0)) AS free FROM pg_shmem_allocations ORDER BY allocated_size DESC; ``` 它帮助解释 main shared memory 的用途，但不是 host 总内存视图。还要记录： ```text MemTotal/MemAvailable cgroup memory.max/current/events swap process RSS/PSS kernel slab/page tables backup/exporter/sidecar ``` ## 27.2.2 `work_mem` 按节点、并发和并行放大 {#item-27-2-2} ### `work_mem` 是 operation base limit 不是： ```text per query per transaction per session preallocated fixed reservation ``` 而是 sort/hash 等 query operation 在 spill 前可用的基础限额。官方说明一条复杂 query 可以同时有多个 operation，多 session 又会同时执行，因此总使用量可以是 `work_mem` 的许多倍。 涉及： ```text Sort: ORDER BY / DISTINCT / Merge Join Hash: Hash Join / Hash Aggregate / IN processing Memoize parallel worker copies ``` 对 hash operation： $$ M_{\text{hash limit}} = \text{work\_mem} \times \text{hash\_mem\_multiplier} $$ 默认 `hash_mem_multiplier=2.0` 时，64MB `work_mem` 对一个 hash operation 的 limit 可能是 128MB。 ### 放大公式 第一阶 upper-bound： $$ M_{\text{query work}} \approx \sum_{o \in \text{concurrent operations}} L_o \times P_o $$ 其中： - $L_o$ 是 sort 的 `work_mem` 或 hash 的放大 limit； - $P_o$ 是 leader + parallel workers 中实际拥有该 operation 的 process 数。 workload envelope： $$ M_{\text{workload}} \approx \sum_{q} A_q \times O_q \times P_q \times L_q $$ - $A_q$：同时 active 的该类 query； - $O_q$：可并存的 memory-intensive nodes； - $P_q$：leader/worker 放大； - $L_q$：相应 node limit。 它是 safety estimate，不代表每次都用满；但用： ```text max_connections × work_mem ``` 也不够，因为忽略多个 nodes、hash multiplier 与 parallel workers。 ### 参考沙箱的反例 参考事实： ```text RAM ~1.91 GiB shared_buffers ~489 MiB work_mem 64 MiB max_connections 500 ``` 天真的： $$ 500\times64\text{MiB} = 31.25\text{GiB} $$ 已经远超 RAM。若每个 active query 有两个 hash node，`hash_mem_multiplier=2`： $$ 500\times2\times128\text{MiB} = 125\text{GiB} $$ 这不表示 PostgreSQL 启动就分配 125GiB，也不表示 500 个 connection 会同时跑满 两个 hash；它说明： > `max_connections=500` 与 `work_mem=64MB` 不能同时作为“安全可用上限”理解。 必须用 pool/admission 保证 active envelope，或按 role/query 缩小 `work_mem`。 ### plan nodes 不一定同时达到 peak 把 plan 中所有 Sort/Hash 的 limit 相加可能过度保守，因为父子 node 生命周期可能 错开；也可能低估，因为： - sibling/subplan 同时存在； - executor context 在 node 结束后仍保留一部分； - parallel process 独立； - multiple portals/cursors； - function/extension 额外 memory； - query 同时存在于多个 session。 方法： 1. 从 `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` 找真实 node、batch、disk； 2. 用 concurrency trace 找同时 active 的 query class； 3. 在 staging 观测 process/container memory； 4. 注入 worst credible concurrency； 5. 留 allocator、kernel 和 forecast headroom。 ### 看 sort 与 hash 是否真的 spill ```sql EXPLAIN ( ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT TEXT ) SELECT ...; ``` 关注： ```text Sort Method: quicksort Memory: ... Sort Method: external merge Disk: ... Hash: Buckets / Batches / Memory Usage Buffers: temp read/written ``` 累计旁证： ```sql SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes FROM pg_stat_database ORDER BY temp_bytes DESC; ``` 以及 `pg_stat_statements.temp_blks_read/written`。注意累计 delta 与 reset/start time。 ### `temp_bytes=0` 的正确结论 第 26 章六个 cell 的 temp bytes 都为零。它支持： ```text do not raise work_mem to fix observed spill ``` 不支持： ```text 64MB is globally optimal all application queries never spill work_mem can safely be lowered globally ``` 压测 mix 没覆盖分析、报表、DDL 和 tenant worst case。 ### 用 scope 区分 OLTP 与分析 不要因为一条月报需要 512MB，把全局 default 改成 512MB： ```sql BEGIN; SET LOCAL work_mem = '512MB'; SET LOCAL statement_timeout = '15min'; SELECT ... monthly report ...; COMMIT; ``` 或 dedicated role/database： ```sql ALTER ROLE dbuser_report IN DATABASE analytics SET work_mem = '256MB'; ``` 新 session 才获得 role/database default。connection pool 中已有 server connection 不会立即更新；transaction pooling 还要验证 pool 对 session state 的处理。 ### `temp_file_limit` 是 guard，不是内存预算 `temp_file_limit` 限制一个 process 可使用的 temp file 空间。它能阻止失控 query 写满磁盘，但： - 超限会 cancel query； - parallel worker/process 语义需测试； - 不能防 OOM； - 不能代替 statement/admission limit； - 太小会杀死合法维护或分析。 把它作为 per-role safety policy，并演练错误处理。 ## 27.2.3 maintenance、autovacuum 与后台进程内存 {#item-27-2-3} ### maintenance 不是一个 session `maintenance_work_mem` 服务： ```text VACUUM CREATE INDEX ALTER TABLE ADD FOREIGN KEY some maintenance operations ``` 单个 session 通常一次只有一个相关 operation，因此它常可高于 `work_mem`；但 cluster 可能同时有： ```text manual CREATE INDEX autovacuum workers restore-created indexes reindex jobs multiple databases/tenants ``` 预算要按并发 maintenance job。 ### autovacuum 放大 若： ```text autovacuum_work_mem = -1 ``` 每个 autovacuum worker 使用 `maintenance_work_mem` 作为上限。第一阶预算： $$ M_{\text{autovacuum}} \le \text{autovacuum\_max\_workers} \times \text{effective autovacuum work mem} $$ 还没包含 worker base RSS、shared buffer 与 extension。 参考沙箱： ```text maintenance_work_mem 125952 kB ≈ 123 MiB autovacuum_work_mem -1 ``` 如果 3 个 worker 同时接近上限，仅这一项约 369MiB。不能因为“一次 VACUUM 很安全” 就忽略多 worker。 ### parallel maintenance 的语义不同 并行 query 通常按 process 应用 `work_mem`，而 parallel utility command 的 `maintenance_work_mem` 一般作用于整个 command，不简单按 worker 倍增；但 worker 仍消耗 CPU/I/O 与其他私有内存。PostgreSQL 官方明确区分这两种策略。 因此不要把： ```text maintenance_work_mem × max_parallel_maintenance_workers ``` 机械当作精确值，也不要假设 parallel maintenance 没有额外资源。 ### 其他后台预算 | 组件 | 参数/上限 | 风险 | |---|---|---| | logical decoding | `logical_decoding_work_mem` × decoder | spill/WAL retention | | temp table | `temp_buffers` per session, on demand | many sessions | | WAL buffers | `wal_buffers` shared | start-time/shared | | worker process | `max_worker_processes` | extensions + parallel | | prepared xact | `max_prepared_transactions` shared structures | lock/WAL lifetime | | replication | sender/receiver/plugin | queue/buffer/plugin | | backup | pgBackRest process/buffer/compression | DB 外 host memory | | monitoring | exporter/query | DB 外或 backend memory | PostgreSQL 参数表只覆盖 server 进程，不覆盖： ```text Patroni HAProxy PgBouncer pgBackRest Prometheus exporters node agents kernel cache SSH/Ansible ``` Pigsty 节点的 host memory budget 必须把整个平台加进去。 ### backend memory context 当前 backend： ```sql SELECT name, type, path, total_bytes, free_bytes, used_bytes FROM pg_backend_memory_contexts ORDER BY total_bytes DESC LIMIT 30; ``` 它是瞬时、当前 session 视图。不要从一个 idle psql 推导所有 backend。 对另一 backend，可在授权与日志边界下使用 memory context logging function，但输出 进入 server log，可能很大，生产诊断要有采样和数据处理计划。 ## 27.2.4 OOM 风险必须用最坏并发估算 {#item-27-2-4} ### 总预算 一个实用模型： $$ M_{\text{host}} \ge M_{\text{shared}} + M_{\text{active backends}} + M_{\text{query work}} + M_{\text{maintenance}} + M_{\text{replication/extensions}} + M_{\text{platform}} + M_{\text{kernel}} + H $$ $H$ 是 safety headroom。 active backend： $$ M_{\text{active backends}} \approx \sum_q A_q \times (B_q+W_q) $$ - $A_q$ 是 active concurrency，不是 connection count； - $B_q$ 是 backend/query base； - $W_q$ 是 node/worker working memory。 idle backend 也有成本，但不能假设都占满 `work_mem`。 ### credible worst case 不是所有理论 maximum 同时出现，但应至少模拟： ```text peak OLTP + report overlap + all autovacuum workers active + backup compression + one replica rebuilding/catching up + exporter/agent normal load + traffic retry after failover ``` 把不可能重叠的场景排除时，要有 scheduler/admission 证据。例如： ```text report queue concurrency = 2 DDL window blocks reports backup compression jobs = 1 pool active cap = 48 ``` 没有 enforcement 的“我们通常不会同时跑”不算边界。 ### OOM 可能杀谁 Linux OOM/cgroup 可能： - kill 某个 backend； - kill postmaster/Patroni； - kill backup/exporter； - 使 node reclaim/swap 抖动，p99 先失守； - 导致 HA 将压力转移到 replica； - 引发 reconnect storm。 所以 acceptance 不只是“benchmark 没被 kill”。要观察： ```text MemAvailable cgroup memory.current/events PSI memory swap in/out major faults process RSS/PSS OOM/kernel logs HA state and reconnect ``` ### swap 不是免费 headroom 完全禁用或保留少量 swap 取决于平台 policy，但不能把可 swap 空间计作 database working set。发生 swap 前后，应以 latency/SLO 定义安全线；大量 backend memory 被换出后， 系统可能还活着却不可用。 ### pool 是 memory governor PgBouncer/client pool 的关键作用： ```text many logical requests -> bounded active server connections -> bounded active query memory ``` pool size 应从 active workload/resource envelope 推导，而不是等于 `max_connections`。 还要保留： ```text reserved_connections superuser_reserved_connections monitor/replication/maintenance slots emergency access ``` ### 参数变更前的内存表 | 项目 | 当前 | candidate | credible concurrency | upper estimate | evidence | |---|---:|---:|---:|---:|---| | shared memory | | | 1 | | `pg_shmem_allocations` | | OLTP work | | | | | plans + active | | report work | | | | | spill test | | parallel | | | | | workers launched | | autovacuum | | | | | workers | | DDL/restore | | | | | runbook | | platform/kernel | | | 1 host | | OS/Pigsty | | headroom | | | | | failure model | 表填不完整时，不要把 `work_mem` 或 `max_connections` 翻倍。 ### 何时接受 内存 candidate 至少通过： - 目标 spill/batch/latency 明确改善； - representative concurrency 没有 memory pressure； - parallel 与 maintenance overlap 已测； - pool/admission enforcement 可证； - temp disk 与 OOM guard 合理； - N+1/restart/failover 下仍有 headroom； - session/role/global scope 正确； - rollback 不依赖 OOM 后恢复。 内存参数的目标不是“尽可能不 spill”。spill 有成本，OOM 是失效；工程要在二者之间 找到可控边界。 --- [上一节：调优是一套实验方法](../01/) · [返回本章目录](../) · [下一节：WAL、检查点与写入平滑](../03/) · [查看全书目录](/toc/) · [查看索引中心](/indexes/)