Lwlock:BufferContent

The BufferContent wait event (specifically LWLock:BufferContent or LWLock:buffer_content) occurs when a process is waiting for a lightweight lock (LWLock) to access the actual data inside a buffer in shared memory.
Other processes may be reading from or writing to the same buffer, and the LWLock ensures that only one process can modify the buffer at a time to maintain data integrity.
Unlike BufferMapping (which is about finding the page in a map), BufferContent is about reading or modifying the data on that page once it has been found. This might indicate when there is high concurrency.

Solution:

  1. Reduce the number of connections.Multiplex large number of application connection over few database connections using transaction level pooling.
  2. Reduce the size of the table (Archive / Purge) to fit in to memory
  3. Partition the table. This will reduce the number of rows in each partition and thus reduce the contention on buffer content locks.
  4. Reduce the data integrity checks in the database side including foreign keys, check contraints and triggers
  5. Application-level sharding:Spread concurrent updates across different logical partitions

Key Distinctions

  1. BufferContent: Waiting to access page content in memory (lock conflict)
  2. BufferMapping: Waiting to locate a buffer in the pool (hash table lock)
  3. BufferIO: Waiting for I/O coordination when reading a page from disk