Horton

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

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

View thread View on postgr.es
From: "Tang, Haiying" <tanghy.fnst@cn.fujitsu.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: "Tsunakawa, Takayuki" <tsunakawa.takay@fujitsu.com>, "Jamison, Kirk" <k.jamison@fujitsu.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-30T05:57:52Z
Lists: pgsql-hackers 
Hi Amit,

In last mail(https://www.postgresql.org/message-id/66851e198f6b41eda59e6257182564b6%40G08CNEXMBPEKD05.g08.fujitsu.local),
I've sent you the performance test results(run only 1 time) on single table. Here is my the retested results(average by 15 times) which I think is more accurate.

In terms of 20G and 100G, the optimization on 100G is linear, but 20G is nonlinear(also include test results on shared buffers of 50G/60G), so it's a little difficult to decide the threshold from the two for me. 
If just consider 100G, I think NBuffers/32 is the optimized max relation size. But I don't know how to judge for 20G. If you have any suggestion, kindly let me know.

#%reg			128M	1G	20G	100G
---------------------------------------------------------------
%reg(NBuffers/512)	 0%	-1%	-5%	-26%
%reg(NBuffers/256)	 0%	 0%	 5%	-20%
%reg(NBuffers/128)	-1%	-1%	-10%	-16%
%reg(NBuffers/64)	-1%	 0%	 0%	 -8%	
%reg(NBuffers/32)	 0%	 0%	-2%	-4%
%reg(NBuffers/16)	 0%	 0%	-6%	 4%
%reg(NBuffers/8)	 1%	 0%	 2%	-2%
%reg(NBuffers/4)	 0%	 0%	 2%	 2%

Optimization details(unit: second):
patched	 (sec)					
shared_buffers	NBuffers/512	NBuffers/256	NBuffers/128	NBuffers/64	NBuffers/32	NBuffers/16	NBuffers/8	NBuffers/4
----------------------------------------------------------------------------------------------------------------------------------------------------------
128M		0.107		0.107		0.107		0.107		0.107		0.107		0.108		0.208
1G		0.107		0.108		0.107 		0.108 		0.208 		0.208 		0.308 		0.409 
20G		0.199 		0.299 		0.317 		0.408 		0.591 		0.900 		1.561 		2.866 
100G		0.318 		0.381 		0.645 		0.992 		1.913 		3.640 		6.615 		13.389

master(HEAD) (sec)					
shared_buffers	NBuffers/512	NBuffers/256	NBuffers/128	NBuffers/64	NBuffers/32	NBuffers/16	NBuffers/8	NBuffers/4
----------------------------------------------------------------------------------------------------------------------------------------------------------
128M		0.107		0.107		0.108		0.108		0.107		0.107		0.107		0.208
1G		0.108 		0.108 		0.108 		0.108 		0.208 		0.207 		0.308 		0.409 
20G		0.208 		0.283 		0.350 		0.408 		0.601 		0.955 		1.529 		2.806 
100G		0.400 		0.459 		0.751 		1.068 		1.984 		3.506 		6.735 		13.101

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