RE: [Patch] Optimize dropping of relation buffers using dlist

k.jamison@fujitsu.com <k.jamison@fujitsu.com>

From: "k.jamison@fujitsu.com" <k.jamison@fujitsu.com>

To: "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>

Cc: 'Kyotaro Horiguchi' <horikyota.ntt@gmail.com>, "amit.kapila16@gmail.com" <amit.kapila16@gmail.com>, "tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>, "andres@anarazel.de" <andres@anarazel.de>, "robertmhaas@gmail.com" <robertmhaas@gmail.com>, "tomas.vondra@2ndquadrant.com" <tomas.vondra@2ndquadrant.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>

Date: 2020-10-22T02:06:43Z

Lists: pgsql-hackers

Attachments

v27-0001-Prevent-invalidating-blocks-in-smgrextend-during.patch (application/octet-stream) patch v27-0001
v27-0002-Add-bool-param-in-smgrnblocks-for-cached-blocks.patch (application/octet-stream) patch v27-0002
v27-0003-Optimize-DropRelFileNodeBuffers-during-recovery.patch (application/octet-stream) patch v27-0003
v27-0004-For-non-recovery-performance-test-case-purposes-.patch (application/octet-stream) patch v27-0004

On Thursday, October 22, 2020 10:34 AM, Tsunakwa-san wrote:
> > I have confirmed that the above comment (commenting out the lines in
> > RelationTruncate) solves the issue for non-recovery case.
> > The attached 0004 patch is just for non-recovery testing and is not
> > included in the final set of patches to be committed for vacuum
> optimization.
> 
> I'm relieved to hear that.
> 
> As for 0004:
> When testing TRUNCATE, remove the change to storage.c because it was
> intended to troubleshoot the VACUUM test.
I've removed it now.

> What's the change in bufmgr.c for?  Is it to be included in 0001 or 0002?

Right. But that should be in 0003. Fixed.

I also fixed the feedback from the previous email:
>(1)
>+	 * as the total nblocks for a given fork. The cached value returned by
>
>nblocks -> blocks


> > The table below shows the vacuum execution time for non-recovery case.
> > I've also subtracted the execution time when VACUUM (truncate off) is set.
> >
> > [NON-RECOVERY CASE - VACUUM execution Time in seconds]
> (snip)
> > | 100GB | 65.456 | 1.795   | -3546.57% |
> 
> So, the full shared buffer scan for 10,000 relations took about as long as 63
> seconds (= 6.3 ms per relation).  It's nice to shorten this long time.
> 
> I'll review the patch soon.

Thank you very much for the reviews. Attached are the latest set of patches.

Regards,
Kirk Jamison

Commits

Fix size overflow in calculation introduced by commits d6ad34f3 and bea449c6.
- 519e4c9ee21a 14.0 landed
Optimize DropRelFileNodesAllBuffers() for recovery.
- bea449c635c0 14.0 landed
Optimize DropRelFileNodeBuffers() for recovery.
- d6ad34f3410f 14.0 landed
Cache smgrnblocks() results in recovery.
- c5315f4f4484 14.0 cited
Add a check to prevent overwriting valid data if smgrnblocks() gives a
- ffae5cc5a602 8.2.0 cited