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: "k.jamison@fujitsu.com" <k.jamison@fujitsu.com>, 'Kyotaro Horiguchi' <horikyota.ntt@gmail.com>
Cc: "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.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-01T01:55:10Z
Lists: pgsql-hackers

Attachments

Hi,

I revised the patch again. Attached is V19.
The previous patch's algorithm missed entering the optimization loop.
So I corrected that and removed the extra function I added
in the previous versions.

The revised patch goes something like this:
	for (forks of rel)
	{
		if (smgrcachednblocks() == InvalidBlockNumber) 
			break; //go to full scan
		if (nBlocksToInvalidate < buf_full_scan_threshold)
			for (blocks of the fork)
		else
			break; //go to full scan
	}
	<execute full scan>

Recovery performance measurement results below.
But it seems there are overhead even with large shared buffers.

| s_b   | master | patched | %reg  | 
|-------|--------|---------|-------| 
| 128MB | 36.052 | 39.451  | 8.62% | 
| 1GB   | 21.731 | 21.73   | 0.00% | 
| 20GB  | 24.534 | 25.137  | 2.40% | 
| 100GB | 30.54  | 31.541  | 3.17% |

I'll investigate further. Or if you have any feedback or advice, I'd appreciate it.

Machine specs used for testing:
RHEL7, 8 core, 256 GB RAM, xfs

Configuration:
wal_level = replica
autovacuum = off
full_page_writes = off

# For streaming replication from primary. 
synchronous_commit = remote_write
synchronous_standby_names = ''

# For Standby.
#hot_standby = on
#primary_conninfo

shared_buffers = 128MB
# 1GB, 20GB, 100GB

Just in case it helps for some understanding,
I also attached the recovery log 018_wal_optimize_node_replica.log
with some ereport that prints whether we enter the optimization loop or do full scan.

Regards,
Kirk Jamison

Commits

  1. Fix size overflow in calculation introduced by commits d6ad34f3 and bea449c6.

  2. Optimize DropRelFileNodesAllBuffers() for recovery.

  3. Optimize DropRelFileNodeBuffers() for recovery.

  4. Cache smgrnblocks() results in recovery.

  5. Add a check to prevent overwriting valid data if smgrnblocks() gives a