Re: Contention preventing locking
От | Konstantin Knizhnik |
---|---|
Тема | Re: Contention preventing locking |
Дата | |
Msg-id | 3d1b49c5-5d70-dd01-5337-c4a6f156e906@postgrespro.ru обсуждение исходный текст |
Ответ на | Re: Contention preventing locking (Amit Kapila <amit.kapila16@gmail.com>) |
Список | pgsql-hackers |
On 26.02.2018 17:00, Amit Kapila wrote: > On Thu, Feb 15, 2018 at 9:30 PM, Konstantin Knizhnik > <k.knizhnik@postgrespro.ru> wrote: >> Hi, >> >> PostgreSQL performance degrades signficantly in case of high contention. >> You can look at the attached YCSB results (ycsb-zipf-pool.png) to estimate >> the level of this degradation. >> >> Postgres is acquiring two kind of heavy weight locks during update: it locks >> TID of the updated tuple and XID of transaction created this version. >> In debugger I see the following picture: if several transactions are trying >> to update the same record, then first of all they compete for >> SnapshotDirty.xmax transaction lock in EvalPlanQualFetch. >> Then in heap_tuple_update they are trying to lock TID of the updated tuple: >> heap_acquire_tuplock in heapam.c >> > There is no function named heap_tuple_update. Do you mean to say heap_update? Yes, sorry. I mean heap_update. > >> After that transactions wait completion of the transaction updated the >> tuple: XactLockTableWait in heapam.c >> >> 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). >> > The attempts are controlled by marking the tuple as locked by me after > waiting on SnapshotDirty.xmax. So, the backend which marks the tuple > as locked must get the tuple to update and I think it is ensured in > code that only one backend will be allowed to do so. I can see some > serialization in this logic, but I think we should first try to get > the theory behind the contention problem you are seeing before trying > to find the solution for it. > Sorry, I could not say, that I completely understand logic of locking updated tuples in Postgres, although I have spent some time inspecting this code. It seems to be strange and quite inefficient to me that updating one tuple requires obtaining three heavy-weight locks. Did you read this thread: https://www.postgresql.org/message-id/CANP8%2BjKoMAyXvMO2hUqFzHX8fYSFc82q9MEse2zAEOC8vxwDfA%40mail.gmail.com In any, the summary of all my last experiments with locking is the following: 1. My proposal with transaction lock chaining can reduce performance degradation with increasing number of competing transactions. But degradation still takes place. Moreover, my recent experiments shows that lock chaining can cause deadlocks. It seems to me that I know the reason of such deadlocks, but right now have no idea how to fix them. 2. Replacing tuple TID locks with tuple PK lock has positive effect only if lock is hold until end of transaction. Certainly we can not lock all updated tuples (it will cause lock hash overflow). But we can keep until end of transaction just some small number of tuple locks. Unfortunately it is not the only problem with this solution: it is relatively simple to implement in case of integer primary key, but handling all kind of PKs, including compound keys requires more efforts. Also there can be no PK at all... 3. Alternative to PK lock is root tuple lock (head of hot update chain). Unfortunately there is no efficient way (at least I do not know one) of locating root tuple. Scanning the whole page seems to be too inefficient. If we perform index scan, than we actually start with root tuple, so it is possible to somehow propagate it. But it doesn't work in case of heap scan. And I suspect that as in case of PK lock, it can increase performance only of lock is hold until end of transaction. So the problem with lock contention really exist. It can be quite easily reproduced at the computer with large number of core with either pgbench with zipf distribution, either with YCSB benchmark, either with my pgrw benchmark... But how to fix it is unclear. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Claudio FreireДата:
Сообщение: Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently