Lwlock:BufferMapping
This indicates the heavy activity in shared_buffers.
BufferMapping is a PostgreSQL wait event that occurs when a backend process is waiting to acquire a lock on the shared buffer mapping structure—specifically,
the hash partition lock that protects the buffer lookup table (the buffer mapping lock). This lock is required when locating or pinning a data page in shared buffers,
such as during reads or writes of table or index pages. Contention on BufferMapping typically arises under high concurrency when many sessions simultaneously access different pages,
causing serialized access to the buffer lookup mechanism. This wait event is classified under the LWLock (lightweight lock) category and can indicate a need to increase shared_buffers, adjust access patterns,
or investigate query concurrency bottlenecks.
What does it mean?
To quickly find a data page in memory, PostgreSQL uses a Hash Table. This table maps a "Page ID" (which table and which block number) to a "Buffer ID" (where it is in RAM).
Because this hash table is a shared resource used by every single connection, it is split into 128 partitions (in standard builds). Each partition is protected by an LWLock (Lightweight Lock).
- When you see BufferMapping, your session is trying to lock one of those 128 partitions to look up, add, or remove a page, but another session is already holding that lock.
- This event often correlates with High CPU usage. Because LWLocks are very "fast," processes will "spin" or retry rapidly to get the lock, which consumes CPU cycles.
What causes it?
- If your shared_buffers is too small to hold the data your queries need, PostgreSQL must constantly "evict" old pages to make room for new ones.
Every time a page is moved in or out, the hash table must be updated.
If 100 sessions are all trying to swap pages at the same time, they will collide on the BufferMapping locks.
- Queries that perform full table scans on very large tables rapidly "cycle" through the buffer pool. This creates a high volume of requests to the hash table, increasing the likelihood of lock contention
- High concurrency: A large number of simultaneous connections accessing different data pages can lead to increased contention for the BufferMapping locks.
- Excessive indexes and bloated indexes and unpartitioned huge tables are the common reasons.
How to fix it?
- Increase shared_buffers: Allocating more memory to shared_buffers allows PostgreSQL to cache more data pages, reducing the frequency of page evictions and updates to the buffer mapping table.
- Optimize queries: Review and optimize queries to minimize full table scans and improve data access patterns. Using indexes effectively can reduce the number of pages that need to be accessed.
- Reduce connections and concurrency: If possible, limit the number of simultaneous connections or stagger heavy workloads to reduce contention on the BufferMapping locks.
- Partition large tables: Consider partitioning large tables to reduce the amount of data each query needs to access, which can help decrease the load on the buffer mapping mechanism.
- Monitor and tune: Regularly monitor wait events and system performance. Use tools like pg_stat_statements to identify problematic queries and adjust configurations as needed.