In this article I am grouping likewise wait events
The LWLock:BufferIO and IO:DataFileRead:
LWLock:BufferIO:
- This wait event occurs when a session is waiting for a lightweight lock (LWLock) on a buffer I/O operation.
- It typically indicates contention for accessing shared buffers or waiting for I/O operations to complete.
IO:DataFileRead:
- This wait event occurs when a session is waiting for a read operation from a data file (e.g., reading a table or index block from disk).
- It indicates that the requested data is not in the shared buffers and must be read from disk.
These wait events in PostgreSQL (or Amazon RDS PostgreSQL) indicate that sessions are waiting for I/O operations related to reading data from disk into the shared buffers. These wait events are common in database workloads, but if they occur frequently or for long durations, they can indicate performance bottlenecks. Here’s how to diagnose and address these wait events.
Cause:
1)We see these wait events mainly when there are inefficient queries, performing full table scan.
2)When there is bloating on tables. Check if there are any dead tuples, and the table require vacuum
3)When there are no appropriate indexes.
4)Finally when shared_buffers is too small, and it is unable to help with no. of operations.
How to Identify this issue.
1)In AWS RDS click on monitoring tab, search for DiskQueueDepth, here you will be seeing high count, usually above 15 or so..
2)Also in performance insight in monitoring tab we can find read and write operations per seconds, we will see spike in read operation per second.
3)If your postgresql is on VM, then you can verify the IOSTATS, and we see spike in operations.