View a markdown version of this page

针对 RDS for PostgreSQL 中常见 PostgreSQL 性能问题的初始故障排除 - Amazon Relational Database Service
快速诊断清单表和索引膨胀并行查询资源耗尽高连接数和身份验证压力使用性能详情等待事件进行故障排除Autovacuum 微调相关信息

针对 RDS for PostgreSQL 中常见 PostgreSQL 性能问题的初始故障排除

本指南涵盖了影响 RDS for PostgreSQL 数据库的四种最常见的性能问题：表和索引膨胀、并行查询资源耗尽、高连接和身份验证压力以及 autovacuum 微调。当您遇到性能降级问题时，在开始更深入的调查之前，请使用本指南作为首轮诊断检查清单。

各个部分中描述了您可能观察到的症状，提供用于确认根本原因的诊断查询，并给出具体修复步骤的建议。

了解“没有任何改变”下出现的性能下降

PostgreSQL 工作负载通常会毫无问题地运行数周或数个月，然后性能会突然下降，即使应用程序代码和查询模式没有任何改变。之所以会发生这种情况，是因为数据库环境从来都不是真正静态的：有多种不可见的因素会随着时间的推移而发生变化，并可能触发计划变更或资源争用：

  • 膨胀积累会带来工作负载变化。PostgreSQL 的多版本并发控制（MVCC）会保留旧的行版本，直到 autovacuum 回收这些版本。当无效元组的积累速度超过 autovacuum 处理它们的速度时，表和索引的物理大小就会变大。然后，查询计划程序可能会从高效的索引扫描切换到顺序扫描，因为成本估算值会随着表大小的增加而变化。您的 SQL 没有改变，但计划程序查看的数据已经改变。

  • 新的参数值会造成工作负载变化。对于一个值范围，参数化查询的性能可能良好，但当应用程序开始使用不同的范围时，性能可能会欠佳。PostgreSQL 可能会重用通用执行计划，而其中未考虑到新范围内的数据偏差，或者计划程序的统计信息可能未准确反映这些值的分布。如果同时还存在膨胀，其影响力会放大，一个次优的计划现在会扫描大量的无效数据。

  • 即使运行 autovacuum，统计信息也可能过时。Autovacuum 根据插入或更新的行数触发 ANALYZE，而不是根据数据分布是否发生了重大变化。如果您的应用程序转为查询不同的值范围或时间窗口，则即使最近运行了 autovacuum，计划程序的成本估算也可能不准确。

  • 数据库的总体增长会带来工作负载变化。当表随着时间的推移而增长时，查询必须扫描的数据页面数量也随之增加。即使查询逻辑和索引保持不变，在较小的表上表现良好的查询，随着表大小的增长也可能会随之产生延迟。监控 以跟踪存储增长趋势。

在调查“没有任何改变”下出现的性能下降时，请将膨胀积累、新的参数值范围、数据库的总体增长和过时的统计信息视为最有可能的根本原因。使用本指南中的诊断步骤来确认是哪个因素造成的问题。

有关更多信息，请参阅下列内容：

快速诊断清单

首次调查性能问题时，请使用以下已排序的分类步骤：

  1. 检查 pg_stat_activity。查看连接数、事务中空闲会话和长时间运行的查询。有关更多信息，请参阅 RDS for PostgreSQL 优化的基本概念。

  2. 检查是否有膨胀。寻找 pg_stat_user_tables 中是否有高 n_dead_tup，并考虑使用 pgstattuple 实现精确测量。有关更多信息，请参阅使用 pg_repack 从表中删除膨胀。

  3. 检查 pg_stat_user_tables。寻找高 n_dead_tup 值和过时的 last_autovacuum 时间戳。有关更多信息，请参阅在 Amazon RDS 上使用 PostgreSQL autovacuum。

  4. 查看慢速查询的 EXPLAIN ANALYZE。在大型表上查找并行计划和顺序扫描。有关更多信息，请参阅 RDS for PostgreSQL 中并行查询的最佳实践。

  5. 检查 CloudWatch 和性能详情指标。查看 CPU 利用率、连接计数、IOPS 和可用内存。有关更多信息，请参阅监控 Amazon RDS。有关常见的等待事件和更正操作，请参阅 RDS for PostgreSQL 等待事件。

  6. 检查数据库参数组。检查 max_parallel_workers_per_gather 和 autovacuum 设置。有关更多信息，请参阅在 Amazon RDS 和 Amazon Aurora 上微调 PostgreSQL 参数。

表和索引膨胀

当无效元组在表中的积累速度超过 autovacuum 回收它们的速度时，就会出现表和索引膨胀。随着时间的推移，这会导致查询性能逐渐降低、存储使用量增加和查询计划不理想。

症状

  • 在数周或数月内，查询性能会逐渐下降

  • 尽管数据量保持稳定，但存储使用量仍在增长

  • 由于统计信息过时，查询计划程序选择顺序扫描而不是索引扫描

  • 表格统计信息中出现高 dead_tuple_count

诊断

您可以通过查询系统目录来估算所有表的膨胀。这种方法不需要任何扩展程序：

SELECT schemaname, relname,
       n_dead_tup,
       n_live_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

为了解决膨胀问题，您可以使用 pg_repack 扩展程序，以最少的锁定来重组表和索引。有关更多信息，请参阅使用 pg_repack 从表中删除膨胀和使用 pg_repack 从 Amazon Aurora 和 RDS for PostgreSQL 中删除膨胀。

重要

您不必依靠手动维护，而是确保启用 autovacuum 并根据您的工作负载进行适当微调。有关微调建议，请参阅 Autovacuum 微调。

并行查询资源耗尽

PostgreSQL 可以并行执行查询，来提高大型顺序扫描和聚合的性能。但是，每个并行工作线程都是一个完整的后端进程，会计入 max_worker_processes（以及子限制 max_parallel_workers），并分配自己的 work_mem。包含 4 个并行工作线程的单个查询可能会消耗数百兆字节的内存和大量的 CPU。在高并发度下，过多的并行执行会迅速耗尽 CPU 和内存。

常见症状包括突然的 CPU 峰值、每次查询的内存使用率过高，以及未发生应用程序更改但 CloudWatch 中的 DatabaseConnections 升高。您也可以观察到等待事件，例如 IPC:BgWorkerStartup、IPC:ExecuteGather 和 IPC:ParallelFinish。有关这些等待事件的更多信息，请参阅 IPC:parallel 等待事件。

对于大多数 OLTP 和高并发的生产工作负载，请在数据库参数组中设置 max_parallel_workers_per_gather = 0 来禁用自动并行执行。然后，您可以通过为每个会话或每个角色设置参数，选择性地为特定的分析或报告会话启用并行执行。

有关诊断和控制并行查询行为的详细指导，请参阅 RDS for PostgreSQL 中并行查询的最佳实践。

高连接数和身份验证压力

连接流失（频繁打开和关闭数据库连接而没有入池）会造成身份验证开销，并可能耗尽可用的连接槽。保持打开状态的空闲连接也会占用连接槽而不执行有用的工作。

症状

  • 性能详情监控中的 total_auth_attempts 升高。有关更多信息，请参阅 RDS for PostgreSQL 的非本机计数器。

  • 建立连接的速度缓慢

  • FATAL: too many connections for role 或 remaining connection slots are reserved 错误

  • CPU 峰值与连接流失相关

诊断

运行以下查询以检查当前连接状态：

SELECT
  setting::int AS max_connections,
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_settings
WHERE name = 'max_connections';

相对于 max_connections 过高的 idle 或 idle in transaction 连接数表示连接未正确释放。事务中空闲连接造成的问题尤其严重，因为它们会保持锁定并阻止 autovacuum 回收无效元组。

修复

  • 部署连接池。使用 PgBouncer 或 Amazon RDS 代理来减少与数据库的直接连接数。连接池可重复使用现有连接，而不是为每个请求创建新连接。

  • 设置 idle_in_transaction_session_timeout。当会话在事务中保持空闲的时间超过指定的持续时间时，此参数会自动终止会话。这样可以防止长时间运行的空闲事务保持锁定并阻止 autovacuum。

  • 查看应用程序连接处理方式。确保您的应用程序立即关闭连接，并且保持事务打开的时间不会超过必要的时间。

注意

并行查询工作线程会消耗 CPU 和内存。如果您观察到资源耗尽伴随着并行查询活动出现，请参阅并行查询资源耗尽，获取有关控制并行工作线程使用的指南。

使用性能详情等待事件进行故障排除

性能详情捕获等待事件，这些事件可显示数据库在哪些地方花费了时间。调查性能问题时，等待事件可帮助您确定瓶颈是 CPU、I/O、锁定、网络还是进程间通信。在本指南所描述的问题中，出现的常见等待事件类别包括：

  • CPU：会话在 CPU 上处于活动状态或正在等待 CPU。CPU 等待时间过高通常与并行度过高或扫描膨胀表的查询计划效率欠佳有关。

  • IPC（进程间通信）：诸如 IPC:BgWorkerStartup、IPC:ExecuteGather 和 IPC:ParallelFinish 的等待事件指示并行查询的协调开销。

  • IO：诸如 IO:DataFileRead 的等待事件表示由于所需的页面不在共享内存中，查询正在从存储中读取数据。这种情况常见于膨胀表的大小超过缓冲区缓存时。

  • 锁定：诸如 Lock:transactionid 和 Lock:tuple 的等待事件表示会话之间的争用。事务中空闲连接会保持锁定，这会阻止其他查询和 autovacuum。

  • 客户端：诸如 Client:ClientRead 的等待事件表示数据库正在等待应用程序发送数据。大量的客户端等待事件可能表示连接流失或网络延迟。

有关通常表示性能问题的等待事件及其建议纠正措施的完整参考，请参阅 RDS for PostgreSQL 等待事件。

Autovacuum 微调

Autovacuum 是一个后台进程，它可以回收无效元组，防止表和索引膨胀，更新计划程序统计信息，并防止事务 ID 回绕。默认 autovacuum 设置比较保守，设计用于小型数据库。对于高写入量生产工作负载，几乎总是需要进行微调。

当 autovacuum 无法跟上您的写入工作负载时，膨胀会累积，计划程序统计信息会过时，事务 ID 回绕的风险就会增加。如果 age(relfrozenxid) 接近 20 亿，则数据库将关闭以防止数据损坏。

有关微调 autovacuum 参数、监控 vacuum 活动和配置每个表上覆盖的详细指南，请参阅在 Amazon RDS 上使用 PostgreSQL autovacuum。

相关信息