Re: Confine vacuum skip logic to lazy_scan_skip

On 7/24/24 07:40, Thomas Munro wrote:
> On Tue, Jul 16, 2024 at 1:52 PM Noah Misch <noah(at)leadboat(dot)com> wrote:
>> On Mon, Jul 15, 2024 at 03:26:32PM +1200, Thomas Munro wrote:
>> That's reasonable. radixtree already forbids mutations concurrent with
>> iteration, so there's no new concurrency hazard. One alternative is
>> per_buffer_data big enough for MaxOffsetNumber, but that might thrash caches
>> measurably. That patch is good to go apart from these trivialities:
>
> Thanks! I have pushed that patch, without those changes you didn't like.
>
> Here's are Melanie's patches again. They work, and the WAL flush
> frequency problem is mostly gone since we increased the BAS_VACUUM
> default ring size (commit 98f320eb), but I'm still looking into how
> this read-ahead and the write-behind generated by vacuum (using
> patches not yet posted) should interact with each other and the ring
> system, and bouncing ideas around about that with my colleagues. More
> on that soon, hopefully. I suspect that there won't be changes to
> these patches as a result, but I still want to hold off for a bit.

I've been looking at some other vacuum-related patches, so I took a look
at these remaining bits too. I don't have much to say about the code
(seems perfectly fine to me), so I decided to do a bit of testing.

I did a simple test (see the attached .sh script) that runs vacuum on a
table with different fractions of rows updated. The table has ~100 rows
per page, with 50% fill factor, and the updates touch ~1%, 0.1%, 0.05%
rows, and then even smaller fractions up to 0.0001%. This determines how
many pages get touched. With 1% fraction almost every page gets
modified, then the fraction quickly drops. With 0.0001% only about 0.01%
of pages gets modified.

Attached is a CSV with raw results from two machines, and also a PDF
with a comparison of the two build (master vs. patched). In vast
majority of cases, the patched build is much faster, usually 2-3x.

There are a couple cases where it regresses by ~30%, but only on one of
the machines with older storage (RAID on SATA SSDs), with 1% rows
updated (which means almost all pages get modified). So this is about
sequential access. It's a bit weird, but probably not a fatal issue.

There is also a couple smaller regressions on the "xeon" machine with
M.2 SSD, for lower update fractions - 0.05% rows updated means 5% pages
need vacuum. But I think this is more a sign of us being too aggressive
in detecting (and forcing) sequential patterns - on master, we end up
scanning 50% pages, thanks to SKIP_PAGES_THRESHOLD. I'll start a new
thread about that ...

Anyway, these results look very nice - a couple limited regressions,
substantial speedups in plausible/common cases.

regards

--
Tomas Vondra