Hi Andrey,
Thanks a lot for your replay and reference information.
The default NUM_SUBTRANS_BUFFERS is 32. My implementation is
local_cache_subtrans_pages can be adjusted dynamically.
If we configure local_cache_subtrans_pages as 64, every backend use only
extra 64*8192=512KB memory.
So the local cache is similar to the first level cache. And subtrans SLRU is
the second level cache.
And I think extra memory is very well worth it. It really resolve massive
subtrans stuck issue which I mentioned in previous email.
I have view the patch of [0] before. For SLRU buffers adding GUC
configuration parameters are very nice.
I think for subtrans, its optimize is not enough. For
SubTransGetTopmostTransaction, we should get the SubtransSLRULock first, then
call SubTransGetParent in loop.
Prevent acquire/release SubtransSLRULock in SubTransGetTopmostTransaction->
SubTransGetParent in loop.
After I apply this patch which I optimize SubTransGetTopmostTransaction,
with my test case, I still get stuck result.
[1] solution. Actually first, we try to use Buffer manager to replace SLRU
for subtrans too. And we have implemented it.
With the test case which I mentioned in previous mail, It was still stuck. In
default there is 2048 subtrans in one page.
When some processes get the top transaction in one page, they should
pin/unpin and lock/unlock the same page repeatedly.
I found than it was stuck at pin/unpin page for some backends.
Compare test results, pgbench with subtrans_128.sql
Concurrency PG master PG master with path[0] Local cache optimize
300 stuck stuck
no stuck
500 stuck stuck
no stuck
1000 stuck stuck
no stuck
Maybe we can test different approach with my test case. For massive
concurrency, if it will not be stuck, we can get a good solution.
[0] https://commitfest.postgresql.org/34/2627/
[1]
https://www.postgresql.org/message-id/flat/20180814213500.GA74618%4060f81dc409fc.ant.amazon.com
Thanks
Pengcheng
-----Original Message-----
From: Andrey Borodin <[email protected]>
Sent: 2021年8月30日 18:25
To: Pengchengliu <[email protected]>
Cc: [email protected]
Subject: Re: suboverflowed subtransactions concurrency performance optimize
Hi Pengcheng!
You are solving important problem, thank you!
> 30 авг. 2021 г., в 13:43, Pengchengliu <[email protected]> написал(а):
>
> To resolve this performance problem, we think about a solution which
> cache SubtransSLRU to local cache.
> First we can query parent transaction id from SubtransSLRU, and copy
> the SLRU page to local cache page.
> After that if we need query parent transaction id again, we can query
> it from local cache directly.
A copy of SLRU in each backend's cache can consume a lot of memory. Why create
a copy if we can optimise shared representation of SLRU?
JFYI There is a related patch to make SimpleLruReadPage_ReadOnly() faster for
bigger SLRU buffers[0].
Also Nik Samokhvalov recently published interesting investigation on the topic,
but for some reason his message did not pass the moderation. [1]
Also it's important to note that there was a community request to move SLRUs to
shared_buffers [2].
Thanks!
Best regards, Andrey Borodin.
[0] https://commitfest.postgresql.org/34/2627/
[1]
https://www.postgresql.org/message-id/flat/BE73A0BB-5929-40F4-BAF8-55323DE39561%40yandex-team.ru
[2]
https://www.postgresql.org/message-id/flat/20180814213500.GA74618%4060f81dc409fc.ant.amazon.com
