LWLock - buffer_io
buffer_io event occurs when 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.
Each shared buffer has an I/O lock that is associated with the
buffer_io 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
As soon as the page is read inside the shared buffer pool, the
buffer_io lock is released.
buffer_io wait event precedes the DataFileRead wait event. The
DataFileRead wait event occurs while data is being read from storage.
Common causes for the
buffer_io 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_buffersparameter) 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
- Checkpoints occurring too frequently or needing to flush too many modified pages
- Sudden spikes for database connections trying to perform operations on the same page
Run the following query to see the shared buffer cache hit ratio.
SELECT round(100 * sum(blks_hit) / sum(blks_hit + blks_read), 3) as cache_hit_ratio FROM pg_stat_database;
We recommend different actions depending on the causes of your wait event:
- Observe statistics for correlation between sharp decreases in the
buffer_iowait 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.
checkpoint_timeoutbased on your workload peak time if you see
cache_hit_ratiometric dips. Then identify which query might be causing it.
- 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.