AWS AmazonRDS documentation change
Summary
Added documentation about using Performance Insights wait events for troubleshooting PostgreSQL performance issues, including new section on wait event categories and their implications.
Security assessment
Changes focus on performance monitoring and diagnostic guidance without addressing security vulnerabilities or weaknesses. Added content explains how to interpret wait events for performance tuning, but contains no security-specific context or references.
Diff
diff --git a/AmazonRDS/latest/UserGuide/PostgreSQL.InitialTroubleshooting.md b/AmazonRDS/latest/UserGuide/PostgreSQL.InitialTroubleshooting.md index 7d881b10d..7a511fec4 100644 --- a//AmazonRDS/latest/UserGuide/PostgreSQL.InitialTroubleshooting.md +++ b//AmazonRDS/latest/UserGuide/PostgreSQL.InitialTroubleshooting.md @@ -7 +7 @@ -Quick diagnostic checklistTable and index bloatParallel query resource exhaustionHigh connection and authentication pressureAutovacuum tuningRelated information +Quick diagnostic checklistTable and index bloatParallel query resource exhaustionHigh connection and authentication pressureUsing Performance Insights wait events for troubleshootingAutovacuum tuningRelated information @@ -57 +57 @@ Use the following ordered triage steps when you first investigate a performance - 5. **Check CloudWatch and Performance Insights metrics.** Review CPU utilization, connection count, IOPS, and freeable memory. For more information, see [Monitoring Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MonitoringOverview.html). + 5. **Check CloudWatch and Performance Insights metrics.** Review CPU utilization, connection count, IOPS, and freeable memory. For more information, see [Monitoring Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MonitoringOverview.html). For common wait events and corrective actions, see [RDS for PostgreSQL wait events](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.summary.html). @@ -105 +105 @@ Rather than relying on manual maintenance, ensure that autovacuum is enabled and -PostgreSQL can execute queries in parallel to improve performance for large sequential scans and aggregations. However, each parallel worker is a full backend process that consumes a connection slot from `max_connections` and allocates its own `work_mem`. A single query with 4 parallel workers uses 5 connection slots (1 leader + 4 workers) and can consume hundreds of megabytes of memory. Under high concurrency, this can exhaust both connections and memory rapidly. +PostgreSQL can execute queries in parallel to improve performance for large sequential scans and aggregations. However, each parallel worker is a full backend process that counts against `max_worker_processes` (and the sub-limit `max_parallel_workers`) and allocates its own `work_mem`. A single query with 4 parallel workers can consume hundreds of megabytes of memory and significant CPU. Under high concurrency, excessive parallelism can exhaust CPU and memory rapidly. @@ -107 +107 @@ PostgreSQL can execute queries in parallel to improve performance for large sequ -Common symptoms include sudden CPU spikes, `FATAL: too many connections` errors, high memory usage per query, and elevated `DatabaseConnections` in CloudWatch without application changes. You may also observe wait events such as `IPC:BgWorkerStartup`, `IPC:ExecuteGather`, and `IPC:ParallelFinish`. For more information about these wait events, see [IPC:parallel wait events](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rpg-ipc-parallel.html). +Common symptoms include sudden CPU spikes, high memory usage per query, and elevated `DatabaseConnections` in CloudWatch without application changes. You may also observe wait events such as `IPC:BgWorkerStartup`, `IPC:ExecuteGather`, and `IPC:ParallelFinish`. For more information about these wait events, see [IPC:parallel wait events](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rpg-ipc-parallel.html). @@ -158 +158,20 @@ A high number of `idle` or `idle in transaction` connections relative to `max_co -Parallel query workers also consume connection slots. If you observe connection exhaustion alongside parallel query activity, see Parallel query resource exhaustion for guidance on controlling parallel worker usage. +Parallel query workers consume CPU and memory. If you observe resource exhaustion alongside parallel query activity, see Parallel query resource exhaustion for guidance on controlling parallel worker usage. + +## Using Performance Insights wait events for troubleshooting + +Performance Insights captures wait events that show where your database is spending time. When you investigate performance issues, wait events help you identify whether the bottleneck is CPU, I/O, locking, network, or inter-process communication. Common wait event categories that appear during the issues described in this guide include: + + * **CPU** — The session is active on CPU or waiting for CPU. High CPU wait events often correlate with excessive parallelism or inefficient query plans scanning bloated tables. + + * **IPC (inter-process communication)** — Wait events such as `IPC:BgWorkerStartup`, `IPC:ExecuteGather`, and `IPC:ParallelFinish` indicate parallel query coordination overhead. + + * **IO** — Wait events such as `IO:DataFileRead` indicate that queries are reading data from storage because the required pages are not in shared memory. This is common when bloated tables exceed the buffer cache. + + * **Lock** — Wait events such as `Lock:transactionid` and `Lock:tuple` indicate contention between sessions. Idle-in-transaction connections can hold locks that block other queries and autovacuum. + + * **Client** — Wait events such as `Client:ClientRead` indicate the database is waiting for the application to send data. High client wait events can indicate connection churn or network latency. + + + + +For a complete reference of wait events that commonly indicate performance problems and their recommended corrective actions, see [RDS for PostgreSQL wait events](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.summary.html).