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

简短描述

要确定导致高 CPU 使用率的原因，请使用以下工具：

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

解决方法

查看 CloudWatch 指标

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

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

使用增强监控

增强监控可提供操作系统 (OS) 级别的视图。例如，您可以查看工作负载平均值、CPU 分布（System% 或 Nice%）和操作系统进程列表。有关详细信息，请参阅操作系统监控。

您可以每隔 1、5 和 15 分钟检查 loadAverageMinute 数据。当负载平均值大于 vCPU 数量时，表示实例正处于高负载状态。如果负载平均值小于数据库实例类的 vCPU 数量，则 CPU 节流可能不会导致应用程序延迟。在对 CPU 使用率高进行故障排除时，请检查负载平均值以避免误报。

例如，您的数据库实例使用 db.m5.2xlarge 实例类，预调配 IOPS 为 3000，达到了 CPU 配额。该实例类与八个 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，请完成以下步骤：

1. 修改现有的自定义数据库参数组。

2. 将 pg_stat_statements 添加到 shared_preload_libraries。

3. 将 track_activity_query_size 设置为 4096。

4. 将 pg_stat_statements.track 设置为 ALL。

5. 将 pg_stat_statements.max 设置为 10000。

6. 选择 Apply Immediately（立即应用），然后重启数据库实例。

7. 在要监控的数据库上，运行以下命令：

   demo=> select current_database();current_database------------------  
   demo  
   (1 row)  
   
   demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

设置 pg_stat_statements 后，使用以下方法之一来监控输出。您可以查看在数据库中耗时最长、缓冲区缓存命中率较低或基于每次执行的查询。

要查看哪些查询在数据库中耗时最长，请针对您的 PostgreSQL 版本运行以下查询。

PostgreSQL 版本 12 及更早版本：

SELECT total_time, queryFROM pg_stat_statements  
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更高版本：

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

要列出缓冲区缓存命中率较低的查询，请针对您的 PostgreSQL 版本运行以下查询。

PostgreSQL 版本 12 及更早版本：

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM 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_percentFROM pg_stat_statements   
ORDER BY 3 DESC LIMIT 10;

要列出基于每次执行的查询，以对一段时间内的查询进行采样，请针对您的 PostgreSQL 版本运行以下查询。

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_writtenFROM 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（PostgreSQL 空闲连接对性能的影响）。

您可以使用增强监控来查看操作系统进程列表中的空闲连接。但是，该列表最多仅显示 100 个进程。要在数据库级别检查空闲连接，请运行以下查询。

查看当前处于空闲和活动状态的会话：

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activityWHERE 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 进程守护程序会在设置的元组阈值失效时运行 ANALYZE 命令。

有关详细信息，请参阅以下资源：

• 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 命令以刷新 pg_statistic 表。请对 RDS for PostgreSQL 数据库实例中的每个数据库运行 ANALYZE 命令。

为避免由于资源利用率较高而出现性能问题，请重新生成所有统计数据。要在主要版本升级后为当前数据库中的所有常规表生成统计数据，请运行以下不带参数的命令：

ANALYZE VERBOSE

检查 PostgreSQL 错误日志

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

降低 CPU 使用率

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

• 使用 EXPLAIN 和 EXPLAIN ANALYZE 来确定调整查询计划的方法。有关详细信息，请参阅 PostgreSQL 网站上的 Using EXPLAIN（使用 EXPLAIN）。
• 如果有重复运行的查询，请使用预处理语句降低 CPU 压力。重复运行的预处理语句会缓存查询计划。当计划已缓存以便后续运行时，查询的计划时间会减少。

相关信息

使用 PostgreSQL 的最佳实践