;
```
If disabling parallelism yields better or more consistent results, consider disabling it for specific queries at the session level using SET commands. For a broader impact, you might want to disable parallelism at the instance level by adjusting the relevant parameters in your cluster or instance parameter group. For more information, see [Amazon Aurora PostgreSQL parameters](AuroraPostgreSQL.Reference.ParameterGroups.md).
### Monitor parallel query usage
Use the following queries to gain visibility into parallel query activity and capacity:
Check active parallel worker processes:
```
SELECT
COUNT(*)
FROM
pg_stat_activity
WHERE
backend_type = 'parallel worker';
```
This query shows the number of active parallel worker processes. A high value may indicate that your `max\$1parallel\$1workers` is configured with a high value and you might want to consider reducing it.
Check concurrent parallel queries:
```
SELECT
COUNT(DISTINCT leader_pid)
FROM
pg_stat_activity
WHERE
leader_pid IS NOT NULL;
```
This query returns the number of distinct leader processes that have launched parallel queries. A high number here indicates that multiple sessions are running parallel queries concurrently, which can increase demand on CPU and memory.
### Review and adjust parallel query settings
Review the following parameters to ensure they align with your workload:
+ `max_parallel_workers`: Total number of parallel workers across all sessions.
+ `max_parallel_workers_per_gather`: Max workers per query.
For OLAP workloads, increasing these values can improve performance. For OLTP workloads, lower values are generally preferred.
```
SHOW max_parallel_workers;
SHOW max_parallel_workers_per_gather;
```
### Optimize resource allocation
Monitor CPU utilization and consider adjusting the number of vCPUs if consistently high and if your application benefits from parallel queries. Ensure adequate memory is available for parallel operations.
+ Use Performance Insights metrics to determine if the system is CPU-bound.
+ Each parallel worker uses its own `work_mem`. Ensure total memory usage is within instance limits.
The parallel queries may consume very substantially more resources than non-parallel queries, because each worker process is a completely separate process which has roughly the same impact on the system as an additional user session. This should be taken into account when choosing a value for this setting, as well as when configuring other settings that control resource utilization, such as `work_mem`. For more information, see the [PostgreSQL documentation](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM). Resource limits such as `work_mem` are applied individually to each worker, which means the total utilization may be much higher across all processes than it would normally be for any single process.
Consider increasing vCPUs or tuning memory parameters if your workload is heavily parallelized.
### Investigate connection management
If experiencing connection exhaustion, review application connection pooling strategies. Consider implementing connection pooling at the application level if not already in use.
### Review and optimize maintenance operations
Coordinate index creation and other maintenance tasks to prevent resource contention. Consider scheduling these operations during off-peak hours. Avoid scheduling heavy maintenance (e.g., parallel index builds) during periods of high user query load. These operations can consume parallel workers and impact performance for regular queries.
### Utilize Query Plan Management (QPM)
In Aurora PostgreSQL, the Query Plan Management (QPM) feature is designed to ensure plan adaptability and stability, regardless of database environment changes that might cause query plan regression. For more information, see [Overview of Aurora PostgreSQL query plan management](AuroraPostgreSQL.Optimize.overview.md).QPM provides some control over the optimizer. Review approved plans in QPM to ensure they align with current parallelism settings. Update or remove outdated plans that may be forcing suboptimal parallel execution.
You can also fix the plans using pg\$1hint\$1plan. For more information, see [Fixing plans using pg\$1hint\$1plan](AuroraPostgreSQL.Optimize.Maintenance.md#AuroraPostgreSQL.Optimize.Maintenance.pg_hint_plan). You can use the hint named `Parallel` to enforce parallel execution. For more information, see the [Hints for parallel plans](https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_table.md#hints-for-parallel-plans).
# IPC:ProcArrayGroupUpdate
The `IPC:ProcArrayGroupUpdate` event occurs when a session is waiting for the group leader to update the transaction status at the end of that operation. While PostgreSQL generally associates IPC type wait events with parallel query operations, this particular wait event is not specific to parallel queries.
**Topics**
+ [Supported engine versions](#apg-rpg-ipcprocarraygroup.supported)
+ [Context](#apg-rpg-ipcprocarraygroup.context)
+ [Likely causes of increased waits](#apg-rpg-ipcprocarraygroup.causes)
+ [Actions](#apg-rpg-ipcprocarraygroup.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
**Understanding the process array** – The process (proc) array is a shared memory structure in PostgreSQL. It holds information about all running processes, including transaction details. During transaction completion (`COMMIT` or `ROLLBACK`), the ProcArray needs to be updated to reflect the change and clear the transactionID from the array. The session attempting to finish its transaction must acquire an exclusive lock on the ProcArray. This prevents other processes from obtaining shared or exclusive locks on it.
**Group update mechanism** – While performing a COMMIT or ROLLBACK, if a backend process cannot obtain a ProcArrayLock in exclusive mode, it updates a special field called ProcArrayGroupMember. This adds the transaction to the list of sessions that intend to end. This backend process then sleeps and the time it sleeps is instrumented as the ProcArrayGroupUpdate wait event. The first process in the ProcArray with procArrayGroupMember, referred to as the leader process, acquires the ProcArrayLock in exclusive mode. It then clears the list of processes waiting for group transactionID clearing. Once this completes, the leader releases the ProcArrayLock and then wakes up all processes in this list, notifying them that their transaction is completed.
## Likely causes of increased waits
The more processes that are running, the longer a leader will hold on to a procArrayLock in exclusive mode. Consequently, the more write transactions end up in a group update scenario causing a potential pile up of processes waiting on the `ProcArrayGroupUpdate` wait event. In Database Insights' Top SQL view, you will see that COMMIT is the statement with the majority of this wait event. This is expected but will require deeper investigation into the specific write SQL being run to determine what appropriate action to take.
## Actions
We recommend different actions depending on the causes of your wait event. Identify `IPC:ProcArrayGroupUpdate` events by using Amazon RDS Performance Insights or by querying the PostgreSQL system view `pg_stat_activity`.
**Topics**
+ [Monitoring transaction commit and rollback operations](#apg-rpg-ipcprocarraygroup.actions.monitor)
+ [Reducing concurrency](#apg-rpg-ipcprocarraygroup.actions.concurrency)
+ [Implementing connection pooling](#apg-rpg-ipcprocarraygroup.actions.pooling)
### Monitoring transaction commit and rollback operations
**Monitor commits and rollbacks** – An increased number of commits and rollbacks can lead to increased pressure on the ProcArray. For example, if a SQL statement begins to fail due to increased duplicate key violations, you may see an increase in rollbacks which can increase ProcArray contention and table bloat.
Amazon RDS Database Insights provides the PostgreSQL metrics `xact_commit` and `xact_rollback` to report the number of commits and rollbacks per second.
### Reducing concurrency
**Batching transactions** – Where possible, batch operations in single transactions to reduce commit/rollback operations.
**Limit concurrency** – Reduce the number of concurrently active transactions to alleviate lock contention on the ProcArray. While it will require some testing, reducing the total number of concurrent connections can reduce contention and maintain throughput.
### Implementing connection pooling
**Connection pooling solutions** – Use connection pooling to manage database connections efficiently, reducing the total number of backends and therefore the workload on the ProcArray. While it will require some testing, reducing the total number of concurrent connections can reduce contention and maintain throughput.
For more information, see [Connection pooling for Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.BestPractices.connection_pooling.html).
**Reduce connection storms** – Similarly, a pattern of frequently creating and terminating connections causes additional pressure on the ProcArray. By reducing this pattern, overall contention is reduced.
# Lock:advisory
The `Lock:advisory` event occurs when a PostgreSQL application uses a lock to coordinate activity across multiple sessions.
**Topics**
+ [Relevant engine versions](#apg-waits.lockadvisory.context.supported)
+ [Context](#apg-waits.lockadvisory.context)
+ [Causes](#apg-waits.lockadvisory.causes)
+ [Actions](#apg-waits.lockadvisory.actions)
## Relevant engine versions
This wait event information is relevant for Aurora PostgreSQL versions 9.6 and higher.
## Context
PostgreSQL advisory locks are application-level, cooperative locks explicitly locked and unlocked by the user's application code. An application can use PostgreSQL advisory locks to coordinate activity across multiple sessions. Unlike regular, object- or row-level locks, the application has full control over the lifetime of the lock. For more information, see [Advisory Locks](https://www.postgresql.org/docs/12/explicit-locking.html#ADVISORY-LOCKS) in the PostgreSQL documentation.
Advisory locks can be released before a transaction ends or be held by a session across transactions. This isn't true for implicit, system-enforced locks, such as an access-exclusive lock on a table acquired by a `CREATE INDEX` statement.
For a description of the functions used to acquire (lock) and release (unlock) advisory locks, see [Advisory Lock Functions](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS) in the PostgreSQL documentation.
Advisory locks are implemented on top of the regular PostgreSQL locking system and are visible in the `pg_locks` system view.
## Causes
This lock type is exclusively controlled by an application explicitly using it. Advisory locks that are acquired for each row as part of a query can cause a spike in locks or a long-term buildup.
These effects happen when the query is run in a way that acquires locks on more rows than are returned by the query. The application must eventually release every lock, but if locks are acquired on rows that aren't returned, the application can't find all of the locks.
The following example is from [Advisory Locks](https://www.postgresql.org/docs/12/explicit-locking.html#ADVISORY-LOCKS) in the PostgreSQL documentation.
```
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100;
```
In this example, the `LIMIT` clause can only stop the query's output after the rows have already been internally selected and their ID values locked. This can happen suddenly when a growing data volume causes the planner to choose a different execution plan that wasn't tested during development. The buildup in this case happens because the application explicitly calls `pg_advisory_unlock` for every ID value that was locked. However, in this case it can't find the set of locks acquired on rows that weren't returned. Because the locks are acquired on the session level, they aren't released automatically at the end of the transaction.
Another possible cause for spikes in blocked lock attempts is unintended conflicts. In these conflicts, unrelated parts of the application share the same lock ID space by mistake.
## Actions
Review application usage of advisory locks and detail where and when in the application flow each type of advisory lock is acquired and released.
Determine whether a session is acquiring too many locks or a long-running session isn't releasing locks early enough, leading to a slow buildup of locks. You can correct a slow buildup of session-level locks by ending the session using `pg_terminate_backend(pid)`.
A client waiting for an advisory lock appears in `pg_stat_activity` with `wait_event_type=Lock` and `wait_event=advisory`. You can obtain specific lock values by querying the `pg_locks` system view for the same `pid`, looking for `locktype=advisory` and `granted=f`.
You can then identify the blocking session by querying `pg_locks` for the same advisory lock having `granted=t`, as shown in the following example.
```
SELECT blocked_locks.pid AS blocked_pid,
blocking_locks.pid AS blocking_pid,
blocked_activity.usename AS blocked_user,
blocking_activity.usename AS blocking_user,
now() - blocked_activity.xact_start AS blocked_transaction_duration,
now() - blocking_activity.xact_start AS blocking_transaction_duration,
concat(blocked_activity.wait_event_type,':',blocked_activity.wait_event) AS blocked_wait_event,
concat(blocking_activity.wait_event_type,':',blocking_activity.wait_event) AS blocking_wait_event,
blocked_activity.state AS blocked_state,
blocking_activity.state AS blocking_state,
blocked_locks.locktype AS blocked_locktype,
blocking_locks.locktype AS blocking_locktype,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
```
All of the advisory lock API functions have two sets of arguments, either one `bigint` argument or two `integer` arguments:
+ For the API functions with one `bigint` argument, the upper 32 bits are in `pg_locks.classid` and the lower 32 bits are in `pg_locks.objid`.
+ For the API functions with two `integer` arguments, the first argument is `pg_locks.classid` and the second argument is `pg_locks.objid`.
The `pg_locks.objsubid` value indicates which API form was used: `1` means one `bigint` argument; `2` means two `integer` arguments.
# Lock:extend
The `Lock:extend` event occurs when a backend process is waiting to lock a relation to extend it while another process has a lock on that relation for the same purpose.
**Topics**
+ [Supported engine versions](#apg-waits.lockextend.context.supported)
+ [Context](#apg-waits.lockextend.context)
+ [Likely causes of increased waits](#apg-waits.lockextend.causes)
+ [Actions](#apg-waits.lockextend.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
The event `Lock:extend` indicates that a backend process is waiting to extend a relation that another backend process holds a lock on while it's extending that relation. Because only one process at a time can extend a relation, the system generates a `Lock:extend` wait event. `INSERT`, `COPY`, and `UPDATE` operations can generate this event.
## Likely causes of increased waits
When the `Lock:extend` event appears more than normal, possibly indicating a performance problem, typical causes include the following:
**Surge in concurrent inserts or updates to the same table **
There might be an increase in the number of concurrent sessions with queries that insert into or update the same table.
**Insufficient network bandwidth**
The network bandwidth on the DB instance might be insufficient for the storage communication needs of the current workload. This can contribute to storage latency that causes an increase in `Lock:extend` events.
## Actions
We recommend different actions depending on the causes of your wait event.
**Topics**
+ [Reduce concurrent inserts and updates to the same relation](#apg-waits.lockextend.actions.action1)
+ [Increase network bandwidth](#apg-waits.lockextend.actions.increase-network-bandwidth)
### Reduce concurrent inserts and updates to the same relation
First, determine whether there's an increase in `tup_inserted` and `tup_updated` metrics and an accompanying increase in this wait event. If so, check which relations are in high contention for insert and update operations. To determine this, query the `pg_stat_all_tables` view for the values in `n_tup_ins` and `n_tup_upd` fields. For information about the `pg_stat_all_tables` view, see [pg\$1stat\$1all\$1tables](https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW) in the PostgreSQL documentation.
To get more information about blocking and blocked queries, query `pg_stat_activity` as in the following example:
```
SELECT
blocked.pid,
blocked.usename,
blocked.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query,
blocking.wait_event AS blocking_wait_event,
blocking.wait_event_type AS blocking_wait_event_type
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
where
blocked.wait_event = 'extend'
and blocked.wait_event_type = 'Lock';
pid | usename | query | blocking_id | blocking_query | blocking_wait_event | blocking_wait_event_type
------+----------+------------------------------+-------------+------------------------------------------------------------------+---------------------+--------------------------
7143 | myuser | insert into tab1 values (1); | 4600 | INSERT INTO tab1 (a) SELECT s FROM generate_series(1,1000000) s; | DataFileExtend | IO
```
After you identify relations that contribute to increase `Lock:extend` events, use the following techniques to reduce the contention:
+ Find out whether you can use partitioning to reduce contention for the same table. Separating inserted or updated tuples into different partitions can reduce contention. For information about partitioning, see [Managing PostgreSQL partitions with the pg\$1partman extension](PostgreSQL_Partitions.md).
+ If the wait event is mainly due to update activity, consider reducing the relation's fillfactor value. This can reduce requests for new blocks during the update. The fillfactor is a storage parameter for a table that determines the maximum amount of space for packing a table page. It's expressed as a percentage of the total space for a page. For more information about the fillfactor parameter, see [CREATE TABLE](https://www.postgresql.org/docs/13/sql-createtable.html) in the PostgreSQL documentation.
**Important**
We highly recommend that you test your system if you change the fillfactor because changing this value can negatively impact performance, depending on your workload.
### Increase network bandwidth
To see whether there's an increase in write latency, check the `WriteLatency` metric in CloudWatch. If there is, use the `WriteThroughput` and `ReadThroughput` Amazon CloudWatch metrics to monitor the storage related traffic on the DB cluster. These metrics can help you to determine if network bandwidth is sufficient for the storage activity of your workload.
If your network bandwidth isn't enough, increase it. If your DB instance is reaching the network bandwidth limits, the only way to increase the bandwidth is to increase your DB instance size.
For more information about CloudWatch metrics, see [Amazon CloudWatch metrics for Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md). For information about network performance for each DB instance class, see [Hardware specifications for DB instance classesfor Aurora](Concepts.DBInstanceClass.Summary.md).
# Lock:Relation
The `Lock:Relation` event occurs when a query is waiting to acquire a lock on a table or view (relation) that's currently locked by another transaction.
**Topics**
+ [Supported engine versions](#apg-waits.lockrelation.context.supported)
+ [Context](#apg-waits.lockrelation.context)
+ [Likely causes of increased waits](#apg-waits.lockrelation.causes)
+ [Actions](#apg-waits.lockrelation.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
Most PostgreSQL commands implicitly use locks to control concurrent access to data in tables. You can also use these locks explicitly in your application code with the `LOCK` command. Many lock modes aren't compatible with each other, and they can block transactions when they're trying to access the same object. When this happens, Aurora PostgreSQL generates a `Lock:Relation` event. Some common examples are the following:
+ Exclusive locks such as `ACCESS EXCLUSIVE` can block all concurrent access. Data definition language (DDL) operations such as `DROP TABLE`, `TRUNCATE`, `VACUUM FULL`, and `CLUSTER` acquire `ACCESS EXCLUSIVE` locks implicitly. `ACCESS EXCLUSIVE` is also the default lock mode for `LOCK TABLE` statements that don't specify a mode explicitly.
+ Using `CREATE INDEX (without CONCURRENT)` on a table conflicts with data manipulation language (DML) statements `UPDATE`, `DELETE`, and `INSERT`, which acquire `ROW EXCLUSIVE` locks.
For more information about table-level locks and conflicting lock modes, see [Explicit Locking](https://www.postgresql.org/docs/13/explicit-locking.html) in the PostgreSQL documentation.
Blocking queries and transactions typically unblock in one of the following ways:
+ Blocking query – The application can cancel the query or the user can end the process. The engine can also force the query to end because of a session's statement-timeout or a deadlock detection mechanism.
+ Blocking transaction – A transaction stops blocking when it runs a `ROLLBACK` or `COMMIT` statement. Rollbacks also happen automatically when sessions are disconnected by a client or by network issues, or are ended. Sessions can be ended when the database engine is shut down, when the system is out of memory, and so forth.
## Likely causes of increased waits
When the `Lock:Relation` event occurs more frequently than normal, it can indicate a performance issue. Typical causes include the following:
**Increased concurrent sessions with conflicting table locks**
There might be an increase in the number of concurrent sessions with queries that lock the same table with conflicting locking modes.
**Maintenance operations**
Health maintenance operations such as `VACUUM` and `ANALYZE` can significantly increase the number of conflicting locks. `VACUUM FULL` acquires an `ACCESS EXCLUSIVE` lock, and `ANALYZE` acquires a `SHARE UPDATE EXCLUSIVE` lock. Both types of locks can cause a `Lock:Relation` wait event. Application data maintenance operations such as refreshing a materialized view can also increase blocked queries and transactions.
**Locks on reader instances**
There might be a conflict between the relation locks held by the writer and readers. Currently, only `ACCESS EXCLUSIVE` relation locks are replicated to reader instances. However, the `ACCESS EXCLUSIVE` relation lock will conflict with any `ACCESS SHARE` relation locks held by the reader. This can cause an increase in lock relation wait events on the reader.
## Actions
We recommend different actions depending on the causes of your wait event.
**Topics**
+ [Reduce the impact of blocking SQL statements](#apg-waits.lockrelation.actions.reduce-blocks)
+ [Minimize the effect of maintenance operations](#apg-waits.lockrelation.actions.maintenance)
+ [Check for reader locks](#apg-waits.lockrelation.actions.readerlocks)
### Reduce the impact of blocking SQL statements
To reduce the impact of blocking SQL statements, modify your application code where possible. Following are two common techniques for reducing blocks:
+ Use the `NOWAIT` option – Some SQL commands, such as `SELECT` and `LOCK` statements, support this option. The `NOWAIT` directive cancels the lock-requesting query if the lock can't be acquired immediately. This technique can help prevent a blocking session from causing a pile-up of blocked sessions behind it.
For example: Assume that transaction A is waiting on a lock held by transaction B. Now, if B requests a lock on a table that’s locked by transaction C, transaction A might be blocked until transaction C completes. But if transaction B uses a `NOWAIT` when it requests the lock on C, it can fail fast and ensure that transaction A doesn't have to wait indefinitely.
+ Use `SET lock_timeout` – Set a `lock_timeout` value to limit the time a SQL statement waits to acquire a lock on a relation. If the lock isn't acquired within the timeout specified, the transaction requesting the lock is canceled. Set this value at the session level.
### Minimize the effect of maintenance operations
Maintenance operations such as `VACUUM` and `ANALYZE` are important. We recommend that you don't turn them off because you find `Lock:Relation` wait events related to these maintenance operations. The following approaches can minimize the effect of these operations:
+ Run maintenance operations manually during off-peak hours.
+ To reduce `Lock:Relation` waits caused by autovacuum tasks, perform any needed autovacuum tuning. For information about tuning autovacuum, see [ Working with PostgreSQL autovacuum on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html) in the * Amazon RDS User Guide*.
### Check for reader locks
You can see how concurrent sessions on a writer and readers might be holding locks that block each other. One way to do this is by running queries that return the lock type and relation. In the table, you can find a sequence of queries to two such concurrent sessions, a writer session and a reader session.
The replay process waits for the duration of `max_standby_streaming_delay` before canceling the reader query. As shown in the example, the lock timeout of 100ms is well below the default `max_standby_streaming_delay` of 30 seconds. The lock times out before it's an issue.
| Sequence event | Session | Command or Output |
| --- | --- | --- |
| Sets an environment variable called READER with the specified value and tries to connect to the DB instance with this endpoint. | Reader session | CLI command: export READER=aurorapg2.12345678910.us-west-1.rds.amazonaws.com
psql -h $READER
Output:
```
psql (15devel, server 10.14)
Type "help" for help.
``` |
| Sets an environment variable called WRITER and tries to connect to the DB instance with this endpoint . | Writer session | CLI command: export WRITER=aurorapg1.12345678910.us-west-1.rds.amazonaws.com
psql -h $WRITER
Output:
```
psql (15devel, server 10.14)
Type "help" for help.
``` |
| The writer session creates table t1 on the writer instance. | Writer session | PostgreSQL query: postgres=> CREATE TABLE t1(b integer);
CREATE TABLE
|
| If there are no conflicting queries on the writer, the ACCESS EXCLUSIVE lock is acquired on the writer immediately. | Writer session | `ACCESS EXCLUSIVE` lock enabled |
| The reader session sets a lock timeout interval of 100 milliseconds. | Reader session | PostgreSQL query: postgres=> SET lock_timeout=100;
SET
|
| The reader session tries to read data from table t1 on the reader instance. | Reader session | PostgreSQL query: postgres=> SELECT * FROM t1;
Sample output:
```
b
---
(0 rows)
``` |
| The writer session drops t1. | Writer session | PostgreSQL query: postgres=> BEGIN;
BEGIN
postgres=> DROP TABLE t1;
DROP TABLE
postgres=>
|
| The query times out and is canceled on the reader. | Reader session | PostgreSQL query: postgres=> SELECT * FROM t1;
Sample output:
```
ERROR: canceling statement due to lock timeout
LINE 1: SELECT * FROM t1;
^
``` |
| To determine the cause of the error. the reader session queries `pg_locks` and `pg_stat_activity` | Reader session | PostgreSQL query: postgres=> SELECT locktype, relation, mode, backend_type
postgres=> FROM pg_locks l, pg_stat_activity t1
postgres=> WHERE l.pid=t1.pid AND relation = 't1'::regclass::oid;
|
| The result indicates that the `aurora wal replay` process is holding an `ACCESS EXCLUSIVE` lock on table t1. | Reader session | Query result: locktype | relation | mode | backend_type
----------+----------+---------------------+-------------------
relation | 68628525 | AccessExclusiveLock | aurora wal replay
(1 row)
|
# Lock:transactionid
The `Lock:transactionid` event occurs when a transaction is waiting for a row-level lock.
**Topics**
+ [Supported engine versions](#apg-waits.locktransactionid.context.supported)
+ [Context](#apg-waits.locktransactionid.context)
+ [Likely causes of increased waits](#apg-waits.locktransactionid.causes)
+ [Actions](#apg-waits.locktransactionid.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
The event `Lock:transactionid` occurs when a transaction is trying to acquire a row-level lock that has already been granted to a transaction that is running at the same time. The session that shows the `Lock:transactionid` wait event is blocked because of this lock. After the blocking transaction ends in either a `COMMIT` or `ROLLBACK` statement, the blocked transaction can proceed.
The multiversion concurrency control semantics of Aurora PostgreSQL guarantee that readers don't block writers and writers don't block readers. For row-level conflicts to occur, blocking and blocked transactions must issue conflicting statements of the following types:
+ `UPDATE`
+ `SELECT … FOR UPDATE`
+ `SELECT … FOR KEY SHARE`
The statement `SELECT … FOR KEY SHARE` is a special case. The database uses the clause `FOR KEY SHARE` to optimize the performance of referential integrity. A row-level lock on a row can block `INSERT`, `UPDATE`, and `DELETE` commands on other tables that reference the row.
## Likely causes of increased waits
When this event appears more than normal, the cause is typically `UPDATE`, `SELECT … FOR UPDATE`, or `SELECT … FOR KEY SHARE` statements combined with the following conditions.
**Topics**
+ [High concurrency](#apg-waits.locktransactionid.concurrency)
+ [Idle in transaction](#apg-waits.locktransactionid.idle)
+ [Long-running transactions](#apg-waits.locktransactionid.long-running)
### High concurrency
Aurora PostgreSQL can use granular row-level locking semantics. The probability of row-level conflicts increases when the following conditions are met:
+ A highly concurrent workload contends for the same rows.
+ Concurrency increases.
### Idle in transaction
Sometimes the `pg_stat_activity.state` column shows the value `idle in transaction`. This value appears for sessions that have started a transaction, but haven't yet issued a `COMMIT` or `ROLLBACK`. If the `pg_stat_activity.state` value isn't `active`, the query shown in `pg_stat_activity` is the most recent one to finish running. The blocking session isn't actively processing a query because an open transaction is holding a lock.
If an idle transaction acquired a row-level lock, it might be preventing other sessions from acquiring it. This condition leads to frequent occurrence of the wait event `Lock:transactionid`. To diagnose the issue, examine the output from `pg_stat_activity` and `pg_locks`.
### Long-running transactions
Transactions that run for a long time get locks for a long time. These long-held locks can block other transactions from running.
## Actions
Row-locking is a conflict among `UPDATE`, `SELECT … FOR UPDATE`, or `SELECT … FOR KEY SHARE` statements. Before attempting a solution, find out when these statements are running on the same row. Use this information to choose a strategy described in the following sections.
**Topics**
+ [Respond to high concurrency](#apg-waits.locktransactionid.actions.problem)
+ [Respond to idle transactions](#apg-waits.locktransactionid.actions.find-blocker)
+ [Respond to long-running transactions](#apg-waits.locktransactionid.actions.concurrency)
### Respond to high concurrency
If concurrency is the issue, try one of the following techniques:
+ Lower the concurrency in the application. For example, decrease the number of active sessions.
+ Implement a connection pool. To learn how to pool connections with RDS Proxy, see [Amazon RDS Proxyfor Aurora](rds-proxy.md).
+ Design the application or data model to avoid contending `UPDATE` and `SELECT … FOR UPDATE` statements. You can also decrease the number of foreign keys accessed by `SELECT … FOR KEY SHARE` statements.
### Respond to idle transactions
If `pg_stat_activity.state` shows `idle in transaction`, use the following strategies:
+ Turn on autocommit wherever possible. This approach prevents transactions from blocking other transactions while waiting for a `COMMIT` or `ROLLBACK`.
+ Search for code paths that are missing `COMMIT`, `ROLLBACK`, or `END`.
+ Make sure that the exception handling logic in your application always has a path to a valid `end of transaction`.
+ Make sure that your application processes query results after ending the transaction with `COMMIT` or `ROLLBACK`.
### Respond to long-running transactions
If long-running transactions are causing the frequent occurrence of `Lock:transactionid`, try the following strategies:
+ Keep row locks out of long-running transactions.
+ Limit the length of queries by implementing autocommit whenever possible.
# Lock:tuple
The `Lock:tuple` event occurs when a backend process is waiting to acquire a lock on a tuple.
**Topics**
+ [Supported engine versions](#apg-waits.locktuple.context.supported)
+ [Context](#apg-waits.locktuple.context)
+ [Likely causes of increased waits](#apg-waits.locktuple.causes)
+ [Actions](#apg-waits.locktuple.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
The event `Lock:tuple` indicates that a backend is waiting to acquire a lock on a tuple while another backend holds a conflicting lock on the same tuple. The following table illustrates a scenario in which sessions generate the `Lock:tuple` event.
| Time | Session 1 | Session 2 | Session 3 |
| --- | --- | --- | --- |
| t1 | Starts a transaction. | | |
| t2 | Updates row 1. | | |
| t3 | | Updates row 1. The session acquires an exclusive lock on the tuple and then waits for session 1 to release the lock by committing or rolling back. | |
| t4 | | | Updates row 1. The session waits for session 2 to release the exclusive lock on the tuple. |
Or you can simulate this wait event by using the benchmarking tool `pgbench`. Configure a high number of concurrent sessions to update the same row in a table with a custom SQL file.
To learn more about conflicting lock modes, see [Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html) in the PostgreSQL documentation. To learn more about `pgbench`, see [pgbench](https://www.postgresql.org/docs/current/pgbench.html) in the PostgreSQL documentation.
## Likely causes of increased waits
When this event appears more than normal, possibly indicating a performance problem, typical causes include the following:
+ A high number of concurrent sessions are trying to acquire a conflicting lock for the same tuple by running `UPDATE` or `DELETE` statements.
+ Highly concurrent sessions are running a `SELECT` statement using the `FOR UPDATE` or `FOR NO KEY UPDATE` lock modes.
+ Various factors drive application or connection pools to open more sessions to execute the same operations. As new sessions are trying to modify the same rows, DB load can spike, and `Lock:tuple` can appear.
For more information, see [Row-Level Locks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS) in the PostgreSQL documentation.
## Actions
We recommend different actions depending on the causes of your wait event.
**Topics**
+ [Investigate your application logic](#apg-waits.locktuple.actions.problem)
+ [Find the blocker session](#apg-waits.locktuple.actions.find-blocker)
+ [Reduce concurrency when it is high](#apg-waits.locktuple.actions.concurrency)
+ [Troubleshoot bottlenecks](#apg-waits.locktuple.actions.bottlenecks)
### Investigate your application logic
Find out whether a blocker session has been in the `idle in transaction` state for long time. If so, consider ending the blocker session as a short-term solution. You can use the `pg_terminate_backend` function. For more information about this function, see [Server Signaling Functions](https://www.postgresql.org/docs/13/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL) in the PostgreSQL documentation.
For a long-term solution, do the following:
+ Adjust the application logic.
+ Use the `idle_in_transaction_session_timeout` parameter. This parameter ends any session with an open transaction that has been idle for longer than the specified amount of time. For more information, see [Client Connection Defaults](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT) in the PostgreSQL documentation.
+ Use autocommit as much as possible. For more information, see [SET AUTOCOMMIT](https://www.postgresql.org/docs/current/ecpg-sql-set-autocommit.html) in the PostgreSQL documentation.
### Find the blocker session
While the `Lock:tuple` wait event is occurring, identify the blocker and blocked session by finding out which locks depend on one another. For more information, see [Lock dependency information](https://wiki.postgresql.org/wiki/Lock_dependency_information) in the PostgreSQL wiki. To analyze past `Lock:tuple` events, use the Aurora function `aurora_stat_backend_waits`.
The following example queries all sessions, filtering on `tuple` and ordering by `wait_time`.
```
--AURORA_STAT_BACKEND_WAITS
SELECT a.pid,
a.usename,
a.app_name,
a.current_query,
a.current_wait_type,
a.current_wait_event,
a.current_state,
wt.type_name AS wait_type,
we.event_name AS wait_event,
a.waits,
a.wait_time
FROM (SELECT pid,
usename,
left(application_name,16) AS app_name,
coalesce(wait_event_type,'CPU') AS current_wait_type,
coalesce(wait_event,'CPU') AS current_wait_event,
state AS current_state,
left(query,80) as current_query,
(aurora_stat_backend_waits(pid)).*
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND usename<>'rdsadmin') a
NATURAL JOIN aurora_stat_wait_type() wt
NATURAL JOIN aurora_stat_wait_event() we
WHERE we.event_name = 'tuple'
ORDER BY a.wait_time;
pid | usename | app_name | current_query | current_wait_type | current_wait_event | current_state | wait_type | wait_event | waits | wait_time
-------+---------+----------+------------------------------------------------+-------------------+--------------------+---------------+-----------+------------+-------+-----------
32136 | sys | psql | /*session3*/ update tab set col=1 where col=1; | Lock | tuple | active | Lock | tuple | 1 | 1000018
11999 | sys | psql | /*session4*/ update tab set col=1 where col=1; | Lock | tuple | active | Lock | tuple | 1 | 1000024
```
### Reduce concurrency when it is high
The `Lock:tuple` event might occur constantly, especially in a busy workload time. In this situation, consider reducing the high concurrency for very busy rows. Often, just a few rows control a queue or the Boolean logic, which makes these rows very busy.
You can reduce concurrency by using different approaches based in the business requirement, application logic, and workload type. For example, you can do the following:
+ Redesign your table and data logic to reduce high concurrency.
+ Change the application logic to reduce high concurrency at the row level.
+ Leverage and redesign queries with row-level locks.
+ Use the `NOWAIT` clause with retry operations.
+ Consider using optimistic and hybrid-locking logic concurrency control.
+ Consider changing the database isolation level.
### Troubleshoot bottlenecks
The `Lock:tuple` can occur with bottlenecks such as CPU starvation or maximum usage of Amazon EBS bandwidth. To reduce bottlenecks, consider the following approaches:
+ Scale up your instance class type.
+ Optimize resource-intensive queries.
+ Change the application logic.
+ Archive data that is rarely accessed.
# LWLock:buffer\$1content (BufferContent)
The `LWLock:buffer_content` event occurs when a session is waiting to read or write a data page in memory while another session has that page locked for writing. In Aurora PostgreSQL 13 and higher, this wait event is called `BufferContent`.
**Topics**
+ [Supported engine versions](#apg-waits.lockbuffercontent.context.supported)
+ [Context](#apg-waits.lockbuffercontent.context)
+ [Likely causes of increased waits](#apg-waits.lockbuffercontent.causes)
+ [Actions](#apg-waits.lockbuffercontent.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
To read or manipulate data, PostgreSQL accesses it through shared memory buffers. To read from the buffer, a process gets a lightweight lock (LWLock) on the buffer content in shared mode. To write to the buffer, it gets that lock in exclusive mode. Shared locks allow other processes to concurrently acquire shared locks on that content. Exclusive locks prevent other processes from getting any type of lock on it.
The `LWLock:buffer_content` (`BufferContent`) event indicates that multiple processes are attempting to get lightweight locks (LWLocks) on contents of a specific buffer.
## Likely causes of increased waits
When the `LWLock:buffer_content` (`BufferContent`) event appears more than normal, possibly indicating a performance problem, typical causes include the following:
**Increased concurrent updates to the same data**
There might be an increase in the number of concurrent sessions with queries that update the same buffer content. This contention can be more pronounced on tables with a lot of indexes.
**Workload data is not in memory**
When data that the active workload is processing is not in memory, these wait events can increase. This effect is because processes holding locks can keep them longer while they perform disk I/O operations.
**Excessive use of foreign key constraints**
Foreign key constraints can increase the amount of time a process holds onto a buffer content lock. This effect is because read operations require a shared buffer content lock on the referenced key while that key is being updated.
## Actions
We recommend different actions depending on the causes of your wait event. You might identify `LWLock:buffer_content` (`BufferContent`) events by using Amazon RDS Performance Insights or by querying the view `pg_stat_activity`.
**Topics**
+ [Improve in-memory efficiency](#apg-waits.lockbuffercontent.actions.in-memory)
+ [Reduce usage of foreign key constraints](#apg-waits.lockbuffercontent.actions.foreignkey)
+ [Remove unused indexes](#apg-waits.lockbuffercontent.actions.indexes)
+ [Remove duplicate indexes](#apg-waits.lockbuffercontent.actions.duplicate-indexes)
+ [Drop or REINDEX invalid indexes](#apg-waits.lockbuffercontent.actions.invalid-indexes)
+ [Use partial indexes](#apg-waits.lockbuffercontent.actions.partial-indexes)
+ [Remove table and index bloat](#apg-waits.lockbuffercontent.actions.bloat)
### Improve in-memory efficiency
To increase the chance that active workload data is in memory, partition tables or scale up your instance class. For information about DB instance classes, see [Amazon AuroraDB instance classes](Concepts.DBInstanceClass.md).
Monitor the `BufferCacheHitRatio` metric, which measures the percentage of requests served by the buffer cache of a DB instance in your DB cluster. This metric provides insight into the amount of data being served from memory. A high hit ratio indicates that your DB instance has sufficient memory available for your working data set, while a low ratio suggests that your queries are frequently accessing data from storage.
The cache read hit per table and cache read hit per index under Memory setting section of the [PG Collector](https://github.com/awslabs/pg-collector) report can provide insights into the tables and indexes cache hit ratio.
### Reduce usage of foreign key constraints
Investigate workloads experiencing high numbers of `LWLock:buffer_content` (`BufferContent`) wait events for usage of foreign key constraints. Remove unnecessary foreign key constraints.
### Remove unused indexes
For workloads experiencing high numbers of `LWLock:buffer_content` (`BufferContent`) wait events, identify unused indexes and remove them.
The unused indexes section of the [PG Collector](https://github.com/awslabs/pg-collector) report can provide insights into the unused indexes in the database.
### Remove duplicate indexes
Identify duplicate indexes and remove them.
The duplicate indexes section of the [PG Collector](https://github.com/awslabs/pg-collector) report can provide insights into the duplicate indexes in the database.
### Drop or REINDEX invalid indexes
Invalid indexes typically occur when using `CREATE INDEX CONCURRENTLY` or `REINDEX CONCURRENTLY` and the command fails or is aborted.
Invalid indexes can't be used for queries, though they will still be updated and take up disk space.
The Invalid indexes section of the [PG Collector](https://github.com/awslabs/pg-collector) report can provide insights into the invalid indexes in the database.
### Use partial indexes
Partial indexes can be leveraged to enhance query performance and reduce index size. A partial index is an index built over a subset of a table, with the subset defined by a conditional expression. As detailed in the [partial index](https://www.postgresql.org/docs/current/indexes-partial.html) documentation, partial indexes can reduce the overhead of maintaining indexes, as PostgreSQL does not need to update the index in all cases.
### Remove table and index bloat
Excessive table and index bloat can negatively impact database performance. Bloated tables and indexes increase the active working set size, degrading in-memory efficiency. Additionally, bloat increases storage costs and slows query execution. To diagnose bloat, refer to the [Diagnosing table and index bloat](AuroraPostgreSQL.diag-table-ind-bloat.md). Further, the Fragmentation (Bloat) section of the [PG Collector](https://github.com/awslabs/pg-collector) report can provide insights into tables and indexes bloat.
To address table and index bloat, there are a few options:
**VACUUM FULL**
`VACUUM FULL` creates a new copy of the table, copying over only the live tuples, and then replaces the old table with the new one while holding an `ACCESS EXCLUSIVE` lock. This prevents any reading or writing to the table, which can cause an outage. Additionally, `VACUUM FULL` will take longer if the table is large.
**pg\$1repack**
The `pg_repack` is helpful in situations where `VACUUM FULL` might not be suitable. It creates a new table that contains the data of the bloated table, tracks the changes from the original table, and then replaces the original table with the new one. It doesn't lock the original table for read or write operations while it's building the new table. For more information, for how to use `pg_repack`, see [Removing bloat with pg\$1repack](https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-maintenance-rds-aurora/pg-repack.html) and [pg\$1repack](https://reorg.github.io/pg_repack/).
**REINDEX**
The `REINDEX` command can be leveraged to address index bloat. `REINDEX` writes a new version of the index without the dead pages or the empty or nearly-empty pages, thereby reducing the space consumption of the index. For detailed information about the [https://www.postgresql.org/docs/current/sql-reindex.html](https://www.postgresql.org/docs/current/sql-reindex.html) command, please refer to the REINDEX documentation.
After removing bloat from tables and indexes, it may be necessary to increase the autovacuum frequency on those tables. Implementing aggressive autovacuum settings at the table level can help prevent future bloat from occurring. For more information, please refer to the documentation on [https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-maintenance-rds-aurora/autovacuum.html](https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-maintenance-rds-aurora/autovacuum.html).
# LWLock:buffer\$1mapping
This event occurs when a session is waiting to associate a data block with a buffer in the shared buffer pool.
**Note**
This event appears as `LWLock:buffer_mapping` in Aurora PostgreSQL version 12 and lower, and `LWLock:BufferMapping` in version 13 and higher.
**Topics**
+ [Supported engine versions](#apg-waits.lwl-buffer-mapping.context.supported)
+ [Context](#apg-waits.lwl-buffer-mapping.context)
+ [Causes](#apg-waits.lwl-buffer-mapping.causes)
+ [Actions](#apg-waits.lwl-buffer-mapping.actions)
## Supported engine versions
This wait event information is relevant for Aurora PostgreSQL version 9.6 and higher.
## Context
The *shared buffer pool* is an Aurora PostgreSQL memory area that holds all pages that are or were being used by processes. When a process needs a page, it reads the page into the shared buffer pool. The `shared_buffers` parameter sets the shared buffer size and reserves a memory area to store the table and index pages. If you change this parameter, make sure to restart the database. For more information, see [Shared buffers](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.buffer-pool).
The `LWLock:buffer_mapping` wait event occurs in the following scenarios:
+ A process searches the buffer table for a page and acquires a shared buffer mapping lock.
+ A process loads a page into the buffer pool and acquires an exclusive buffer mapping lock.
+ A process removes a page from the pool and acquires an exclusive buffer mapping lock.
## Causes
When this event appears more than normal, possibly indicating a performance problem, the database is paging in and out of the shared buffer pool. Typical causes include the following:
+ Large queries
+ Bloated indexes and tables
+ Full table scans
+ A shared pool size that is smaller than the working set
## Actions
We recommend different actions depending on the causes of your wait event.
**Topics**
+ [Monitor buffer-related metrics](#apg-waits.lwl-buffer-mapping.actions.monitor-metrics)
+ [Assess your indexing strategy](#apg-waits.lwl-buffer-mapping.actions.indexes)
+ [Reduce the number of buffers that must be allocated quickly](#apg-waits.lwl-buffer-mapping.actions.buffers)
### Monitor buffer-related metrics
When `LWLock:buffer_mapping` waits spike, investigate the buffer hit ratio. You can use these metrics to get a better understanding of what is happening in the buffer cache. Examine the following metrics:
`BufferCacheHitRatio`
This Amazon CloudWatch metric measures the percentage of requests that are served by the buffer cache of a DB instance in your DB cluster. You might see this metric decrease in the lead-up to the `LWLock:buffer_mapping` wait event.
`blks_hit`
This Performance Insights counter metric indicates the number of blocks that were retrieved from the shared buffer pool. After the `LWLock:buffer_mapping` wait event appears, you might observe a spike in `blks_hit`.
`blks_read`
This Performance Insights counter metric indicates the number of blocks that required I/O to be read into the shared buffer pool. You might observe a spike in `blks_read` in the lead-up to the `LWLock:buffer_mapping` wait event.
### Assess your indexing strategy
To confirm that your indexing strategy is not degrading performance, check the following:
Index bloat
Ensure that index and table bloat aren't leading to unnecessary pages being read into the shared buffer. If your tables contain unused rows, consider archiving the data and removing the rows from the tables. You can then rebuild the indexes for the resized tables.
Indexes for frequently used queries
To determine whether you have the optimal indexes, monitor DB engine metrics in Performance Insights. The `tup_returned` metric shows the number of rows read. The `tup_fetched` metric shows the number of rows returned to the client. If `tup_returned` is significantly larger than `tup_fetched`, the data might not be properly indexed. Also, your table statistics might not be current.
### Reduce the number of buffers that must be allocated quickly
To reduce the `LWLock:buffer_mapping` wait events, try to reduce the number of buffers that must be allocated quickly. One strategy is to perform smaller batch operations. You might be able to achieve smaller batches by partitioning your tables.
# LWLock:BufferIO (IPC:BufferIO)
The `LWLock:BufferIO` event occurs when Aurora PostgreSQL or RDS for PostgreSQL is waiting for other processes to finish their input/output (I/O) operations when concurrently trying to access a page. Its purpose is for the same page to be read into the shared buffer.
**Topics**
+ [Relevant engine versions](#apg-waits.lwlockbufferio.context.supported)
+ [Context](#apg-waits.lwlockbufferio.context)
+ [Causes](#apg-waits.lwlockbufferio.causes)
+ [Actions](#apg-waits.lwlockbufferio.actions)
## Relevant engine versions
This wait event information is relevant for all Aurora PostgreSQL versions. For Aurora PostgreSQL 12 and earlier versions this wait event is named as lwlock:buffer\$1io whereas in Aurora PostgreSQL 13 version it is named as lwlock:bufferio. From Aurora PostgreSQL 14 version BufferIO wait event moved from `LWLock` to `IPC` wait event type (IPC:BufferIO).
## Context
Each shared buffer has an I/O lock that is associated with the `LWLock:BufferIO` wait event, each time a block (or a page) has to be retrieved outside the shared buffer pool.
This lock is used to handle multiple sessions that all require access to the same block. This block has to be read from outside the shared buffer pool, defined by the `shared_buffers` parameter.
As soon as the page is read inside the shared buffer pool, the `LWLock:BufferIO` lock is released.
**Note**
The `LWLock:BufferIO` wait event precedes the [IO:DataFileRead](apg-waits.iodatafileread.md) wait event. The `IO:DataFileRead` wait event occurs while data is being read from storage.
For more information on lightweight locks, see [Locking Overview](https://github.com/postgres/postgres/blob/65dc30ced64cd17f3800ff1b73ab1d358e92efd8/src/backend/storage/lmgr/README#L20).
## Causes
Common causes for the `LWLock:BufferIO` event to appear in top waits include the following:
+ Multiple backends or connections trying to access the same page that's also pending an I/O operation
+ The ratio between the size of the shared buffer pool (defined by the `shared_buffers` parameter) and the number of buffers needed by the current workload
+ The size of the shared buffer pool not being well balanced with the number of pages being evicted by other operations
+ Large or bloated indexes that require the engine to read more pages than necessary into the shared buffer pool
+ Lack of indexes that forces the DB engine to read more pages from the tables than necessary
+ Sudden spikes for database connections trying to perform operations on the same page
## Actions
We recommend different actions depending on the causes of your wait event:
+ Observe Amazon CloudWatch metrics for correlation between sharp decreases in the `BufferCacheHitRatio` and `LWLock:BufferIO` wait events. This effect can mean that you have a small shared buffers setting. You might need to increase it or scale up your DB instance class. You can split your workload into more reader nodes.
+ Verify whether you have unused indexes, then remove them.
+ Use partitioned tables (which also have partitioned indexes). Doing this helps to keep index reordering low and reduces its impact.
+ Avoid indexing columns unnecessarily.
+ Prevent sudden database connection spikes by using a connection pool.
+ Restrict the maximum number of connections to the database as a best practice.
# LWLock:lock\$1manager
This event occurs when the Aurora PostgreSQL engine maintains the shared lock's memory area to allocate, check, and deallocate a lock when a fast path lock isn't possible.
**Topics**
+ [Supported engine versions](#apg-waits.lw-lock-manager.context.supported)
+ [Context](#apg-waits.lw-lock-manager.context)
+ [Likely causes of increased waits](#apg-waits.lw-lock-manager.causes)
+ [Actions](#apg-waits.lw-lock-manager.actions)
## Supported engine versions
This wait event information is relevant for Aurora PostgreSQL version 9.6 and higher.
## Context
When you issue a SQL statement, Aurora PostgreSQL records locks to protect the structure, data, and integrity of your database during concurrent operations. The engine can achieve this goal using a fast path lock or a path lock that isn't fast. A path lock that isn't fast is more expensive and creates more overhead than a fast path lock.
### Fast path locking
To reduce the overhead of locks that are taken and released frequently, but that rarely conflict, backend processes can use fast path locking. The database uses this mechanism for locks that meet the following criteria:
+ They use the DEFAULT lock method.
+ They represent a lock on a database relation rather than a shared relation.
+ They are weak locks that are unlikely to conflict.
+ The engine can quickly verify that no conflicting locks can possibly exist.
The engine can't use fast path locking when either of the following conditions is true:
+ The lock doesn't meet the preceding criteria.
+ No more slots are available for the backend process.
For more information about fast path locking, see [fast path](https://github.com/postgres/postgres/blob/master/src/backend/storage/lmgr/README#L70-L76) in the PostgreSQL lock manager README and [pg-locks](https://www.postgresql.org/docs/15/view-pg-locks.html) in the PostgreSQL documentation.
### Example of a scaling problem for the lock manager
In this example, a table named `purchases` stores five years of data, partitioned by day. Each partition has two indexes. The following sequence of events occurs:
1. You query many days worth of data, which requires the database to read many partitions.
1. The database creates a lock entry for each partition. If partition indexes are part of the optimizer access path, the database creates a lock entry for them, too.
1. When the number of requested locks entries for the same backend process is higher than 16, which is the value of `FP_LOCK_SLOTS_PER_BACKEND`, the lock manager uses the non–fast path lock method.
Modern applications might have hundreds of sessions. If concurrent sessions are querying the parent without proper partition pruning, the database might create hundreds or even thousands of non–fast path locks. Typically, when this concurrency is higher than the number of vCPUs, the `LWLock:lock_manager` wait event appears.
**Note**
The `LWLock:lock_manager` wait event isn't related to the number of partitions or indexes in a database schema. Instead, it's related to the number of non–fast path locks that the database must control.
## Likely causes of increased waits
When the `LWLock:lock_manager` wait event occurs more than normal, possibly indicating a performance problem, the most likely causes of sudden spikes are as follows:
+ Concurrent active sessions are running queries that don't use fast path locks. These sessions also exceed the maximum vCPU.
+ A large number of concurrent active sessions are accessing a heavily partitioned table. Each partition has multiple indexes.
+ The database is experiencing a connection storm. By default, some applications and connection pool software create more connections when the database is slow. This practice makes the problem worse. Tune your connection pool software so that connection storms don't occur.
+ A large number of sessions query a parent table without pruning partitions.
+ A data definition language (DDL), data manipulation language (DML), or a maintenance command exclusively locks either a busy relation or tuples that are frequently accessed or modified.
## Actions
We recommend different actions depending on the causes of your wait event.
**Topics**
+ [Use partition pruning](#apg-waits.lw-lock-manager.actions.pruning)
+ [Remove unnecessary indexes](#apg-waits.lw-lock-manager.actions.indexes)
+ [Tune your queries for fast path locking](#apg-waits.lw-lock-manager.actions.tuning)
+ [Tune for other wait events](#apg-waits.lw-lock-manager.actions.other-waits)
+ [Reduce hardware bottlenecks](#apg-waits.lw-lock-manager.actions.hw-bottlenecks)
+ [Use a connection pooler](#apg-waits.lw-lock-manager.actions.pooler)
+ [Upgrade your Aurora PostgreSQL version](#apg-waits.lw-lock-manager.actions.pg-version)
### Use partition pruning
*Partition pruning* is a query optimization strategy that excludes unneeded partitions from table scans, thereby improving performance. Partition pruning is turned on by default. If it is turned off, turn it on as follows.
```
SET enable_partition_pruning = on;
```
Queries can take advantage of partition pruning when their `WHERE` clause contains the column used for the partitioning. For more information, see [Partition Pruning](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING) in the PostgreSQL documentation.
### Remove unnecessary indexes
Your database might contain unused or rarely used indexes. If so, consider deleting them. Do either of the following:
+ Learn how to find unnecessary indexes by reading [Unused Indexes](https://wiki.postgresql.org/wiki/Index_Maintenance#Unused_Indexes) in the PostgreSQL wiki.
+ Run PG Collector. This SQL script gathers database information and presents it in a consolidated HTML report. Check the "Unused indexes" section. For more information, see [pg-collector](https://github.com/awslabs/pg-collector) in the AWS Labs GitHub repository.
### Tune your queries for fast path locking
To find out whether your queries use fast path locking, query the `fastpath` column in the `pg_locks` table. If your queries aren't using fast path locking, try to reduce number of relations per query to fewer than 16.
### Tune for other wait events
If `LWLock:lock_manager` is first or second in the list of top waits, check whether the following wait events also appear in the list:
+ `Lock:Relation`
+ `Lock:transactionid`
+ `Lock:tuple`
If the preceding events appear high in the list, consider tuning these wait events first. These events can be a driver for `LWLock:lock_manager`.
### Reduce hardware bottlenecks
You might have a hardware bottleneck, such as CPU starvation or maximum usage of your Amazon EBS bandwidth. In these cases, consider reducing the hardware bottlenecks. Consider the following actions:
+ Scale up your instance class.
+ Optimize queries that consume large amounts of CPU and memory.
+ Change your application logic.
+ Archive your data.
For more information about CPU, memory, and EBS network bandwidth, see [Amazon RDS Instance Types](https://aws.amazon.com/rds/instance-types/).
### Use a connection pooler
If your total number of active connections exceeds the maximum vCPU, more OS processes require CPU than your instance type can support. In this case, consider using or tuning a connection pool. For more information about the vCPUs for your instance type, see [Amazon RDS Instance Types](https://aws.amazon.com/rds/instance-types/).
For more information about connection pooling, see the following resources:
+ [Amazon RDS Proxyfor Aurora](rds-proxy.md)
+ [pgbouncer](http://www.pgbouncer.org/usage.html)
+ [Connection Pools and Data Sources](https://www.postgresql.org/docs/7.4/jdbc-datasource.html) in the *PostgreSQL Documentation*
### Upgrade your Aurora PostgreSQL version
If your current version of Aurora PostgreSQL is lower than 12, upgrade to version 12 or higher. PostgreSQL versions 12 and 13 have an improved partition mechanism. For more information about version 12, see [PostgreSQL 12.0 Release Notes]( https://www.postgresql.org/docs/release/12.0/). For more information about upgrading Aurora PostgreSQL, see [Database engine updates for Amazon Aurora PostgreSQL](AuroraPostgreSQL.Updates.md).
# LWLock:MultiXact
The `LWLock:MultiXactMemberBuffer`, `LWLock:MultiXactOffsetBuffer`, `LWLock:MultiXactMemberSLRU`, and `LWLock:MultiXactOffsetSLRU` wait events indicate that a session is waiting to retrieve a list of transactions that modifies the same row in a given table.
+ `LWLock:MultiXactMemberBuffer` – A process is waiting for I/O on a simple least-recently used (SLRU) buffer for a multixact member.
+ `LWLock:MultiXactMemberSLRU` – A process is waiting to access the simple least-recently used (SLRU) cache for a multixact member.
+ `LWLock:MultiXactOffsetBuffer` – A process is waiting for I/O on a simple least-recently used (SLRU) buffer for a multixact offset.
+ `LWLock:MultiXactOffsetSLRU` – A process is waiting to access the simple least-recently used (SLRU) cache for a multixact offset.
**Topics**
+ [Supported engine versions](#apg-waits.xactsync.context.supported)
+ [Context](#apg-waits.lwlockmultixact.context)
+ [Likely causes of increased waits](#apg-waits.lwlockmultixact.causes)
+ [Actions](#apg-waits.lwlockmultixact.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
A *multixact* is a data structure that stores a list of transaction IDs (XIDs) that modify the same table row. When a single transaction references a row in a table, the transaction ID is stored in the table header row. When multiple transactions reference the same row in a table, the list of transaction IDs is stored in the multixact data structure. The multixact wait events indicate that a session is retrieving from the data structure the list of transactions that refer to a given row in a table.
## Likely causes of increased waits
Three common causes of multixact use are as follows:
+ **Sub-transactions from explicit savepoints** – Explicitly creating a savepoint in your transactions spawns new transactions for the same row. For example, using `SELECT FOR UPDATE`, then `SAVEPOINT`, and then `UPDATE`.
Some drivers, object-relational mappers (ORMs), and abstraction layers have configuration options for automatically wrapping all operations with savepoints. This can generate many multixact wait events in some workloads. The PostgreSQL JDBC Driver's `autosave` option is an example of this. For more information, see [pgJDBC](https://jdbc.postgresql.org/) in the PostgreSQL JDBC documentation. Another example is the PostgreSQL ODBC driver and its `protocol` option. For more information, see [psqlODBC Configuration Options](https://odbc.postgresql.org/docs/config.html) in the PostgreSQL ODBC driver documentation.
+ **Sub-transactions from PL/pgSQL EXCEPTION clauses** – Each `EXCEPTION` clause that you write in your PL/pgSQL functions or procedures creates a `SAVEPOINT` internally.
+ **Foreign keys** – Multiple transactions acquire a shared lock on the parent record.
When a given row is included in a multiple transaction operation, processing the row requires retrieving transaction IDs from the `multixact` listings. If lookups can't get the multixact from the memory cache, the data structure must be read from the Aurora storage layer. This I/O from storage means that SQL queries can take longer. Memory cache misses can start occurring with heavy usage due to a large number of multiple transactions. All these factors contribute to an increase in this wait event.
## Actions
We recommend different actions depending on the causes of your wait event. Some of these actions can help in immediate reduction of the wait events. But, others might require investigation and correction to scale your workload.
**Topics**
+ [Perform vacuum freeze on tables with this wait event](#apg-waits.lwlockmultixact.actions.vacuumfreeze)
+ [Increase autovacuum frequency on tables with this wait event](#apg-waits.lwlockmultixact.actions.autovacuum)
+ [Increase memory parameters](#apg-waits.lwlockmultixact.actions.memoryparam)
+ [Reduce long-running transactions](#apg-waits.lwlockmultixact.actions.longtransactions)
+ [Long term actions](#apg-waits.lwlockmultixact.actions.longactions)
### Perform vacuum freeze on tables with this wait event
If this wait event spikes suddenly and affects your production environment, you can use any of the following temporary methods to reduce its count.
+ Use *VACUUM FREEZE* on the affected table or table partition to resolve the issue immediately. For more information, see [VACUUM](https://www.postgresql.org/docs/current/sql-vacuum.html).
+ Use the VACUUM (FREEZE, INDEX\$1CLEANUP FALSE) clause to perform a quick vacuum by skipping the indexes. For more information, see [Vacuuming a table as quickly as possible](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.html#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.Executing).
### Increase autovacuum frequency on tables with this wait event
After scanning all tables in all databases, VACUUM will eventually remove multixacts, and their oldest multixact values are advanced. For more information, see [Multixacts and Wraparound](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND). To keep the LWLock:MultiXact wait events to its minimum, you must run the VACUUM as often as necessary. To do so, ensure that the VACUUM in your Aurora PostgreSQL DB cluster is configured optimally.
If using VACUUM FREEZE on the affected table or table partition resolves the wait event issue, we recommend using a scheduler, such as `pg_cron`, to perform the VACUUM instead of adjusting autovacuum at the instance level.
For the autovacuum to happen more frequently, you can reduce the value of the storage parameter `autovacuum_multixact_freeze_max_age` in the affected table. For more information, see [autovacuum\$1multixact\$1freeze\$1max\$1age](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE).
### Increase memory parameters
You can optimize memory usage for multixact caches by adjusting the following parameters. These settings control how much memory is reserved for these caches, which can help reduce multixact wait events in your workload. We recommend starting with the following values:
For Aurora PostgreSQL 17 and later:
+ `multixact_offset_buffers` = 128
+ `multixact_member_buffers` = 256
For Aurora PostgreSQL 16 and earlier:
+ `multixact_offsets_cache_size` = 128
+ `multixact_members_cache_size` = 256
**Note**
In Aurora PostgreSQL 17, parameter names were changed from `multixact_offsets_cache_size` to `multixact_offset_buffers` and from `multixact_members_cache_size` to `multixact_member_buffers` to align with community PostgreSQL 17.
You can set these parameters at the cluster level so that all instances in your cluster remain consistent. We recommend you to test and adjust the values to best suit your specific workload requirements and instance class. You must reboot the writer instance for the parameter changes to take effect.
The parameters are expressed in terms of multixact cache entries. Each cache entry uses `8 KB` of memory. To calculate the total memory reserved, multiply each parameter value by `8 KB`. For example, if you set a parameter to 128, the total reserved memory would be `128 * 8 KB = 1 MB`.
### Reduce long-running transactions
Long-running transaction causes the vacuum to retain its information until the transaction is committed or until the read-only transaction is closed. We recommend that you proactively monitor and manage long-running transactions. For more information, see [Database has long running idle in transaction connection](PostgreSQL.Tuning_proactive_insights.md#proactive-insights.idle-txn). Try to modify your application to avoid or minimize your use of long-running transactions.
### Long term actions
Examine your workload to discover the cause for the multixact spillover. You must fix the issue in order to scale your workload and reduce the wait event.
+ You must analyze the DDL (data definition language) used to create your tables. Make sure that the table structures and indexes are well designed.
+ When the affected tables have foreign keys, determine whether they are needed or if there is another way to enforce referential integrity.
+ When a table has large unused indexes, it can cause autovacuum to not fit your workload and might block it from running. To avoid this, check for unused indexes and remove them completely. For more information, see [Managing autovacuum with large indexes](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.html).
+ Reduce the use of savepoints in your transactions.
# LWLock:pg\$1stat\$1statements
The LWLock:pg\$1stat\$1statements wait event occurs when the `pg_stat_statements` extension takes an exclusive lock on the hash table that tracks SQL statements. This happens in the following scenarios:
+ When the number of tracked statements reaches the configured `pg_stat_statements.max` parameter value and there is a need to make room for more entries, the extension performs a sort on the number of calls, removes the 5% of the least-executed statements, and re-populates the hash with the remaining entries.
+ When `pg_stat_statements` performs a `garbage collection` operation to the `pgss_query_texts.stat` file on disk and rewrites the file.
**Topics**
+ [Supported engine versions](#apg-rpg-lwlockpgstat.supported)
+ [Context](#apg-rpg-lwlockpgstat.context)
+ [Likely causes of increased waits](#apg-rpg-lwlockpgstat.causes)
+ [Actions](#apg-rpg-lwlockpgstat.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Context
**Understanding the pg\$1stat\$1statements extension** – The pg\$1stat\$1statements extension tracks SQL statement execution statistics in a hash table. The extension tracks SQL statements up to the limit defined by the `pg_stat_statements.max` parameter. This parameter determines the maximum number of statements that can be tracked which corresponds to the maximum number of rows in the pg\$1stat\$1statements view.
**Statement statistics persistence** – The extension persists statement statistics across instance restarts by:
+ Writing data to a file named pg\$1stat\$1statements.stat
+ Using the pg\$1stat\$1statements.save parameter to control persistence behavior
When pg\$1stat\$1statements.save is set to:
+ on (default): Statistics are saved at shutdown and reloaded at server start
+ off: Statistics are neither saved at shutdown nor reloaded at server start
**Query text storage** – The extension stores the text of tracked queries in a file named `pgss_query_texts.stat`. This file can grow to double the average size of all tracked SQL statements before garbage collection occurs. The extension requires an exclusive lock on the hash table during cleanup operations and rewrite `pgss_query_texts.stat` file.
**Statement deallocation process** – When the number of tracked statements reaches the `pg_stat_statements.max` limit and new statements need to be tracked, the extension:
+ Takes an exclusive lock (LWLock:pg\$1stat\$1statements) on the hash table.
+ Loads existing data into local memory.
+ Performs a quicksort based on the number of calls.
+ Removes the least-called statements (bottom 5%).
+ Re-populates the hash table with the remaining entries.
**Monitoring statement deallocation** – In PostgreSQL 14 and later, you can monitor statement deallocation using the pg\$1stat\$1statements\$1info view. This view includes a dealloc column that shows how many times statements were deallocated to make room for new ones
If the deallocation of statements occurs frequently, it will lead to more frequent garbage collection of the `pgss_query_texts.stat` file on disk.
## Likely causes of increased waits
The typical causes of increased `LWLock:pg_stat_statements` waits include:
+ An increase in the number of unique queries used by the application.
+ The `pg_stat_statements.max` parameter value being small compared to the number of unique queries being used.
## Actions
We recommend different actions depending on the causes of your wait event. You might identify `LWLock:pg_stat_statements` events by using Amazon RDS Performance Insights or by querying the view `pg_stat_activity`.
Adjust the following `pg_stat_statements` parameters to control tracking behavior and reduce LWLock:pg\$1stat\$1 statements wait events.
**Topics**
+ [Disable pg\$1stat\$1statements.track parameter](#apg-rpg-lwlockpgstat.actions.disabletrack)
+ [Increase pg\$1stat\$1statements.max parameter](#apg-rpg-lwlockpgstat.actions.increasemax)
+ [Disable pg\$1stat\$1statements.track\$1utility parameter](#apg-rpg-lwlockpgstat.actions.disableutility)
### Disable pg\$1stat\$1statements.track parameter
If the LWLock:pg\$1stat\$1statements wait event is adversely impacting database performance, and a rapid solution is required before further analysis of the `pg_stat_statements` view to identify the root cause, the `pg_stat_statements.track` parameter can be disabled by setting it to `none`. This will disable the collection of statement statistics.
### Increase pg\$1stat\$1statements.max parameter
To reduce deallocation and minimize garbage collection of the `pgss_query_texts.stat` file on disk, increase the value of the `pg_stat_statements.max` parameter. The default value is `5,000`.
**Note**
The `pg_stat_statements.max` parameter is static. You must restart your DB instance to apply any changes to this parameter.
### Disable pg\$1stat\$1statements.track\$1utility parameter
You can analyze the pg\$1stat\$1statements view to determine which utility commands are consuming the most resources tracked by `pg_stat_statements`.
The `pg_stat_statements.track_utility` parameter controls whether the module tracks utility commands, which include all commands except SELECT, INSERT, UPDATE, DELETE, and MERGE. By default, this parameter is set to `on`.
For example, when your application uses many savepoint queries, which are inherently unique, it can increase statement deallocation. To address this, you can disable the `pg_stat_statements.track_utility` parameter to stop `pg_stat_statements` from tracking savepoint queries.
**Note**
The `pg_stat_statements.track_utility` parameter is a dynamic parameter. You can change its value without restarting your database instance.
**Example of unique save point queries in pg\$1stat\$1statements**
```
query | queryid
-------------------------------------------------+---------------------
SAVEPOINT JDBC_SAVEPOINT_495701 | -7249565344517699703
SAVEPOINT JDBC_SAVEPOINT_1320 | -1572997038849006629
SAVEPOINT JDBC_SAVEPOINT_26739 | 54791337410474486
SAVEPOINT JDBC_SAVEPOINT_1294466 | 8170064357463507593
ROLLBACK TO SAVEPOINT JDBC_SAVEPOINT_65016 | -33608214779996400
SAVEPOINT JDBC_SAVEPOINT_14185 | -2175035613806809562
SAVEPOINT JDBC_SAVEPOINT_45837 | -6201592986750645383
SAVEPOINT JDBC_SAVEPOINT_1324 | 6388797791882029332
```
PostgreSQL 17 introduces several enhancements for utility command tracking:
+ Savepoint names are now displayed as constants.
+ Global Transaction IDs (GIDs) of two-phase commit commands are now displayed as constants.
+ Names of DEALLOCATE statements are shown as constants.
+ CALL parameters are now displayed as constants.
# Timeout:PgSleep
The `Timeout:PgSleep` event occurs when a server process has called the `pg_sleep` function and is waiting for the sleep timeout to expire.
**Topics**
+ [Supported engine versions](#apg-waits.timeoutpgsleep.context.supported)
+ [Likely causes of increased waits](#apg-waits.timeoutpgsleep.causes)
+ [Actions](#apg-waits.timeoutpgsleep.actions)
## Supported engine versions
This wait event information is supported for all versions of Aurora PostgreSQL.
## Likely causes of increased waits
This wait event occurs when an application, stored function, or user issues a SQL statement that calls one of the following functions:
+ `pg_sleep`
+ `pg_sleep_for`
+ `pg_sleep_until`
The preceding functions delay execution until the specified number of seconds have elapsed. For example, `SELECT pg_sleep(1)` pauses for 1 second. For more information, see [Delaying Execution](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-DELAY) in the PostgreSQL documentation.
## Actions
Identify the statement that was running the `pg_sleep` function. Determine if the use of the function is appropriate.