如何确定并排查我的 RDS for PostgreSQL 或 Aurora PostgreSQL 实例中的性能问题和运行缓慢的查询？

4 分钟阅读

我的 Amazon Relational Database Service（Amazon RDS）for PostgreSQL 实例速度很慢。我想确定运行缓慢的查询并进行问题排查。

解决方案

Amazon RDS for PostgreSQL 实例的性能可能由于多种原因而受到影响，例如：

硬件尺寸不足
工作负载发生变化
流量增加
内存出现问题
采用次优的查询计划

确定原因

结合使用以下工具来确定查询运行缓慢的原因：

Amazon CloudWatch 指标
增强监控指标
性能详情指标
数据库统计数据
原生数据库工具

CloudWatch 指标

要确定资源不足导致的性能瓶颈，请监控这些适用于 Amazon RDS 数据库实例的常见 CloudWatch 指标。

CPUUtilization - 使用的计算机处理容量的百分比
FreeableMemory - 数据库实例上的可用 RAM（以兆字节为单位）
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';

然后，运行此命令以终止消耗资源的空闲连接：

**注意：**请务必将 example-pid 替换为从 pg_stat_activity 获得的空闲连接的 PID：

SELECT pg_terminate_backend(example-pid);

通过审查以下指标验证 RDS 实例是否实现了所需的网络吞吐量：

NetworkReceiveThroughput
NetworkTransmitThroughput

这些指标以字节/秒为单位表示传入和传出的网络流量。规模过小或非 Amazon Elastic Block Service（Amazon EBS）优化的实例类可能会影响网络吞吐量，从而导致实例速度变慢。网络吞吐量低会导致对所有应用程序请求的响应速度变慢，无论数据库性能如何。

通过审查以下指标评估 I/O 性能：

ReadIOPS 和 WriteIOPS - 每秒平均磁盘读取或写入操作次数
ReadLatency 和 WriteLatency - 读取或写入操作所用的平均时间（以毫秒为单位）
ReadThroughput 和 WriteThroughput - 每秒从磁盘读取或写入磁盘的平均兆字节数
DiskQueueDepth - 等待写入磁盘或从磁盘读取的 I/O 操作数

增强监控指标

借助增强监控，您可以查看操作系统级别的指标以及消耗高 CPU 和内存的前 100 个进程的列表。您可以激活每秒级别的增强监控，以确定 RDS 实例上的间歇性性能问题。

您可以评估可用的操作系统指标，以诊断可能与 CPU、工作负载、I/O、内存和网络相关的性能问题。

从进程列表中，确定 CPU% 或 Mem% 值较高的进程。然后，从数据库中找到相关的连接。

例如：

NAME	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	无限制

连接到数据库，然后运行此查询以查找连接和查询相关信息：

SELECT * FROM pg_stat_activity WHERE pid = 10322;

性能详情指标

性能详情允许您评估按等待、SQL、主机或用户划分的数据库工作负载。您还可以捕获数据库和 SQL 级别的指标。

性能详情控制面板上的首要 SQL 选项卡显示对数据库负载贡献最大的 SQL 语句。如果数据库加载或等待加载 (AAS) 高于最大 vCPU 值，则表示实例类上的工作负载被节流。

SQL 统计数据中每个调用的平均延迟提供了查询的平均运行时间。与平均运行时间最高的 SQL 相比，通常会看到不同的 SQL 是最大的数据库负载贡献者。这是因为首要 SQL 列表基于总运行时间。

数据库统计数据

以下统计数据可以帮助您评估 PostgreSQL 中的数据库性能：

数据分布统计数据
扩展统计数据
监控统计数据

有关如何阅读和理解这些统计数据的信息，请参阅了解 PostgreSQL 中的统计数据。

原生数据库工具

要识别慢速查询，请使用原生 pgBadger 工具。有关更多信息，请参阅基于原生和外部工具在 Amazon RDS for PostgreSQL 中优化和调整查询。

优化性能

调整内存设置

PostgreSQL 数据库服务器在其整个生命周期中分配一定的内存区域用于缓存数据，以提高读写访问权限。此内存区域称为共享缓冲区。数据库用于共享内存缓冲区的内存量由 shared_buffers 参数控制。

除了共享内存区域外，每个后端进程都消耗内存来在数据库服务器内执行操作。使用的内存量基于为 work_mem 和 maintenance_work_mem 参数设置的值。有关更多信息，请参阅服务器配置的 PostgreSQL 文档。

如果您持续观察到数据库实例的内存压力很大，请考虑降低这些参数的值。可以在附加到数据库实例的自定义参数组中降低这些参数的值。

Aurora PostgreSQL 查询计划管理

使用 Amazon 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 视图来查找与当前活动相关的数据。此视图提供后端 pid、查询和其他详细信息。要查找长时间运行的查询，请运行以下查询：

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;

您的 RDS for PostgreSQL 实例允许您在数据库中创建 pg_stat_statements 扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

使用 pg_stat_statements，您可以查看查询的统计数据。在创建扩展之前，请务必将 pg_stat_statements 条目添加到 shared_preload_libraries 中。

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

使用此查询可以确定影响实例性能的首要 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_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 5 ASC
LIMIT 10;

通过为实例适当设置 log_min_duration_statement 参数并使用 auto_explain 模块，即可在数据库错误日志中捕获长时间运行的查询或查询计划。如果语句至少运行了指定的时间，log_min_duration_statement 参数会记录每条已完成语句的持续时间。例如，如果将此参数设置为 250 毫秒，则会记录运行时间为 250 毫秒或更长时间的所有 SQL 语句。auto_explain 模块允许您捕获在数据库中运行的查询的解释计划。

您还可以使用 explain 和 explain analyze 命令来捕获计划。根据 auto_explain 模块或查询的 explain 命令确定查询调整机会。有关更多信息，请参阅有关使用 EXPLAIN 的 PostgreSQL 文档。

如果您的系统调整良好，但仍然面临资源节流，请考虑向上扩展实例类。向上扩展实例类，以便为您的数据库实例分配更多的计算和内存资源。有关更多信息，请参阅数据库实例类的硬件规格。

如何确定并排查我的 RDS for PostgreSQL 或 Aurora PostgreSQL 实例中的性能问题和运行缓慢的查询？

解决方案

确定原因

优化性能

相关信息

相关内容