public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: Melanie Plageman <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Robert Haas <[email protected]>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
Date: Wed, 27 Aug 2025 17:55:27 +0500
Message-ID: <CALdSSPhvV8mf36+8SVKviJA3SkzzP1iJ09T23Pk8EYNZHEt3xA@mail.gmail.com> (raw)
In-Reply-To: <CAAKRu_bGCgUuhmy1Mzkw3yCmbGcjNQAjV=OmjuW6hz90PuXKWA@mail.gmail.com>
References: <CAAKRu_ZMw6Npd_qm2KM+FwQ3cMOMx1Dh3VMhp8-V7SOLxdK9-g@mail.gmail.com>
<CAAKRu_YXP1Us41fkZJeBK5SSj5whNC0C9fZNT6Brdtm+F0agpg@mail.gmail.com>
<CAAKRu_bAR5uCfjuc06vc_xrZjNCJLs493NgHjTOUDso9qGdE0w@mail.gmail.com>
<CAAKRu_ac4vUdU-U3ax8j=N8y2OM9-pbFZhafLVfzDOM8MtzvvA@mail.gmail.com>
<[email protected]>
<CAAKRu_a-aVGxNEdkJt+96HGryQXuQNuXe+PhR0KcnUhXSOPBJw@mail.gmail.com>
<[email protected]>
<CAAKRu_ZH8kL0Zm0j7m7DC9fzk7ru7yf9rm2pEQRvx1iXX25aPQ@mail.gmail.com>
<CAAKRu_bGCgUuhmy1Mzkw3yCmbGcjNQAjV=OmjuW6hz90PuXKWA@mail.gmail.com>
On Sat, 2 Aug 2025 at 02:36, Melanie Plageman <[email protected]> wrote:
>
> On Thu, Jul 31, 2025 at 6:58 PM Melanie Plageman
> <[email protected]> wrote:
> >
> > The patch "Set-pd_prune_xid-on-insert.txt" can be applied as the last
> > patch in the set. It sets pd_prune_xid on insert (so pages filled by
> > COPY or insert can also be set all-visible in the VM before they are
> > vacuumed). I gave it a .txt extension because it currently fails
> > 035_standby_logical_decoding due to a recovery conflict. I need to
> > investigate more to see if this is a bug in my patch set or elsewhere
> > in Postgres.
>
> I figured out that if we set the VM on-access, we need to enable
> hot_standby_feedback in more places in 035_standby_logical_decoding.pl
> to avoid recovery conflicts. I've done that in the attached updated
> version 6. There are a few other issues in
> 035_standby_logical_decoding.pl that I reported here [1]. With these
> changes, setting pd_prune_xid on insert passes tests. Whether or not
> we want to do it (and what the heuristic should be for deciding when
> to do it) is another question.
>
> - Melanie
>
> [1] https://www.postgresql.org/message-id/flat/CAAKRu_YO2mEm%3DZWZKPjTMU%3DgW5Y83_KMi_1cr51JwavH0ctd7w%4...
v6-0015:
I chose to verify whether this single modification would be beneficial
on the HEAD.
Benchmark I did:
```
\timing
CREATE TABLE zz(i int);
alter table zz set (autovacuum_enabled = false);
TRUNCATE zz;
copy zz from program 'yes 2 | head -n 180000000';
copy zz from program 'yes 2 | head -n 180000000';
delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')',
'}')::int[])[2] = 7 ;
VACUUM FREEZE zz;
```
And I checked perf top footprint for last statement (vacuum). My
detailed results are attached. It is a HEAD vs HEAD+v6-0015 benchmark.
TLDR: function inlining is indeed beneficial, TransactionIdPrecedes
function disappears from perf top footprint, though query runtime is
not changed much. So, while not resulting in query speedup, this can
save CPU.
Maybe we can derive an artificial benchmark, which will show query
speed up, but for now I dont have one.
--
Best regards,
Kirill Reshke
without:
Overhead Shared Ob Symbol
7.13% postgres [.] heap_page_prune_and_freeze
6.46% postgres [.] heap_prune_record_unchanged_lp_normal
5.78% [kernel] [k] _raw_spin_unlock_irqrestore
4.51% postgres [.] heap_prepare_freeze_tuple
4.38% postgres [.] HeapTupleSatisfiesVacuumHorizon
4.04% postgres [.] heap_page_is_all_visible
3.79% postgres [.] hash_search_with_hash_value
3.58% [kernel] [k] copy_page_from_iter_atomic
3.51% [kernel] [k] mark_buffer_dirty
3.28% postgres [.] pg_checksum_page
2.08% postgres [.] pg_comp_crc32c_avx512
1.96% postgres [.] heap_pre_freeze_checks
1.90% postgres [.] compactify_tuples
1.87% postgres [.] PageRepairFragmentation
1.82% postgres [.] PageSetChecksumCopy
1.75% libc.so.6 [.] __memmove_evex_unaligned_erms
1.72% postgres [.] heap_log_freeze_cmp
1.72% postgres [.] log_heap_prune_and_freeze
1.43% postgres [.] LWLockReleaseInternal
1.36% postgres [.] heap_freeze_prepared_tuples
1.30% postgres [.] AdvanceXLInsertBuffer
1.30% postgres [.] TransactionIdPrecedes
0.96% postgres [.] HeapTupleSatisfiesVacuum
0.91% [kernel] [k] filemap_get_entry
0.87% [kernel] [k] ext4_da_write_end
0.84% postgres [.] GetPrivateRefCountEntry
0.83% [kernel] [k] fault_in_readable
0.81% postgres [.] heap_tuple_needs_eventual_freeze
0.80% postgres [.] TransactionIdDidCommit
0.71% [kernel] [k] __block_commit_write
0.69% postgres [.] LWLockAttemptLock
0.64% postgres [.] TransactionLogFetch
0.62% postgres [.] heap_page_prune_execute
0.62% [kernel] [k] refill_stock
0.57% postgres [.] LockBufHdr
0.53% [kernel] [k] lruvec_stat_mod_folio
0.52% [kernel] [k] refill_obj_stock
0.51% postgres [.] TransactionIdFollows
reshke=# \timing
Timing is on.
reshke=# copy zz from program 'yes 2 | head -n 180000000';
COPY 180000000
Time: 58795.832 ms (00:58.796)
reshke=# copy zz from program 'yes 2 | head -n 180000000';
delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')', '}')::int[])[2] = 7 ;
VACUUM FREEZE zz;
COPY 180000000
Time: 62297.357 ms (01:02.297)
DELETE 1592921
Time: 373495.158 ms (06:13.495)
VACUUM
Time: 199150.554 ms (03:19.151)
with:
7.07% postgres [.] heap_prune_record_unchanged_lp_normal
6.67% postgres [.] heap_page_prune_and_freeze
5.87% [kernel] [k] _raw_spin_unlock_irqrestore
4.48% postgres [.] HeapTupleSatisfiesVacuumHorizon
4.05% postgres [.] heap_prepare_freeze_tuple
4.00% [kernel] [k] mark_buffer_dirty
3.92% [kernel] [k] copy_page_from_iter_atomic
3.59% postgres [.] heap_page_is_all_visible
3.58% postgres [.] pg_checksum_page
3.42% postgres [.] hash_search_with_hash_value
2.26% postgres [.] pg_comp_crc32c_avx512
2.12% postgres [.] PageRepairFragmentation
2.07% postgres [.] PageSetChecksumCopy
1.89% postgres [.] heap_pre_freeze_checks
1.83% postgres [.] heap_log_freeze_cmp
1.65% [kernel] [k] filemap_get_entry
1.64% postgres [.] compactify_tuples
1.53% postgres [.] LWLockReleaseInternal
1.50% postgres [.] log_heap_prune_and_freeze
1.49% postgres [.] heap_freeze_prepared_tuples
1.40% libc.so.6 [.] __memmove_evex_unaligned_erms
1.38% postgres [.] AdvanceXLInsertBuffer
1.04% postgres [.] HeapTupleSatisfiesVacuum
1.03% [kernel] [k] __block_commit_write
0.96% postgres [.] LWLockAttemptLock
0.92% [kernel] [k] ext4_da_write_end
0.90% postgres [.] heap_tuple_needs_eventual_freeze
0.80% postgres [.] GetPrivateRefCountEntry
0.78% [kernel] [k] fault_in_readable
0.75% postgres [.] TransactionIdDidCommit
0.72% [kernel] [k] lruvec_stat_mod_folio
0.69% [kernel] [k] refill_stock
0.69% postgres [.] TransactionLogFetch
0.64% [kernel] [k] refill_obj_stock
0.61% postgres [.] XLogInsert
0.56% postgres [.] heap_page_prune_execute
0.56% [kernel] [k] _raw_spin_lock
0.50% postgres [.] uint32_hash
reshke=# copy zz from program 'yes 2 | head -n 180000000';
copy zz from program 'yes 2 | head -n 180000000';
delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')', '}')::int[])[2] = 7 ;
COPY 180000000
Time: 57489.665 ms (00:57.490)
COPY 180000000
Time: 60711.024 ms (01:00.711)
DELETE 1592921
Time: 375481.858 ms (06:15.482)
reshke=#
VACUUM FREEZE zz;
VACUUM
Time: 196442.340 ms (03:16.442)
Attachments:
[text/plain] attach.txt (4.4K, 2-attach.txt)
download | inline:
without:
Overhead Shared Ob Symbol
7.13% postgres [.] heap_page_prune_and_freeze
6.46% postgres [.] heap_prune_record_unchanged_lp_normal
5.78% [kernel] [k] _raw_spin_unlock_irqrestore
4.51% postgres [.] heap_prepare_freeze_tuple
4.38% postgres [.] HeapTupleSatisfiesVacuumHorizon
4.04% postgres [.] heap_page_is_all_visible
3.79% postgres [.] hash_search_with_hash_value
3.58% [kernel] [k] copy_page_from_iter_atomic
3.51% [kernel] [k] mark_buffer_dirty
3.28% postgres [.] pg_checksum_page
2.08% postgres [.] pg_comp_crc32c_avx512
1.96% postgres [.] heap_pre_freeze_checks
1.90% postgres [.] compactify_tuples
1.87% postgres [.] PageRepairFragmentation
1.82% postgres [.] PageSetChecksumCopy
1.75% libc.so.6 [.] __memmove_evex_unaligned_erms
1.72% postgres [.] heap_log_freeze_cmp
1.72% postgres [.] log_heap_prune_and_freeze
1.43% postgres [.] LWLockReleaseInternal
1.36% postgres [.] heap_freeze_prepared_tuples
1.30% postgres [.] AdvanceXLInsertBuffer
1.30% postgres [.] TransactionIdPrecedes
0.96% postgres [.] HeapTupleSatisfiesVacuum
0.91% [kernel] [k] filemap_get_entry
0.87% [kernel] [k] ext4_da_write_end
0.84% postgres [.] GetPrivateRefCountEntry
0.83% [kernel] [k] fault_in_readable
0.81% postgres [.] heap_tuple_needs_eventual_freeze
0.80% postgres [.] TransactionIdDidCommit
0.71% [kernel] [k] __block_commit_write
0.69% postgres [.] LWLockAttemptLock
0.64% postgres [.] TransactionLogFetch
0.62% postgres [.] heap_page_prune_execute
0.62% [kernel] [k] refill_stock
0.57% postgres [.] LockBufHdr
0.53% [kernel] [k] lruvec_stat_mod_folio
0.52% [kernel] [k] refill_obj_stock
0.51% postgres [.] TransactionIdFollows
reshke=# \timing
Timing is on.
reshke=# copy zz from program 'yes 2 | head -n 180000000';
COPY 180000000
Time: 58795.832 ms (00:58.796)
reshke=# copy zz from program 'yes 2 | head -n 180000000';
delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')', '}')::int[])[2] = 7 ;
VACUUM FREEZE zz;
COPY 180000000
Time: 62297.357 ms (01:02.297)
DELETE 1592921
Time: 373495.158 ms (06:13.495)
VACUUM
Time: 199150.554 ms (03:19.151)
with:
7.07% postgres [.] heap_prune_record_unchanged_lp_normal
6.67% postgres [.] heap_page_prune_and_freeze
5.87% [kernel] [k] _raw_spin_unlock_irqrestore
4.48% postgres [.] HeapTupleSatisfiesVacuumHorizon
4.05% postgres [.] heap_prepare_freeze_tuple
4.00% [kernel] [k] mark_buffer_dirty
3.92% [kernel] [k] copy_page_from_iter_atomic
3.59% postgres [.] heap_page_is_all_visible
3.58% postgres [.] pg_checksum_page
3.42% postgres [.] hash_search_with_hash_value
2.26% postgres [.] pg_comp_crc32c_avx512
2.12% postgres [.] PageRepairFragmentation
2.07% postgres [.] PageSetChecksumCopy
1.89% postgres [.] heap_pre_freeze_checks
1.83% postgres [.] heap_log_freeze_cmp
1.65% [kernel] [k] filemap_get_entry
1.64% postgres [.] compactify_tuples
1.53% postgres [.] LWLockReleaseInternal
1.50% postgres [.] log_heap_prune_and_freeze
1.49% postgres [.] heap_freeze_prepared_tuples
1.40% libc.so.6 [.] __memmove_evex_unaligned_erms
1.38% postgres [.] AdvanceXLInsertBuffer
1.04% postgres [.] HeapTupleSatisfiesVacuum
1.03% [kernel] [k] __block_commit_write
0.96% postgres [.] LWLockAttemptLock
0.92% [kernel] [k] ext4_da_write_end
0.90% postgres [.] heap_tuple_needs_eventual_freeze
0.80% postgres [.] GetPrivateRefCountEntry
0.78% [kernel] [k] fault_in_readable
0.75% postgres [.] TransactionIdDidCommit
0.72% [kernel] [k] lruvec_stat_mod_folio
0.69% [kernel] [k] refill_stock
0.69% postgres [.] TransactionLogFetch
0.64% [kernel] [k] refill_obj_stock
0.61% postgres [.] XLogInsert
0.56% postgres [.] heap_page_prune_execute
0.56% [kernel] [k] _raw_spin_lock
0.50% postgres [.] uint32_hash
reshke=# copy zz from program 'yes 2 | head -n 180000000';
copy zz from program 'yes 2 | head -n 180000000';
delete from zz where (REPLACE(REPLACE(ctid::text, '(', '{'), ')', '}')::int[])[2] = 7 ;
COPY 180000000
Time: 57489.665 ms (00:57.490)
COPY 180000000
Time: 60711.024 ms (01:00.711)
DELETE 1592921
Time: 375481.858 ms (06:15.482)
reshke=#
VACUUM FREEZE zz;
VACUUM
Time: 196442.340 ms (03:16.442)
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
In-Reply-To: <CALdSSPhvV8mf36+8SVKviJA3SkzzP1iJ09T23Pk8EYNZHEt3xA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox