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

Tang, Haiying <tanghy.fnst@cn.fujitsu.com>

From: "Tang, Haiying" <tanghy.fnst@cn.fujitsu.com>
To: "Tsunakawa, Takayuki" <tsunakawa.takay@fujitsu.com>, "Jamison, Kirk" <k.jamison@fujitsu.com>
Cc: 'Amit Kapila' <amit.kapila16@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Thomas Munro <thomas.munro@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-12-18T07:45:33Z
Lists: pgsql-hackers
Hello Kirk,

I noticed you have pushed a new version for your patch which has some changes on TRUNCATE on TOAST relation. 
Although you've done performance test for your changed part. I'd like to do a double check for your patch(hope you don't mind).
Below is the updated recovery performance test results for your new patch. All seems good.

*TOAST relation with PLAIN strategy like integer : 
1. Recovery after VACUUM test results(average of 15 times)
shared_buffers		master(sec)        patched(sec)     %reg=((patched-master)/patched)
--------------------------------------------------------------------------------------
128M			2.111 			1.604 			-24%
10G			57.135 			1.878 			-97%
20G			167.122 		1.932 			-99%

2. Recovery after TRUNCATE test results(average of 15 times)
shared_buffers  	master(sec)       patched(sec)     %reg=((patched-master)/patched)
--------------------------------------------------------------------------------------
128M			2.326 			1.718 			-26%
10G			82.397 			1.738 			-98%
20G			169.275 		1.718 			-99%

*TOAST relation with NON-PLAIN strategy like text/varchar: 
1. Recovery after VACUUM test results(average of 15 times)
shared_buffers		master(sec)        patched(sec)     %reg=((patched-master)/patched)
--------------------------------------------------------------------------------------
128M			3.174 			2.493 			-21%
10G			72.716 			2.246 			-97%
20G			163.660 		2.474 			-98%

2. Recovery after TRUNCATE test results(average of 15 times): Although it looks like there are some improvements after patch applied. I think that's because of the average calculation. TRUNCATE results should be similar between master and patched because they all do full scan.
shared_buffers  	master(sec)       patched(sec)     %reg=((patched-master)/patched)
--------------------------------------------------------------------------------------
128M			4.978 			4.958 			0%
10G			97.048 			88.751 			-9%
20G			183.230 		173.226 		-5% 

[Machine spec]
CPU : 40 processors  (Intel(R) Xeon(R) Silver 4210 CPU @ 2.20GHz)
Memory: 128G
OS: CentOS 8

[Failover test data]
Total table Size: 600M
Table: 10000 tables (1000 rows per table)

[Configure in postgresql.conf]
autovacuum = off
wal_level = replica
max_wal_senders = 5
max_locks_per_transaction = 10000

If you have any questions on my test results, please let me know.

Regards
Tang


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