RE: [Patch] Optimize dropping of relation buffers using dlist
k.jamison@fujitsu.com <k.jamison@fujitsu.com>
From: "k.jamison@fujitsu.com" <k.jamison@fujitsu.com>
To: "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>
Cc: 'Thomas Munro' <thomas.munro@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-11-04T02:58:27Z
Lists: pgsql-hackers
Attachments
- v29-0001-Prevent-invalidating-blocks-in-smgrextend-during.patch (application/octet-stream) patch v29-0001
- v29-0002-Add-bool-param-in-smgrnblocks-for-cached-blocks.patch (application/octet-stream) patch v29-0002
- v29-0003-Optimize-DropRelFileNodeBuffers-during-recovery.patch (application/octet-stream) patch v29-0003
- v29-0004-TRUNCATE-optimization.patch (application/octet-stream) patch v29-0004
Hi, I've updated the patch 0004 (Truncate optimization) with the previous comments of Tsunakawa-san already addressed in the patch. (Thank you very much for the review.) The change here compared to the previous version is that in DropRelFileNodesAllBuffers() we don't check for the accurate flag anymore when deciding whether to optimize or not. For relations with blocks that do not exceed the threshold for full scan, we call DropRelFileNodeBuffers where the flag will be checked anyway. Otherwise, we proceed to the traditional buffer scan. Thoughts? I've done recovery performance for TRUNCATE. Test case: 1 parent table with 100 child partitions. TRUNCATE each child partition (1 transaction per table). Currently, it takes a while to recover when we have large shared_buffers setting, but with the patch applied the recovery is almost constant (0.206 s below). | s_b | master | patched | |-------|--------|---------| | 128MB | 0.105 | 0.105 | | 1GB | 0.205 | 0.205 | | 20GB | 2.008 | 0.206 | | 100GB | 9.315 | 0.206 | Method of Testing (assuming streaming replication is configured): 1. Create 1 parent table and 100 child partitions 2. Insert data to each table. 3. Pause WAL replay on standby. ( SELECT pg_wal_replay_pause(); ) 4. TRUNCATE each child partitions on primary (1 transaction per table). Stop the primary 5. Resume the WAL replay and promote standby. ( SELECT pg_wal_replay_resume(); pg_ctl promote) I have confirmed that the relations became empty on standby. Your thoughts, feedback are very much appreciated. Regards, Kirk Jamison
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