On 20.02.2018 16:42, Simon Riggs wrote:
On 20 February 2018 at 13:19, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
On 20.02.2018 14:26, Simon Riggs wrote:
On 15 February 2018 at 16:00, Konstantin Knizhnik
<k.knizh...@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
