From: | Thomas Munro <thomas(dot)munro(at)gmail(dot)com> |
---|---|
To: | Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Handing off SLRU fsyncs to the checkpointer |
Date: | 2020-08-26 03:58:14 |
Message-ID: | CA+hUKGKAch2h0s75yOLWj+ZqCAxaeezWNpOW+oZLnwkjvw4NOA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Aug 25, 2020 at 9:16 PM Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)com> wrote:
> I just wanted to help testing this patch (defer SLRU fsyncs during recovery) and also faster compactify_tuples() patch [2] as both are related to the WAL recovery performance in which I'm interested in. This is my first message to this mailing group so please let me know also if I should adjust testing style or formatting.
Hi Jakub,
Thanks very much for these results!
> - Handing SLRU sync work over to the checkpointer: in my testing it accelerates WAL recovery performance on slower / higer latency storage by ~20%
Wow. Those fsyncs must have had fairly high latency (presumably due
to queuing behind other write back activity).
> - Faster sort in compactify_tuples(): in my testing it accelerates WAL recovery performance for HOT updates also by ~20%
Nice.
> In the last final case the top profile is as follows related still to the sorting but as I understand in much optimal way:
>
> 26.68% postgres postgres [.] qsort_itemoff
> ---qsort_itemoff
> |--14.17%--qsort_itemoff
> | |--10.96%--compactify_tuples
> | | PageRepairFragmentation
> | | heap2_redo
> | | StartupXLOG
> | --3.21%--qsort_itemoff
> | --3.10%--compactify_tuples
> | PageRepairFragmentation
> | heap2_redo
> | StartupXLOG
> --12.51%--compactify_tuples
> PageRepairFragmentation
> heap2_redo
> StartupXLOG
I wonder if there is something higher level that could be done to
reduce the amount of compaction work required in the first place, but
in the meantime I'm very happy if we can improve the situation so much
with such a microscopic improvement that might eventually benefit
other sorting stuff...
> 8.38% postgres libc-2.17.so [.] __memmove_ssse3_back
> ---__memmove_ssse3_back
> compactify_tuples
> PageRepairFragmentation
> heap2_redo
Hmm, I wonder if this bit could go teensy bit faster by moving as many
adjacent tuples as you can in one go rather than moving them one at a
time...
> The append-only bottleneck appears to be limited by syscalls/s due to small block size even with everything in FS cache (but not in shared buffers, please compare with TEST1 as there was no such bottleneck at all):
>
> 29.62% postgres [kernel.kallsyms] [k] copy_user_enhanced_fast_string
> ---copy_user_enhanced_fast_string
> |--17.98%--copyin
> [..]
> | __pwrite_nocancel
> | FileWrite
> | mdwrite
> | FlushBuffer
> | ReadBuffer_common
> | ReadBufferWithoutRelcache
> | XLogReadBufferExtended
> | XLogReadBufferForRedoExtended
> | --17.57%--btree_xlog_insert
To move these writes out of recovery's way, we should probably just
run the bgwriter process during crash recovery. I'm going to look
into that.
The other thing is of course the checkpointer process, and our
end-of-recovery checkpoint. I was going to suggest it should be
optional and not done by the recovery process itself, which is why
some earlier numbers I shared didn't include the end-of-recovery
checkpoint, but then I realised it complicated the numbers for this
little patch and, anyway, it'd be a good idea to open that can of
worms separately...
> | btree_redo
> | StartupXLOG
> |
> --11.64%--copyout
> [..]
> __pread_nocancel
> --11.44%--FileRead
> mdread
> ReadBuffer_common
> ReadBufferWithoutRelcache
> XLogReadBufferExtended
> XLogReadBufferForRedoExtended
For these reads, the solution should be WAL prefetching, but the patch
I shared for that (and will be updating soon) is just one piece of the
puzzle, and as it stands it actually *increases* the number of
syscalls by adding some posix_fadvise() calls, so ... erm, for an
all-in-kernel-cache-already workload like what you profiled there it
can only make things worse on that front. But... when combined with
Andres's work-in-progress AIO stuff, a whole bunch of reads can be
submitted with a single system call ahead of time and then the results
are delivered directly into our buffer pool by kernel threads or
hardware DMA, so we'll not only avoid going off CPU during recovery
but we'll also reduce the system call count.
> Turning on/off the defer SLRU patch and/or fsync doesn't seem to make any difference, so if anyone is curious the next sets of append-only bottlenecks is like below:
>
> 14.69% postgres postgres [.] hash_search_with_hash_value
> ---hash_search_with_hash_value
> |--9.80%--BufTableLookup
> | ReadBuffer_common
> | ReadBufferWithoutRelcache
> | XLogReadBufferExtended
> | XLogReadBufferForRedoExtended
Hypothesis: Your 24GB buffer pool requires somewhere near 70MB of
buffer mapping table (huh, pg_shmem_allocations doesn't show that
correctly), so it doesn't fit into any level of your memory cache
hierarchy and it's super random access, so every buffer lookup is
costing you a ~60-100ns memory stall. Maybe?
If that's the reason for this showing up in your profile, I think I
could probably add a little cache line prefetch phase to the WAL
prefetch patch to fix it. I've actually tried prefetching the buffer
mapping cache lines before, without success, but never in recovery.
I'll make a note to look into that.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2020-08-26 04:38:52 | Re: some unused parameters cleanup |
Previous Message | Mahendra Singh Thalor | 2020-08-26 03:24:46 | Re: display offset along with block number in vacuum errors |