排查内存使用率高的问题

PostgreSQL 上的内存问题通常是配置问题。 控制内存的参数协同工作。 该 work_mem 参数适用于每个排序或哈希操作、每个查询以及每个连接。 因此，一个看起来不大的数值，可能会被消耗数百次。 内存指南可帮助你找到哪个乘数是问题。

本指南介绍的症状

• 内存利用率始终很高，或者持续攀升且没有回落。
• 内存耗尽错误，或被 OOM 终止机制终止的后端进程。
• 参数更改后，内存使用量急剧上升。
• 与连接数而非查询量相关的性能下降。

开始之前

启用 PostgreSQL 服务器日志、会话数据和查询存储运行时。 将 pg_qs.query_capture_mode 设置为 TOP 或 ALL。 启用 metrics.collector_database_activity。 请参阅 “使用故障排除指南”。

PostgreSQL 的内存实际上都去了哪里

了解这四类用户后，这些选项卡的含义就不言自明了，因为每个选项卡都对应其中一类：

那些乘数型因素、work_mem 以及每个连接的开销，几乎是所有意外情况的根源。

本指南的组织方式

演练指南：调优变更后内存持续增长

1. 请注意该横幅。 本指南报告 检测到内存参数更改，这意味着一个或多个内存参数高于其默认值。 此信息会立即更改调查的重点：工作负荷前的可疑配置。

2. 确认时间安排。 内存选项卡显示，内存利用率是逐步上升并保持在该水平，而不是突然飙升后再回落。 持续存在的阶跃式变化通常表明是配置变更，而不是流量事件。

3. 排除工作负荷。工作负荷 显示跨步骤保持不变的元组活动。 服务器未执行更多操作;每个工作单元现在花费更多的内存。

4. 检查乘数。用户连接 显示大约 400 个活动连接。 内存参数 显示 work_mem 已提升到 64 MB。 把这两个数字结合起来，结论就是：400 个连接，每个连接都可能运行多种排序或哈希操作，而且每种操作各占用 64 MB，这比表面上的标称值所显示的资源投入大得多。

5. 双方采取行动。 你将 work_mem 下调回接近安全基线的水平，并针对少数确实需要更高值的报表查询按角色分别进行设置。 此外，你引入 PgBouncer 来减少连接数，从而同时降低乘数和每个连接的开销。

通用原则是：对于内存，解读一个参数值时，一定要结合它可同时并发应用的次数一起看。

选项卡参考

Memory

确认症状和对应的窗口。 区分两个形状：

• 持续存在的阶跃变化表明是配置问题。
• 恢复的峰值表明存在特定查询或工作负载事件。

工作量

读取元组活动与写入元组活动。 随着内存一同增加的，是实实在在的额外工作量。 当内存攀升时，平面意味着每个工作单元变得更加昂贵，或者某些内容不会释放内存。

Sessions

来自采样数据 pg_stat_activity 的长时间运行会话：

• 连接持续时间（collection_time - backend_start）：使用连接池时，预期会较高。
• 查询持续时间 （collection_time - query_start）：应保持在正常范围内。

长时间运行单个查询的会话，可能会在排序或哈希操作中占用大量内存，并且直到查询结束才会释放。

当会话处于空闲状态且连接时长超过阈值时，本指南会显示检测到长时间空闲会话横幅提示。 空闲会话仍会占用各自连接专属的内存，因此，大量这类会话即使不执行任何实际工作，也会抬高基础内存占用。 确定并结束过时的会话，查看使会话保持打开状态的应用程序逻辑，并设置 idle_session_timeout （PostgreSQL 14 及更高版本）。

立即缓解。 请先取消当前正在运行的语句。 这是两个选项中干扰较小的一个，因为会话及其事务都得以保留：

SELECT pg_cancel_backend(<pid>);

如果会话处于事务空闲状态，或者取消操作也无法释放资源，请终止整个后端进程：

SELECT pg_terminate_backend(<pid>);

设置防护措施，防止再次发生。 设置会话和事务可以运行多长时间的限制：

Queries

按 数据用量对查询进行排名，即窗口内 shared_blks_hit 和 shared_blks_dirtied 的总和。

选择一个查询 ID 以查看每个存储桶的详细信息：平均行数和总行数、平均/最小/最大数据使用量以及执行时长。 数据读取量高但返回行数少的查询，其读取的数据远多于实际返回的数据，通常是由于缺少索引或谓词条件过于宽泛。

1. 运行 索引优化 ，根据实际工作负荷获取索引建议。 此步骤是最有效的长期修复。
2. 对该查询运行 EXPLAIN (ANALYZE, BUFFERS)，查看时间和 I/O 实际消耗在何处。 在大型表上查找顺序扫描、针对大行计数的嵌套循环，以及对溢出到磁盘的排序。
3. 减小表臃肿。 作为一次性步骤，运行 VACUUM (ANALYZE, VERBOSE) <table_name>;。 如果臃肿问题反复出现，根源在上游。 请参阅 监控 autovacuum。
4. 添加计划所需的索引，并删除不缩小结果的联接。
5. 对持续热的大型表进行分区。
6. 重新审视表设计：为子表中的外键创建索引，删除未使用的索引（它们会给每次写入带来开销），并在批量加载期间禁用触发器。

具体就内存而言，请有针对性地调优 shared_buffers、work_mem、maintenance_work_mem、max_locks_per_transaction 和 max_parallel_workers_per_gather。 每项设置都会增加内存消耗，而并行工作进程会使每个查询的内存消耗成倍增加。 如果工作负荷确实占用大量内存，则从 “常规用途 ”迁移到 “内存优化 ”时，每个 vCore 的内存比在同一层中纵向扩展更多。

用户连接

按状态和持续时间进行连接： 短 （低于 1 秒）、 正常 （1 秒到 20 分钟）、 长 （超过 20 分钟），派生自日志断开连接消息。

连接计数是内存乘数，因此此选项卡通常比它看起来更重要。 使用 statement_timeout、idle_in_transaction_session_timeout 和 idle_session_timeout 限制空闲会话（PostgreSQL 14 及更高版本）。

每个 PostgreSQL 连接都是一个单独的操作系统进程，具有自己的内存。 即使在执行任何一条查询之前，数以百计的多数处于空闲状态的连接也会实实在在地占用 CPU 和内存资源，因此使用连接池通常比纵向扩展更划算。 Azure Database for PostgreSQL灵活服务器中的 PgBouncer 内置于Azure Database for PostgreSQL灵活服务器中。 使用 pgbouncer.enabled 启用它，并启用 metrics.pgbouncer_diagnostics 以获取其指标。 两者都是动态的，无需重启。 PgBouncer 不受 可突发 层级支持。

调整池的大小：

两种值得识别的故障模式：

• 资源池耗尽。 活动服务器连接数保持在 default_pool_size，而等待中的客户端数量则在上升。 提高 default_pool_size、优化占用连接的慢查询，或将模式从 session 切换为 transaction。
• PgBouncer 饱和度。 PgBouncer 是单线程的，因此无论服务器有多大，它都使用一个核心。 其典型特征是：6432 端口上的延迟比直接连接到 5432 更高，同时等待的客户端不断增加，而活跃的服务器连接数则保持在较低水平default_pool_size。 在单独的 VM 上跨多个 PgBouncer 实例横向扩展，或评估多线程池程序，例如 PgCat。

内存参数

当前值、默认值和已修改的内容。

如何阅读：

• 保留在其 PostgreSQL 默认值处的参数不显示任何风险指示器。 仅对修改后的值进行求值。
• 建议的值派生自 SKU 的内存和当前 max_connections值，并且从不建议低于 PostgreSQL 默认值。
• 如果无法识别 SKU，则基于比率的建议显示为 “未知 ”，并且仅应用绝对规则。
• 值为服务器级别。 通过 SET 或 ALTER ROLE ... SET 按会话或按角色设置的覆盖项不会显示，因此此处看起来正常的值并不能保证就是实际生效的配置。

优秀的标准是什么样的

本指南无法告诉你的内容

• 每个后端的实际驻留内存。 数据使用量衡量的是缓冲池的工作情况，而不是已占用的字节数。
• 给定会话具有哪些有效设置。 仅显示服务器级值。
• 特定排序或哈希溢出。 使用 EXPLAIN (ANALYZE, BUFFERS)，请参阅 排查高临时文件利用率 问题，以便进行溢出分析。
• OOM 是否是 PostgreSQL 的错误。 扩展和后台进程也会消耗内存。
• 子采样事件。 会话数据是采样得到的；因此，短暂的分配峰值可能会被遗漏。

相关内容

• 使用Azure Database for PostgreSQL灵活服务器中的故障排除指南
• 故障排除指南的遥测参考
• 排查高临时文件利用率问题
• Azure Database for PostgreSQL 的灵活服务器中的计算选项
• Azure Database for PostgreSQL 灵活服务器中的 PgBouncer