PostgreSQL 性能调优实战:从 pg_stat 画像到 work_mem、并行查询与 vacuum 调度
PostgreSQL 的默认参数是"安全保守"而非"最优",一台 64GB 内存的服务器跑着为 VPS 设计的配置是常态。PG 18 又加入了 AIO 等新变量,调优面进一步变化。本文给一套以观测数据驱动的 PG 调优流程:先画像、再动参数(shared_buffers/work_mem/并行度)、最后治理 vacuum——每一步都有验证指标,不做玄学调参。
一、先拿三张画像表
-- 1) 缓存命中与 IO 分布(PG16+ pg_stat_io 按操作细分)
SELECT backend_type, context, read_source,
(read_bytes+write_bytes)/1e9 AS gb
FROM pg_stat_io ORDER BY read_bytes DESC LIMIT 15;
-- read_source: shared_buffers / local / readahead / stream —— 判断 IO 从哪来
-- 2) 等待事件:数据库在等什么
SELECT event_type, event, count(*) FROM pg_stat_activity
WHERE state='active' GROUP BY 1,2 ORDER BY 3 DESC;
-- Lock/LWLock/IO/DataFileRead 的占比决定优化方向
-- 3) 表级健康度:死元组、自动 vacuum 次数
SELECT relname, n_live_tup, n_dead_tup,
last_autovacuum, autovacuum_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
决策树很直接:等待事件以 IO 为主 → 内存与并行/扫描策略问题;Lock/LWLock 多 → 事务与索引设计问题;CPU(无等待、active 高) → 查询计划与 work_mem 问题。
二、内存三件套:shared_buffers、effective_cache_size、work_mem
shared_buffers = 16GB # 物理内存的 25%(专用 DB 机);不是越大越好
effective_cache_size = 48GB # 给优化器的"OS cache+SHM 可用总量"提示,不实际分配
work_mem = 64MB # 每排序/哈希节点上限!乘并发数才是真实内存
maintenance_work_mem = 2GB # vacuum/create index/restore 用,可激进
work_mem 是最常被调错的一个:它是每个查询里每个排序/哈希节点的上限,复杂查询可能同时开多个节点。设太大的后果是并发高峰 OOM;调优方法是抓真实溢出证据:
SELECT query, temp_files, temp_bytes FROM pg_stat_database WHERE datname='okgoes';
-- temp_bytes 持续增长 = work_mem 不足,排序落盘;逐步翻倍直到 temp 归零
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- "Sort Method: external merge" 即单查询证据
PG 18 的 AIO 让顺序读更高效,但随机 IO 减少仍靠 effective_cache_size 引导优化器选对索引扫描——这个提示值要诚实反映 OS page cache 规模(innodb_buffer_pool 思维在 PG 里对应"OS 也参与缓存")。
三、并行查询:max_parallel_workers_per_gather 的边界
max_parallel_workers_per_gather = 4 # OLAP 混合负载可 6~8
parallel_tuple_cost / parallel_setup_cost # 默认值偏保守,小表并行反而慢
min_parallel_table_scan_size = 128MB # 低于此不并行;按需调低
判断该不该并行看执行计划里的 Workers Planned/Launched。三类查询并行收益大:大表顺序扫描聚合、大排序、哈希 join。三类无效甚至有害:短点查(setup 开销)、高并发小查询(worker 池被抽干导致排队)、写路径。
混合负载系统的稳妥配置:OLTP 连接池限死 max_parallel_workers_per_gather=0(按角色设:ALTER ROLE app_oltp SET max_parallel_workers_per_gather = 0;),报表角色单独放开——PG 的 GUC 支持 role/database/session 三级覆盖,这是它比 MySQL 参数管理强的地方。
四、vacuum/autovacuum:防膨胀的调度工程
死元组是 MVCC 的账单。默认 autovacuum 阈值(deadlock 50M + 20%)对大表太迟钝,对小表又可能过频:
autovacuum_vacuum_scale_factor = 0.05 # 全局降到 5%
autovacuum_vacuum_cost_delay = 0 # SSD 上 throttle 意义不大(12+ 默认已 2ms→实践中常设 0)
autovacuum_max_workers = 6 # 大库并发 vacuum 需要更多槽位
按表调优(热点表单独策略):
ALTER TABLE articles SET (autovacuum_vacuum_scale_factor=0.01, autovacuum_analyze_scale_factor=0.02);
ALTER TABLE audit_log SET (autovacuum_enabled=false); -- 只追加表用定期手动 VACUUM (PARALLEL 4)
监控三指标:n_dead_tup/n_live_tup 比值、事务 ID 年龄(age(datfrozenxid),逼近 20 亿触发紧急 vacuum)、bloat 估算(pgstattuple 抽样)。XID 年龄告警必须配——冻结风暴是唯一能把 PG 拖到只读的路径。
五、连接与池化:PG 的进程模型决定上限
每个连接一个进程,空闲连接也占内存(~5-10MB)且推高 max_connections 竞争。基线:应用连接池总大小 ≤ CPU 核数 × 2(与 MySQL 篇同结论),超出部分交给 PgBouncer:
; PgBouncer transaction 模式注意三坑:
; 1) prepared statement:PG17+ pgbouncer 支持 server_prepared_statement 透传,老版本要 disable 应用侧 PS
; 2) SET/临时表/LISTEN 会串态——transaction pooling 下禁用
; 3) advisory lock 按连接持有,跨请求逻辑改用表锁或 Redis
六、调优验证闭环
每个参数改动走同一流程:基线采样(pg_stat_statements top-N + pg_stat_io + temp_bytes,≥24h)→ 单参数改动 → 同窗口对比。pg_stat_statements 是核心工具:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT round(total_exec_time) total_ms, calls, mean_exec_time, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
-- 调参后对比同一 queryid 的 mean_exec_time 与 shared_blks_read
拒绝"改了三个参数一起看效果"——归因不清的调优无法沉淀为团队知识。把每轮改动与前后指标写进变更单,一年后就是一份自己系统的性能参数演进史。
小结
PG 调优的正确顺序:先用 pg_stat_io/pg_stat_activity/pg_stat_user_tables 三张画像定位瓶颈类型,再动内存三件套(work_mem 以 temp_bytes 为证据逐步加)、并行度按角色隔离、autovacuum 按表分级并盯 XID 年龄;连接规模用池 + PgBouncer 封顶。PG 18 的 AIO 改变了 IO 侧的下限,但"参数诚实反映硬件、改动单变量可归因"这两条纪律不变。调优的终点不是参数表,而是一份带指标的变更史——它让下一次事故排查从猜测变成比对。
评论(0)
登录 后参与评论。