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 |
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 |