如何排查 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 兼容版实例中 CPU 使用率高的问题？

解决方法

要确定 CPU 利用率高的原因，请采取以下措施。然后，减少数据库 (DB) 实例的 CPU 使用率。

查看数据库实例指标

要确定您的工作负载何时导致 CPU 使用率过高，请使用 Amazon CloudWatch 将 WriteIOPs、ReadIOPs、ReadThroughput 和 WriteThroughput 指标与 CPUUtilization 指标进行比较。对于 Aurora PostgreSQL 兼容版，您还可以比较 BufferCacheHitRatio 指标。如果指标值与 CPUUtilization 指标一样高，则表示您的工作负载可能会导致 CPU 使用率高。

使用增强监控

使用增强监控来检查数据库实例的操作系统 (OS)。要收集详细数据，请将 Granularity 属性的时间间隔设置为 1、5、10、15、30 或 60 秒。

要排查 CPU 使用率高的原因，请检查 LoadAverageMinute 操作系统指标。如果负载平均值大于 vCPU 数量，则表示实例的利用率高。如果负载平均值小于数据库实例类的 vCPU 数量，则 CPU 节流可能不会导致应用程序延迟。

您也可以查看数据库实例的操作系统进程列表。增强监控最多可以识别 100 个影响实例性能的进程。要确定查询的资源使用情况，请使用增强监控的结果运行 PostgreSQL pg_stat_activity 查询。

使用 CloudWatch 数据库洞察

启用 Amazon CloudWatch 数据库洞察，以确定造成数据库负载的查询。然后，查看数据库负载图表上的 Top SQL（主要 SQL）选项卡，了解 CPU 利用率增加的具体时间。

查看本机 PostgreSQL 视图和目录

对于实时问题，请启用 pg_stat_activity 或 pg_stat_statements 对发送最多流量的计算机、客户端和 IP 地址进行分组。有关详细信息，请参阅 PostgreSQL 网站上的 pg_stat_activity 和 pg_stat_statements。

启用 pg_stat_statements

完成以下步骤：

1. 修改自定义数据库参数组中的以下值：
   将 pg_stat_statements 添加到 shared_preload_libraries。
   将 track_activity_query_size 设置为 4,096。
   将 pg_stat_statements.track 设置为 ALL。
   将 pg_stat_statements.max 设置为 10,000。
2. 选择 Apply Immediately（立即应用），然后重启数据库实例。

选择要监控的数据库

运行以下查询：

select current_database();

在当前数据库上安装扩展

运行以下命令：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

查看在数据库中耗时最长的查询

为您的 PostgreSQL 版本运行以下查询。

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 版本运行以下查询。

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 版本运行以下查询。

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。要解决此问题，请检查并结束空闲连接。有关更多信息，请参阅 Performance impact of idle PostgreSQL connections（PostgreSQL 空闲连接对性能的影响）。

要检查空闲时间超过 10 分钟的会话，请运行以下查询：

SELECT * FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled','active')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

要仅结束处于空闲状态超过 10 分钟的会话，请运行以下查询：

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

要结束所有空闲连接，请运行以下查询之一：

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

**注意：**请将 example-username 替换为您的用户名。

-或-

SELECT pg_terminate_backend (example-pid);

**注意：**请将 example-pid 替换为您的查询 PID。

如果您的应用程序创建的数据库连接过多，请减少连接的数量。或者，使用诸如 PgBouncer 之类的连接池工具。有关更多信息，请参阅 PgBouncer 网站上的 PgBouncer。您也可以使用 Amazon RDS 代理来设置连接池。

检查数据库锁

如果您的数据库锁导致查询累积并运行更长时间，那么数据库实例的 CPU 利用率可能会增加。要解决与锁相关的问题，请查看数据库洞察中是否存在等待事件，例如 Lock:Relation、Lock:tuple、Lock:transactionid 或其他与锁相关的事件。

要确定导致查询被阻止的原因，请参阅如何确定 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 兼容版数据库实例上的查询被阻止的原因？

如果锁定会话未处于 ACTIVE 状态，请使用数据库洞察来识别锁定的查询。要解决此问题，请结束所有锁定的会话。

运行 ANALYZE 命令

如果您不经常对数据库中的表运行 ANALYZE，由于系统中的统计数据过时，查询可能会使用更多计算资源。有关更多信息，请参阅 PostgreSQL 网站上的 ANALYZE。

Autovacuum 会从表中移除未使用的空间并回收数据库中的空间。当您达到所配置早期版本的阈值时，Autovacuum 进程守护程序还会运行 ANALYZE 命令来定期更新表统计信息。

要确定上次对表运行 autovacuum 和 autoanalyze 的时间，请运行以下查询：

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

为防止在主要引擎版本升级后出现性能问题，请针对数据库实例中的每个数据库运行 ANALYZE 命令，以刷新 pg_statistic 表。

为防止因资源使用量增加而出现性能问题，请运行以下不带参数的命令来重新生成所有统计信息：

ANALYZE VERBOSE;

检查 PostgreSQL 错误日志

在 Amazon RDS for PostgreSQL 中启用查询日志记录功能。然后，检查 PostgreSQL 错误日志，确认您已设置了 log_min_duration_statement 和 log_statement 参数。有关更多信息，请参阅 PostgreSQL 网站上的 Error reporting and logging（错误报告和日志记录）。

降低 CPU 使用率

要降低 CPU 使用率，请采取以下措施：

• 使用 EXPLAIN 和 EXPLAIN ANALYZE 来确定调整查询计划的方法。有关更多信息，请参阅 PostgreSQL 网站上的 Using EXPLAIN（使用 EXPLAIN）。
• 如果您发现存在不必要的锁定会话，请使用锁定会话的 PID 来结束会话。
• 如果有重复运行的查询，请使用预处理语句降低 CPU 使用率。有关更多信息，请参阅 PostgreSQL 网站上的 PREPARE。

相关信息

使用 PostgreSQL 的最佳实践

OS monitoring（操作系统监控）

Understanding autovacuum in Amazon RDS for PostgreSQL environments（了解 autovacuum 在 Amazon RDS for PostgreSQL 环境中的表现）

A case study of tuning autovacuum in Amazon RDS for PostgreSQL（在 Amazon RDS for PostgreSQL 中对 autovacuum 进行调优的案例研究）