🔍 登录 注册

PostgreSQL 性能调优实战:从 pg_stat 画像到 work_mem、并行查询与 vacuum 调度

半 半山听雨 2026-11-17 👁 3 ⏱ 5min

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 侧的下限,但"参数诚实反映硬件、改动单变量可归因"这两条纪律不变。调优的终点不是参数表,而是一份带指标的变更史——它让下一次事故排查从猜测变成比对。

读完了?给作者一点支持

本文采用 CC BY 4.0 协议转载 · © 半山听雨

评论(0)

登录 后参与评论。

  • 还没有评论,来抢沙发。

🔗 相关文章

审计人的职业规划地图:事务所、企业内审、监管与自由执业的四条路线

"做了三年审计接下来去哪"是行业论坛上的永恒问题。2026 年的就业市场给出了新的答案坐标:AI 压缩了基础核算与核对类岗位,同时抬高了风险判断、内控体系、数据审计类岗位的身价。本文把审计人的出路整理成四条主路线——事务所纵深、企业内审/风控、监管与准则机构、自由执业/专家证人——每条给进入条件、典型轨迹、5 年收入区间感与适配画像,帮你做一次结构化的自我定位。

1 天前👍 1

AI 正在怎样改变审计工作:一份可落地的工具地图与边界清单

"AI 取代审计师"是标题党,"AI 不改审计流程"是天真的。2026 年的真实图景是:审计的每个环节都出现了成熟度不一的 AI 应用——有的已进四大方法论并受监管审视(分析程序、抽凭),有的仍处试点(风险评价辅助),有的干脆不该用(对管理层意图的判断)。本文按审计流程逐环节给出一份工具地图:能做什么、成熟度如何、边界在哪,以及从业者该怎么上手。

1 天前👍 1

内部审计的转型:从合规检查到持续审计,2026 年的方法论与落地路径

企业内部审计正经历十年来最大的一次定位调整:监管趋严(上市公司内控规范迭代、"十五五"会计规划对行业数字化基建的明确指向)加上 AI 工具成熟,让传统"年度计划 + 现场抽查"模式的价值密度持续下降。领先实践已经给出替代方案:风险导向的动态审计计划 + 持续审计(continuous auditing)+ 咨询角色。本文给企业内审负责人与准备进入内审的事务所人士一套转型方法论:架构怎么搭、数据管道怎么建、异常规则库怎么运营、以及如何向审计委员会证明价值。

1 天前👍 1