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: "Tsunakawa, Takayuki" <tsunakawa.takay@fujitsu.com>, "Jamison, Kirk" <k.jamison@fujitsu.com>
Cc: 'Kyotaro Horiguchi' <horikyota.ntt@gmail.com>, "andres@anarazel.de" <andres@anarazel.de>, "amit.kapila16@gmail.com" <amit.kapila16@gmail.com>, "tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>, "thomas.munro@gmail.com" <thomas.munro@gmail.com>, "robertmhaas@gmail.com" <robertmhaas@gmail.com>, "tomas.vondra@2ndquadrant.com" <tomas.vondra@2ndquadrant.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2020-12-04T03:42:19Z
Lists: pgsql-hackers 
Hello, Kirk

Thanks for providing the new patches.
I did the recovery performance test on them, the results look good. I'd like to share them with you and everyone else. 
(I also record VACUUM and TRUNCATE execution time on master/primary in case you want to have a look.)  

1. VACUUM and Failover test results(average of 15 times) 
[VACUUM] ---execution time on master/primary
shared_buffers      master(sec)       patched(sec)     %reg=((patched-master)/master)
--------------------------------------------------------------------------------------
128M                9.440              9.483                       0%
10G                74.689             76.219                       2%
20G               152.538            138.292                      -9%

[Failover] ---execution time on standby
shared_buffers     master(sec)        patched(sec)     %reg=((patched-master)/master)
--------------------------------------------------------------------------------------
128M                3.629                2.961                    -18%
10G                82.443                2.627                    -97%
20G               171.388                2.607                    -98%

2. TRUNCATE and Failover test results(average of 15 times) 
[TRUNCATE] ---execution time on master/primary
shared_buffers     master(sec)        patched(sec)     %reg=((patched-master)/master)
--------------------------------------------------------------------------------------
128M               49.271               49.867                     1%
10G               172.437              175.197                     2%
20G               279.658              278.752                     0%

[Failover] ---execution time on standby
shared_buffers    master(sec)       patched(sec)     %reg=((patched-master)/master)
--------------------------------------------------------------------------------------
128M               4.877                3.989                    -18%
10G               92.680                3.975                    -96%
20G              182.035                3.962                    -98% 

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

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

If you have question on my test, 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