About the performance of startup after dropping many tables - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Hello
guys,</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal" style="text-indent:10.5pt"><span
lang="EN-US">wehave PG 8.3.13 in our system. When running performance cases, we find the startup recovery cost about 3
minutes.It is too long in our system. </span><p class="MsoNormal" style="text-indent:10.5pt"><span lang="EN-US">We
diagnosethe problem by adding timestamp. Finally, we find almost all 3 minutes were used by the relation dropping and
bufferinvalidation loop in xact_redo_commit. </span><p class="MsoNormal" style="text-indent:10.5pt"><span
lang="EN-US">Beforethe problem happens, we drop 40000 tables and reboot linux. So the out loop will run 40000 times .
Andwe have 13000 share buffer pages in PG. But in DropRelFileNodeBuffers who is used to drop shared buffer associated
tothe specified relation we will have to run through all the shared buffers for each relation to check whether the
buffercan be dropped, no matter how many pages the relation has in shared buffer. </span><p class="MsoNormal"
style="text-indent:10.5pt"><spanlang="EN-US">In all, we will have 40000 * 13000 LWLock acquire and release. Is this
necessary?How about building a hash to record all relfilenode to be dropped, and run through the shared buffers once to
checkwhere the buffer's relfilenode is going to be dropped! If we can do this, LWLock traffic will be 13000 , we will
havemuch better performance!</span><p class="MsoNormal" style="text-indent:10.5pt"><span lang="EN-US">Does this work?
Andis there any risk to do so?</span><p class="MsoNormal" style="text-indent:10.5pt"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">Thanks!</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">Best reguards,</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanstyle="font-family:宋体">甘嘉栋</span><span lang="EN-US">(Gan Jiadong)</span><p
class="MsoNormal"><spanlang="EN-US">E-MAIL: <a href="mailto:ganjd@huawei.com">ganjd@huawei.com</a></span><p
class="MsoNormal"><spanlang="EN-US">Tel</span><span style="font-family:宋体">：</span><span
lang="EN-US">+86-755-289720578</span><pclass="MsoNormal"><span
lang="EN-US">*********************************************************************************************************</span><p
class="MsoNormal"><spanlang="EN-US">This e-mail and its attachments contain confidential information from HUAWEI, which
isintended only for the person or entity whose address is listed above. Any use of the information contained herein in
anyway (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other
thanthe intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or
emailimmediately and delete it!</span><p class="MsoNormal"><span
lang="EN-US">*********************************************************************************************************
</span><pclass="MsoNormal"><span lang="EN-US"> </span></div>