Horton

Re: suboverflowed subtransactions concurrency performance optimize

x4mmm@yandex-team.ru

View thread View on postgr.es
From: Andrey Borodin <x4mmm@yandex-team.ru>
To: Pengchengliu <pengchengliu@tju.edu.cn>
Cc: pgsql-hackers@postgresql.org
Date: 2021-09-03T04:11:16Z
Lists: pgsql-hackers

Attachments

<html><head></head><body dir="auto" style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="ApplePlainTextBody"><div dir="auto" style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="ApplePlainTextBody"><div dir="auto" style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="ApplePlainTextBody"><div class="ApplePlainTextBody"><br><br><blockquote type="cite">31 авг. 2021 г., в 11:43, Pengchengliu &lt;pengchengliu@tju.edu.cn&gt; написал(а):<br><br>Hi Andrey,<br> Thanks a lot for your replay and reference information.<br><br> The default NUM_SUBTRANS_BUFFERS is 32. My implementation is local_cache_subtrans_pages can be adjusted dynamically.<br> If we configure local_cache_subtrans_pages as 64, every backend use only extra 64*8192=512KB memory. <br> So the local cache is similar to the first level cache. And subtrans SLRU is the second level cache.<br> And I think extra memory is very well worth it. It really resolve massive subtrans stuck issue which I mentioned in previous email.<br><br> I have view the patch of [0] before. For SLRU buffers adding GUC configuration parameters are very nice.<br> I think for subtrans, its optimize is not enough. For SubTransGetTopmostTransaction, we should get the SubtransSLRULock first, then call SubTransGetParent in loop.<br> Prevent acquire/release &nbsp;SubtransSLRULock in SubTransGetTopmostTransaction-&gt; SubTransGetParent in loop.<br> After I apply this patch which I &nbsp;optimize SubTransGetTopmostTransaction, &nbsp;with my test case, I still get stuck result.<br></blockquote><br>SubTransGetParent() acquires only Shared lock on SubtransSLRULock. The problem may arise only when someone reads page from disk. But if you have big enough cache - this will never happen. And this cache will be much less than 512KB*max_connections.<br><br>I think if we really want to fix exclusive SubtransSLRULock I think best option would be to split SLRU control lock into array of locks - one for each bank (in v17-0002-Divide-SLRU-buffers-into-n-associative-banks.patch). With this approach we will have to rename s/bank/partition/g for consistency with locks and buffers partitions. I really liked having my own banks, but consistency worth it anyway.<br><br>Thanks!<br><br>Best regards, Andrey Borodin.</div></div></div></body></html>

Commits

  1. Revert "Add single-item cache when looking at topmost XID of a subtrans XID"

  2. Add single-item cache when looking at topmost XID of a subtrans XID

  3. Fix handling of partitioned index in RelationGetNumberOfBlocksInFork()