public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: Pg Hackers <[email protected]>
Subject: Re: Page freezing, FSM, and WAL replay
Date: Fri, 8 Nov 2024 17:21:46 +0500
Message-ID: <CALdSSPiwW0556i5pvF_4x=AcFdqp0yjcgBLzPOB316Wr3TTUUg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Fri, 8 Nov 2024 at 17:11, Alvaro Herrera <[email protected]> wrote:
>
> We recently had a customer report a very strange problem, involving a
> very large insert-only table: without explanation, insertions would
> stall for several seconds, causing application timeout and process
> accumulation and other nastiness.
>
> After some investigation, we narrowed this down to happening immediately
> after the first VACUUM on the table right after a standby got promoted.
> It wasn't at first obvious what the connection between these factors
> was, but eventually we realized that VACUUM must have been skipping a
> bunch of pages because they had been marked all-frozen previously, so
> the FSM was not updated with the correct freespace figures for those
> pages. The FSM pages had been transmitted as full-page images on WAL
> before the promotion (because wal_log_hints), so they contained
> optimistic numbers on amount of free space coming from the previous
> master. (Because this only happens on the first change to that FSM page
> after a checkpoint, it's quite likely that one page every few thousand
> or so contains optimistic figures while the others remain all zeroes, or
> something like that.)
>
> Before VACUUM, nothing too bad would happen, because the upper layers of
> the FSM would not know about those optimistic numbers. But when VACUUM
> does FreeSpaceMapVacuum, it propagates those numbers upwards; as soon as
> that happens, inserters looking for pages would be told about those
> pages (wrongly catalogued to contain sufficient free space), go to
> insert there, and fail because there isn't actually any freespace; ask
> FSM for another page, lather, rinse, repeat until all those pages are
> all catalogued correctly by FSM, at which point things continue
> normally. (There are many processes doing this chase-up concurrently
> and it seems a pretty contentious process, about which see last
> paragraph; it can be seen in pg_xlogdump that it takes several seconds
> for things to settle).
>
> After considering several possible solutions, I propose to have
> heap_xlog_visible compute free space for any page being marked frozen;
> Pavan adds to that to have heap_xlog_clean compute free space for all
> pages also. This means that if we later promote this standby and VACUUM
> skips all-frozen pages, their FSM numbers are going to be up-to-date
> anyway. Patch attached.
>
>
> Now, it's possible that the problem occurs for all-visible pages not
> just all-frozen. I haven't seen that one, maybe there's some reason why
> it cannot. But fixing both things together is an easy change in the
> proposed patch: just do it on xlrec->flags != 0 rather than checking for
> the specific all-frozen flag.
>
> (This problem seems to be made worse by the fact that
> RecordAndGetPageWithFreeSpace (or rather fsm_set_and_search) holds
> exclusive lock on the FSM page for the whole duration of update plus
> search. So when there are many inserters, they all race to the update
> process. Maybe it'd be less terrible if we would release exclusive
> after the update and grab shared lock for the search in
> fsm_set_and_search, but we still have to have the exclusive for the
> update, so the contention point remains. Maybe there's not sufficient
> improvement to make a practical difference, so I'm not proposing
> changing this.)
>
> --
> Álvaro Herrera
Hi!
Sorry for disturbing you after so much time. Today, while I was doing
my stuff and researching several FSM-related questions, I noticed that
the comment in the `heap_xlog_visible` function used improper
punctuation.
After some investigation, I conclude that this is an oversight of
ab7dbd6, which was proposed in this thread.
I'd like to propose a fix for that.
Sorry for making so much noise for this minor matter.
--
Best regards,
Kirill Reshke
Attachments:
[application/octet-stream] v1-0001-Fixup-FSM-comment-inside-heap_xlog_visible.patch (1.5K, ../CALdSSPiwW0556i5pvF_4x=AcFdqp0yjcgBLzPOB316Wr3TTUUg@mail.gmail.com/2-v1-0001-Fixup-FSM-comment-inside-heap_xlog_visible.patch)
download | inline diff:
From 0c273cf2ace18b1918c2ba188ff5c88f42c5d519 Mon Sep 17 00:00:00 2001
From: reshke kirill <[email protected]>
Date: Fri, 8 Nov 2024 12:06:13 +0000
Subject: [PATCH v1] Fixup FSM comment inside heap_xlog_visible
---
src/backend/access/heap/heapam_xlog.c | 8 ++++----
1 file changed, 4 insertions(+), 4 deletions(-)
diff --git a/src/backend/access/heap/heapam_xlog.c b/src/backend/access/heap/heapam_xlog.c
index c5208f3df61..160f1fbeb8e 100644
--- a/src/backend/access/heap/heapam_xlog.c
+++ b/src/backend/access/heap/heapam_xlog.c
@@ -249,13 +249,13 @@ heap_xlog_visible(XLogReaderState *record)
/*
* Since FSM is not WAL-logged and only updated heuristically, it
- * easily becomes stale in standbys. If the standby is later promoted
+ * easily becomes stale in standbys. If the standby is later promoted
* and runs VACUUM, it will skip updating individual free space
* figures for pages that became all-visible (or all-frozen, depending
- * on the vacuum mode,) which is troublesome when FreeSpaceMapVacuum
+ * on the vacuum mode), which is troublesome when FreeSpaceMapVacuum
* propagates too optimistic free space values to upper FSM layers;
- * later inserters try to use such pages only to find out that they
- * are unusable. This can cause long stalls when there are many such
+ * Later, inserters try to use such pages only to find out that they
+ * are unusable. This can cause long stalls when there are many such
* pages.
*
* Forestall those problems by updating FSM's idea about a page that
--
2.34.1
view thread (2+ messages)
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]
Subject: Re: Page freezing, FSM, and WAL replay
In-Reply-To: <CALdSSPiwW0556i5pvF_4x=AcFdqp0yjcgBLzPOB316Wr3TTUUg@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