IO:WALSync
Supported engine versions
This wait event information is supported for all versions of RDS for PostgreSQL 10 and higher.
Context
To make a transaction durable, PostgreSQL writes the WAL buffers to the WAL file and
then flushes the WAL file to durable storage. A process reports the wait event
IO:WALWrite while it writes the WAL buffers to the WAL file, and
IO:WALSync while it waits for the flush to complete. For more
information about IO:WALWrite, see IO:WALWrite.
The process that performs the flush can be a backend that commits a transaction or a
background process such as the WAL writer. Only one process flushes the WAL at a time.
Other sessions that wait for the same flush to complete report the wait event
LWLock:WALWrite. For this reason, IO:WALSync often
appears together with LWLock:WALWrite in Performance Insights.
When the synchronous_commit parameter is set to on (the
default), a COMMIT doesn't return to the client until the WAL for the transaction
is flushed. As a result, the time spent in IO:WALSync adds directly to the
commit latency.
Likely causes of increased waits
If this wait event occurs often, look for the following causes.
- High commit rate
-
Each commit of a transaction that changes data requires a WAL flush. Workloads that run many small transactions, such as single row inserts or updates with autocommit, cause frequent flushes. To check the commit rate, monitor the
xact_commitcounter metric. For more information, see Detailed Database Metrics for Amazon RDS for PostgreSQL.
- Storage write latency
-
The time to flush the WAL file depends on the write latency of the storage volume. When the storage volume reaches its IOPS or throughput limit, or when other write activity such as checkpoints competes with the WAL writes, the flush takes longer. To check the storage write latency, monitor the
WriteLatency,WriteIOPS, andWriteThroughputmetrics in CloudWatch. For more information, see Amazon CloudWatch metrics for Amazon RDS.
- Multi-AZ DB instance deployment
-
In a Multi-AZ DB instance deployment, writes to the primary DB instance are synchronously replicated to the standby replica. A Multi-AZ DB instance deployment can have increased write and commit latency compared to a Single-AZ deployment. For more information, see Multi-AZ DB instance deployments for Amazon RDS.
Actions
To reduce this wait event, take the following actions.
Topics
Reduce the number of commits
To reduce the number of WAL flushes, combine statements into transaction blocks instead of committing each statement. For bulk data loads, use multirow INSERT statements or the COPY command. Use Performance Insights to find the queries that run most often.
Consider asynchronous commit for transactions that can tolerate data loss
When synchronous_commit is set to off, a COMMIT returns
before the WAL for the transaction is flushed, so the session doesn't wait on
IO:WALSync. If the database crashes, the most recent transactions
that were reported as committed can be lost, but the database stays consistent.
You can set this parameter for a specific session or transaction instead of for
the whole DB instance. For more information, see Asynchronous
Commit
Monitor the WAL flush time
When the track_wal_io_timing parameter is turned on, PostgreSQL
records the number of WAL flushes and the time spent on them. In RDS for PostgreSQL 15
and higher, this parameter is turned on by default. You can check these values in
the following views:
-
RDS for PostgreSQL 14 to 17: the
wal_syncandwal_sync_timecolumns of thepg_stat_walview -
RDS for PostgreSQL 18 and higher: the
fsyncsandfsync_timecolumns of thepg_stat_ioview for rows whereobjectiswal
Divide the flush time by the number of flushes to get the average time for one
flush. For more information, see The
Cumulative Statistics System
Improve storage write performance
If the storage write latency is high, consider increasing the Provisioned IOPS or throughput of the storage volume, or moving to Provisioned IOPS storage. Before you scale the storage, tune your workload first.
For a DB instance that uses Provisioned IOPS storage, you can also use a dedicated log volume (DLV). A DLV moves PostgreSQL database transaction logs to a storage volume that's separate from the volume containing the database tables. For more information, see Dedicated log volume (DLV).