# Wait events for Aurora PostgreSQL Limitless Database
<a name="limitless-monitoring-waits"></a>

A wait event in Aurora PostgreSQL indicates a resource for which a session is waiting, such as input/output (I/O) and locks. Wait events are helpful in finding out why sessions are waiting for resources, and identifying bottlenecks. For more information, see [Aurora PostgreSQL wait events](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.waits).

Aurora PostgreSQL Limitless Database has its own wait events that are related to routers and shards. Many of them are for routers waiting on shards to complete tasks. Shard wait events contain details on tasks that are being performed.

**Topics**
+ [

## Querying for wait events
](#limitless-monitoring-waits.query)
+ [

# Limitless Database wait events
](limitless-waits-reference.md)

## Querying for wait events
<a name="limitless-monitoring-waits.query"></a>

You can use the [limitless\$1stat\$1activity](limitless-monitoring-views.md#limitless_stat_activity) view to query for wait events, as shown in the following example.

```
SELECT wait_event FROM rds_aurora.limitless_stat_activity WHERE wait_event_type='AuroraLimitless';

      wait_event
----------------------
 RemoteStatementSetup
 RemoteStatementSetup
(2 rows)
```

You can also use the `aurora_stat_system_waits` function to list the number of waits and the total time spent on each wait event, as shown in the following example.

```
postgres_limitless=> SELECT type_name,event_name,waits,wait_time
    FROM aurora_stat_system_waits()
    NATURAL JOIN aurora_stat_wait_event()
    NATURAL JOIN aurora_stat_wait_type()
    WHERE type_name='AuroraLimitless'
    ORDER BY wait_time DESC;

    type_name    |       event_name          |  waits  |  wait_time
-----------------+---------------------------+---------+-------------
 AuroraLimitless | RemoteStatementSetup      |    7518 | 75236507897
 AuroraLimitless | RemoteStatementExecution  |      40 |      132986
 AuroraLimitless | Connect                   |       5 |        1453
(3 rows)
```

# Limitless Database wait events
<a name="limitless-waits-reference"></a>

The following wait events apply to Aurora PostgreSQL Limitless Database. You can monitor these wait events to identify bottlenecks in Aurora PostgreSQL Limitless Database processing.

**Topics**
+ [

## IO:TwophaseFilePoolWrite wait event
](#limitless-waits-TwophaseFilePoolWrite)
+ [

## IO:TwophaseFilePoolRead wait event
](#limitless-waits-TwophaseFilePoolRead)
+ [

## AuroraLimitless:Connect wait event
](#limitless-waits-Connect)
+ [

## AuroraLimitless:AsyncConnect wait event
](#limitless-waits-AsyncConnect)
+ [

## AuroraLimitless:RemoteStatementSetup wait event
](#limitless-waits-RemoteStatementSetup)
+ [

## AuroraLimitless:RemoteDDLExecution wait event
](#limitless-waits-RemoteDDLExecution)
+ [

## AuroraLimitless:RemoteStatementExecution wait event
](#limitless-waits-RemoteStatementExecution)
+ [

## AuroraLimitless:FetchRemoteResults wait event
](#limitless-waits-FetchRemoteResults)
+ [

## AuroraLimitless:AsyncGetInitialResponse wait event
](#limitless-waits-AsyncGetInitialResponse)
+ [

## AuroraLimitless:AsyncGetNextResponse wait event
](#limitless-waits-AsyncGetNextResponse)
+ [

## AuroraLimitless:AbortedCommandCleanup wait event
](#limitless-waits-AbortedCommandCleanup)
+ [

## AuroraLimitless:DistributedCommitPrepare wait event
](#limitless-waits-DistributedCommitPrepare)
+ [

## AuroraLimitless:DistributedCommit wait event
](#limitless-waits-DistributedCommit)
+ [

## AuroraLimitless:DistributedCommitPrepareThrottle wait event
](#limitless-waits-DistributedCommitPrepareThrottle)
+ [

## AuroraLimitless:PreparedTransactionResolution wait event
](#limitless-waits-PreparedTransactionResolution)
+ [

## AuroraLimitless:SendPreparedTransactionOutcome wait event
](#limitless-waits-SendPreparedTransactionOutcome)
+ [

## AuroraLimitless:CommitClockBarrier wait event
](#limitless-waits-CommitClockBarrier)
+ [

## AuroraLimitless:SnapshotClockBarrier wait event
](#limitless-waits-SnapshotClockBarrier)
+ [

## AuroraLimitless:ReaderSnapshotClockBarrier wait event
](#limitless-waits-ReaderSnapshotClockBarrier)
+ [

## AuroraLimitless:GatherDistributedDeadlockGraph wait event
](#limitless-waits-GatherDistributedDeadlockGraph)
+ [

## AuroraLimitless:DistributedDeadlockDetection wait event
](#limitless-waits-DistributedDeadlockDetection)
+ [

## AuroraLimitless:DistributedDeadlockAbort wait event
](#limitless-waits-DistributedDeadlockAbort)
+ [

## AuroraLimitless:GatherRemoteStats wait event
](#limitless-waits-GatherRemoteStats)
+ [

## AuroraLimitless:GlobalSequenceRefresh wait event
](#limitless-waits-GlobalSequenceRefresh)
+ [

## AuroraLimitless:GlobalVacuumTimeExchange wait event
](#limitless-waits-GlobalVacuumTimeExchange)
+ [

## AuroraLimitless:DistributedTransactionMonitorGather wait event
](#limitless-waits-DistributedTransactionMonitorGather)
+ [

## AuroraLimitlessActivity:AdminTaskSchedulerMain wait event
](#limitless-waits-AdminTaskSchedulerMain)
+ [

## AuroraLimitlessActivity:AdminTaskExecutorMain wait event
](#limitless-waits-AdminTaskExecutorMain)
+ [

## AuroraLimitlessActivity:AdminTaskMonitorMain wait event
](#limitless-waits-AdminTaskMonitorMain)
+ [

## AuroraLimitlessActivity:DatabaseCleanupMonitorMain wait event
](#limitless-waits-DatabaseCleanupMonitorMain)
+ [

## AuroraLimitlessActivity:TopologyCleanupMonitorMain wait event
](#limitless-waits-TopologyCleanupMonitorMain)
+ [

## AuroraLimitlessActivity:ToplogyChangeMonitorMain wait event
](#limitless-waits-ToplogyChangeMonitorMain)
+ [

## AuroraLimitlessActivity:DistributedTransactionMonitorMain wait event
](#limitless-waits-DistributedTransactionMonitorMain)
+ [

## AuroraLimitlessActivity:GlobalVacuumMonitorMain wait event
](#limitless-waits-GlobalVacuumMonitorMain)

## IO:TwophaseFilePoolWrite wait event
<a name="limitless-waits-TwophaseFilePoolWrite"></a>

Waiting for a write of a two-phase state file within the two-phase state file pool. This is an Aurora specific event.

### Causes
<a name="limitless-waits-TwophaseFilePoolWrite.causes"></a>

Processes executing a `PREPARED TRANSACTION` command, including participants in a Limitless Database distributed transaction, must persist transaction state in a two-phase file. Aurora uses a file pool to improve performance of this operation.

### Action
<a name="limitless-waits-TwophaseFilePoolWrite.action"></a>

This is a synchronous write I/O operation and therefore a high latency in this event has similar causes to `IO:XactSync` and can be investigated in the same way. If using Limitless Database, you might need to reduce the number of distributed transactions being executed.

## IO:TwophaseFilePoolRead wait event
<a name="limitless-waits-TwophaseFilePoolRead"></a>

Waiting for a read of a two-phase state file within the two-phase state file pool.

### Causes
<a name="limitless-waits-TwophaseFilePoolWrite.causes"></a>

Processes executing a `COMMIT PREPARED` command against a previously prepared transaction, including participants in a Limitless Database distributed transaction, might need to read previosly persisted transaction state from a two-phase file. Aurora uses a file pool to improve performance of this operation.

### Action
<a name="limitless-waits-TwophaseFilePoolWrite.action"></a>

This is a read I/O operation. Therefore, a high latency in this event has similar causes to `IO:DataFileRead` and can be investigated the same. If using Limitless Database, you might need to reduce the number of distributed transactions being executed.

## AuroraLimitless:Connect wait event
<a name="limitless-waits-Connect"></a>

The process is waiting for a connection to another node in the cluster to be established.

### Causes
<a name="limitless-waits-Connect.causes"></a>

Connections are established between processes and remote nodes to execute queries, distributed transactions, and perform DDLs.

### Action
<a name="limitless-waits-Connect"></a>

Reduce the number of simultaneous connections to the cluster or tune the use of cross-shard queries.

## AuroraLimitless:AsyncConnect wait event
<a name="limitless-waits-AsyncConnect"></a>

This event is similar to `Connect`, but represents a process waiting for parallel connections to a set of nodes to be established.

### Causes
<a name="limitless-waits-AsyncConnect.causes"></a>

Parallel connection establishment is most commonly done when executing DDL statements.

### Action
<a name="limitless-waits-AsyncConnect"></a>

Reduce the number of DDL statements or combine multiple DDLs in the same session to improve connection reuse.

## AuroraLimitless:RemoteStatementSetup wait event
<a name="limitless-waits-RemoteStatementSetup"></a>

The process is waiting for remote query execution setup, such as cursor open, close, or prepared statement creation.

### Causes
<a name="limitless-waits-TwophaseFilePoolRead"></a>

This wait event increases with the number of scans on sharded tables where the statement could not be single-shard optimized.

### Action
<a name="limitless-waits-TwophaseFilePoolRead"></a>

Optimize queries to reduce the number of scan operations or increase eligibility for single-shard optimization.

## AuroraLimitless:RemoteDDLExecution wait event
<a name="limitless-waits-RemoteDDLExecution"></a>

The process is waiting for a remote DDL command to finish.

### Causes
<a name="limitless-waits-RemoteDDLExecution"></a>

When you issue a DDL command on a DB shard group, it must be distributed to other router and shard nodes before confirming the operation. Some DDL operations can run for a long time, because data must be adapted to schema changes.

### Action
<a name="limitless-waits-RemoteDDLExecution"></a>

Identify long-running DDL commands so that you can optimize them.

## AuroraLimitless:RemoteStatementExecution wait event
<a name="limitless-waits-RemoteStatementExecution"></a>

A process is waiting for a remote command to finish.

### Causes
<a name="limitless-waits-RemoteStatementExecution"></a>

A SQL command is running on a remote node. This event will appear frequently for internal communications, such as `auto_analyze` and heartbeat checks.

### Action
<a name="limitless-waits-"></a>

Idenfify long-running commands using the limitless\$1stat\$1statements view. In many cases this is an expected event, especially for background workers or internal processes and no action is needed.

## AuroraLimitless:FetchRemoteResults wait event
<a name="limitless-waits-FetchRemoteResults"></a>

A process is waiting to retrieve rows from a remote node.

### Causes
<a name="limitless-waits-FetchRemoteResults"></a>

This wait event can increase when fetching a large number of rows from a remote table, such as a sharded or reference table.

### Action
<a name="limitless-waits-FetchRemoteResults"></a>

Identify unoptimized `SELECT` queries using the `limitless_stat_statements` view. Optimize queries to retrieve only necessary data. You can also tune the `rds_aurora.limitless_maximum_adaptive_fetch_size` parameter.

## AuroraLimitless:AsyncGetInitialResponse wait event
<a name="limitless-waits-AsyncGetInitialResponse"></a>

The process is waiting for an initial response when pipeline mode is used in query execution.

### Causes
<a name="limitless-waits-AsyncGetInitialResponse"></a>

This will typically be seen during router to shard execution for queries with single-shard data placement and is an expected part of normal execution.

### Action
<a name="limitless-waits-AsyncGetInitialResponse"></a>

No further action is required.

## AuroraLimitless:AsyncGetNextResponse wait event
<a name="limitless-waits-AsyncGetNextResponse"></a>

The process is waiting for an additional responses when pipeline mode is used in query execution.

### Causes
<a name="limitless-waits-AsyncGetNextResponse"></a>

This will typically be seen during router to shard execution for queries with single-shard data placement and is an expected part of normal execution.

### Action
<a name="limitless-waits-AsyncGetNextResponse"></a>

No further action is required.

## AuroraLimitless:AbortedCommandCleanup wait event
<a name="limitless-waits-AbortedCommandCleanup"></a>

The process is waiting for the result of a remote cleanup query. Cleanup queries are issued to shard nodes to return them to an appropriate state when a distributed transaction is ended.

### Causes
<a name="limitless-waits-AbortedCommandCleanup"></a>

Transaction cleanup is done when a transaction is aborted either because an error was found or because an user issued an explicit ABORT command or canceled the running query.

### Action
<a name="limitless-waits-AbortedCommandCleanup.action"></a>

Investigate the cause for the transaction being canceled.

## AuroraLimitless:DistributedCommitPrepare wait event
<a name="limitless-waits-DistributedCommitPrepare"></a>

The process is committing a distributed transaction and is waiting for all the participants to acknowledge the prepare command.

### Causes
<a name="limitless-waits-DistributedCommitPrepare"></a>

Transactions that modify multiple nodes must perform a distributed commit. A long wait in `DistributedCommitPrepare` could be caused by long waits in the `IO:TwophaseFilePoolWrite` event on participating nodes.

### Action
<a name="limitless-waits-DistributedCommitPrepare.action"></a>

Reduce the number of transactions that modify data on multiple nodes. Investigate `IO:TwophaseFilePoolWrite` events in other nodes of the cluster.

## AuroraLimitless:DistributedCommit wait event
<a name="limitless-waits-DistributedCommit"></a>

The process is committing a distributed transaction and is waiting for the lead participant to acknowledge the commit command.

### Causes
<a name="limitless-waits-DistributedCommit.causes"></a>

Transactions that modify multiple nodes must perform a distributed commit. A long wait in `DistributedCommit` could be caused by long waits in the `IO:XactSync` event on the lead participant.

### Action
<a name="limitless-waits-DistributedCommit.action"></a>

Reduce the number of transactions that modify data on multiple nodes. Investigate `IO:XactSync` events in other nodes of the cluster.

## AuroraLimitless:DistributedCommitPrepareThrottle wait event
<a name="limitless-waits-DistributedCommitPrepareThrottle"></a>

The process is attempting to prepare a distributed transaction and is being throttled to due to existing prepared transactions.

### Causes
<a name="limitless-waits-DistributedCommitPrepareThrottle.causes"></a>

Transactions that modify multiple nodes must perform a distributed commit. Participants in these transactions must perform a prepare operation as part of the commit protocol. Aurora limits the number of concurrent prepares, and if this limit is exceeded the process will wait in the `DistributedCommitPrepareThrottle` event.

### Action
<a name="limitless-waits-DistributedCommitPrepareThrottle.action"></a>

Reduce the number of transactions that modify data on multiple nodes. Investigate `IO:TwophaseFilePoolWrite` events as increased time in those events could cause existing prepared transactions to build up, resulting in throttling for new prepare attempts.

## AuroraLimitless:PreparedTransactionResolution wait event
<a name="limitless-waits-PreparedTransactionResolution"></a>

The process has encountered a tuple modified by a distributed transaction that is in the prepared state. The process must determine if the distributed transaction will become visible in its snapshot.

### Causes
<a name="limitless-waits-PreparedTransactionResolution.causes"></a>

Transactions that modify multiple nodes must perform a distributed commit which includes a prepare phase. A high number of distributed transactions or increased latency on distributed commits can cause other processes to encounter the `PreparedTransactionResolution` wait event.

### Action
<a name="limitless-waits-PreparedTransactionResolution.action"></a>

Reduce the number of transactions that modify data on multiple nodes. Investigate distributed commit related events as increased time in those events could increase latency in the commit path of distributed transactions. You might also wish to investigate network and CPU loads.

## AuroraLimitless:SendPreparedTransactionOutcome wait event
<a name="limitless-waits-SendPreparedTransactionOutcome"></a>

The process is executing on a node that is coordinating a distributed transaction and another process has inquired as to the state of that transaction, or the process has committed a distributed transaction and is sending the outcome to participants.

### Causes
<a name="limitless-waits-SendPreparedTransactionOutcome.causes"></a>

Processes that encounter the `PreparedTransactionResolution` wait event will query the transaction coordinator. The response on the transaction coordinator will ecounter SendPreparedTransactionOutcome.

### Action
<a name="limitless-waits-SendPreparedTransactionOutcome.action"></a>

Reduce the number of transactions that modify data on multiple nodes. Investigate distributed commit related events and `IO:TwophaseFilePoolWrite` and `IO:TwophaseFilePoolRead` events as those events could increase latency in the commit path of distributed transactions. You might also wish to investigate network and CPU loads.

## AuroraLimitless:CommitClockBarrier wait event
<a name="limitless-waits-CommitClockBarrier"></a>

The process is committing a transaction and must wait to ensure that the assigned commit time is guaranteed to be in the past for all nodes in the cluster.

### Causes
<a name="limitless-waits-CommitClockBarrier.causes"></a>

CPU or network staturation could cause increased clock drift, resulting in time spent in this wait event.

### Action
<a name="limitless-waits-CommitClockBarrier.action"></a>

Investigate CPU or network saturation in your cluster.

## AuroraLimitless:SnapshotClockBarrier wait event
<a name="limitless-waits-SnapshotClockBarrier"></a>

The process has received a snapshot time from another node with a clock in the future and is waiting for its own clock to reach that time.

### Causes
<a name="limitless-waits-SnapshotClockBarrier.causes"></a>

This typically occurs after the process has received results from a function that was pushed down to a shard and there is clock drift between the nodes. CPU or network staturation could cause increased clock drift, resulting in time spent in this wait event.

### Action
<a name="limitless-waits-SnapshotClockBarrier.action"></a>

Investigate CPU or network staturation in your cluster.

## AuroraLimitless:ReaderSnapshotClockBarrier wait event
<a name="limitless-waits-ReaderSnapshotClockBarrier"></a>

This event occurs on read nodes. The process is waiting for the read node to replay the write stream so that all writes that happened before the process snapshot time have been applied.

### Causes
<a name="limitless-waits-ReaderSnapshotClockBarrier.causes"></a>

An increase in Aurora replica lag can cause increased wait time in this event.

### Action
<a name="limitless-waits-ReaderSnapshotClockBarrier.action"></a>

Investigate Aurora replica lag.

## AuroraLimitless:GatherDistributedDeadlockGraph wait event
<a name="limitless-waits-GatherDistributedDeadlockGraph"></a>

The process is communicating with other nodes to collect lock graphs as part of distributed deadlock detection.

### Causes
<a name="limitless-waits-GatherDistributedDeadlockGraph.causes"></a>

When a process is waiting on a lock it will perform a distributed deadlock check after waiting longer than `rds_aurora.limitless_distributed_deadlock_timeout`.

### Action
<a name="limitless-waits-GatherDistributedDeadlockGraph"></a>

Investigate causes of lock contention in your application and consider tuning `rds_aurora.limitless_distributed_deadlock_timeout`.

## AuroraLimitless:DistributedDeadlockDetection wait event
<a name="limitless-waits-DistributedDeadlockDetection"></a>

The process is communicating with other nodes to detect a distributed deadlock.

### Causes
<a name="limitless-waits-DistributedDeadlockDetection.causes"></a>

When a process is waiting on a lock it will perform a distributed deadlock check after waiting longer than `rds_aurora.limitless_distributed_deadlock_timeout`.

### Action
<a name="limitless-waits-DistributedDeadlockDetection.action"></a>

Investigate causes of lock contention in your application and consider tuning `rds_aurora.limitless_distributed_deadlock_timeout`.

## AuroraLimitless:DistributedDeadlockAbort wait event
<a name="limitless-waits-DistributedDeadlockAbort"></a>

The process is communicating with another node to abort a session chosen as the victim in a distributed deadlock.

### Causes
<a name="limitless-waits-DistributedDeadlockAbort.causes"></a>

Application patterns are resulting in distributed deadlocks.

### Action
<a name="limitless-waits-DistributedDeadlockAbort.action"></a>

Investigate application patterns resulting in distributed deadlocks.

## AuroraLimitless:GatherRemoteStats wait event
<a name="limitless-waits-GatherRemoteStats"></a>

The process is gathering statistics from other nodes in the cluster.

### Causes
<a name="limitless-waits-GatherRemoteStats.causes"></a>

Monitoring or activty queries and views, such as `limitless_stat_activity`, will retrieve statistics from other nodes.

### Action
<a name="limitless-waits-GatherRemoteStats.action"></a>

No further action is required.

## AuroraLimitless:GlobalSequenceRefresh wait event
<a name="limitless-waits-GlobalSequenceRefresh"></a>

The process is generating a new sequence value and must request a new chunk from the global sequence.

### Causes
<a name="limitless-waits-GlobalSequenceRefresh.causes"></a>

A high rate of sequence value generation can result in stalls in this event if `rds_aurora.limitless_sequence_chunk_size` is insufficient.

### Action
<a name="limitless-waits-GlobalSequenceRefresh.action"></a>

This is a normal occurrence. If you see excessive time in this event consider tuning `rds_aurora.limitless_sequence_chunk_size`. See documentation on sequences in Limitless Database.

## AuroraLimitless:GlobalVacuumTimeExchange wait event
<a name="limitless-waits-GlobalVacuumTimeExchange"></a>

The process is exchanging snapshot data to support vacuum.

### Causes
<a name="limitless-waits-GlobalVacuumTimeExchange.causes"></a>

Nodes in Limitless Database exchange oldest active snapshot time data with other nodes to compute the correct cutoff time for vacuum execution.

### Action
<a name="limitless-waits-GlobalVacuumTimeExchange.action"></a>

No further action is required.

## AuroraLimitless:DistributedTransactionMonitorGather wait event
<a name="limitless-waits-DistributedTransactionMonitorGather"></a>

The process is gathering transaction metadata from other nodes to support distributed transaction cleanup.

### Causes
<a name="limitless-waits-DistributedTransactionMonitorGather.causes"></a>

Nodes in Limitless Database exchange transaction metadata with other nodes to determine when distributed transaction state can be purged.

### Action
<a name="limitless-waits-DistributedTransactionMonitorGather.action"></a>

No further action is required.

## AuroraLimitlessActivity:AdminTaskSchedulerMain wait event
<a name="limitless-waits-AdminTaskSchedulerMain"></a>

Waiting in main loop of task scheduler process.

## AuroraLimitlessActivity:AdminTaskExecutorMain wait event
<a name="limitless-waits-AdminTaskExecutorMain"></a>

Waiting in main loop of task executor process.

## AuroraLimitlessActivity:AdminTaskMonitorMain wait event
<a name="limitless-waits-AdminTaskMonitorMain"></a>

Waiting in main loop of task monitor process.

## AuroraLimitlessActivity:DatabaseCleanupMonitorMain wait event
<a name="limitless-waits-DatabaseCleanupMonitorMain"></a>

Waiting in main loop of database cleanup monitor process.

## AuroraLimitlessActivity:TopologyCleanupMonitorMain wait event
<a name="limitless-waits-TopologyCleanupMonitorMain"></a>

Waiting in main loop of topology cleanup monitor process.

## AuroraLimitlessActivity:ToplogyChangeMonitorMain wait event
<a name="limitless-waits-ToplogyChangeMonitorMain"></a>

Waiting in main loop of topology change monitor process.

## AuroraLimitlessActivity:DistributedTransactionMonitorMain wait event
<a name="limitless-waits-DistributedTransactionMonitorMain"></a>

Waiting in main loop of distributed transaction monitor process.

## AuroraLimitlessActivity:GlobalVacuumMonitorMain wait event
<a name="limitless-waits-GlobalVacuumMonitorMain"></a>

Waiting in main loop of global vacuum monitor process.