PostgreSQL 异常：pg_stat_statements.max 设置过大导致的问题PostgreSQL 异常：pg_stat_statements.max 设置过大导致的问题

文章目录

• • 前言
  • 【生产环境重要提示】先读这一节
  • 一、pg_stat_statements.max 是什么
  • 二、max 与共享内存的关系（核心机制）
  • • 2.1 共享内存按 max 预分配
    • 2.2 查询文本存在外部文件，不在共享内存
    • 2.3 改了 max 必须重启
  • 三、设置过大导致的异常
  • • 3.1 异常一：启动失败 / 共享内存无法分配
    • 3.2 异常二：query 列变 NULL（查询文本被丢弃）
    • 3.3 异常三：监控视图变慢、甚至拖垮整库
    • • 场景一：聚合统计 SQL 本身很慢、占用内存高
      • 场景二：这条 SQL 在跑时整库都卡，连 pg_class 都卡
    • 3.4 误区：dealloc 高不是"太大"，而是"太小"
  • 四、诊断
  • 五、处理方式
  • • 5.1 调小 max 并重启
    • 5.2 处理 query 列变 NULL
    • 5.3 关闭 save 以减小启停开销（权衡）
    • 5.4 极端情况：卸载模块
    • 5.5 监控工具改用 showtext=false
  • 六、合理取值建议
  • 七、生产环境重要提示
  • 八、总结
  • 参考资料

前言

pg_stat_statements 是 PostgreSQL 最常用的 SQL 统计插件，几乎所有线上库都会开启。其中 pg_stat_statements.max 控制"最多跟踪多少条不同语句"。很多同学为了让统计"不丢"、监控"全量"，顺手把这个值调到几百万、几千万甚至上亿，结果触发了一连串异常：数据库起不来、监控里 query 列变成 NULL、启停变慢、共享内存膨胀。

本文说明为什么 pg_stat_statements.max 不能盲目调大，过大时会出现哪些异常、如何诊断和处理。配套的安装使用说明见 PostgreSQL插件-pg_stat_statements-安装和使用。

本文以 PostgreSQL 16 / 17 的 pg_stat_statements 为准，其他版本行为可能有差异。

【生产环境重要提示】先读这一节

一、pg_stat_statements.max 是什么

pg_stat_statements.max 是模块跟踪的最大语句条数，也就是 pg_stat_statements 视图里最多有多少行。当实际观察到的不同语句数超过该值时，最少使用的条目会被淘汰。

• 默认值：5000
• 取值范围：下限 100，上限约 1073741823（以 pg_settings 实际 min_val / max_val 为准）
• 上下文：postmaster，只能在服务启动时设置
• 必须配合 shared_preload_libraries = 'pg_stat_statements' 加载，且 compute_query_id 开启（auto 或 on）

查看当前值、取值范围、上下文以及是否有"待重启"的变更：

SELECT name, setting, min_val, max_val, context, pending_restart
FROM pg_settings
WHERE name = 'pg_stat_statements.max';

其中 pending_restart = true 表示配置文件已改、但尚未重启生效——这对判断"我改了为什么没起作用"非常关键。

二、max 与共享内存的关系（核心机制）

理解下面三点，就能解释"为什么调大 max 会出问题"。

2.1 共享内存按 max 预分配

官方文档明确：模块需要与 pg_stat_statements.max 成比例的额外共享内存，并且只要加载了模块就会占用，即使 track = none。

关键在于这是"预留"，不是"按需增长"：

• 不是"先少分一点，条目多了再加"；
• 而是"启动时按 max 预留，加载即占用"。

粗略估算，每个统计条目包含大量计数器（调用次数、各类时间、块统计、WAL 统计、min/max 等），单条目在数百字节量级，再加上 hash 表本身的开销。因此：

具体字节数随版本而异，这里只给量级，方便判断风险。重点是：max 调到百万级，启动时就预留数百 MB ~ 数 GB 共享内存，无论你实际有多少不同 SQL。

2.2 查询文本存在外部文件，不在共享内存

代表性查询文本保存在外部磁盘文件中（源码中为数据目录下的 pg_stat_tmp/pgss_query_texts.stat），不占用共享内存。所以单条 SQL 文本再长也能存下来。但官方也指出：

如果累积了大量长查询文本，外部文件可能增长到不可控；此时 pg_stat_statements 可能选择丢弃查询文本，视图中所有条目的 query 列会变为 NULL，但各 queryid 对应的统计仍保留。发生这种情况应减小 pg_stat_statements.max。

也就是说，max 越大，能积累的长文本越多，外部文件越容易膨胀，越容易触发"query 变 NULL"的自保护。

2.3 改了 max 必须重启

pg_stat_statements.max 的 context 是 postmaster：

• reload / pg_ctl reload 不会生效；
• SET 也不可改；
• 改完必须 pg_ctl restart（或等效方式）重启实例。

pg_settings.pending_restart 字段可用于检测"配置已改、待重启"的状态。

三、设置过大导致的异常

3.1 异常一：启动失败 / 共享内存无法分配

现象： 把 pg_stat_statements.max 调大后重启，postmaster 起不来，日志里出现内存分配失败。

原因： 启动时按 max 预留的共享内存，叠加 PostgreSQL 自身（shared_buffers 等）以及其他扩展的共享内存需求，超过了操作系统可用内存或共享内存限制。

可能出现的错误（不同版本 / 平台措辞不同，示例）：

FATAL: could not map anonymous shared memory: … Cannot allocate memory
FATAL: out of memory

后果： 数据库无法启动，整个实例不可用，业务全部中断。这是 max 过大最严重的后果。

3.2 异常二：query 列变 NULL（查询文本被丢弃）

现象： 查询 pg_stat_statements 时，query 字段为 NULL，但 calls、total_exec_time 等统计仍然存在。

原因： 外部查询文本文件膨胀到不可控，触发了模块的自保护：丢弃查询文本，只保留统计。max 越大，越容易积累大量长文本，越容易触发。

— 检查是否有条目 query 为 NULL
SELECT count(*) FILTER (WHERE query IS NULL) AS null_query_count,
count(*) AS total
FROM pg_stat_statements;

后果： 监控和排障看不到 SQL 文本，难以定位慢查询和异常 SQL。

3.3 异常三：监控视图变慢、甚至拖垮整库

基础表现：

• 条目数越多，扫描 pg_stat_statements 视图越慢，DBA 工具和监控拉取会变慢；
• pg_stat_statements.save = on（默认）时，统计在关机时落盘、启动时加载；max 大、条目多时，持久化文件变大、正常启停变慢，异常关机后恢复也慢。

下面是两个真实场景。

场景一：聚合统计 SQL 本身很慢、占用内存高

监控工具常跑这种"只看总量、不看文本"的聚合：

SELECT coalesce(sum(calls),0) AS total,
coalesce(sum(total_time),0) AS total_time
FROM pg_stat_statements;

为什么慢、占内存：

所以"内存会不会很大"取决于查询文本文件有多大，而不是 max 本身：文件小就还好，文件大（条目多、SQL 文本长）时，单次查询就要 palloc 一块同等大小的 buffer。

修复：聚合根本不需要 query 文本，直接调函数并传 false：

— PG13+ 列名是 total_exec_time
SELECT coalesce(sum(calls),0) AS total,
coalesce(sum(total_exec_time),0) AS total_time
FROM pg_stat_statements(false);

— PG12 及更早列名才是 total_time
— SELECT coalesce(sum(calls),0) AS total,
— coalesce(sum(total_time),0) AS total_time
— FROM pg_stat_statements(false);

showtext=false 让 query 列返回 NULL，跳过读取查询文本文件，秒级返回。这是官方为"只要统计、不要文本"设计的用法。即使如此，若 N 仍是百万级，扫描 + 物化仍有成本，但远小于带文本。

注意：PG13 起 total_time 被拆分为 total_plan_time / total_exec_time；如果你的 SQL 还用 total_time，要么是 PG12 及更早，要么需要在 PG13+ 改成 total_exec_time。

场景二：这条 SQL 在跑时整库都卡，连 pg_class 都卡

更隐蔽的现象：这条 SQL 自身 CPU 不满、内存不高，但它执行期间整库变卡，连 select * from pg_class 都很久才出结果；SQL 结束后立刻恢复。

这不是 CPU 瓶颈，而是**“大量 I/O + 长扫描持锁”**叠加的结果。先看源码里的两个关键事实（以 PG16 为例）：

• 查询视图时，函数会先调用 qtext_load_file() 把整个查询文本文件读进当前 backend 的本地内存（按文件大小 palloc 一块 buffer），然后对共享 hash 表做全量扫描；
• 扫描期间一直持有 pgss->lock 的共享锁。源码注释原话是：“With a large hash table, we might be holding the lock rather longer than one could wish. However, this only blocks creation of new hash table entries…”（hash 表很大时持锁时间可能偏长，但这只会阻塞新建条目）。

因此卡顿来自两部分：

注意：PostgreSQL 的 LWLock 允许共享锁"插队"（shared barging），不会因为有人在等排他锁就阻塞后续共享请求。所以"更新已有条目也被卡"的说法并不成立，真正被卡的是新建条目。

卡顿时抓证据：

SELECT pid, state, wait_event_type, wait_event, left(query,80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

重点看：

• 大量 wait_event_type = 'IO' → 说明是 I/O 饱和（对应主因）；
• 部分会话 wait_event_type = 'LWLock' → 说明卡在新建条目（对应放大器）；
• OS 层面用 top 看该进程是否为 D（不可中断 I/O 等待），iostat -x 1 看磁盘 %util 是否打满，iotop / pidstat -d 看哪个 backend 在大量读写；
• 查看 pg_stat_tmp/pgss_query_texts.stat 和 pgsql_tmp* 临时文件的大小。

修复同场景一：用 showtext=false 让这条 SQL 不再读查询文本文件、大幅缩短执行时间和持锁时间，整库卡顿随之消失。若工具 SQL 不能改，就从数据侧控制：调小 max 并重启、定期 pg_stat_statements_reset()、避免拼接超长 SQL（用绑定变量让条目更易归并）。

3.4 误区：dealloc 高不是"太大"，而是"太小"

pg_stat_statements_info.dealloc 表示"因为不同语句数超过 max 而淘汰条目"的次数。

• dealloc 持续增长 → 说明 max 偏小，需要调大；
• 这与"max 过大"是相反的方向，千万别看到 dealloc 高就再去减小 max。

四、诊断

— 1. 查看 max 取值范围、上下文、是否待重启
SELECT name, setting, min_val, max_val, context, pending_restart
FROM pg_settings
WHERE name = 'pg_stat_statements.max';

— 2. 实际条目数 vs max
SELECT count(*) AS entries,
(SELECT setting::bigint
FROM pg_settings
WHERE name = 'pg_stat_statements.max') AS max
FROM pg_stat_statements;

— 3. 是否在频繁淘汰（判断是否偏小）
SELECT dealloc, stats_reset
FROM pg_stat_statements_info;

— 4. 是否发生查询文本丢弃
SELECT count(*) FILTER (WHERE query IS NULL) AS null_query_count,
count(*) AS total
FROM pg_stat_statements;

操作系统层面：

• 共享内存占用：观察 postmaster 进程的 RSS / 内存映射（如 pmap <postmaster_pid> 或 ps、top）。PostgreSQL 9.3 起默认使用 mmap 匿名共享内存，ipcs -m 通常看不到，以 pmap 和进程 RSS 为准。
• 数据目录下与 pg_stat_statements 相关的文件大小：查询文本文件 pg_stat_tmp/pgss_query_texts.stat，以及 save = on 时的统计落盘文件 pg_stat_statements.stat。

五、处理方式

5.1 调小 max 并重启

# postgresql.conf
pg_stat_statements.max = 10000

pg_ctl restart

重启后预留共享内存下降，启动失败的问题即可解除。

5.2 处理 query 列变 NULL

• 减小 pg_stat_statements.max（官方建议），重启；
• 必要时执行 pg_stat_statements_reset() 清空统计重新开始；
• 治本：减少超长 SQL 文本来源，避免拼接超长 SQL，使用参数化 / 绑定变量，让 SQL 更容易归并。

5.3 关闭 save 以减小启停开销（权衡）

如果启停明显变慢且不需要跨重启保留统计，可设：

pg_stat_statements.save = off

权衡：重启后统计清零，历史趋势会断。该参数是 SIGHUP 级参数，改完执行 pg_ctl reload 即可生效，不需要重启实例；但它的效果体现在"关机时是否保存"，所以对当前运行中的统计没有即时影响。

5.4 极端情况：卸载模块

从 shared_preload_libraries 移除 pg_stat_statements 并重启，彻底释放其预留的共享内存；代价是失去 SQL 统计能力，仅作为应急手段。

5.5 监控工具改用 showtext=false

监控 / DBA 工具如果只是聚合统计、看 top SQL 的指标，而不需要 query 文本，应直接调用函数并传 false，避免读查询文本文件、避免长时间持锁拖垮整库：

— 例：只取总量
SELECT coalesce(sum(calls),0) AS total,
coalesce(sum(total_exec_time),0) AS total_time
FROM pg_stat_statements(false);

需要看文本时，再单独按 queryid 取（工具可自行缓存首次取到的文本，这正是 pg_stat_statements 自身的做法）。频繁轮询的监控尤其要这样改。

六、合理取值建议

• 默认 5000 适合多数中小型库；
• 高并发、多租户、动态 SQL 较多的库：1 万 ~ 10 万比较常见；
• 不要超过实际需求量级；监控 entries 和 dealloc 动态调整；
• 设百万、千万、上亿没有收益，只有风险；
• 经验做法：先设 10000，观察条目数是否接近 max 或 dealloc 是否增长，再适度上调，不要一上来就百万。

七、生产环境重要提示

• pg_stat_statements.max 是 postmaster 参数，修改必须重启；reload / SET 无效，用 pg_settings.pending_restart 检测待重启状态。
• 共享内存"加载即预留"，即使 track = none 也会占用；不要以为关掉 track 就能省内存，要省内存只能调小 max 并重启，或卸载模块。
• 调大前评估操作系统可用内存，避免"改完起不来"；先在测试环境验证启动与内存占用。
• max 上限理论约 10 亿，但绝不可设这么大，几百万以上就应警惕。
• save = on（默认）时，大 max 会让启停变慢、持久化文件变大。
• 变更流程：备份 postgresql.conf、确认重启窗口、检查主从一致性、低峰期操作、验证重启后监控正常。
• 处理 query 变 NULL 时，方向是减小 max；处理 dealloc 高时，方向是调大 max，两者相反，别弄反。
• 查视图（showtext=true）会读查询文本文件、长时间持锁，可能拖垮整库；监控 / DBA 工具只要统计不要文本时一律用 pg_stat_statements(false)。

八、总结

一句话：max 不是越大越好，它是启动时按值预留的共享内存；想省内存就别拍脑袋往大里填。

参考资料

• PostgreSQL 17 文档 — F.30. pg_stat_statements：https://www.postgresql.org/docs/17/pgstatstatements.html
• PostgreSQL 17 文档 — 52.24. pg_settings 视图：https://www.postgresql.org/docs/17/view-pg-settings.html
• PostgreSQL 16 文档 — F.32. pg_stat_statements：https://www.postgresql.org/docs/16/pgstatstatements.html
• pg_stat_statements 源码 entry_dealloc：https://github.com/postgres/postgres/blob/master/contrib/pg_stat_statements/pg_stat_statements.c