About the performance of startup after dropping many tables
От | Gan Jiadong |
---|---|
Тема | About the performance of startup after dropping many tables |
Дата | |
Msg-id | 008b01cbcf17$c9622140$5c2663c0$@com обсуждение исходный текст |
Ответы |
Re: About the performance of startup after dropping many tables
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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>
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Fujii MasaoДата:
Сообщение: Re: [COMMITTERS] pgsql: Hot Standby feedback for avoidance of cleanup conflicts on stand