如何排查 Amazon RDS 或 Amazon Aurora PostgreSQL 的高 CPU 利用率问题？

简短描述

如果发现负载的 CPU 利用率很高，请使用以下多种工具来确定原因：

• Amazon CloudWatch 指标
• 增强型监控指标
• 性能详情指标
• 本机 PostgreSQL 视图和目录，例如 pg_stat_statements、pg_stat_activity 和 pg_stat_user_tables
• 数据库中的空闲连接
• ANALYZE 命令
• PostgreSQL 日志记录参数，用于记录长时间运行的查询、autovacuum、锁定等待以及连接和断开连接请求

解决方法

Amazon CloudWatch 指标

使用 CloudWatch 指标来识别长时间的 CPU 模式。将图表 WriteIOPs、ReadIOPs、ReadThroughput 和 WriteThroughput 与 CPU 利用率进行比较，找出工作负载导致 CPU 过高的时间。

确定时间范围后，查看与您的数据库实例关联的增强型监控数据。可以将增强型监控设置为每隔 1、5、10、15、30 或 60 秒收集一次数据。由此，就可以收集到比 CloudWatch 更精细的数据。

增强型监控

增强型监控可提供操作系统（OS）级别的视图。此视图有助于从更精细的层面上确定 CPU 负载过高的原因。例如，您可以查看负载平均值、CPU 分布（System% 或 Nice%）和操作系统进程列表。

借助增强型监控，您可以每隔 1、5 和 15 分钟检查 loadAverageMinute 数据。负载平均值大于 vCPU 数量表示该实例处于负载过重的状态。如果负载平均值小于数据库实例类的 vCPU 数量，则 CPU 节流可能不会导致应用程序延迟。在诊断 CPU 使用的原因时，请检查负载平均值以避免误报。

例如，假设您的数据库实例使用 db.m5.2xlarge 实例类，预调配 IOPS 为 3000，达到了 CPU 限制。在以下示例中，该实例类有 8 个与之关联的 vCPU。对于相同的负载平均值，超过 170 表示计算机在测量的时间范围内处于重负载状态。

负载平均分钟

15	170.25
5	391.31
1	596.74

CPU 利用率

用户（%）	0.71
系统（%）	4.9
Nice 值（%）	93.92
总计（%）	99.97

**注意：**在增强型监控中，Nice% 表示工作负载对数据库使用的 CPU 量。

开启增强型监控后，您还可以查看与数据库实例关联的操作系统进程列表。增强型监控能最多显示 100 个进程，有助于确定对性能影响最大的进程。您可以将增强型监控结果与 pg_stat_activity 结果相结合，以帮助确定查询的资源使用情况。

性能详情

使用 Amazon RDS 性能详情来确定造成数据库负载的查询。查看与特定时间段对应的 SQL 选项卡。

本机 PostgreSQL 视图和目录

在数据库引擎级别，可以使用 pg_stat_activity 和 pg_stat_statements。如果问题是实时发生的，请使用 pg_stat_activity 或 pg_stat_statements 对发送最多流量的计算机、客户端和 IP 地址进行分组。使用此数据来检查一段时间内的增加情况或应用程序服务器的增加情况。您还可以验证应用程序服务器是否存在会话阻塞或锁定问题。有关更多信息，请参阅 PostgreSQL 网站上的 pg_stat_activity 和 pg_stat_statements。

要启用 pg_stat_statements，请修改现有的自定义参数组并设置以下值：

• 将 pg_stat_statements 添加到 shared_preload_libraries
• track_activity_query_size = 4096
• pg_stat_statements.track = ALL
• pg_stat_statements.max = 10000

选择立即应用，然后重启数据库实例。接着，在要监控的数据库上运行类似于以下内容的命令：

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

**注意：**前面的命令会将扩展程序安装在 demo 数据库中。

设置完 pg_stat_statements 后，使用以下一种方法来监控输出。

要按 total_time 列出查询，并查看数据库中花费时间最多的查询，请运行以下一种查询：

PostgreSQL 版本 12 及更早版本

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更高版本

SELECT total_plan_time+total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY 1 DESC LIMIT 10;

要列出缓冲区缓存命中率较低的查询，请运行以下一种查询：

PostgreSQL 版本 12 及更早版本

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更高版本

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY 3 DESC LIMIT 10;

要在每次执行时列出查询，以对一段时间内的查询进行抽样，请运行以下查询：

PostgreSQL 版本 12 及更早版本

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更高版本

SELECT query,
calls,
(total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,
 rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read,
 temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY 3 DESC LIMIT 10;

数据库中的空闲连接

数据库中的空闲连接可能会消耗计算资源，例如内存和 CPU。当实例的 CPU 利用率较高时，请检查数据库上是否存在空闲连接。有关更多信息，请参阅 Performance impact of idle PostgreSQL connections。要检查空闲连接，请使用增强型监控查看操作系统进程列表。但是，此列表只能显示最多 100 个进程。

要检查空闲连接，请在数据库级别运行以下查询。

运行以下查询以查看当前处于空闲和活动状态的会话：

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE query != '<IDLE>
'AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin'
ORDER BY query_start desc;

SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query
FROM pg_stat_activity
WHERE not pid=pg_backend_pid()
AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin';

运行以下查询以获取每个用户和应用程序名称的连接数：

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;
    application_name    | count
------------------------+-------
 psql                   |     1
 PostgreSQL JDBC Driver |     1
                        |     5
(3 rows)

postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;
 usename  | count
----------+-------
 master   |     4
 user1    |     1
 rdsadmin |     2
(3 rows)

确定空闲连接后，运行以下任一查询以结束连接：

psql=> SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'example-username'
   AND pid <> pg_backend_pid()
   AND state in ('idle');

-或者-

SELECT pg\_terminate\_backend (example-pid);

如果应用程序的连接过多，请对其进行更改，以释放管理这些连接的内存和 CPU 资源。您可以更改应用程序以限制连接数量，也可以使用诸如 PgBouncer 之类的连接池。您还可以使用 Amazon RDS 代理，这是一项可设置连接池的托管服务。

ANALYZE 命令

ANALYZE 命令会收集有关数据库中表格内容的统计信息，并将结果存储在 pg\ _statistic 系统目录中。然后，查询计划器会使用这些统计数据来帮助确定最有效的查询执行计划。如果不经常对数据库中的表运行 ANALYZE，则查询可能会消耗更多的计算资源。由于系统中存在您访问的关系的过时统计信息，因此查询会消耗更多资源。这些问题会出现在以下情况下：

• 不经常运行 Autovacuum。
• 主要版本升级后未运行 ANALYZE。

不经常运行 Autovacuum： Autovacuum 是一个进程守护程序，可自动运行 VACUUM 和 ANALYZE 命令。Autovacuum 会检查数据库中是否存在膨胀表，并回收空间以供重复使用。每当设置的元组阈值失效时，autovacuum 进程守护程序会运行 ANALYZE 操作来确保定期更新表统计信息。然后，查询计划器可以根据最近的统计数据使用最有效的查询计划。如果 autovacuum 未运行，则查询计划器可能会创建次优的查询计划，导致查询消耗更多的资源。有关详细信息，请参阅以下文章：

• Understanding autovacuum in Amazon RDS for PostgreSQL environments
• A case study of tuning autovacuum in Amazon RDS for PostgreSQL

要获取有关上次对表运行 autovacuum 和 autoanalyze 的时间信息，请运行以下查询：

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

**主要版本升级后未运行 ANALYZE：**PostgreSQL 数据库在任何主要引擎版本升级后通常都会遇到性能问题。出现这些问题的一个常见原因是，升级后未执行 ANALYZE 操作以刷新 pg_statistic 表。请对 RDS for PostgreSQL 数据库实例中的每个数据库运行 ANALYZE 操作。在主要版本升级期间，不会传输优化器统计信息。因此，为避免由于资源利用率过高而出现性能问题，请重新生成所有统计信息。

要在主要版本升级后为当前数据库中的所有常规表生成统计信息，请在不带任何参数的情况下运行以下命令：

ANALYZE VERBOSE

PostgreSQL 日志记录参数

使用 Amazon RDS for PostgreSQL 启用查询日志记录。然后，检查 PostgreSQL 错误日志，确认已为 log_min_duration_statement 和 log_statement 参数设置适当的值。有关更多信息，请参阅 PostgreSQL 网站上的 Error reporting and logging。

降低 CPU 利用率

确定导致 CPU 利用率过高的查询后，使用以下方法进一步降低 CPU 利用率：

• 要找到调整位置，请使用 EXPLAIN 和 EXPLAIN ANALYZE 以识别警告。有关更多信息，请参阅 PostgreSQL 网站上的 EXPLAIN。
• 如果有重复运行的查询，请使用预处理语句降低 CPU 压力。重复运行预处理语句会缓存查询计划。由于已缓存查询计划，所以规划的时间对于后续运行来说要少得多。

相关信息

使用 PostgreSQL 的最佳实践