Re: Parallel heap vacuum - Mailing list pgsql-hackers

On Mon, Mar 3, 2025 at 3:24 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
>
> Another performance regression I can see in the results is that heap
> vacuum phase (phase III) got slower with the patch. It's weired to me
> since I don't touch the code of heap vacuum phase. I'm still
> investigating the cause.

I have investigated this regression. I've confirmed that In both
scenarios (patched and unpatched), the entire table and its associated
indexes were loaded into the shared buffer before the vacuum. Then,
the 'perf record' analysis, focused specifically on the heap vacuum
phase of the patched code, revealed numerous soft page faults
occurring:

    62.37%    13.90%  postgres  postgres            [.] lazy_vacuum_heap_rel
            |
            |--52.44%--lazy_vacuum_heap_rel
            |          |
            |          |--46.33%--lazy_vacuum_heap_page (inlined)
            |          |          |
            |          |          |--32.42%--heap_page_is_all_visible (inlined)
            |          |          |          |
            |          |          |          |--26.46%--HeapTupleSatisfiesVacuum
            |          |          |          |
HeapTupleSatisfiesVacuumHorizon
            |          |          |          |
HeapTupleHeaderXminCommitted (inlined)
            |          |          |          |          |
            |          |          |          |           --18.52%--page_fault
            |          |          |          |                     do_page_fault
            |          |          |          |
__do_page_fault
            |          |          |          |
handle_mm_fault
            |          |          |          |
__handle_mm_fault
            |          |          |          |
handle_pte_fault
            |          |          |          |                     |
            |          |          |          |
|--16.53%--filemap_map_pages
            |          |          |          |                     |          |
            |          |          |          |                     |
        --2.63%--alloc_set_pte
            |          |          |          |                     |
                  pfn_pte
            |          |          |          |                     |
            |          |          |          |
--1.99%--pmd_page_vaddr
            |          |          |          |
            |          |          |           --1.99%--TransactionIdPrecedes

I did not observe these page faults in the 'perf record' results for
the HEAD version. Furthermore, when I disabled parallel heap vacuum
while keeping parallel index vacuuming enabled, the regression
disappeared. Based on these findings, the likely cause of the
regression appears to be that during parallel heap vacuum operations,
table blocks were loaded into the shared buffer by parallel vacuum
workers. However, in the heap vacuum phase, the leader process needed
to process all blocks, resulting in soft page faults while creating
Page Table Entries (PTEs). Without the patch, the backend process had
already created PTEs during the heap scan, thus preventing these
faults from occurring during the heap vacuum phase.

It appears to be an inherent side effect of utilizing parallel
queries. Given this understanding, it's likely an acceptable trade-off
that we can accommodate.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com