Re: Contention preventing locking

On 20.02.2018 16:42, Simon Riggs wrote:
> On 20 February 2018 at 13:19, Konstantin Knizhnik
> <k.knizhnik@postgrespro.ru> wrote:
>>
>> On 20.02.2018 14:26, Simon Riggs wrote:
>>> On 15 February 2018 at 16:00, Konstantin Knizhnik
>>> <k.knizhnik@postgrespro.ru> wrote:
>>>
>>>> So in heap_acquire_tuplock all competing transactions are waiting for TID
>>>> of
>>>> the updated version. When transaction which changed this tuple is
>>>> committed,
>>>> one of the competitors will grant this lock and proceed, creating new
>>>> version of the tuple. Then all other competitors will be awaken and ...
>>>> find
>>>> out that locked tuple is not the last version any more.
>>>> Then they will locate new version and try to lock it... The more
>>>> competitors
>>>> we have, then more attempts they all have to perform (quadratic
>>>> complexity).
>>> What about the tuple lock? You are saying that is ineffective?
>>>
>>> src/backend/access/heap/README.tuplock
>>
>> In my last mail ni this thread I have mentioned that update of tuple cause
>> setting of three heavy weight locks:
>>
>> 1. Locking of SnapshotDirty.xmax transaction (XactLockTableWait) in
>> EvalPlanQualFetch
>> 2. Tuple lock (heap_acquire_tuplock) in heap_tuple_update
>> 3. Transaction lock (XactLockTableWait) in heap_tuple_update
>>
>> So what I see in debugger and monitoring pg_locks, is that under high
>> contention there are a lot transactions waiting for SnapshotDirty.xmax.
>> This lock is obtained before tuple lock, so tuple lock can not help in this
>> case.
> Hmm, that situation occurs erroneously, as I previously observed
> https://www.postgresql.org/message-id/CANP8%2BjKoMAyXvMO2hUqFzHX8fYSFc82q9MEse2zAEOC8vxwDfA%40mail.gmail.com
>
> So if you apply the patch on the thread above, does performance improve?
>
First of all this patch is not correct: it cause pin/unpin buffer 
disbalance and I get a lot of errors:

Update failed: ERROR:  buffer 343 is not owned by resource owner Portal

When I slightly modify it by moving ReleaseBuffer(buffer) inside switch 
I get almost the same performance as Vanilla.
And when I combined it with pklock patch, then I get similar performance 
with vanilla+pklock, but with much larger dips:

https://docs.google.com/spreadsheets/d/1QOYfUehy8U3sdasMjGnPGQJY8JiRfZmlS64YRBM0YTo/edit?usp=sharing 



-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company