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
-
Fix size overflow in calculation introduced by commits d6ad34f3 and bea449c6.
- 519e4c9ee21a 14.0 landed
-
Optimize DropRelFileNodesAllBuffers() for recovery.
- bea449c635c0 14.0 landed
-
Optimize DropRelFileNodeBuffers() for recovery.
- d6ad34f3410f 14.0 landed
-
Cache smgrnblocks() results in recovery.
- c5315f4f4484 14.0 cited
-
Add a check to prevent overwriting valid data if smgrnblocks() gives a
- ffae5cc5a602 8.2.0 cited