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.
My idea was that we can avoid such performance degradation if we somehow
queue competing transactions so that they will get control one-by-one,
without doing useless work.
First of all I tried to replace TID lock with PK (primary key) lock. Unlike
TID, PK of record is not changed during hot update. The second idea is that
instead immediate releasing lock after update we can hold it until the end
of transaction. And this optimization really provides improve of performance
- it corresponds to pg_f1_opt configuration at the attached diagram.
For some workloads it provides up to two times improvement comparing with
vanilla Postgres. But there are many problems with correct (non-prototype)
implementation of this approach:
handling different types of PK, including compound keys, PK updates,...
Try locking the root tid rather than the TID, that is at least unique
per page for a chain of tuples, just harder to locate.
But locking primary key is more efficient, isn't it?
The problem with primary key is that it doesn't always exists or can be
compound, but if it exists and has integer type, then get be obtained
easily than root tid.
For pgrw benchmark primary key lock provides even better results than
transaction lock chaining:
https://docs.google.com/spreadsheets/d/1QOYfUehy8U3sdasMjGnPGQJY8JiRfZmlS64YRBM0YTo/edit?usp=sharing
But for YCSB benchmark xlock optimization is more efficient:
https://docs.google.com/spreadsheets/d/136BfsABXEbzrleZHYoli7XAyDFUAxAXZDHl5EvJR_0Q/edit?usp=sharing
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
