Re: FSM Corruption (was: Could not read block at end of the relation)

Your v3 has the right functionality. As further confirmation of the fix, I
tried reverting the non-test parts of commit 917dc7d "Fix WAL-logging of FSM
and VM truncation". That commit's 008_fsm_truncation.pl fails with 917dc7d
reverted from master, and adding this patch makes it pass again. I ran
pgindent and edited comments. I think the attached version is ready to go.

While updating comments in FreeSpaceMapPrepareTruncateRel(), I entered a
rabbit hole about the comments 917dc7d left about torn pages. I'm sharing
these findings just in case it helps a reader of the $SUBJECT patch avoid the
same rabbit hole. Both fsm and vm read with RBM_ZERO_ON_ERROR, so I think
they're fine with torn pages. Per the README sentences I'm adding, FSM could
stop writing WAL. I'm not proposing that, but I do bet it's the right thing.
visibilitymap_prepare_truncate() has mirrored fsm truncate since 917dc7d. The
case for removing WAL there is clearer still, because parallel function
visibilitymap_clear() does not write WAL. I'm attaching a WIP patch to remove
visibilitymap_prepare_truncate() WAL. I'll abandon that or pursue it for v18,
in a different thread.

On Fri, Mar 29, 2024 at 09:47:52AM +0100, Ronan Dunklau wrote:
> Le jeudi 21 mars 2024, 14:51:25 CET Ronan Dunklau a écrit :
> > Le samedi 16 mars 2024, 05:58:34 CET Noah Misch a écrit :
> > > > - Get profiles with both master and patched. (lseek or freespace.c
> > > > functions>
> > > >
> > > > rising by 0.1%-1% would fit what we know.)
>
> Running perf during the same benchmark, I get the following samples for lseek:
>
> With the patch:
>
> 0.02% postgres libc.so.6 [.] llseek(at)GLIBC_2(dot)2(dot)5
>
> Without the patch:
>
> 0.01% postgres libc.so.6 [.] llseek(at)GLIBC_2(dot)2(dot)5
>
>
> So nothing too dramatic.
> I haven't been able to come up with a better benchmark.

I tried, without success, to reproduce the benefit of commit 719c84c "Extend
relations multiple blocks at a time to improve scalability" like
https://www.postgresql.org/message-id/flat/CA%2BTgmob7xED4AhoqLspSOF0wCMYEomgHfuVdzNJnwWVoE_c60g%40mail.gmail.com
did. My conditions must be lacking whatever factor caused the consistent >20%
improvement in the linked message. Environment was ext4 on magnetic disk,
Linux 3.10.0-1160.99.1.el7.x86_64, 8 threads. I'm attaching the script I used
to test 719c84c^, 719c84c, and [719c84c with GetPageWithFreeSpace() made to
always find nothing]. I'm also attaching the script's output after filtration
through "grep ^TIME | sort -n". The range of timings within each test
scenario dwarfs any difference across scenarios. I casually tried some other
variations without obvious change:

- 24MB shared_buffers
- 128MB shared_buffers
- 4 threads
- 32 threads
- bgwriter_flush_after & backend_flush_after at their defaults, instead of 0
- 2024-04 code (e2a2357) unmodified, then with GetPageWithFreeSpace() made to always find nothing
- logged tables
- INSERT ... SELECT instead of COPY

If I were continuing the benchmark study, I would try SSD, a newer kernel,
and/or shared_buffers=48GB. Instead, since your perf results show only +0.01%
CPU from new lseek() calls, I'm going to stop there and say it's worth taking
the remaining risk that some realistic scenario gets a material regression
from those new lseek() calls.