0

We are seeing Commits excessively waiting on WALWrite during certain times of high load. Not only certain sessions, but all commits, even vacuum. By taking snapshots of pg_stat_all_tables and pg_statio_all_tables I think it is clear that during these particular times, where the WALWrite events are so prominent, one particular table and its toast table is to blame. It receives heavy inserts, updates and deletes during the times that the many WALWrite waits are present. 'Heavy' both in terms of by many sessions (around 20) and in terms of amount of data (big textfields that are toasted).

Looking at snapshots of pg_stat_wal during the times the waits happen, I can see that:

  • wal_records per second is up from an average of 2.000 to 10.000-20.000
  • wal_fpi per second is up from an average of 150 to 500-1.000
  • wal_kbytes per second is up from an average of 600 to 3.000-5.000
  • wal_write and wal_sync per second are up from an average of 50 to 200

Do the above mentioned numbers from pg_stat_wal indicate what the reason for the WALWrite events is?

  • Is it the number of commits per second?
  • Is it the size of the commits (in megabytes)?
  • Is it the number of sessions that commit at the same time?

If these numbers do not paint a clear picture, how can I find out? I'd like to fix the bottleneck, but need to identify it first.

Update on spec

  • PG 14.1 on AWS RDS (db.r6i.4xlarge (16 vCPU, 128GB RAM) with 1 Replica (db.r6i.2xlarge).
  • Storage type is General Purpose SSD (gp3) with Provisioned IOPS of 12000 IOPS and Storage throughput of 500 MiBps.
  • NO Dedicated Log Volume for PostgreSQL
  • checkpoint_timeout 900
  • max_wal_size 15360
  • min_wal_size 8192
  • synchronous_standby_names NULL
  • wal_level replica
  • wal_buffers 64MB
  • shared_buffers 32GB
  • wal_compression ON
  • commit_delay 0
  • synchronous_commit ON
4
  • 1
    What's your machine config - (esp. disk - HDD/SATA SSD/NVMe - PCI, Server (Windows/Linux/...) - version of PostgreSQL)?
    – Vérace
    Commented Apr 5 at 0:48
  • @Vérace Please see above, I have added the spec. TY
    – Peter
    Commented Apr 5 at 11:09
  • What is the value of config setting synchronous_commit?
    – bobflux
    Commented Apr 6 at 7:09
  • @bobflux TY for mentioning synchronous_commit. It is ON. Please find my follow-up question on synchronous_commit here: dba.stackexchange.com/questions/338410/…
    – Peter
    Commented Apr 8 at 6:31

1 Answer 1

2

That almost certainly means that your disk is overloaded. You might be able to tune your workload to be less I/O-intense, otherwise you need better storage.

You could try to enable wal_compression, which can reduce the volume of WAL somewhat, at the expense of CPU time.

2
  • TY for your answer. wal_compression is ON already. Please see above, I have added the spec.
    – Peter
    Commented Apr 5 at 11:11
  • 1
    Well, then the disk is too lame for your workload. Commented Apr 5 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.