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: "Tang, Haiying" <tanghy.fnst@cn.fujitsu.com>, "Jamison, Kirk" <k.jamison@fujitsu.com>, 'Amit Kapila' <amit.kapila16@gmail.com>
Cc: "Tsunakawa, Takayuki" <tsunakawa.takay@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>, "jankirk.jamison@gmail.com" <jankirk.jamison@gmail.com>
Date: 2021-01-07T03:57:44Z
Lists: pgsql-hackers 
>I'd like take a look at them and redo some of the tests using my machine. I'll send my test reults in a separate email after this.

I did the same tests with Kirk's scripts using the latest patch on my own machine. The results look pretty good and similar with Kirk's. 

average of 5 runs.

[VACUUM failover test for 1000 relations] Unit is second, %reg=(patched-master)/ master

| s_b		| Master	| Patched	| %reg		| 
|--------------|---------------|--------------|--------------| 
| 128 MB	| 0.408		| 0.308   	|  -24.44%  	| 
| 1 GB		| 0.809		| 0.308   	|  -61.94%	| 
| 20 GB		| 12.529 	| 0.308   	|  -97.54%	| 
| 100 GB  	| 59.310 	| 0.369   	|  -99.38%	|

[TRUNCATE failover test for 1000 relations] Unit is second, %reg=(patched-master)/ master

| s_b		| Master	| Patched	| %reg		| 
|--------------|---------------|--------------|--------------| 
| 128 MB	| 0.287		| 0.207   	|  -27.91%  	| 
| 1 GB		| 0.688		| 0.208   	|  -69.84%	| 
| 20 GB		| 12.449 	| 0.208   	|  -98.33%	| 
| 100 GB  	| 61.800 	| 0.207   	|  -99.66%	|

Besides, I did the test for threshold value again. (I rechecked my test process and found out that I forgot to check the data synchronization state on standby which may introduce some NOISE to my results.)
The following results show we can't get optimize over NBuffers/32 just like Kirk's test results, so I do approve with Kirk on the threshold value.

%regression:
| rel_size |128MB|1GB|20GB| 100GB |
|----------|----|----|----|-------| 
| NB/512   | 0% | 0% | 0% | -48%  | 
| NB/256   | 0% | 0% | 0% | -33%  | 
| NB/128   | 0% | 0% | 0% | -9%   | 
| NB/64    | 0% | 0% | 0% | -5%   | 
| NB/32    | 0% | 0% |-4% | -3%   | 
| NB/16    | 0% | 0% |-4% | 1%    | 
| NB/8     | 1% | 0% | 1% | 3%    |

Optimization details(unit: second):
patched:
shared_buffers	NBuffers/512	NBuffers/256	NBuffers/128	NBuffers/64	NBuffers/32	NBuffers/16	NBuffers/8
-------------------------------------------------------------------------------------------------------------------------------------
128M		0.107 		0.107 		0.107 		0.106 		0.107 		0.107 		0.107 
1G		0.107 		0.107 		0.107 		0.107 		0.107 		0.107 		0.107 
20G		0.107 		0.108 		0.207 		0.307 		0.442 		0.876 		1.577 
100G		0.208 		0.308 		0.559 		1.060 		1.961 		4.567 		7.922 

master:
shared_buffers	NBuffers/512	NBuffers/256	NBuffers/128	NBuffers/64	NBuffers/32	NBuffers/16	NBuffers/8
-------------------------------------------------------------------------------------------------------------------------------------
128M		0.107 		0.107 		0.107 		0.107 		0.107 		0.107 		0.106 
1G		0.107 		0.107 		0.107 		0.107 		0.107 		0.107 		0.107 
20G		0.107 		0.107 		0.208 		0.308 		0.457 		0.910 		1.560 
100G		0.308 		0.409 		0.608 		1.110 		2.011 		4.516 		7.721 

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

Any question to my test is welcome.

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