public inbox for [email protected]help / color / mirror / Atom feed
Understanding recovery conflict due to bufferpin 5+ messages / 2 participants [nested] [flat]
* Understanding recovery conflict due to bufferpin @ 2021-03-17 04:44 vinay kumar <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: vinay kumar @ 2021-03-17 04:44 UTC (permalink / raw) To: [email protected] Hi Team, Need some understanding regarding recovery conflict due to buffer pin. We see "User was holding shared buffer pin for too long" . I understand that this can be due to holding lock to update content of a db block or page. 1> Is the lock held on a buffer page or page at disk level? 2> Is there any serialization present to maintain the locking mechanism? Thanks ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding recovery conflict due to bufferpin @ 2021-03-17 12:41 Laurenz Albe <[email protected]> parent: vinay kumar <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2021-03-17 12:41 UTC (permalink / raw) To: vinay kumar <[email protected]>; [email protected] On Wed, 2021-03-17 at 10:14 +0530, vinay kumar wrote: > Need some understanding regarding recovery conflict due to buffer pin. > > We see "User was holding shared buffer pin for too long" . > > I understand that this can be due to holding lock to update content of a db block or page. > > 1> Is the lock held on a buffer page or page at disk level? A buffer page. And it is not a lock, it is a pin > 2> Is there any serialization present to maintain the locking mechanism? I don't quite understand - what you observe *is* the serialization of access. See this blog for some details: https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding recovery conflict due to bufferpin @ 2021-03-23 17:18 vinay kumar <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: vinay kumar @ 2021-03-23 17:18 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] Hi Laurenz, Thank you for replying back to me. My query is does the buffer pin pins block present in shared buffers(assuming the block is already read into memory previously) or it's pinned to the block at the OS level? Also have another query: Do we have any queue mechanism to attain buffer pins on a block? To provide an example: Let's say, I need to read tuples present in a block by multiple backends. Shouldn't the backends wait in FIFO order to hold pins on the block? To give you an example, if I have the few backends requesting access to a block in the following order: -> backend 1: reads the tuples from block -> backend 2: reads the tuples from block -> backend 3: reads the tuples from block -> WAL replay: waiting to modify block either due to replaying change or Vacuum operation. Will the order of requests to access blocks be maintained in a cache or any other memory area? If possible to implement lazy cache invalidation (invalidating blocks in buffer cache when no conflicts query is run), it would be great and helpful to lots of users who query data from standby to avoid recovery conflict and don't have to re-run the entire query once again consuming resources. Thanks On Wed, Mar 17, 2021 at 6:11 PM Laurenz Albe <[email protected]> wrote: > On Wed, 2021-03-17 at 10:14 +0530, vinay kumar wrote: > > Need some understanding regarding recovery conflict due to buffer pin. > > > > We see "User was holding shared buffer pin for too long" . > > > > I understand that this can be due to holding lock to update content of a > db block or page. > > > > 1> Is the lock held on a buffer page or page at disk level? > > A buffer page. And it is not a lock, it is a pin > > > 2> Is there any serialization present to maintain the locking mechanism? > > I don't quite understand - what you observe *is* the serialization > of access. > > See this blog for some details: > > https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ > > Yours, > Laurenz Albe > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding recovery conflict due to bufferpin @ 2021-03-23 21:42 Laurenz Albe <[email protected]> parent: vinay kumar <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2021-03-23 21:42 UTC (permalink / raw) To: vinay kumar <[email protected]>; +Cc: [email protected] On Tue, 2021-03-23 at 22:48 +0530, vinay kumar wrote: > My query is does the buffer pin pins block present in shared buffers(assuming the > block is already read into memory previously) or it's pinned to the block at the OS level? > > Also have another query: > > Do we have any queue mechanism to attain buffer pins on a block? > > To provide an example: > > Let's say, I need to read tuples present in a block by multiple backends. > Shouldn't the backends wait in FIFO order to hold pins on the block? > > To give you an example, if I have the few backends requesting access to a block > in the following order: > > -> backend 1: reads the tuples from block > -> backend 2: reads the tuples from block > -> backend 3: reads the tuples from block > -> WAL replay: waiting to modify block either due to replaying change or Vacuum operation. > > Will the order of requests to access blocks be maintained in a cache or any other memory area? > > If possible to implement lazy cache invalidation (invalidating blocks in buffer cache > when no conflicts query is run), it would be great and helpful to lots of users who query > data from standby to avoid recovery conflict and don't have to re-run the entire query once > again consuming resources. A pin is not a lock, it just protects a page from being swapped out of cache. Several backends can pin the same page simultaneously. The buffer pin is an internal PostgreSLQ concept and has nothing to do with the operating system. I recommend that you start by reading the appropriate README: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/buffer/README That explains the concepts better than I could. The key sentence when it comes to replication conflicts is this: To physically remove a tuple or compact free space on a page, one must hold a pin and an exclusive lock, *and* observe while holding the exclusive lock that the buffer's shared reference count is one (ie, no other backend holds a pin). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding recovery conflict due to bufferpin @ 2021-03-24 01:07 vinay kumar <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: vinay kumar @ 2021-03-24 01:07 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] Thanks for the clarification. On Wed, Mar 24, 2021, 03:12 Laurenz Albe <[email protected]> wrote: > On Tue, 2021-03-23 at 22:48 +0530, vinay kumar wrote: > > My query is does the buffer pin pins block present in shared > buffers(assuming the > > block is already read into memory previously) or it's pinned to the > block at the OS level? > > > > Also have another query: > > > > Do we have any queue mechanism to attain buffer pins on a block? > > > > To provide an example: > > > > Let's say, I need to read tuples present in a block by multiple backends. > > Shouldn't the backends wait in FIFO order to hold pins on the block? > > > > To give you an example, if I have the few backends requesting access to > a block > > in the following order: > > > > -> backend 1: reads the tuples from block > > -> backend 2: reads the tuples from block > > -> backend 3: reads the tuples from block > > -> WAL replay: waiting to modify block either due to replaying change or > Vacuum operation. > > > > Will the order of requests to access blocks be maintained in a cache or > any other memory area? > > > > If possible to implement lazy cache invalidation (invalidating blocks in > buffer cache > > when no conflicts query is run), it would be great and helpful to lots > of users who query > > data from standby to avoid recovery conflict and don't have to re-run > the entire query once > > again consuming resources. > > A pin is not a lock, it just protects a page from being swapped out of > cache. > Several backends can pin the same page simultaneously. > > The buffer pin is an internal PostgreSLQ concept and has nothing to do > with the operating system. > > I recommend that you start by reading the appropriate README: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/buffer/README > That explains the concepts better than I could. > > The key sentence when it comes to replication conflicts is this: > > To physically remove a tuple or compact free space on a page, one > must hold a pin and an exclusive lock, *and* observe while holding the > exclusive lock that the buffer's shared reference count is one (ie, > no other backend holds a pin). > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-03-24 01:07 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-17 04:44 Understanding recovery conflict due to bufferpin vinay kumar <[email protected]> 2021-03-17 12:41 ` Laurenz Albe <[email protected]> 2021-03-23 17:18 ` vinay kumar <[email protected]> 2021-03-23 21:42 ` Laurenz Albe <[email protected]> 2021-03-24 01:07 ` vinay kumar <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox