如何确定我的 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 兼容版数据库实例中的性能问题和运行缓慢的查询并对其进行故障排除？

解决方法

硬件尺寸不足、工作负载变化、流量增加、内存问题或未优化的查询会影响数据库实例性能。要解决性能问题，请执行以下操作。

确定原因

检查 CloudWatch 指标

要确定因资源不足而导致的性能瓶颈，请监控 Amazon CloudWatch 指标 CPUUtilization、FreaableMemory 和 SwapUsage。

当 CPU 利用率较高时，数据库实例上的活动工作负载需要更多 CPU 资源。工作负载的内存可用性较低会导致频繁交换，从而导致较高的内存利用率和交换空间利用率。长时间运行的查询、突然增加的流量或大量的空闲连接都可能导致 CPU 利用率和内存资源过高。

要查看运行时的活动查询，请运行以下命令：

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

要查看数据库中的空闲连接，请运行以下命令：

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

要终止空闲连接，请运行以下命令：

SELECT pg_terminate_backend(example-pid);

**注意：**请将 example-pid 替换为空闲连接的进程 ID。

要验证您的数据库实例是否达到预期的网络吞吐量，请检查 NetworkReceiveThroughput 和 NetworkTransmitThroughput 指标。尺寸较小或未优化的 Amazon Elastic Block Service (Amazon EBS) 实例类可能会影响网络吞吐量并导致实例速度变慢。网络吞吐量低可能会导致对所有应用程序请求的响应速度变慢，无论数据库性能如何。

要评估 I/O 性能，请检查 ReadIOPS、WriteIOPS、ReadLatency、WriteLatency、ReadThroughput、WriteThroughput 和 DiskQueueDepth 指标。有关详细信息，请参阅如何排查 Amazon RDS 实例中由 IOPS 瓶颈导致的 Amazon EBS 卷延迟问题？

使用增强监控

使用增强监控查看操作系统 (OS) 级别的指标，并列出使用高 CPU 和内存的前 100 个进程。激活增强监控，将 Granularity（粒度）设置为 1，以确定数据库实例上的间歇性性能问题。

评估可用的操作系统指标，以诊断与 CPU、工作负载、I/O、内存和网络相关的性能问题。从进程列表中找出 CPU% 或 Mem% 值较高的进程。

示例：

名称	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250.66 MB	27.7 MB	85.93	2.21	无限制

连接到数据库，然后运行以下查询以查找数据库中 CPU 较高的连接：

SELECT * FROM pg_stat_activity WHERE pid = 10322;

**注意：**请将 10322 替换为连接的进程 ID。

检查性能详情指标

使用性能详情可按等待、SQL、主机或用户评估数据库工作负载。您还可以获取 database 和 SQL-level 指标。

使用“性能详情”控制面板上的 Top SQL（主要 SQL）可查看导致大部分数据库负载的 SQL 语句。如果数据库负载或等待负载 (AAS) 高于最大 vCPU，则数据库实例类上的工作负载将受到限制。

使用 SQL 统计数据中每个调用的平均延迟可查看查询的平均运行时间。Top SQL（主要 SQL）基于总运行时间。因此，运行时间最长的 SQL 通常与作为数据库负载主要贡献者的 SQL 不同。

查看数据库统计数据

要评估 PostgreSQL 中的数据库性能，请查看数据分布统计数据、扩展统计数据和监控统计数据。有关统计数据的信息，请参阅了解 PostgreSQL 中的统计数据。

查看本地数据库工具

要确定运行缓慢的查询，请使用 GitHub 网站上的本地工具 pgbadger。有关详细信息，请参阅 Optimizing and tuning queries in Amazon RDS for PostgreSQL based on native and external tools。

优化性能

调整内存设置

您可以将 shared_buffers 参数设置为有助于提高查询性能的值。

work_mem 和 maintenance_work_mem 参数用于定义用于后端进程的内存量。有关详细信息，请参阅 PostgreSQL 网站上的 20.4 资源消耗。如果您经常遇到数据库实例上内存使用率高的情况，请降低附加到实例的自定义参数组中的参数值。

使用 Aurora PostgreSQL 兼容版查询计划管理

使用 Aurora PostgreSQL 兼容版查询计划管理来控制查询运行计划的更改方式和时间。有关详细信息，请参阅 Aurora PostgreSQL 查询计划管理的最佳实践。

对运行缓慢的查询进行故障排除

基础设施问题、未优化的查询计划或较高的总体资源使用率会导致查询运行缓慢。PostgreSQL 查询计划器使用表统计数据来创建查询计划。架构更改和旧的统计数据可能会影响计划。过载的表和索引也可能会导致查询运行缓慢。

当表达到死元组阈值时，autovacuum 进程守护程序会创建 autovacuum 工作进程，从表中移除死元组。autovacuum 进程守护程序还会运行 ANALYZE 操作来刷新表的统计数据。

运行以下查询以检查死元组和 autovacuum 或 vacuum 操作，以及自动分析或分析运行：

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count
FROM pg_stat_user_tables
ORDER BY 5 DESC;

使用 pg_stat_activity 视图查找与当前活动相关的数据，例如后端进程 ID 或查询。要查找长时间运行的查询，请运行以下查询：

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU'
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

等待锁定的查询可能会运行缓慢。要检查查询是否在等待锁定，请运行以下查询：

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view's locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

使用 pg_stat_statements 查看查询的统计数据。在创建 pg_stat_statements 扩展之前，请将 pg_stat_statements 条目添加到 shared_preload_libraries 中。要在数据库中创建 pg_stat_statements 扩展，请运行以下查询：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

**注意：**只有在将自定义参数组附加到数据库实例时，才能修改 pg_stats_statements 的参数。

要确定影响数据库实例性能的 SQL 查询，请运行以下查询。

PostgreSQL 版本 12 及更早版本：

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

PostgreSQL 版本 13 及更高版本：

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

要查找缓冲区缓存命中率较低的查询，请运行以下查询。

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_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

要在数据库错误日志中查找长时间运行的查询或查询计划，请为您的数据库实例配置 log_min_duration_statement 参数，然后使用 auto_explain 模块。

您也可以使用 explain 和 explain analyze 命令来获取查询计划。使用 auto_explain 模块或 explain 命令来确定如何调整查询优化。有关详细信息，请参阅 PostgreSQL 网站上的 14.1 Using EXPLAIN 和 F3. auto_explain - log execution plans of slow queries。

如果您优化了系统，但仍然遇到性能问题，则最佳做法是纵向扩展数据库实例类。纵向扩展数据库实例时，会分配更多的计算和内存资源。

相关信息

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

在 RDS for PostgreSQL 数据库实例上使用参数

为什么尽管内存足够，我的 Amazon RDS 数据库实例仍在使用虚拟内存？