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

From: "k(dot)jamison(at)fujitsu(dot)com" <k(dot)jamison(at)fujitsu(dot)com>
To: "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com>
Cc: 'Thomas Munro' <thomas(dot)munro(at)gmail(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andres Freund <andres(at)anarazel(dot)de>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: [Patch] Optimize dropping of relation buffers using dlist
Date: 2020-11-04 02:58:27
Message-ID: OSBPR01MB2341773F204CC467FB882F70EFEF0@OSBPR01MB2341.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v29-0001-Prevent-invalidating-blocks-in-smgrextend-during.patch application/octet-stream 1.1 KB
v29-0002-Add-bool-param-in-smgrnblocks-for-cached-blocks.patch application/octet-stream 8.8 KB
v29-0003-Optimize-DropRelFileNodeBuffers-during-recovery.patch application/octet-stream 7.3 KB
v29-0004-TRUNCATE-optimization.patch application/octet-stream 5.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuro Yamada 2020-11-04 03:04:48 Re: list of extended statistics on psql
Previous Message Amit Langote 2020-11-04 02:32:18 Re: ModifyTable overheads in generic plans