LWLock - buffer_mapping
This event occurs when a session is waiting to associate a data block with a buffer in the shared buffer pool.
The shared buffer pool is an PostgreSQL memory area that holds all pages that are or were being used by processes. When a process needs a page, it reads the page into the shared buffer pool. The
shared_buffers parameter sets the shared buffer size and reserves a memory area to store the table and index pages. If you change this parameter, make sure to restart the database. For more information, see Shared Buffer Area.
buffer_mapping wait event occurs in the following scenarios:
- A process searches the buffer table for a page and acquires a shared buffer mapping lock.
- A process loads a page into the buffer pool and acquires an exclusive buffer mapping lock.
- A process removes a page from the pool and acquires an exclusive buffer mapping lock.
When this event appears more than normal, possibly indicating a performance problem, the database is paging in and out of the shared buffer pool. Typical causes include the following:
- Large queries
- Bloated indexes and tables
- Full table scans
- A shared pool size that is smaller than the working set
We recommend different actions depending on the causes of your wait event.
buffer_mapping waits spike, investigate the buffer hit ratio. You can use these metrics to get a better understanding of what is happening in the buffer cache. Examine the following metrics:
This metric measures the percentage of requests that are served by the buffer cache of a DB instance in your DB cluster. You might see this metric decrease in the lead-up to the
This statistics counter metric indicates the number of blocks that were retrieved from the shared buffer pool. After the
buffer_mappingwait event appears, you might observe a spike in
This statistics counter metric indicates the number of blocks that required I/O to be read into the shared buffer pool. You might observe a spike in
blks_readin the lead-up to the
By querying the view
pg_stat_database, you can get the above statistics counter metrics.
SELECT round(100 * sum(blks_hit) / sum(blks_hit + blks_read), 3) as cache_hit_ratio FROM pg_stat_database;
To confirm that your indexing strategy is not degrading performance, check the following:
Ensure that index and table bloat aren’t leading to unnecessary pages being read into the shared buffer. If your tables contain unused rows, consider archiving the data and removing the rows from the tables. You can then rebuild the indexes for the resized tables.
Indexes for frequently used queries
To determine whether you have the optimal indexes, monitor DB engine performance metrics. The
tup_returnedmetric shows the number of rows read. The
tup_fetchedmetric shows the number of rows returned to the client. If
tup_returnedis significantly larger than
tup_fetched, the data might not be properly indexed. Also, your table statistics might not be current.
To reduce the
buffer_mapping wait events, try to reduce the number of buffers that must be allocated quickly. One strategy is to perform smaller batch operations. You might be able to achieve smaller batches by partitioning your tables.