public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v5 2/5] Introduce a new syntax to add/drop publications
36+ messages / 5 participants
[nested] [flat]
* [PATCH v5 2/5] Introduce a new syntax to add/drop publications
@ 2021-02-13 05:11 Japin Li <[email protected]>
0 siblings, 0 replies; 36+ messages in thread
From: Japin Li @ 2021-02-13 05:11 UTC (permalink / raw)
At present, if we want to update publications in subscription, we can
use SET PUBLICATION, however, it requires supply all publications that
exists and the new publications if we want to add new publications, it's
inconvenient. The new syntax only supply the new publications. When
the refresh is true, it only refresh the new publications.
---
src/backend/commands/subscriptioncmds.c | 144 ++++++++++++++++++++++++
src/backend/parser/gram.y | 20 ++++
src/include/nodes/parsenodes.h | 2 +
3 files changed, 166 insertions(+)
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 5cf874e0b4..ad271deb64 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -47,6 +47,7 @@
#include "utils/syscache.h"
static List *fetch_table_list(WalReceiverConn *wrconn, List *publications);
+static List *merge_subpublications(HeapTuple tuple, List *newpublist, bool addpub);
static void ReportSlotConnectionError(List *rstates, Oid subid, char *slotname, char *err);
@@ -962,6 +963,53 @@ AlterSubscription(AlterSubscriptionStmt *stmt, bool isTopLevel)
break;
}
+ case ALTER_SUBSCRIPTION_ADD_PUBLICATION:
+ case ALTER_SUBSCRIPTION_DROP_PUBLICATION:
+ {
+ bool copy_data = false;
+ bool isadd = stmt->kind == ALTER_SUBSCRIPTION_ADD_PUBLICATION;
+ bool refresh;
+ List *publist = NIL;
+
+ publist = merge_subpublications(tup, stmt->publication, isadd);
+
+ parse_subscription_options(stmt->options,
+ NULL, /* no "connect" */
+ NULL, NULL, /* no "enabled" */
+ NULL, /* no "create_slot" */
+ NULL, NULL, /* no "slot_name" */
+ isadd ? ©_data : NULL, /* for drop, no "copy_data" */
+ NULL, /* no "synchronous_commit" */
+ &refresh,
+ NULL, NULL, /* no "binary" */
+ NULL, NULL); /* no "streaming" */
+
+ values[Anum_pg_subscription_subpublications - 1] =
+ publicationListToArray(publist);
+ replaces[Anum_pg_subscription_subpublications - 1] = true;
+
+ update_tuple = true;
+
+ /* Refresh if user asked us to. */
+ if (refresh)
+ {
+ if (!sub->enabled)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("ALTER SUBSCRIPTION with refresh is not allowed for disabled subscriptions"),
+ errhint("Use ALTER SUBSCRIPTION ... SET PUBLICATION ... WITH (refresh = false).")));
+
+ PreventInTransactionBlock(isTopLevel, "ALTER SUBSCRIPTION with refresh");
+
+ /* Only refresh the added/dropped list of publications. */
+ sub->publications = stmt->publication;
+
+ AlterSubscription_refresh(sub, copy_data);
+ }
+
+ break;
+ }
+
case ALTER_SUBSCRIPTION_REFRESH:
{
bool copy_data;
@@ -1546,3 +1594,99 @@ ReportSlotConnectionError(List *rstates, Oid subid, char *slotname, char *err)
errhint("Use %s to disassociate the subscription from the slot.",
"ALTER SUBSCRIPTION ... SET (slot_name = NONE)")));
}
+
+/*
+ * Merge ccurrent subscription's publications and user specified publications
+ * by ADD/DROP PUBLICATIONS.
+ *
+ * If isadd == true, we will add the list of publications into current
+ * subscription's publications. Otherwise, we will delete the list of
+ * publications from current subscription's publications.
+ */
+static List *
+merge_subpublications(HeapTuple tuple, List *newpublist, bool addpub)
+{
+ Datum datum;
+ bool isnull;
+ List *publist = NIL;
+ List *errlist = NIL;
+ ListCell *lc;
+
+ /* Get publications */
+ datum = SysCacheGetAttr(SUBSCRIPTIONOID,
+ tuple,
+ Anum_pg_subscription_subpublications,
+ &isnull);
+ Assert(!isnull);
+ publist = textarray_to_stringlist(DatumGetArrayTypeP(datum));
+
+ foreach(lc, newpublist)
+ {
+ char *name = strVal(lfirst(lc));
+ ListCell *cell = NULL;
+
+ foreach(cell, publist)
+ {
+ char *pubname = strVal(lfirst(cell));
+
+ if (strcmp(name, pubname) == 0)
+ {
+ if (addpub)
+ errlist = lappend(errlist, makeString(name));
+ else
+ publist = list_delete_cell(publist, cell);
+ break;
+ }
+ }
+
+ if (addpub && cell == NULL)
+ publist = lappend(publist, makeString(name));
+ else if (!addpub && cell == NULL)
+ errlist = lappend(errlist, makeString(name));
+ }
+
+ if (errlist != NIL)
+ {
+ StringInfoData errstr;
+ bool first = true;
+ int len = list_length(errlist);
+
+ initStringInfo(&errstr);
+ if (len == 1)
+ appendStringInfo(&errstr, "publication name");
+ else
+ appendStringInfo(&errstr, "publication names");
+
+ appendStringInfoString(&errstr, " \"");
+ foreach(lc, errlist)
+ {
+ char *name = strVal(lfirst(lc));
+
+ if (first)
+ appendStringInfo(&errstr, "%s", name);
+ else
+ appendStringInfo(&errstr, ", %s", name);
+
+ first = false;
+ }
+ appendStringInfoChar(&errstr, '"');
+
+ if (addpub)
+ appendStringInfo(&errstr, " %s already in subscription",
+ len == 1 ? "is" : "are");
+ else
+ appendStringInfo(&errstr, " %s no exist in subscription",
+ len == 1 ? "does" : "do");
+
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s", errstr.data)));
+ }
+
+ if (publist == NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("subscription must contain at least one publication")));
+
+ return publist;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dd72a9fc3c..e45f98d353 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -9609,6 +9609,26 @@ AlterSubscriptionStmt:
n->options = $6;
$$ = (Node *)n;
}
+ | ALTER SUBSCRIPTION name ADD_P PUBLICATION name_list opt_definition
+ {
+ AlterSubscriptionStmt *n =
+ makeNode(AlterSubscriptionStmt);
+ n->kind = ALTER_SUBSCRIPTION_ADD_PUBLICATION;
+ n->subname = $3;
+ n->publication = $6;
+ n->options = $7;
+ $$ = (Node *)n;
+ }
+ | ALTER SUBSCRIPTION name DROP PUBLICATION name_list opt_definition
+ {
+ AlterSubscriptionStmt *n =
+ makeNode(AlterSubscriptionStmt);
+ n->kind = ALTER_SUBSCRIPTION_DROP_PUBLICATION;
+ n->subname = $3;
+ n->publication = $6;
+ n->options = $7;
+ $$ = (Node *)n;
+ }
| ALTER SUBSCRIPTION name SET PUBLICATION name_list opt_definition
{
AlterSubscriptionStmt *n =
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 236832a2ca..e109607936 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3580,6 +3580,8 @@ typedef enum AlterSubscriptionType
ALTER_SUBSCRIPTION_OPTIONS,
ALTER_SUBSCRIPTION_CONNECTION,
ALTER_SUBSCRIPTION_PUBLICATION,
+ ALTER_SUBSCRIPTION_ADD_PUBLICATION,
+ ALTER_SUBSCRIPTION_DROP_PUBLICATION,
ALTER_SUBSCRIPTION_REFRESH,
ALTER_SUBSCRIPTION_ENABLED
} AlterSubscriptionType;
--
2.25.1
--=-=-=
Content-Type: text/x-patch
Content-Disposition: attachment;
filename=v5-0003-Test-ALTER-SUBSCRIPTION-.-ADD-DROP-PUBLICATION.patch
^ permalink raw reply [nested|flat] 36+ messages in thread
* Eager page freeze criteria clarification
@ 2023-07-28 15:12 Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-10-12 00:43 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
0 siblings, 2 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-07-28 15:12 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Peter Geoghegan <[email protected]>; Andres Freund <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
Hi,
While hacking on the pruning and page-level freezing code and am a bit
confused by the test guarding eager freezing [1]:
/*
* Freeze the page when heap_prepare_freeze_tuple indicates that at least
* one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
* freeze when pruning generated an FPI, if doing so means that we set the
* page all-frozen afterwards (might not happen until final heap pass).
*/
if (pagefrz.freeze_required || tuples_frozen == 0 ||
(prunestate->all_visible && prunestate->all_frozen &&
fpi_before != pgWalUsage.wal_fpi))
{
I'm trying to understand the condition fpi_before !=
pgWalUsage.wal_fpi -- don't eager freeze if pruning emitted an FPI.
Is this test meant to guard against unnecessary freezing or to avoid
freezing when the cost is too high? That is, are we trying to
determine how likely it is that the page has been recently modified
and avoid eager freezing when it would be pointless (because the page
will soon be modified again)? Or are we trying to determine how likely
the freeze record is to emit an FPI and avoid eager freezing when it
isn't worth the cost? Or something else?
The commit message says:
> Also teach VACUUM to trigger page-level freezing whenever it detects
> that heap pruning generated an FPI. We'll have already written a large
> amount of WAL just to do that much, so it's very likely a good idea to
> get freezing out of the way for the page early.
And I found the thread where it was discussed [2]. Several possible
explanations are mentioned in the thread.
But, the final rationale is still not clear to me. Could we add a
comment above the if condition specifying both:
a) what the test is a proxy for
b) the intended outcome (when do we expect to eager freeze)
And perhaps we could even describe a scenario where this heuristic is effective?
- Melanie
[1] https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L1802
[2] https://www.postgresql.org/message-id/flat/CAH2-Wzm_%3DmrWO%2BkUAJbR_gM_6RzpwVA8n8e4nh3dJGHdw_urew%4...
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-07-28 18:59 ` Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Peter Geoghegan @ 2023-07-28 18:59 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
On Fri, Jul 28, 2023 at 11:13 AM Melanie Plageman
<[email protected]> wrote:
> if (pagefrz.freeze_required || tuples_frozen == 0 ||
> (prunestate->all_visible && prunestate->all_frozen &&
> fpi_before != pgWalUsage.wal_fpi))
> {
>
> I'm trying to understand the condition fpi_before !=
> pgWalUsage.wal_fpi -- don't eager freeze if pruning emitted an FPI.
You mean "prunestate->all_visible && prunestate->all_frozen", which is
a condition of applying FPI-based eager freezing, but not traditional
lazy freezing?
Obviously, the immediate impact of that is that the FPI trigger
condition is not met unless we know for sure that the page will be
marked all-visible and all-frozen in the visibility map afterwards. A
page that's eligible to become all-visible will also be seen as
eligible to become all-frozen in the vast majority of cases, but there
are some rare and obscure cases involving MultiXacts that must be
considered here. There is little point in freezing early unless we
have at least some chance of not having to freeze the same page again
in the future (ideally forever).
There is generally no point in freezing most of the tuples on a page
when there'll still be one or more not-yet-eligible unfrozen tuples
that get left behind -- we might as well not bother with freezing at
all when we see a page where that'll be the outcome from freezing.
However, there is (once again) a need to account for rare and extreme
cases -- it still needs to be possible to do that. Specifically, we
may be forced to freeze a page that's left with some remaining
unfrozen tuples when VACUUM is fundamentally incapable of freezing
them due to its OldestXmin/removable cutoff being too old. That can
happen when VACUUM needs to freeze according to the traditional
age-based settings, and yet the OldestXmin/removable cutoff gets held
back (by a leaked replication slot or whatever).
(Actually, VACUUM FREEZE freeze will freeze only a subset of the
tuples from some heap pages far more often. VACUUM FREEZE seems like a
bad design to me, though -- it uses the most aggressive possible XID
cutoff for freezing when it should probably hold off on freezing those
individual pages where we determine that it makes little sense. We
need to focus more on physical pages and their costs, and less on XID
cutoffs.)
> Is this test meant to guard against unnecessary freezing or to avoid
> freezing when the cost is too high? That is, are we trying to
> determine how likely it is that the page has been recently modified
> and avoid eager freezing when it would be pointless (because the page
> will soon be modified again)?
Sort of. This cost of freezing over time is weirdly nonlinear, so it's
hard to give a simple answer.
The justification for the FPI trigger optimization is that FPIs are
overwhelmingly the cost that really matters when it comes to freezing
(and vacuuming in general) -- so we might as well make the best out of
a bad situation when pruning happens to get an FPI. There can easily
be a 10x or more cost difference (measured in total WAL volume)
between freezing without an FPI and freezing with an FPI.
> Or are we trying to determine how likely
> the freeze record is to emit an FPI and avoid eager freezing when it
> isn't worth the cost?
No, that's not something that we're doing right now (we just talked
about doing something like that). In 16 VACUUM just "makes the best
out of a bad situation" when an FPI was already required during
pruning. We have already "paid for the privilege" of writing some WAL
for the page at that point, so it's reasonable to not squander a
window of opportunity to avoid future FPIs in future VACUUM
operations, by freezing early.
We're "taking a chance" on being able to get freezing out of the way
early when an FPI triggers freezing. It's not guaranteed to work out
in each individual case, of course, but even if we assume it's fairly
unlikely to work out (which is very pessimistic) it's still very
likely a good deal.
This strategy (the 16 strategy of freezing eagerly because we already
got an FPI) seems safer than a strategy involving freezing eagerly
because we won't get an FPI as a result. If for no other reason than
this: with the approach in 16 we already know for sure that we'll have
written an FPI anyway. It's hard to imagine somebody being okay with
the FPIs, but not being okay with the other extra WAL.
> But, the final rationale is still not clear to me. Could we add a
> comment above the if condition specifying both:
> a) what the test is a proxy for
> b) the intended outcome (when do we expect to eager freeze)
> And perhaps we could even describe a scenario where this heuristic is effective?
There are lots of scenarios where it'll be effective. I agree that
there is a need to document this stuff a lot better. I have a pending
doc patch that overhauls the user-facing docs in this area.
My latest draft is here:
https://postgr.es/m/CAH2-Wz=UUJz+MMb1AxFzz-HDA=1t1Fx_KmrdOVoPZXkpA-TFwg@mail.gmail.com
https://www.postgresql.org/message-id/attachment/146830/routine-vacuuming.html
I've been meaning to get back to that, but other commitments have kept
me from it. I'd welcome your involvement with that effort.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
@ 2023-07-28 19:27 ` Melanie Plageman <[email protected]>
2023-07-28 19:45 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
0 siblings, 2 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-07-28 19:27 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
On Fri, Jul 28, 2023 at 3:00 PM Peter Geoghegan <[email protected]> wrote:
>
> On Fri, Jul 28, 2023 at 11:13 AM Melanie Plageman
> <[email protected]> wrote:
> > if (pagefrz.freeze_required || tuples_frozen == 0 ||
> > (prunestate->all_visible && prunestate->all_frozen &&
> > fpi_before != pgWalUsage.wal_fpi))
> > {
> >
> > I'm trying to understand the condition fpi_before !=
> > pgWalUsage.wal_fpi -- don't eager freeze if pruning emitted an FPI.
>
> You mean "prunestate->all_visible && prunestate->all_frozen", which is
> a condition of applying FPI-based eager freezing, but not traditional
> lazy freezing?
Ah no, a very key word in my sentence was off:
I meant "don't eager freeze *unless* pruning emitted an FPI"
I was asking about the FPI-trigger optimization specifically.
I'm clear on the all_frozen/all_visible criteria.
> > Is this test meant to guard against unnecessary freezing or to avoid
> > freezing when the cost is too high? That is, are we trying to
> > determine how likely it is that the page has been recently modified
> > and avoid eager freezing when it would be pointless (because the page
> > will soon be modified again)?
>
> Sort of. This cost of freezing over time is weirdly nonlinear, so it's
> hard to give a simple answer.
>
> The justification for the FPI trigger optimization is that FPIs are
> overwhelmingly the cost that really matters when it comes to freezing
> (and vacuuming in general) -- so we might as well make the best out of
> a bad situation when pruning happens to get an FPI. There can easily
> be a 10x or more cost difference (measured in total WAL volume)
> between freezing without an FPI and freezing with an FPI.
...
> In 16 VACUUM just "makes the best
> out of a bad situation" when an FPI was already required during
> pruning. We have already "paid for the privilege" of writing some WAL
> for the page at that point, so it's reasonable to not squander a
> window of opportunity to avoid future FPIs in future VACUUM
> operations, by freezing early.
>
> We're "taking a chance" on being able to get freezing out of the way
> early when an FPI triggers freezing. It's not guaranteed to work out
> in each individual case, of course, but even if we assume it's fairly
> unlikely to work out (which is very pessimistic) it's still very
> likely a good deal.
>
> This strategy (the 16 strategy of freezing eagerly because we already
> got an FPI) seems safer than a strategy involving freezing eagerly
> because we won't get an FPI as a result. If for no other reason than
> this: with the approach in 16 we already know for sure that we'll have
> written an FPI anyway. It's hard to imagine somebody being okay with
> the FPIs, but not being okay with the other extra WAL.
I see. I don't have an opinion on the "best of a bad situation"
argument. Though, I think it is worth amending the comment in the code
to include this explanation.
But, ISTM that there should also be some independent heuristic to
determine whether or not it makes sense to freeze the page. That could
be related to whether or not it will be cheap to do so (in which case
we can check if we will have to emit an FPI as part of the freeze
record) or it could be related to whether or not the freezing is
likely to be pointless (we are likely to update the page again soon).
It sounds like it was discussed before, but I'd be interested in
revisiting it and happy to test out various ideas.
- Melanie
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-07-28 19:45 ` Peter Geoghegan <[email protected]>
1 sibling, 0 replies; 36+ messages in thread
From: Peter Geoghegan @ 2023-07-28 19:45 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
On Fri, Jul 28, 2023 at 3:27 PM Melanie Plageman
<[email protected]> wrote:
> I see. I don't have an opinion on the "best of a bad situation"
> argument. Though, I think it is worth amending the comment in the code
> to include this explanation.
I think that the user-facing docs should be completely overhauled to
make that clear, and reasonably accessible. It's hard to talk about in
code comments because it's something that can only be effective over
time, across multiple VACUUM operations.
> But, ISTM that there should also be some independent heuristic to
> determine whether or not it makes sense to freeze the page. That could
> be related to whether or not it will be cheap to do so (in which case
> we can check if we will have to emit an FPI as part of the freeze
> record) or it could be related to whether or not the freezing is
> likely to be pointless (we are likely to update the page again soon).
It sounds like you're interested in adding additional criteria to
trigger page-level freezing. That's something that the current
structure anticipates.
To give a slightly contrived example: it would be very easy to add
another condition, where (say) we call random() to determine whether
or not to freeze the page. You'd very likely want to gate this new
trigger criteria in the same way as the FPI criteria (i.e. only do it
when "prunestate->all_visible && prunestate->all_frozen" hold). We've
decoupled the decision to freeze from what it means to execute
freezing itself.
Having additional trigger criteria makes a lot of sense. I'm sure that
it makes sense to add at least one more, and it seems possible that
adding several more makes sense. Obviously, that will have to be new
work targeting 17, though. I made a decision to stop working on
VACUUM, though, so I'm afraid I won't be able to offer much help with
any of this. (Happy to give more background information, though.)
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-08-28 14:00 ` Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 19:09 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
1 sibling, 2 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-08-28 14:00 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
On Fri, Jul 28, 2023 at 3:27 PM Melanie Plageman
<[email protected]> wrote:
> On Fri, Jul 28, 2023 at 3:00 PM Peter Geoghegan <[email protected]> wrote:
> > > Is this test meant to guard against unnecessary freezing or to avoid
> > > freezing when the cost is too high? That is, are we trying to
> > > determine how likely it is that the page has been recently modified
> > > and avoid eager freezing when it would be pointless (because the page
> > > will soon be modified again)?
> >
> > Sort of. This cost of freezing over time is weirdly nonlinear, so it's
> > hard to give a simple answer.
> >
> > The justification for the FPI trigger optimization is that FPIs are
> > overwhelmingly the cost that really matters when it comes to freezing
> > (and vacuuming in general) -- so we might as well make the best out of
> > a bad situation when pruning happens to get an FPI. There can easily
> > be a 10x or more cost difference (measured in total WAL volume)
> > between freezing without an FPI and freezing with an FPI.
> ...
> > In 16 VACUUM just "makes the best
> > out of a bad situation" when an FPI was already required during
> > pruning. We have already "paid for the privilege" of writing some WAL
> > for the page at that point, so it's reasonable to not squander a
> > window of opportunity to avoid future FPIs in future VACUUM
> > operations, by freezing early.
> >
> > We're "taking a chance" on being able to get freezing out of the way
> > early when an FPI triggers freezing. It's not guaranteed to work out
> > in each individual case, of course, but even if we assume it's fairly
> > unlikely to work out (which is very pessimistic) it's still very
> > likely a good deal.
> >
> > This strategy (the 16 strategy of freezing eagerly because we already
> > got an FPI) seems safer than a strategy involving freezing eagerly
> > because we won't get an FPI as a result. If for no other reason than
> > this: with the approach in 16 we already know for sure that we'll have
> > written an FPI anyway. It's hard to imagine somebody being okay with
> > the FPIs, but not being okay with the other extra WAL.
>
> I see. I don't have an opinion on the "best of a bad situation"
> argument. Though, I think it is worth amending the comment in the code
> to include this explanation.
>
> But, ISTM that there should also be some independent heuristic to
> determine whether or not it makes sense to freeze the page. That could
> be related to whether or not it will be cheap to do so (in which case
> we can check if we will have to emit an FPI as part of the freeze
> record) or it could be related to whether or not the freezing is
> likely to be pointless (we are likely to update the page again soon).
>
> It sounds like it was discussed before, but I'd be interested in
> revisiting it and happy to test out various ideas.
Hi, in service of "testing various ideas", I've benchmarked the
heuristics proposed in this thread, as well a few others that Andres and
I considered, for determining whether or not to opportunistically freeze
a page during vacuum. Note that this heuristic would be in addition to
the existing criterion that we only opportunistically freeze pages that
can be subsequently set all frozen in the visibility map.
I believe that there are two goals that should dictate whether or not we
should perform opportunistic freezing:
1. Is it cheap? For example, if the buffer is already dirty, then no
write amplification occurs, since it must be written out anyway.
Freezing is also less expensive if we can do it without emitting an
FPI.
2. Will it be effective; that is, will it stay frozen?
Opportunistically freezing a page that will immediately be modified is
a waste.
The current heuristic on master meets neither of these goals: it freezes
a page if pruning emitted an FPI for it. This doesn't evaluate whether
or not freezing itself would be cheap, but rather attempts to hide
freezing behind an expensive operation. Furthermore, it often fails to
freeze cold data and may indiscriminately freeze hot data.
For the second goal, I've relied on past data to predict future
behavior, so I tried several criteria to estimate the likelihood that a
page will not be imminently modified. What was most effective was
Andres' suggestion of comparing the page LSN to the insert LSN at the
end of the last vacuum of that table; this approximates whether the page
has been recently modified, which is a decent proxy for whether it'll be
modified in the future. To do this, we need to save that insert LSN
somewhere. In the attached WIP patch, I saved it in the table stats, for
now -- knowing that those are not crash-safe.
Other discarded heuristic ideas included comparing the next transaction
ID at the end of the vacuum of a relation to the visibility cutoff xid
in the page -- but that wasn't effective for freezing data from bulk
loads.
The algorithms I evaluated all attempt to satisfy goal (1) by freezing
only if the buffer is already dirty and also by considering whether or
not an FPI would be emitted. Those that attempt to satisfy goal (2) do
so using the LSN comparison with varying thresholds. I ended up testing
master and the following five alternatives:
1. Dirty buffer, no FPI required
2. Dirty buffer, no FPI required OR page LSN is older than 10% of the
LSNs since the last vacuum of the table.
3. Dirty buffer, no FPI required AND page LSN is older than 10% of the
LSNs since the last vacuum of the table.
4. Dirty buffer, no FPI required OR page LSN is older than 33% of the
LSNs since the last vacuum of the table.
5. Dirty buffer, no FPI required AND page LSN is older than 33% of the
LSNs since the last vacuum of the table.
I ran several benchmarks and compared these based on two metrics:
1. Percentage of pages frozen at the end of the benchmark. For
workloads with a working set much smaller than their data set, this
metric should be high. Conversely, for workloads with a working set
that is more-or-less their entire data set, this metric should be low.
2. Page freezes per page frozen at the end of the benchmark. This
should be as low as possible. Since each benchmark starts with zero
frozen pages, a metric of 1 indicates that each frozen page was frozen
only once.
Some of the benchmarks were run for a fixed number of transactions.
Those that do not specify a number of transactions were run for 45
minutes. I collected metrics from OS utilities and Postgres statistics
to examine throughput, FPIs emitted, and many other performance metrics
over the course of the benchmark. Below, I've summarized the results and
pointed out any notable negative performance impacts.
Overall, the two algorithms that seem to strike the best balance are (4)
and (5).
The OR condition in algorithm (4), as you might expect, results in
freezing much more of the cold data in workloads with a smaller working
set than data set. It tends to cause more FPIs to be emitted, since the
age criteria alone can trigger freezing -- even when the freeze record
would contain an FPI. Though, these FPIs may happen when the cold data
is eventually frozen in a wraparound vacuum. That is, the absence of
FPIs tracks the absence of frozen data quite closely.
For a workload in which only 10% of the data is being updated, master
often freezes the wrong data and still emits FPIs. For this kind of
workload, algorithms 1 and 2 also did not perform well and emitted more
FPIs than the other algorithms. In my examples, I found that the 10%
cutoff froze data too aggressively -- freezing data that was modified
soon after.
The workloads I benchmarked were as follows:
A. gaussian tpcb-like + select-only:
pgbench scale 600 (DB < SB) with indexes on updated columns
WL 1: 2 clients tpcb-like pgbench with gaussian access distribution
WL 2: 16 clients select-only pgbench
freezing more is better
B. tpcb-like
pgbench scale 600, 16 clients
freezing less is better
C. shifting hot set, autovacuum off, vacuum at end
1 client inserting a single row and updating an indexed column of that
row. 2 million transactions.
freezing more is better
D. shifting hot set, delete old data
10 MB table with index on updated column
WL 1: 1 client inserting one row, updating that row
WL 2: 1 client, rate limited to 0.02 TPS, delete old data keeping
table at 5000 rows
freezing less is better
E. shifting hot set, delete new data, access new data
WL 1: 1 client cycling through 2 inserts of a single row each,
updating an indexed column of the most recently inserted row, and then
deleting that row
WL 2: rate-limited to 0.2 TPS, selecting data from the last 300
seconds
freezing more is better
F. many COPYs, autovacuum on
1 client, copying a total of 50 GB of data, autovacuum will run ~2x,
~2 checkpoints
freezing more is better
G. several COPYs, autovacuum off, vacuum at end
1 client, copying a total of 10 GB of data, no checkpoints
freezing more is better
H. append only table, autovacuum off, vacuum at end
1 client, inserting a single row at a time for 3million transactions
freezing more is better
I. work queue
1 client, inserting a row, sleep for half a second, delete that row
for 5000 transactions.
freezing less is better
Note that the page freezes/page frozen metric can be misleading when the
overall number of pages freezes is low. This is the case for master. It
did few page freezes but those tended to be pages that were modified
again soon after.
Page Freezes/Page Frozen (less is better)
| | Master | (1) | (2) | (3) | (4) | (5) |
|---+--------+---------+---------+---------+---------+---------|
| A | 28.50 | 3.89 | 1.08 | 1.15 | 1.10 | 1.10 |
| B | 1.00 | 1.06 | 1.65 | 1.03 | 1.59 | 1.00 |
| C | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| D | 2.00 | 5199.15 | 5276.85 | 4830.45 | 5234.55 | 2193.55 |
| E | 7.90 | 3.21 | 2.73 | 2.70 | 2.69 | 2.43 |
| F | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| G | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| H | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| I | N/A | 42.00 | 42.00 | N/A | 41.00 | N/A |
% Frozen at end of run
| | Master | (1) | (2) | (3) | (4) | (5) |
|---+--------+-----+-----+-----+------+-----+
| A | 0 | 1 | 99 | 0 | 81 | 0 |
| B | 71 | 96 | 99 | 3 | 98 | 2 |
| C | 0 | 9 | 100 | 6 | 92 | 5 |
| D | 0 | 1 | 1 | 1 | 1 | 1 |
| E | 0 | 63 | 100 | 68 | 100 | 67 |
| F | 0 | 5 | 14 | 6 | 14 | 5 |
| G | 0 | 100 | 100 | 92 | 100 | 67 |
| H | 0 | 11 | 100 | 9 | 86 | 5 |
| I | 0 | 100 | 100 | 0 | 100 | 0 |
I can provide exact pgbench commands, configurations, or detailed
results upon request.
- Melanie
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-08-28 16:26 ` Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
1 sibling, 2 replies; 36+ messages in thread
From: Robert Haas @ 2023-08-28 16:26 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 10:00 AM Melanie Plageman
<[email protected]> wrote:
> For the second goal, I've relied on past data to predict future
> behavior, so I tried several criteria to estimate the likelihood that a
> page will not be imminently modified. What was most effective was
> Andres' suggestion of comparing the page LSN to the insert LSN at the
> end of the last vacuum of that table; this approximates whether the page
> has been recently modified, which is a decent proxy for whether it'll be
> modified in the future. To do this, we need to save that insert LSN
> somewhere. In the attached WIP patch, I saved it in the table stats, for
> now -- knowing that those are not crash-safe.
I wonder what the real plan here is for where to store this. It's not
obvious that we need this to be crash-safe; it's after all only for
use by a heuristic, and there's no actual breakage if the heuristic
goes wrong. At the same time, it doesn't exactly feel like a
statistic.
Then there's the question of whether it's the right metric. My first
reaction is to think that it sounds pretty good. One thing I really
like about it is that if the table is being vacuumed frequently, then
we freeze less aggressively, and if the table is being vacuumed
infrequently, then we freeze more aggressively. That seems like a very
desirable property. It also seems broadly good that this metric
doesn't really care about reads. If there are a lot of reads on the
system, or no reads at all, it doesn't really change the chances that
a certain page is going to be written again soon, and since reads
don't change the insert LSN, here again it seems to do the right
thing. I'm a little less clear about whether it's good that it doesn't
really depend on wall-clock time. Certainly, that's desirable from the
point of view of not wanting to have to measure wall-clock time in
places where we otherwise wouldn't have to, which tends to end up
being expensive. However, if I were making all of my freezing
decisions manually, I might be more freeze-positive on a low-velocity
system where writes are more stretched out across time than on a
high-velocity system where we're blasting through the LSN space at a
higher rate. But maybe that's not a very important consideration, and
I don't know what we'd do about it anyway.
> Page Freezes/Page Frozen (less is better)
>
> | | Master | (1) | (2) | (3) | (4) | (5) |
> |---+--------+---------+---------+---------+---------+---------|
> | A | 28.50 | 3.89 | 1.08 | 1.15 | 1.10 | 1.10 |
> | B | 1.00 | 1.06 | 1.65 | 1.03 | 1.59 | 1.00 |
> | C | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> | D | 2.00 | 5199.15 | 5276.85 | 4830.45 | 5234.55 | 2193.55 |
> | E | 7.90 | 3.21 | 2.73 | 2.70 | 2.69 | 2.43 |
> | F | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> | G | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> | H | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> | I | N/A | 42.00 | 42.00 | N/A | 41.00 | N/A |
Hmm. I would say that the interesting rows here are A, D, and I, with
rows C and E deserving honorable mention. In row A, master is bad. In
row D, your algorithms are all bad, really bad. I don't quite
understand how it can be that bad, actually. Row I looks bad for
algorithms 1, 2, and 4: they freeze pages because it looks cheap, but
the work doesn't really pay off.
> % Frozen at end of run
>
> | | Master | (1) | (2) | (3) | (4) | (5) |
> |---+--------+-----+-----+-----+------+-----+
> | A | 0 | 1 | 99 | 0 | 81 | 0 |
> | B | 71 | 96 | 99 | 3 | 98 | 2 |
> | C | 0 | 9 | 100 | 6 | 92 | 5 |
> | D | 0 | 1 | 1 | 1 | 1 | 1 |
> | E | 0 | 63 | 100 | 68 | 100 | 67 |
> | F | 0 | 5 | 14 | 6 | 14 | 5 |
> | G | 0 | 100 | 100 | 92 | 100 | 67 |
> | H | 0 | 11 | 100 | 9 | 86 | 5 |
> | I | 0 | 100 | 100 | 0 | 100 | 0 |
So all of the algorithms here, but especially 1, 2, and 4, freeze a
lot more often than master.
If I understand correctly, we'd like to see small numbers for B, D,
and I, and large numbers for the other workloads. None of the
algorithms seem to achieve that. (3) and (5) seem like they always
behave as well or better than master, but they produce small numbers
for A, C, F, and H. (1), (2), and (4) regress B and I relative to
master but do better than (3) and (5) on A, C, and the latter two also
on E.
B is such an important benchmarking workload that I'd be loathe to
regress it, so if I had to pick on the basis of this data, my vote
would be (3) or (5), provided whatever is happening with (D) in the
previous metric is not as bad as it looks. What's your reason for
preferring (4) and (5) over (2) and (3)? I'm not clear that these
numbers give us much of an idea whether 10% or 33% or something else
is better in general.
To be honest, having now spent more time looking at the benchmark
results, I feel slightly less good about using the LSN as a metric
here. These results, to me, clearly suggest that some recency metric
is needed. But they don't seem to make a compelling case for this
particular one. Neither do they make a case that this is the wrong
one. They just don't seem that revealing either way.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-08-28 20:30 ` Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-23 19:53 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
1 sibling, 2 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-08-28 20:30 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 12:26 PM Robert Haas <[email protected]> wrote:
>
> On Mon, Aug 28, 2023 at 10:00 AM Melanie Plageman
> <[email protected]> wrote:
> Then there's the question of whether it's the right metric. My first
> reaction is to think that it sounds pretty good. One thing I really
> like about it is that if the table is being vacuumed frequently, then
> we freeze less aggressively, and if the table is being vacuumed
> infrequently, then we freeze more aggressively. That seems like a very
> desirable property. It also seems broadly good that this metric
> doesn't really care about reads. If there are a lot of reads on the
> system, or no reads at all, it doesn't really change the chances that
> a certain page is going to be written again soon, and since reads
> don't change the insert LSN, here again it seems to do the right
> thing. I'm a little less clear about whether it's good that it doesn't
> really depend on wall-clock time. Certainly, that's desirable from the
> point of view of not wanting to have to measure wall-clock time in
> places where we otherwise wouldn't have to, which tends to end up
> being expensive. However, if I were making all of my freezing
> decisions manually, I might be more freeze-positive on a low-velocity
> system where writes are more stretched out across time than on a
> high-velocity system where we're blasting through the LSN space at a
> higher rate. But maybe that's not a very important consideration, and
> I don't know what we'd do about it anyway.
By low-velocity, do you mean lower overall TPS? In that case, wouldn't you be
less likely to run into xid wraparound and thus need less aggressive
opportunistic freezing?
> > Page Freezes/Page Frozen (less is better)
> >
> > | | Master | (1) | (2) | (3) | (4) | (5) |
> > |---+--------+---------+---------+---------+---------+---------|
> > | A | 28.50 | 3.89 | 1.08 | 1.15 | 1.10 | 1.10 |
> > | B | 1.00 | 1.06 | 1.65 | 1.03 | 1.59 | 1.00 |
> > | C | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> > | D | 2.00 | 5199.15 | 5276.85 | 4830.45 | 5234.55 | 2193.55 |
> > | E | 7.90 | 3.21 | 2.73 | 2.70 | 2.69 | 2.43 |
> > | F | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> > | G | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> > | H | N/A | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
> > | I | N/A | 42.00 | 42.00 | N/A | 41.00 | N/A |
>
> Hmm. I would say that the interesting rows here are A, D, and I, with
> rows C and E deserving honorable mention. In row A, master is bad.
So, this is where the caveat about absolute number of page freezes
matters. In algorithm A, master only did 57 page freezes (spread across
the various pgbench tables). At the end of the run, 2 pages were still
frozen.
> In row D, your algorithms are all bad, really bad. I don't quite
> understand how it can be that bad, actually.
So, I realize now that this test was poorly designed. I meant it to be a
worst case scenario, but I think one critical part was wrong. In this
example one client is going at full speed inserting a row and then
updating it. Then another rate-limited client is deleting old data
periodically to keep the table at a constant size. I meant to bulk load
the table with enough data that the delete job would have data to delete
from the start. With the default autovacuum settings, over the course of
45 minutes, I usually saw around 40 autovacuums of the table. Due to the
rate limiting, the first autovacuum of the table ends up freezing many
pages that are deleted soon after. Thus the total number of page freezes
is very high.
I will redo benchmarking of workload D and start the table with the
number of rows which the DELETE job seeks to maintain. My back of the
envelope math says that this will mean ratios closer to a dozen (and not
5000).
Also, I had doubled checkpoint timeout, which likely led master to
freeze so few pages (2 total freezes, neither of which were still frozen
at the end of the run). This is an example where master's overall low
number of page freezes makes it difficult to compare to the alternatives
using a ratio.
I didn't initially question the numbers because it seems like freezing
data and then deleting it right after would naturally be one of the
worst cases for opportunistic freezing, but certainly not this bad.
> Row I looks bad for algorithms 1, 2, and 4: they freeze pages because
> it looks cheap, but the work doesn't really pay off.
Yes, the work queue example looks like it is hard to handle.
> > % Frozen at end of run
> >
> > | | Master | (1) | (2) | (3) | (4) | (5) |
> > |---+--------+-----+-----+-----+------+-----+
> > | A | 0 | 1 | 99 | 0 | 81 | 0 |
> > | B | 71 | 96 | 99 | 3 | 98 | 2 |
> > | C | 0 | 9 | 100 | 6 | 92 | 5 |
> > | D | 0 | 1 | 1 | 1 | 1 | 1 |
> > | E | 0 | 63 | 100 | 68 | 100 | 67 |
> > | F | 0 | 5 | 14 | 6 | 14 | 5 |
> > | G | 0 | 100 | 100 | 92 | 100 | 67 |
> > | H | 0 | 11 | 100 | 9 | 86 | 5 |
> > | I | 0 | 100 | 100 | 0 | 100 | 0 |
>
> So all of the algorithms here, but especially 1, 2, and 4, freeze a
> lot more often than master.
>
> If I understand correctly, we'd like to see small numbers for B, D,
> and I, and large numbers for the other workloads. None of the
> algorithms seem to achieve that. (3) and (5) seem like they always
> behave as well or better than master, but they produce small numbers
> for A, C, F, and H. (1), (2), and (4) regress B and I relative to
> master but do better than (3) and (5) on A, C, and the latter two also
> on E.
>
> B is such an important benchmarking workload that I'd be loathe to
> regress it, so if I had to pick on the basis of this data, my vote
> would be (3) or (5), provided whatever is happening with (D) in the
> previous metric is not as bad as it looks. What's your reason for
> preferring (4) and (5) over (2) and (3)? I'm not clear that these
> numbers give us much of an idea whether 10% or 33% or something else
> is better in general.
(1) seems bad to me because it doesn't consider whether or not freezing
will be useful -- only if it will be cheap. It froze very little of the
cold data in a workload where a small percentage of it was being
modified (especially workloads A, C, H). And it froze a lot of data in
workloads where it was being uniformly modified (workload B).
I suggested (4) and (5) because I think the "older than 33%" threshold
is better than the "older than 10%" threshold. I chose both because I am
still unclear on our values. Are we willing to freeze more aggressively
at the expense of emitting more FPIs? As long as it doesn't affect
throughput? For pretty much all of these workloads, the algorithms which
froze based on page modification recency OR FPI required emitted many
more FPIs than those which froze based only on page modification
recency.
I've attached the WIP patch that I forgot in my previous email.
I'll rerun workload D in a more reasonable way and be back with results.
- Melanie
Attachments:
[text/x-patch] WIP-opp_freeze_cold_data.patch (9.5K, ../../CAAKRu_YzowY80dsktvykUCEJBE0Mco7SuBnvGED2_XyuC_3P=g@mail.gmail.com/2-WIP-opp_freeze_cold_data.patch)
download | inline diff:
From 51786ef91cba43bbb4985dc8ab86f2cdcbf54369 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Sat, 26 Aug 2023 20:16:00 -0400
Subject: [PATCH v1 1/3] Opportunistically freeze cold data when it is cheap
Instead of relying on whether or not pruning emitted an FPI while
vacuuming to determine whether or not to opportunistically freeze,
determine whether or not doing so will be cheap and useful. Determine if
it is cheap by checking if the buffer is already dirty and if freezing
the page will require emitting an FPI. Then, predict if the page will
stay frozen by comparing the page LSN to the insert LSN at the end of
the last vacuum of the relation. This gives us some idea of whether or
not the page has been recently modified, and, hopefully insight into
whether or not it will soon be modified again.
---
src/backend/access/heap/vacuumlazy.c | 26 ++++++++++++++----
src/backend/storage/buffer/bufmgr.c | 19 +++++++++++++
src/backend/storage/buffer/localbuf.c | 20 ++++++++++++++
src/backend/utils/activity/pgstat_relation.c | 29 +++++++++++++++++++-
src/include/pgstat.h | 8 +++++-
src/include/storage/buf_internals.h | 2 ++
src/include/storage/bufmgr.h | 2 ++
7 files changed, 98 insertions(+), 8 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 6a41ee635d..e4d5b402c2 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -210,6 +210,7 @@ typedef struct LVRelState
int64 live_tuples; /* # live tuples remaining */
int64 recently_dead_tuples; /* # dead, but not yet removable */
int64 missed_dead_tuples; /* # removable, but not removed */
+ XLogRecPtr last_vac_lsn;
} LVRelState;
/*
@@ -364,6 +365,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
errcallback.previous = error_context_stack;
error_context_stack = &errcallback;
+ vacrel->last_vac_lsn = pgstat_get_last_vac_lsn(RelationGetRelid(rel),
+ rel->rd_rel->relisshared);
+
/* Set up high level stuff about rel and its indexes */
vacrel->rel = rel;
vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
@@ -597,7 +601,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
rel->rd_rel->relisshared,
Max(vacrel->new_live_tuples, 0),
vacrel->recently_dead_tuples +
- vacrel->missed_dead_tuples);
+ vacrel->missed_dead_tuples, ProcLastRecPtr);
+
pgstat_progress_end_command();
if (instrument)
@@ -1511,6 +1516,7 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno,
return false;
}
+#define FREEZE_CUTOFF_DIVISOR 3
/*
* lazy_scan_prune() -- lazy_scan_heap() pruning and freezing.
*
@@ -1551,9 +1557,11 @@ lazy_scan_prune(LVRelState *vacrel,
recently_dead_tuples;
int nnewlpdead;
HeapPageFreeze pagefrz;
- int64 fpi_before = pgWalUsage.wal_fpi;
OffsetNumber deadoffsets[MaxHeapTuplesPerPage];
HeapTupleFreeze frozen[MaxHeapTuplesPerPage];
+ XLogRecPtr current_lsn;
+ XLogRecPtr lsns_since_last_vacuum;
+ XLogRecPtr freeze_lsn_cutoff;
Assert(BufferGetBlockNumber(buf) == blkno);
@@ -1795,13 +1803,19 @@ retry:
/*
* Freeze the page when heap_prepare_freeze_tuple indicates that at least
- * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also
- * freeze when pruning generated an FPI, if doing so means that we set the
- * page all-frozen afterwards (might not happen until final heap pass).
+ * one XID/MXID from before FreezeLimit/MultiXactCutoff is present. Also,
+ * if freezing would allow us to set the whole page all-frozen, then freeze
+ * if the buffer is already dirty, freezing won't emit an FPI and the page
+ * hasn't been modified recently.
*/
+ current_lsn = GetXLogInsertRecPtr();
+ lsns_since_last_vacuum = current_lsn - vacrel->last_vac_lsn;
+ freeze_lsn_cutoff = current_lsn - (lsns_since_last_vacuum / FREEZE_CUTOFF_DIVISOR);
+
if (pagefrz.freeze_required || tuples_frozen == 0 ||
(prunestate->all_visible && prunestate->all_frozen &&
- fpi_before != pgWalUsage.wal_fpi))
+ (BufferIsProbablyDirty(buf) && !XLogCheckBufferNeedsBackup(buf) &&
+ PageGetLSN(page) < freeze_lsn_cutoff)))
{
/*
* We're freezing the page. Our final NewRelfrozenXid doesn't need to
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 3bd82dbfca..9110b686ae 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -2098,6 +2098,25 @@ ExtendBufferedRelShared(BufferManagerRelation bmr,
return first_block;
}
+bool
+BufferIsProbablyDirty(Buffer buffer)
+{
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ if (!BufferIsValid(buffer))
+ elog(ERROR, "bad buffer ID: %d", buffer);
+
+ if (BufferIsLocal(buffer))
+ return LocalBufferIsProbablyDirty(buffer);
+
+ bufHdr = GetBufferDescriptor(buffer - 1);
+
+ buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+ return buf_state & BM_DIRTY;
+}
+
/*
* MarkBufferDirty
*
diff --git a/src/backend/storage/buffer/localbuf.c b/src/backend/storage/buffer/localbuf.c
index 1735ec7141..f2d4f91336 100644
--- a/src/backend/storage/buffer/localbuf.c
+++ b/src/backend/storage/buffer/localbuf.c
@@ -436,6 +436,26 @@ ExtendBufferedRelLocal(BufferManagerRelation bmr,
return first_block;
}
+bool
+LocalBufferIsProbablyDirty(Buffer buffer)
+{
+ int bufid;
+ BufferDesc *bufHdr;
+ uint32 buf_state;
+
+ Assert(BufferIsLocal(buffer));
+
+ bufid = -buffer - 1;
+
+ Assert(LocalRefCount[bufid] > 0);
+
+ bufHdr = GetLocalBufferDescriptor(bufid);
+
+ buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+ return buf_state & BM_DIRTY;
+}
+
/*
* MarkLocalBufferDirty -
* mark a local buffer dirty
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 9876e0c1e8..cac0a4bc49 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -205,12 +205,37 @@ pgstat_drop_relation(Relation rel)
}
}
+
+XLogRecPtr
+pgstat_get_last_vac_lsn(Oid tableoid, bool shared)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ XLogRecPtr result;
+ Oid dboid = (shared ? InvalidOid : MyDatabaseId);
+
+ if (!pgstat_track_counts)
+ return InvalidXLogRecPtr;
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ dboid, tableoid, false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ result = tabentry->last_vac_lsn;
+ pgstat_unlock_entry(entry_ref);
+ return result;
+}
+
/*
* Report that the table was just vacuumed and flush IO statistics.
*/
void
pgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples)
+ PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ XLogRecPtr last_vac_lsn)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Relation *shtabentry;
@@ -257,6 +282,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->vacuum_count++;
}
+ tabentry->last_vac_lsn = last_vac_lsn;
+
pgstat_unlock_entry(entry_ref);
/*
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 57a2c0866a..9fe607c961 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -11,6 +11,7 @@
#ifndef PGSTAT_H
#define PGSTAT_H
+#include "access/xlogdefs.h"
#include "datatype/timestamp.h"
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
@@ -424,6 +425,7 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter analyze_count;
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+ XLogRecPtr last_vac_lsn;
} PgStat_StatTabEntry;
typedef struct PgStat_WalStats
@@ -589,7 +591,11 @@ extern void pgstat_assoc_relation(Relation rel);
extern void pgstat_unlink_relation(Relation rel);
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples);
+ PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ XLogRecPtr last_vac_lsn);
+
+extern XLogRecPtr pgstat_get_last_vac_lsn(Oid tableoid, bool shared);
+
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter);
diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h
index bc79a329a1..aaa7882099 100644
--- a/src/include/storage/buf_internals.h
+++ b/src/include/storage/buf_internals.h
@@ -430,6 +430,8 @@ extern BlockNumber ExtendBufferedRelLocal(BufferManagerRelation bmr,
BlockNumber extend_upto,
Buffer *buffers,
uint32 *extended_by);
+
+extern bool LocalBufferIsProbablyDirty(Buffer buffer);
extern void MarkLocalBufferDirty(Buffer buffer);
extern void DropRelationLocalBuffers(RelFileLocator rlocator,
ForkNumber forkNum,
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index b379c76e27..7458d5fd83 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -180,6 +180,8 @@ extern Buffer ReadBufferWithoutRelcache(RelFileLocator rlocator,
extern void ReleaseBuffer(Buffer buffer);
extern void UnlockReleaseBuffer(Buffer buffer);
extern void MarkBufferDirty(Buffer buffer);
+
+extern bool BufferIsProbablyDirty(Buffer buffer);
extern void IncrBufferRefCount(Buffer buffer);
extern void CheckBufferIsPinnedOnce(Buffer buffer);
extern Buffer ReleaseAndReadBuffer(Buffer buffer, Relation relation,
--
2.37.2
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-08-29 14:22 ` Robert Haas <[email protected]>
2023-09-01 19:34 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-08-29 14:22 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 4:30 PM Melanie Plageman
<[email protected]> wrote:
> By low-velocity, do you mean lower overall TPS? In that case, wouldn't you be
> less likely to run into xid wraparound and thus need less aggressive
> opportunistic freezing?
Yes. But it also means that we've got slack capacity to do extra work
now without really hurting anything. If we can leverage that capacity
to reduce the pain of future bulk operations, that seems good. When
resources are tight, doing speculative work immediately becomes less
appealing. It's pretty hard to take such things into account, though.
I was just mentioning it.
> So, this is where the caveat about absolute number of page freezes
> matters. In algorithm A, master only did 57 page freezes (spread across
> the various pgbench tables). At the end of the run, 2 pages were still
> frozen.
I'm increasingly feeling that it's hard to make sense of the ratio.
Maybe show the number of pages frozen, the number that are frozen at
the end, and the number of pages in the database at the end of the run
as three separate values.
> (1) seems bad to me because it doesn't consider whether or not freezing
> will be useful -- only if it will be cheap. It froze very little of the
> cold data in a workload where a small percentage of it was being
> modified (especially workloads A, C, H). And it froze a lot of data in
> workloads where it was being uniformly modified (workload B).
Sure, but if the cost of being wrong is low, you can be wrong a lot
and still be pretty happy. It's unclear to me to what extent we should
gamble on making only inexpensive mistakes and to what extent we
should gamble on making only infrequent mistakes, but they're both
valid strategies.
> I suggested (4) and (5) because I think the "older than 33%" threshold
> is better than the "older than 10%" threshold. I chose both because I am
> still unclear on our values. Are we willing to freeze more aggressively
> at the expense of emitting more FPIs? As long as it doesn't affect
> throughput? For pretty much all of these workloads, the algorithms which
> froze based on page modification recency OR FPI required emitted many
> more FPIs than those which froze based only on page modification
> recency.
Let's assume for a moment that the rate at which the insert LSN is
advancing is roughly constant over time, so that it serves as a good
proxy for wall-clock time. Consider four tables A, B, C, and D that
are, respectively, vacuumed once per minute, once per hour, once per
day, and once per week. With a 33% threshold, pages in table A will be
frozen if they haven't been modified in 20 seconds, page in table B
will be frozen if they haven't been modified in 20 minutes, pages in
table C will be frozen if they haven't been modified in 8 hours, and
pages in table D will be frozen if they haven't been modified in 2
days, 8 hours. My intuition is that this feels awfully aggressive for
A and awfully passive for D.
To expand on that: apparently D doesn't actually get much write
activity, else there would be more vacuuming happening. So it's very
likely that pages in table D are going to get checkpointed and evicted
before they get modified again. Freezing them therefore seems like a
good bet: it's a lot cheaper to freeze those pages when they're
already in shared_buffers and dirty than it is if we have to read and
write them specifically for freezing. It's less obvious that what
we're doing in table A is wrong, and it could be exactly right, but
workloads where a row is modified, a human thinks about something
(e.g. whether to complete the proposed purchase), and then the same
row is modified again are not uncommon, and human thinking times can
easily exceed 20 seconds. On the other hand, workloads where a row is
modified, a computer thinks about something, and then the row is
modified again are also quite common, and computer thinking times can
easily be less than 20 seconds. It feels like a toss-up whether we get
it right. For this kind of table, I suspect we'd be happier freezing
pages that are about to be evicted or about to be written as part of a
checkpoint rather than freezing pages opportunistically in vacuum.
Maybe that's something we need to think harder about. If we froze
dirty pages that wouldn't need a new FPI just before evicting them,
and just before they would be written out for a checkpoint, under what
circumstances would we still want vacuum to opportunistically freeze?
I think the answer might be "only when it's very cheap." If it's very
cheap to freeze now, it's appealing to gamble on doing it before a
checkpoint comes along and makes the same operation require an extra
FPI. But if it isn't too cheap, then why not just wait and see what
happens? If the buffer gets modified again before it gets written out,
then freeing immediately is a waste and freeze-on-evict is better. If
it doesn't, freezing immediately and freeze-on-evict are the same
price.
I'm hand-waving a bit here because maybe freeze-on-evict is subject to
some conditions and freezing-immediately is more unconditional or
something like that. But I think the basic point is valid, namely,
sometimes it might be better to defer the decision to the last point
in time at which we can reasonably make it, and by focusing on what
vacuum (or even HOT pruning) do, we're pulling that decision forward
in time, which is good if it makes it cheaper by piggybacking on a
previous FPI, but not good if it means that we're guessing whether the
page will be accessed again soon when we could just as well wait and
see whether that happens or not.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-01 19:34 ` Robert Haas <[email protected]>
2023-09-02 01:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-09-01 19:34 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Tue, Aug 29, 2023 at 10:22 AM Robert Haas <[email protected]> wrote:
> Let's assume for a moment that the rate at which the insert LSN is
> advancing is roughly constant over time, so that it serves as a good
> proxy for wall-clock time. Consider four tables A, B, C, and D that
> are, respectively, vacuumed once per minute, once per hour, once per
> day, and once per week. With a 33% threshold, pages in table A will be
> frozen if they haven't been modified in 20 seconds, page in table B
> will be frozen if they haven't been modified in 20 minutes, pages in
> table C will be frozen if they haven't been modified in 8 hours, and
> pages in table D will be frozen if they haven't been modified in 2
> days, 8 hours. My intuition is that this feels awfully aggressive for
> A and awfully passive for D.
>
> [ discussion of freeze-on-evict ]
Another way of thinking about this is: instead of replacing this
heuristic with a complicated freeze-on-evict system, maybe we just
need to adjust the heuristic. Maybe using an LSN is a good idea, but
maybe the LSN of the last table vacuum isn't the right one to be
using, or maybe it shouldn't be the only one that we use. For
instance, what about using the redo LSN of the last checkpoint, or the
checkpoint before the last checkpoint, or something like that?
Somebody might find that unprincipled, but it seems to me that the
checkpoint cycle has a lot to do with whether or not opportunistic
freezing makes sense. If a page is likely to be modified again before
a checkpoint forces it to be written, then freezing it is likely a
waste. If it's likely to be written out of shared_buffers before it's
modified again, then freezing it now is a pretty good bet. A given
page could be evicted from shared_buffers, and thus written, sooner
than the next checkpoint, but if it's dirty now, it definitely won't
be written any later than the next checkpoint. By looking at the LSN
of a page that I'm about to modify just before I modify it, I can make
some kind of a guess as to whether this is a page that is being
modified more or less than once per checkpoint cycle and adjust
freezing behavior accordingly.
One could also use a hybrid of the two values e.g. normally use the
insert LSN of the last VACUUM, but if that's newer than the redo LSN
of the last checkpoint, then use the latter instead, to avoid doing
too much freezing of pages that may have been quiescent for only a few
tens of seconds. I don't know if that's better or worse. As I think
about it, I realize that I don't really know why Andres suggested a
last-vacuum-LSN-based heuristic in the first place. Before, I wrote of
this that "One thing I really like about it is that if the table is
being vacuumed frequently, then we freeze less aggressively, and if
the table is being vacuumed infrequently, then we freeze more
aggressively." But actually, I don't think it does that. If the table
is being vacuumed frequently, then the last-vacuum-LSN will be newer,
which means we'll freeze *more* aggressively. And I'm not sure why we
want to do that. If the table is being vacuumed a lot, it's probably
also being modified a lot, which suggests that we ought to be more
cautious about freezing, rather than the reverse.
Just spitballing here.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-01 19:34 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-02 01:07 ` Peter Geoghegan <[email protected]>
2023-09-06 14:46 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Peter Geoghegan @ 2023-09-02 01:07 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Fri, Sep 1, 2023 at 12:34 PM Robert Haas <[email protected]> wrote:
> If the table
> is being vacuumed frequently, then the last-vacuum-LSN will be newer,
> which means we'll freeze *more* aggressively.
Sort of like how if you set autovacuum_vacuum_scale_factor low enough,
with standard pgbench, with heap fill factor tuned, autovacuum will
never think that the table doesn't need to be vacuumed. It will
continually see enough dead heap-only tuples to get another autovacuum
each time. Though there won't be any LP_DEAD items at any point --
regardless of when VACUUM actually runs.
When I reported this a couple of years ago, I noticed that autovacuum
would spin whenever I set autovacuum_vacuum_scale_factor to 0.02. But
autovacuum would *never* run (outside of antiwraparound autovacuums)
when it was set just a little higher (perhaps 0.03 or 0.04). So there
was some inflection point at which its behavior totally changed.
> And I'm not sure why we
> want to do that. If the table is being vacuumed a lot, it's probably
> also being modified a lot, which suggests that we ought to be more
> cautious about freezing, rather than the reverse.
Why wouldn't it be both things at the same time, for the same table?
Why not also avoid setting pages all-visible? The WAL records aren't
too much smaller than most freeze records these days -- 64 bytes on
most systems. I realize that the rules for FPIs are a bit different
when page-level checksums aren't enabled, but fundamentally it's the
same situation. No?
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-01 19:34 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-02 01:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
@ 2023-09-06 14:46 ` Robert Haas <[email protected]>
2023-09-06 16:20 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-09-06 14:46 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Fri, Sep 1, 2023 at 9:07 PM Peter Geoghegan <[email protected]> wrote:
> When I reported this a couple of years ago, I noticed that autovacuum
> would spin whenever I set autovacuum_vacuum_scale_factor to 0.02. But
> autovacuum would *never* run (outside of antiwraparound autovacuums)
> when it was set just a little higher (perhaps 0.03 or 0.04). So there
> was some inflection point at which its behavior totally changed.
I think that tables have a "natural" amount of bloat that is a
function of the workload. If you remove all the bloat by, say, running
VACUUM FULL, they quickly bloat again to that point, and then
stabilize (hopefully). It seems like if you set the scale factor to a
value below that "natural" amount of bloat, you might well spin,
especially on a very write-heavy workload like pgbench, because you're
basically trying to squeeze water from a stone on that point.
It's interesting that when you raised the threshold the rate of
vacuuming dropped to zero. I haven't seen that behavior. pgbench does
rely very, very heavily on HOT-pruning, so VACUUM only cleans up a
small percentage of the dead tuples that get created. However, it's
still needed for index vacuuming, and I'm not sure what would cause it
not to trigger for that purpose.
> > And I'm not sure why we
> > want to do that. If the table is being vacuumed a lot, it's probably
> > also being modified a lot, which suggests that we ought to be more
> > cautious about freezing, rather than the reverse.
>
> Why wouldn't it be both things at the same time, for the same table?
Both of what things?
> Why not also avoid setting pages all-visible? The WAL records aren't
> too much smaller than most freeze records these days -- 64 bytes on
> most systems. I realize that the rules for FPIs are a bit different
> when page-level checksums aren't enabled, but fundamentally it's the
> same situation. No?
It's an interesting point. AFAIK, whether or not page-level checksums
are enabled doesn't really matter here. But it seems fair to ask - if
freezing is too aggressive, why is setting all-visible not also too
aggressive? I don't have a good answer to that question right now.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-01 19:34 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-02 01:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-06 14:46 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-06 16:20 ` Peter Geoghegan <[email protected]>
2023-09-06 20:21 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Peter Geoghegan @ 2023-09-06 16:20 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Wed, Sep 6, 2023 at 7:46 AM Robert Haas <[email protected]> wrote:
> It's interesting that when you raised the threshold the rate of
> vacuuming dropped to zero. I haven't seen that behavior. pgbench does
> rely very, very heavily on HOT-pruning, so VACUUM only cleans up a
> small percentage of the dead tuples that get created. However, it's
> still needed for index vacuuming, and I'm not sure what would cause it
> not to trigger for that purpose.
This was a case where index vacuuming was never required. It's just a
simple and easy to recreate example of what I think of as a more
general problem.
> > > And I'm not sure why we
> > > want to do that. If the table is being vacuumed a lot, it's probably
> > > also being modified a lot, which suggests that we ought to be more
> > > cautious about freezing, rather than the reverse.
> >
> > Why wouldn't it be both things at the same time, for the same table?
>
> Both of what things?
Why wouldn't we expect a table to have some pages that ought to be
frozen right away, and others where freezing should in theory be put
off indefinitely? I think that that's very common.
> > Why not also avoid setting pages all-visible? The WAL records aren't
> > too much smaller than most freeze records these days -- 64 bytes on
> > most systems. I realize that the rules for FPIs are a bit different
> > when page-level checksums aren't enabled, but fundamentally it's the
> > same situation. No?
>
> It's an interesting point. AFAIK, whether or not page-level checksums
> are enabled doesn't really matter here. But it seems fair to ask - if
> freezing is too aggressive, why is setting all-visible not also too
> aggressive? I don't have a good answer to that question right now.
As you know, I am particularly concerned about the tendency of
unfrozen all-visible pages to accumulate without bound (at least
without bound expressed in physical units such as pages). The very
fact that pages are being set all-visible by VACUUM can be seen as a
part of a high-level systemic problem -- a problem that plays out over
time, across multiple VACUUM operations. So even if the cost of
setting pages all-visible happened to be much lower than the cost of
freezing (which it isn't), setting pages all-visible without freezing
has unique downsides.
If VACUUM freezes too aggressively, then (pretty much by definition)
we can be sure that the next VACUUM will scan the same pages -- there
may be some scope for VACUUM to "learn from its mistake" when we err
in the direction of over-freezing. But when VACUUM makes the opposite
mistake (doesn't freeze when it should have), it won't scan those same
pages again for a long time, by design. It therefore has no plausible
way of "learning from its mistakes" before it becomes an extremely
expensive and painful lesson (which happens whenever the next
aggressive VACUUM takes place). This is in large part a consequence of
the way that VACUUM dutifully sets pages all-visible whenever
possible. That behavior interacts badly with many workloads, over
time.
VACUUM simply ignores such second-order effects. Perhaps it would be
practical to address some of the issues in this area by avoiding
setting pages all-visible without freezing them, in some general
sense. That at least creates a kind of symmetry between mistakes in
the direction of under-freezing and mistakes in the direction of
over-freezing. That might enable VACUUM to course-correct in either
direction.
Melanie is already planning on combining the WAL records (PRUNE,
FREEZE_PAGE, and VISIBLE). Perhaps that'll weaken the argument for
setting unfrozen pages all-visible even further.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-01 19:34 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-02 01:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-06 14:46 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 16:20 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
@ 2023-09-06 20:21 ` Robert Haas <[email protected]>
0 siblings, 0 replies; 36+ messages in thread
From: Robert Haas @ 2023-09-06 20:21 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Wed, Sep 6, 2023 at 12:20 PM Peter Geoghegan <[email protected]> wrote:
> This was a case where index vacuuming was never required. It's just a
> simple and easy to recreate example of what I think of as a more
> general problem.
OK.
> Why wouldn't we expect a table to have some pages that ought to be
> frozen right away, and others where freezing should in theory be put
> off indefinitely? I think that that's very common.
Oh, I see. I agree that that's pretty common. I think what that means
in practice is that we need to avoid relying too much on
relation-level statistics to guide behavior with respect to individual
relation pages. On the other hand, I don't think it means that we
can't look at relation-wide or even system-wide statistics at all.
Sure, those statistics may not be perfect, but some things are not
practical to track on a page granularity, and having some
course-grained information can, I think, be better than having nothing
at all, if you're careful about how much and in what way you rely on
it.
> As you know, I am particularly concerned about the tendency of
> unfrozen all-visible pages to accumulate without bound (at least
> without bound expressed in physical units such as pages). The very
> fact that pages are being set all-visible by VACUUM can be seen as a
> part of a high-level systemic problem -- a problem that plays out over
> time, across multiple VACUUM operations. So even if the cost of
> setting pages all-visible happened to be much lower than the cost of
> freezing (which it isn't), setting pages all-visible without freezing
> has unique downsides.
I generally agree with all of that.
> If VACUUM freezes too aggressively, then (pretty much by definition)
> we can be sure that the next VACUUM will scan the same pages -- there
> may be some scope for VACUUM to "learn from its mistake" when we err
> in the direction of over-freezing. But when VACUUM makes the opposite
> mistake (doesn't freeze when it should have), it won't scan those same
> pages again for a long time, by design. It therefore has no plausible
> way of "learning from its mistakes" before it becomes an extremely
> expensive and painful lesson (which happens whenever the next
> aggressive VACUUM takes place). This is in large part a consequence of
> the way that VACUUM dutifully sets pages all-visible whenever
> possible. That behavior interacts badly with many workloads, over
> time.
I think this is an insightful commentary with which I partially agree.
As I see it, the difference is that when you make the mistake of
marking something all-visible or freezing it too aggressively, you
incur a price that you pay almost immediately. When you make the
mistake of not marking something all-visible when it would have been
best to do so, you incur a price that you pay later, when the next
VACUUM happens. When you make the mistake of not marking something
all-frozen when it would have been best to do so, you incur a price
that you pay even later, not at the next VACUUM but at some VACUUM
further off. So there are different trade-offs. When you pay the price
for a mistake immediately or nearly immediately, it can potentially
harm the performance of the foreground workload, if you're making a
lot of mistakes. That sucks. On the other hand, when you defer paying
the price until some later bulk operation, the costs of all of your
mistakes get added up and then you pay the whole price all at once,
which means you can be suddenly slapped with an enormous bill that you
weren't expecting. That sucks, too, just in a different way.
> VACUUM simply ignores such second-order effects. Perhaps it would be
> practical to address some of the issues in this area by avoiding
> setting pages all-visible without freezing them, in some general
> sense. That at least creates a kind of symmetry between mistakes in
> the direction of under-freezing and mistakes in the direction of
> over-freezing. That might enable VACUUM to course-correct in either
> direction.
>
> Melanie is already planning on combining the WAL records (PRUNE,
> FREEZE_PAGE, and VISIBLE). Perhaps that'll weaken the argument for
> setting unfrozen pages all-visible even further.
Yeah, so I think the question here is whether it's ever a good idea to
mark a page all-visible without also freezing it. If it's not, then we
should either mark fewer pages all-visible, or freeze more of them.
Maybe I'm all wet here, but I think it depends on the situation. If a
page is already dirty and has had an FPI since the last checkpoint,
then it's pretty appealing to freeze whenever we mark all-visible. We
still have to consider whether the incremental CPU cost and WAL volume
are worth it, but assuming those costs are small enough not to be a
big problem, it seems like a pretty good bet. Making a page
un-all-visible has some cost, but making a page un-all-frozen really
doesn't, so cool. On the other hand, if we have a page that isn't
dirty, hasn't had a recent FPI, and doesn't need pruning, but which
can be marked all-visible, freezing it is a potentially more
significant cost, because marking the buffer all-visible doesn't force
a new FPI, and freezing does.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-09-23 19:53 ` Melanie Plageman <[email protected]>
2023-09-24 16:45 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-25 20:19 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
1 sibling, 2 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-09-23 19:53 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 4:30 PM Melanie Plageman
<[email protected]> wrote:
> On Mon, Aug 28, 2023 at 12:26 PM Robert Haas <[email protected]> wrote:
> > In row D, your algorithms are all bad, really bad. I don't quite
> > understand how it can be that bad, actually.
>
> So, I realize now that this test was poorly designed. I meant it to be a
> worst case scenario, but I think one critical part was wrong. In this
> example one client is going at full speed inserting a row and then
> updating it. Then another rate-limited client is deleting old data
> periodically to keep the table at a constant size. I meant to bulk load
> the table with enough data that the delete job would have data to delete
> from the start. With the default autovacuum settings, over the course of
> 45 minutes, I usually saw around 40 autovacuums of the table. Due to the
> rate limiting, the first autovacuum of the table ends up freezing many
> pages that are deleted soon after. Thus the total number of page freezes
> is very high.
>
> I will redo benchmarking of workload D and start the table with the
> number of rows which the DELETE job seeks to maintain. My back of the
> envelope math says that this will mean ratios closer to a dozen (and not
> 5000).
...
> I'll rerun workload D in a more reasonable way and be back with results.
Hi,
I have edited and rerun all of the benchmarks for a subset of the
algorithms. I ran workloads A-I, except for G (G is not an interesting
workload), on Postgres master as well as Postgres with freeze heuristic
algorithms 4 and 5.
As a refresher, algorithms 4 and 5 are as follows:
Freeze tuples on a page opportunistically if the page would be totally
frozen and:
4. Buffer is already dirty and no FPI is required OR page LSN is older
than 33% of the LSNs since the last vacuum of the table.
5. Buffer is already dirty and no FPI is required AND page LSN is older
than 33% of the LSNs since the last vacuum of the table.
On master, the heuristic is to freeze a page opportunistically if it
would be totally frozen and if pruning emitted an FPI.
I made a few configuration changes for all benchmarks -- most notably
autovacuum is on for all workloads and autovacuum_naptime is decreased
to 10 seconds. The runtime of all time-based workloads was changed to 40
minutes. All workloads were run for a fixed duration except for the COPY
workload (workload F).
It is also worth noting that data loaded into the standard pgbench
tables (with pgbench -i) was loaded with COPY and not COPY FREEZE. Also,
pgbench -i and the pgbench runs were passed the --no-vacuum option.
The updated workloads are as follows:
A. Gaussian TPC-B like + select-only:
pgbench scale 450 (DB < SB) with indexes on updated columns
WL 1: 16 clients doing TPC-B like pgbench but with Gaussian access
distribution (with parameter 6) for updated tables
WL 2: 2 clients, rate-limited to 1000 TPS, doing select-only pgbench
B. TPC-B like
pgbench scale 450
16 clients doing built-in pgbench TPC-C like script
C. Shifting hot set:
16 clients inserting a single row then updating an indexed column in
that row
D. Shifting hot set, delete old data
WL 1: 16 clients inserting one row then updating an indexed column in
that row
WL 2: 1 client, rate limited to 10 TPS, deleting data more than 1
minute old
E. Shifting hot set, delete new data, access new data
WL 1: 16 clients cycling through 2 single row inserts, an update of
an indexed column in a single recently inserted row, and a delete of
a single recently inserted row
WL 2: 1 client, rate-limited to 0.2 TPS, selecting data from the last
5 minutes
F. Many COPYs
1 client, copying a total of 90 GB of data in 70 individual COPYs
G. N/A
H. Append only table
16 clients, inserting a single row at a time
I. Work queue
WL 1: 16 clients inserting a single row, updating a non-indexed
column in that row twice, then deleting that row
WL 2: 1 client, rate-limited to 0.02 TPS, COPYing data into another
table
Below are some of the data I collected, including how much WAL was
generated, how much IO various backends did, TPS, and P99 latency.
These were collected at the end of the pgbench run.
Most numbers were rounded to the nearest whole number to make the chart
easier to see. So, for example, a 0 in WAL GB does not mean that no WAL
was emitted. P99 latency below 0 was rounded to a single decimal place.
All IO time is in milliseconds.
"P99 lat" was calculated using the "time" field from pgbench's
"per-transaction logging" option [1]. It is the "transaction's elapsed time"
or duration. I converted it to milliseconds.
"pages frozen" here refers to the number of pages marked frozen in the
visibility map at the end of the run. "page freezes" refers to the
number of times vacuum froze tuples in lazy_scan_prune(). This does not
include cases in which the page was found to be all frozen but no tuples
were explicitly frozen.
"AVs" is the number of autovacuums for the table or tables specified.
"M" (under algo) indicates unpatched Postgres master.
pgbench_accounts is abbreviated as "Acct", pgbench_history as "Hist",
and pgbench_branches + pgbench_tellers as B+T.
Workload A:
+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
| M | 37 | 3,064,470 | 119,021 | 199 |
| 4 | 41 | 3,071,229 | 119,010 | 199 |
| 5 | 37 | 3,067,462 | 119,043 | 198 |
+------+--------+---------------------+---------------------+------------------+
+------+---------+-----------+----------------+----------------------+
| algo | TPS read | TPS write | P99 latency read | P99 latency write |
+------+---------+-----------+----------------+----------------------+
| M | 1000 | 5,391 | 0.1 | 4.0 |
| 4 | 1000 | 5,391 | 0.1 | 3.9 |
| 5 | 1000 | 5,430 | 0.1 | 3.9 |
+------+---------+-----------+----------------+----------------------+
+------+---------+---------+---------+
| algo | Acct AVs | B+T AVs| Hist AVs|
+------+---------+---------+---------+
| M | 2 | 459 | 21 |
| 4 | 2 | 467 | 20 |
| 5 | 2 | 469 | 21 |
+------+---------+---------+---------+
+------+------------------+------------------+---------------+
| algo | Acct Page Freezes| Acct Pages Frozen| Acct % Frozen |
+------+------------------+--------------------+-------------+
| M | 2,555 | 646 | 0% |
| 4 | 866,141 | 385,039 | 52% |
| 5 | 4 | 0 | 0% |
+------+------------------+--------------------+-------------+
+------+-------------------+-------------------+---------------+
| algo | Hist Page Freezes | Hist Pages Frozen | Hist % Frozen |
+------+-------------------+-------------------+---------------+
| M | 0 | 0 | 0% |
| 4 | 70,227 | 69,835 | 85% |
| 5 | 19,204 | 19,024 | 23% |
+------+-------------------+-------------------+---------------+
+------+-------------------+-------------------+---------------+
| algo | B+T Page Freezes | B+T Pages Frozen | B+T % Frozen |
+------+-------------------+-------------------+---------------+
| M | 152 | 297 | 33% |
| 4 | 74,323 | 809 | 100% |
| 5 | 6,215 | 384 | 48% |
+------+-------------------+-------------------+---------------+
Algorithm 4 leads to a substantial increase in the amount of WAL (due to
additional FPIs). However, the impact on P99 latency and TPS is minimal.
The relatively low number of page freezes on pgbench_accounts is likely
due to the fact that freezing only happens in lazy_scan_prune(), before
index vacuuming. pgbench_accounts' updated column was indexed, so many
tuples could not be removed before freezing, making pages ineligible for
opportunistic freezing.
This does not explain why algorithm 5 froze fewer pages of
pgbench_accounts than even master. I plan to investigate this further. I
also noticed that the overall write TPS seems low for my system. It is
likely due to row-level contention because of the Gaussian access
distribution, but it merits further analysis.
Additionally, I plan to redesign this benchmark. I will run a single
pgbench instance and use pgbench's script weight feature to issue more
write script executions than select-only script executions. Using two
pgbench instances and rate-limiting the select-only script makes it
difficult to tell if there is a potential impact to concurrent read
throughput.
Workload B:
+------+--------+---------------------+-------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes| IO time AV worker|
+------+--------+---------------------+---------------------+----------------+
| M | 73 | 5,972,992 | 647,342 | 75 |
| 4 | 74 | 5,976,167 | 640,682 | 66 |
| 5 | 73 | 5,962,465 | 644,060 | 70 |
+------+--------+---------------------+---------------------+----------------+
+------+--------+-------------+
| algo | TPS | P99 latency |
+------+--------+-------------+
| M | 21,336 | 1.5 |
| 4 | 21,458 | 1.5 |
| 5 | 21,384 | 1.5 |
+------+--------+-------------+
+------+---------+---------+---------+
| algo | Acct AVs| B+T AVs | Hist AVs|
+------+---------+---------+---------+
| M | 1 | 472 | 22 |
| 4 | 1 | 470 | 21 |
| 5 | 1 | 469 | 19 |
+------+---------+---------+---------+
+------+------------------+--------------------+---------------+
| algo | Acct Page Freezes| Acct Pages Frozen | Acct % Frozen |
+------+------------------+--------------------+---------------+
| M | 0 | 0 | 0% |
| 4 | 269,850 | 0 | 0% |
| 5 | 194 | 0 | 0% |
+------+------------------+-------------------+----------------+
+------+-------------------+---------------------+---------------+
| algo | B+T Page Freezes | B+T Pages Frozen | B+T % Frozen |
+------+-------------------+---------------------+---------------+
| M | 0 | 123 | 34% |
| 4 | 34,268 | 148 | 44% |
| 5 | 25 | 114 | 33% |
+------+-------------------+---------------------+---------------+
+------+-------------------+---------------------+---------------+
| algo | Hist Page Freezes | Hist Pages Frozen | Hist % Frozen |
+------+-------------------+---------------------+---------------+
| M | 0 | 0 | 0% |
| 4 | 297,083 | 296,688 | 90% |
| 5 | 85,573 | 85,353 | 26% |
+------+-------------------+---------------------+---------------+
Algorithm 4 is more aggressive and does much more freezing. Since there
are no indexes, more page freezing can happen for updated tables. These
pages, however, are uniformly updated and do not stay frozen, so the
freezing is pointless. This freezing doesn't seem to substantially
impact P99 latency, TPS, or volume of WAL emitted.
Workload C:
+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
| M | 16 | 910,583 | 61,124 | 75 |
| 4 | 17 | 896,213 | 61,124 | 75 |
| 5 | 15 | 900,068 | 61,124 | 76 |
+------+--------+---------------------+---------------------+------------------+
+------+-------+--------------+
| algo | TPS | P99 latency |
+------+-------+--------------+
| M | 10,653| 2 |
| 4 | 10,620| 2 |
| 5 | 10,591| 2 |
+------+-------+--------------+
+------+-----+--------------+-------------+----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen|
+------+-----+--------------+-------------+----------+
| M | 4 | 130,600 | 0 | 0% |
| 4 | 4 | 364,781 | 197,135 | 60% |
| 5 | 4 | 0 | 0 | 0% |
+------+-----+--------------+-------------+----------+
It is notable that algorithm 5 again did not freeze any pages.
Workload D:
+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
| M | 12 | 50,745 | 481 | 1.02 |
| 4 | 12 | 49,590 | 481 | 0.99 |
| 5 | 12 | 51,044 | 481 | 0.98 |
+------+--------+---------------------+---------------------+------------------+
+------+----------+------------+-------------------+--------------------+
| algo | TPS write | TPS delete| P99 latency write | P99 latency delete |
+------+----------+------------+--------------------+-------------------+
| M | 11,034 | 10 | 1.7 | 107 |
| 4 | 10,992 | 10 | 1.7 | 93 |
| 5 | 11,014 | 10 | 1.7 | 95 |
+------+----------+------------+-------------------+--------------------+
+------+-----+--------------+-------------+-----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+-------------+-----------+
| M | 230| 2,066 | 332 | 6% |
| 4 | 233| 631,220 | 1,404 | 25% |
| 5 | 231| 456,066 | 2,352 | 44% |
+------+-----+--------------+--------------+----------+
The P99 delete latency is higher for master. However write TPS is also
higher, so it is possible that the decreased P99 latency for the delete
is unrelated to freezing. Notably algorithm 4 froze least effectively --
it did the most page freezes per page frozen at the end of the run.
Workload E:
+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
| M | 15 | 738,854 | 482 | 1.0 |
| 4 | 15 | 745,182 | 482 | 0.8 |
| 5 | 15 | 733,916 | 482 | 0.9 |
+------+--------+---------------------+---------------------+------------------+
+------+---------+-----------+--------------------+------------------+
| algo | TPS read | TPS write| P99 latency read ms| P99 latency write|
+------+---------+-----------+--------------------+------------------+
| M | 0.2 | 20,171 | 1,655 | 2 |
| 4 | 0.2 | 20,230 | 1,611 | 2 |
| 5 | 0.2 | 20,165 | 1,579 | 2 |
+------+---------+-----------+--------------------+------------------+
+------+-----+--------------+--------------+----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+--------------+----------+
| M | 23 | 2,396 | 0 | 0% |
| 4 | 23 | 231,816 | 121,373 | 71% |
| 5 | 23 | 68,302 | 36,294 | 21% |
+------+-----+--------------+--------------+----------+
The additional page freezes done by algorithm 4 seem somewhat effective
and do not appear to have an impact on throughput, latency, or a large
impact on WAL volume.
Workload F:
+------+--------+---------------------+--------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+-----------------+
| M | 173 | 1,202,231 | 53,957,448 | 12,389 |
| 4 | 189 | 1,212,521 | 55,589,140 | 13,084 |
| 5 | 173 | 1,194,242 | 54,260,118 | 13,407 |
+------+--------+---------------------+--------------------+------------------+
+------+--------------+
| algo | P99 latency |
+------+--------------+
| M | 19875 |
| 4 | 19314 |
| 5 | 19701 |
+------+--------------+
+------+-----+--------------+--------------+----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+--------------+----------+
| M | 3 | 0 | 0 | 0% |
| 4 | 3 | 2,598,727 | 2,598,725 | 24% |
| 5 | 3 | 475,063 | 475,064 | 4% |
+------+-----+--------------+--------------+----------+
Algorithm 4 produced a notably higher volume of WAL but also managed to
freeze a decent portion of the table. P99 latency is higher -- meaning
each COPY did take longer with algorithm 4.
Workload H:
+------+--------+----------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time
AV worker |
+------+--------+----------------------+---------------------+------------------+
| M | 12 | 922,121 | 318 |
1.03 |
| 4 | 16 | 921,543 | 318 |
0.90 |
| 5 | 12 | 921,309 | 318 |
0.74 |
+------+--------+----------------------+---------------------+------------------+
+------+--------+--------------+
| algo | TPS | P99 latency |
+------+--------+--------------+
| M | 21,926 | 0.98 |
| 4 | 22,028 | 0.97 |
| 5 | 21,919 | 0.99 |
+------+--------+--------------+
+------+-----+--------------+-------------+-----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+--------------+----------+
| M | 6 | 0 | 0 | 0% |
| 4 | 6 | 560,317 | 560,198 | 94% |
| 5 | 6 | 11,921 | 11,921 | 2% |
+------+-----+--------------+--------------+----------+
Algorithm 4 produced a much higher volume of WAL and froze much more of
the table. This did not negatively impact throughput or latency.
Workload 5 froze dramatically few pages as compared to algorithm 4.
Workload I:
+------+--------+---------------------+---------------------+-------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time
AV worker |
+------+--------+---------------------+---------------------+-------------------+
| M | 32 | 123,947 | 32,733,204 |
16,803 |
| 4 | 59 | 124,233 | 32,734,188 |
17,151 |
| 5 | 32 | 124,324 | 32,733,166 |
17,122 |
+------+--------+---------------------+---------------------+-------------------+
+------+-----------+---------+------------------+------------------+
| algo | TPS queue | TPS copy| P99 latency queue| P99 latency copy |
+------+-----------+---------+------------------+------------------+
| M | 5,407 | 0.02 | 4 | 8,386 |
| 4 | 5,277 | 0.02 | 5 | 8,520 |
| 5 | 5,401 | 0.02 | 4 | 8,399 |
+------+-----------+---------+------------------+------------------+
+------+-----------+----------+
| algo | Queue AVs | Copy AVs |
+------+-----------+----------+
| M | 236 | 5 |
| 4 | 236 | 5 |
| 5 | 235 | 5 |
+------+----------+-----------+
+------+-------------------+--------------------+---------------+
| algo | Queue Page Freezes| Queue Pages Frozen | Queue % Frozen|
+------+-------------------+--------------------+---------------+
| M | 1 | 1 | 100% |
| 4 | 1 | 1 | 100% |
| 5 | 1 | 1 | 100% |
+------+-------------------+--------------------+---------------+
+------+-------------------+-------------------+---------------+
| algo | Copy Page Freezes | Copy Pages Frozen | Copy % Frozen |
+------+-------------------+-------------------+---------------+
| M | 0 | 0 | 0% |
| 4 | 3,821,658 | 3,821,655 | 64% |
| 5 | 431,044 | 431,043 | 7% |
+------+-------------------+-------------------+---------------+
This is a case in which algorithm 4 seemed to have a measurable negative
performance impact. It emitted twice the WAL and had substantially worse
P99 latency and throughput.
-------
My takeaways from all of the workload results are as follows:
Algorithm 4 is too aggressive and regresses performance compared to
master.
Algorithm 5 freezes surprisingly few pages, especially in workloads
where only the most recent data is being accessed or modified
(append-only, update recent data).
A work queue-like workload with other concurrent workloads is a
particular challenge for the freeze heuristic, and we should think more
about how to handle this.
We should consider freezing again after index vacuuming and unused tuple
reaping.
In many cases a moderate amount of additional freezing (like algorithm
5) does not impact P99 latency and throughput in a meaningful way.
Next steps:
I plan to make some changes to the benchmarks and rerun them with the
new algorithms suggested on the thread.
- Melanie
[1] https://www.postgresql.org/docs/current/pgbench.html
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-23 19:53 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-09-24 16:45 ` Melanie Plageman <[email protected]>
1 sibling, 0 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-09-24 16:45 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Sat, Sep 23, 2023 at 3:53 PM Melanie Plageman
<[email protected]> wrote:
>
> Workload F:
>
> +------+--------+---------------------+--------------------+------------------+
> | algo | WAL GB | cptr bgwriter writes| other reads/writes | IO time AV worker|
> +------+--------+---------------------+---------------------+-----------------+
> | M | 173 | 1,202,231 | 53,957,448 | 12,389 |
> | 4 | 189 | 1,212,521 | 55,589,140 | 13,084 |
> | 5 | 173 | 1,194,242 | 54,260,118 | 13,407 |
> +------+--------+---------------------+--------------------+------------------+
>
> +------+--------------+
> | algo | P99 latency |
> +------+--------------+
> | M | 19875 |
> | 4 | 19314 |
> | 5 | 19701 |
> +------+--------------+
Andres mentioned that the P99 latency for the COPY workload (workload F)
might not be meaningful, so I have calculated the duration total, mean,
median, min, max and standard deviation in milliseconds.
Workload F:
+------+------------+-------+--------+--------+--------+---------+
| algo | Total | Mean| Median | Min | Max | Stddev |
+------+------------+-------+--------+--------+--------+---------+
| M | 1,270,903 | 18,155| 17,755 | 17,090 | 19,994 | 869 |
| 4 | 1,167,135 | 16,673| 16,421 | 15,585 | 19,485 | 811 |
| 5 | 1,250,145 | 17,859| 17,704 | 15,763 | 19,871 | 1,009 |
+------+------------+-------+--------+--------+--------+---------+
Interestingly, algorithm 4 had the lowest total duration for all COPYs.
Some investigation of other data collected during the runs led us to
believe this may be due to autovacuum workers doing more IO with
algorithm 4 and thus generating more WAL and ending up initializing more
WAL files themselves. Whereas on master and with algorithm 5, client
backends had to initialize WAL files themselves, leading COPYs to take
longer. This was supported by the presence of more WALInit wait events
for client backends on master and with algorithm 5.
Calculating these made me realize that my conclusions about the work
queue workload (workload I) didn't make much sense. Because this
workload updated a non-indexed column, most pruning was HOT pruning done
on access and basically no page freezing was done by vacuum. This means
we weren't seeing negative performance effects of freezing related to
the work queue table.
The difference in this benchmark came from the relatively poor
performance of the concurrent COPYs when that table was frozen more
aggressively. I plan to run a new version of this workload which updates
an indexed column for comparison and does not use a concurrent COPY.
This is the duration total, mean, median, min, max, and standard
deviation in milliseconds of the COPYs which ran concurrently with the
work queue pgbench.
Workload I COPYs:
+------+--------+-------+--------+--------+--------+---------+
| algo | Total | Mean | Median | Min | Max | Stddev |
+------+--------+-------+--------+--------+--------+---------+
| M | 191,032| 4,898| 4,726 | 4,486 | 9,353 | 800 |
| 4 | 193,534| 4,962| 4,793 | 4,533 | 9,381 | 812 |
| 5 | 194,351| 4,983| 4,771 | 4,617 | 9,159 | 783 |
+------+--------+-------+--------+--------+--------+---------+
I think this shows that algorithm 4 COPYs performed the worst. This is
in contrast to the COPY-only workload (F) which did not show worse
performance for algorithm 4. I think this means I should modify the work
queue example and use something other than concurrent COPYs to avoid
obscuring characteristics of the work queue example.
- Melanie
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-23 19:53 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-09-25 20:19 ` Robert Haas <[email protected]>
1 sibling, 0 replies; 36+ messages in thread
From: Robert Haas @ 2023-09-25 20:19 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Sat, Sep 23, 2023 at 3:53 PM Melanie Plageman
<[email protected]> wrote:
> Freeze tuples on a page opportunistically if the page would be totally
> frozen and:
>
> 4. Buffer is already dirty and no FPI is required OR page LSN is older
> than 33% of the LSNs since the last vacuum of the table.
>
> 5. Buffer is already dirty and no FPI is required AND page LSN is older
> than 33% of the LSNs since the last vacuum of the table.
>
> On master, the heuristic is to freeze a page opportunistically if it
> would be totally frozen and if pruning emitted an FPI.
> -------
>
> My takeaways from all of the workload results are as follows:
>
> Algorithm 4 is too aggressive and regresses performance compared to
> master.
>
> Algorithm 5 freezes surprisingly few pages, especially in workloads
> where only the most recent data is being accessed or modified
> (append-only, update recent data).
>
> A work queue-like workload with other concurrent workloads is a
> particular challenge for the freeze heuristic, and we should think more
> about how to handle this.
I feel like we have a sort of Goldilocks problem here: the porridge is
either too hot or too cold, never just right. Say we just look at
workload B, looking at the difference between pgbench_accounts (which
is randomly and frequently updated and thus shouldn't be
opportunistically frozen) and pgbench_history (which is append-only
and should thus be frozen aggressively). Algorithm 4 gets
pgbench_history right and pgbench_accounts wrong, and master does the
opposite. In a perfect world, we'd have an algorithm which could
distinguish sharply between those cases, ramping up to maximum
aggressiveness on pgbench_history while doing nothing at all to
pgbench_accounts.
Algorithm 5 partially accomplishes this, but the results aren't
super-inspiring either. It doesn't add many page freezes in the case
where freezing is bad, but it also only manages to freeze a quarter of
pgbench_history, where algorithm 4 manages to freeze basically all of
it. That's a pretty stark difference. Given that algorithm 5 seems to
make some mistakes on some of the other workloads, I don't think it's
entirely clear that it's an improvement over master, at least in
practical terms.
It might be worth thinking harder about what it takes specifically to
get the pgbench_history case, aka the append-only table case, correct.
One thing that probably doesn't work very well is to freeze pages that
are more than X minutes old. Algorithm 5 uses an LSN threshold instead
of a wall-clock based threshold, but the effect is the same. I think
the problem here is that the vacuum operation essentially happens in
an instant. At the instant that it happens, some fraction of the data
added since the last vacuum is older than whatever threshold you pick,
and the rest is newer. If data is added at a constant rate and you
want to freeze at least 90% of the data, your recency threshold has to
be no more than 10% of the time since the last vacuum. But with
autovacuum_naptimes=60s, that's like 6 seconds, and that's way too
aggressive for a table like pgbench_accounts. It seems to me that it's
not possible to get both cases right by twiddling the threshold,
because pgbench_history wants the threshold to be 0, and
pgbench_accounts wants it to be ... perhaps not infinity, because
maybe the distribution is Gaussian or Zipfian or something rather than
uniform, but probably a couple of minutes.
So I feel like if we want to get both pgbench_history and
pgbench_accounts right, we need to consider some additional piece of
information that makes those cases distinguishable. Either of those
tables can contain a page that hasn't been accessed in 20 seconds, but
the correct behavior for such a page differs between one case and the
other. One random idea that I had was to refuse to opportunistically
freeze a page more than once while it remains resident in
shared_buffers. The first time we do it, we set a bit in the buffer
header or something that suppresses further opportunistic freezing.
When the buffer is evicted the bit is cleared. So we can still be
wrong on a heavily updated table like pgbench_acccounts, but if the
table fits in shared_buffers, we'll soon realize that we're getting it
wrong a lot and will stop making the same mistake over and over. But
this kind of idea only works if the working set is small enough to fit
in shared_buffers, so I don't think it's actually a great plan, unless
we only care about suppressing excess freezing on workloads that fit
in shared_buffers.
A variant on the same theme could be to keep some table-level counters
and use them to assess how often we're getting it wrong. If we're
often thawing recently-frozen pages, don't freeze so aggressively. But
this will not work if different parts of the same table behave
differently.
If we don't want to do something like this that somehow responds to
the characteristics of a particular page or table, then it seems we
either have to freeze quite aggressively to pick up insert-only cases
and accept that this will lead to some wasted effort in heavy-update
cases, or else freeze less aggressively and accept that we're going
not going to freeze insert-only pages consistently.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-06 05:09 ` Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Andres Freund @ 2023-09-06 05:09 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Melanie Plageman <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi,
On 2023-08-28 12:26:01 -0400, Robert Haas wrote:
> On Mon, Aug 28, 2023 at 10:00 AM Melanie Plageman
> <[email protected]> wrote:
> > For the second goal, I've relied on past data to predict future
> > behavior, so I tried several criteria to estimate the likelihood that a
> > page will not be imminently modified. What was most effective was
> > Andres' suggestion of comparing the page LSN to the insert LSN at the
> > end of the last vacuum of that table; this approximates whether the page
> > has been recently modified, which is a decent proxy for whether it'll be
> > modified in the future. To do this, we need to save that insert LSN
> > somewhere. In the attached WIP patch, I saved it in the table stats, for
> > now -- knowing that those are not crash-safe.
>
> I wonder what the real plan here is for where to store this. It's not
> obvious that we need this to be crash-safe; it's after all only for
> use by a heuristic, and there's no actual breakage if the heuristic
> goes wrong. At the same time, it doesn't exactly feel like a
> statistic.
I'm not certain either. This is generally something that's not satisfying
right now - although IMO not necessarily for the reason you mention. Given
that we already store, e.g., the time of the last autovacuum in the stats, I
don't see a problem also storing a corresponding LSN. My issue is more that
this kind of information not being crashsafe is really problematic - it's a
well known issue that autovacuum just doesn't do anything for a while after a
crash-restart (or pitr restore or ...), for example.
Given that all the other datapoints are stored in the stats, I think just
storing the LSNs alongside is reasonable.
> Then there's the question of whether it's the right metric. My first
> reaction is to think that it sounds pretty good. One thing I really
> like about it is that if the table is being vacuumed frequently, then
> we freeze less aggressively, and if the table is being vacuumed
> infrequently, then we freeze more aggressively. That seems like a very
> desirable property. It also seems broadly good that this metric
> doesn't really care about reads. If there are a lot of reads on the
> system, or no reads at all, it doesn't really change the chances that
> a certain page is going to be written again soon, and since reads
> don't change the insert LSN, here again it seems to do the right
> thing. I'm a little less clear about whether it's good that it doesn't
> really depend on wall-clock time.
Yea, it'd be useful to have a reasonably approximate wall clock time for the
last modification of a page. We just don't have infrastructure for determining
that. We'd need an LSN->time mapping (xid->time wouldn't be particularly
useful, I think).
A very rough approximate modification time can be computed by assuming an even
rate of WAL generation, and using the LSN at the time of the last vacuum and
the time of the last vacuum, to compute the approximate age.
For a while I thought that'd not give us anything that just using LSNs gives
us, but I think it might allow coming up with a better cutoff logic: Instead
of using a cutoff like "page LSN is older than 10% of the LSNs since the last
vacuum of the table", it would allow us to approximate "page has not been
modified in the last 15 seconds" or such. I think that might help avoid
unnecessary freezing on tables with very frequent vacuuming.
> Certainly, that's desirable from the point of view of not wanting to have to
> measure wall-clock time in places where we otherwise wouldn't have to, which
> tends to end up being expensive.
IMO the bigger issue is that we don't want to store a timestamp on each page.
> > Page Freezes/Page Frozen (less is better)
As, I think, Robert mentioned downthread, I'm not sure this is a useful thing
to judge the different heuristics by. If the number of pages frozen is small,
the ratio quickly can be very large, without the freezing having a negative
effect.
I suspect interesting top-level figures to compare would be:
1) WAL volume (to judge the amount of unnecessary FPIs)
2) data reads + writes (to see the effect of repeated vacuuming of the same
blocks)
3) number of vacuums and/or time spent vacuuming (freezing less aggressively
might increase the number of vacuums due to anti-wrap vacuums, at the same
time, freezing too aggressively could lead to vacuums taking too long)
4) throughput of the workload (to see potential regressions due to vacuuming
overhead)
5) for transactional workloads: p99 latency (to see if vacuuming increases
commit latency and such, just using average tends to hide too much)
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
@ 2023-09-06 14:35 ` Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-09-06 14:35 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Melanie Plageman <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Wed, Sep 6, 2023 at 1:09 AM Andres Freund <[email protected]> wrote:
> Yea, it'd be useful to have a reasonably approximate wall clock time for the
> last modification of a page. We just don't have infrastructure for determining
> that. We'd need an LSN->time mapping (xid->time wouldn't be particularly
> useful, I think).
>
> A very rough approximate modification time can be computed by assuming an even
> rate of WAL generation, and using the LSN at the time of the last vacuum and
> the time of the last vacuum, to compute the approximate age.
>
> For a while I thought that'd not give us anything that just using LSNs gives
> us, but I think it might allow coming up with a better cutoff logic: Instead
> of using a cutoff like "page LSN is older than 10% of the LSNs since the last
> vacuum of the table", it would allow us to approximate "page has not been
> modified in the last 15 seconds" or such. I think that might help avoid
> unnecessary freezing on tables with very frequent vacuuming.
Yes. I'm uncomfortable with the last-vacuum-LSN approach mostly
because of the impact on very frequently vacuumed tables, and
secondarily because of the impact on very infrequently vacuumed
tables.
Downthread, I proposed using the RedoRecPtr of the latest checkpoint
rather than the LSN of the previou vacuum. I still like that idea.
It's a value that we already have, with no additional bookkeeping. It
varies over a much narrower range than the interval between vacuums on
a table. The vacuum interval could be as short as tens of seconds as
long as years, while the checkpoint interval is almost always going to
be between a few minutes at the low end and some tens of minutes at
the high end, hours at the very most. That's quite appealing. Also, I
think the time between checkpoints actually matters here, because in
some sense we're looking to get dirty, already-FPI'd pages frozen
before they get written out, or before a new FPI becomes necessary,
and checkpoints are one way for the first of those things to happen
and the only way for the second one to happen.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-08 04:07 ` Andres Freund <[email protected]>
2023-09-25 18:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-27 18:36 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
0 siblings, 2 replies; 36+ messages in thread
From: Andres Freund @ 2023-09-08 04:07 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Melanie Plageman <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi,
On 2023-09-06 10:35:17 -0400, Robert Haas wrote:
> On Wed, Sep 6, 2023 at 1:09 AM Andres Freund <[email protected]> wrote:
> > Yea, it'd be useful to have a reasonably approximate wall clock time for the
> > last modification of a page. We just don't have infrastructure for determining
> > that. We'd need an LSN->time mapping (xid->time wouldn't be particularly
> > useful, I think).
> >
> > A very rough approximate modification time can be computed by assuming an even
> > rate of WAL generation, and using the LSN at the time of the last vacuum and
> > the time of the last vacuum, to compute the approximate age.
> >
> > For a while I thought that'd not give us anything that just using LSNs gives
> > us, but I think it might allow coming up with a better cutoff logic: Instead
> > of using a cutoff like "page LSN is older than 10% of the LSNs since the last
> > vacuum of the table", it would allow us to approximate "page has not been
> > modified in the last 15 seconds" or such. I think that might help avoid
> > unnecessary freezing on tables with very frequent vacuuming.
>
> Yes. I'm uncomfortable with the last-vacuum-LSN approach mostly
> because of the impact on very frequently vacuumed tables, and
> secondarily because of the impact on very infrequently vacuumed
> tables.
>
> Downthread, I proposed using the RedoRecPtr of the latest checkpoint
> rather than the LSN of the previou vacuum. I still like that idea.
Assuming that "downthread" references
https://postgr.es/m/CA%2BTgmoYb670VcDFbekjn2YQOKF9a7e-kBFoj2WJF1HtH7YPaWQ%40mail.gmail.com
could you sketch out the logic you're imagining a bit more?
> It's a value that we already have, with no additional bookkeeping. It
> varies over a much narrower range than the interval between vacuums on
> a table. The vacuum interval could be as short as tens of seconds as
> long as years, while the checkpoint interval is almost always going to
> be between a few minutes at the low end and some tens of minutes at
> the high end, hours at the very most. That's quite appealing.
The reason I was thinking of using the "lsn at the end of the last vacuum", is
that it seems to be more adapative to the frequency of vacuuming.
One the one hand, if a table is rarely autovacuumed because it is huge,
(InsertLSN-RedoRecPtr) might or might not be representative of the workload
over a longer time. On the other hand, if a page in a frequently vacuumed
table has an LSN from around the last vacuum (or even before), it should be
frozen, but will appear to be recent in RedoRecPtr based heuristics?
Perhaps we can mix both approaches. We can use the LSN and time of the last
vacuum to establish an LSN->time mapping that's reasonably accurate for a
relation. For infrequently vacuumed tables we can use the time between
checkpoints to establish a *more aggressive* cutoff for freezing then what a
percent-of-time-since-last-vacuum appach would provide. If e.g. a table gets
vacuumed every 100 hours and checkpoint timeout is 1 hour, no realistic
percent-of-time-since-last-vacuum setting will allow freezing, as all dirty
pages will be too new. To allow freezing a decent proportion of those, we
could allow freezing pages that lived longer than ~20%
time-between-recent-checkpoints.
Hm, possibly stupid idea: What about using shared_buffers residency as a
factor? If vacuum had to read in a page to vacuum it, a) we would need read IO
to freeze it later, as we'll soon evict the page via the ringbuffer b)
non-residency indicates the page isn't constantly being modified?
> Also, I think the time between checkpoints actually matters here, because in
> some sense we're looking to get dirty, already-FPI'd pages frozen before
> they get written out, or before a new FPI becomes necessary, and checkpoints
> are one way for the first of those things to happen and the only way for the
> second one to happen.
Intuitively it seems easier to take care of that by checking if an FPI is
needed or not?
I guess we, eventually, might want to also freeze if an FPI would be
generated, if we are reasonably certain that the page isn't going to be
modified again soon (e.g. when table stats indicate effectively aren't any
updates / deletes). Although perhaps it's better to take care of that via
freeze-on-writeout style logic.
I suspect than even for freeze-on-writeout we might end up needing some
heuristics
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
@ 2023-09-25 18:45 ` Robert Haas <[email protected]>
2023-09-25 21:16 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-09-25 18:45 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Melanie Plageman <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Fri, Sep 8, 2023 at 12:07 AM Andres Freund <[email protected]> wrote:
> > Downthread, I proposed using the RedoRecPtr of the latest checkpoint
> > rather than the LSN of the previou vacuum. I still like that idea.
>
> Assuming that "downthread" references
> https://postgr.es/m/CA%2BTgmoYb670VcDFbekjn2YQOKF9a7e-kBFoj2WJF1HtH7YPaWQ%40mail.gmail.com
> could you sketch out the logic you're imagining a bit more?
I'm not exactly sure what the question is here. I mean, it doesn't
quite make sense to just ask whether the page LSN is newer than the
last checkpoint's REDO record, because I think that's basically just
asking whether or not we would need an FPI, and the freeze criteria
that Melanie has been considering incorporate that check in some other
way already. But maybe some variant on that idea is useful - the
distance to the second-most-recent checkpoint, or a multiple or
percentage of the distance to the most-recent checkpoint, or whatever.
> The reason I was thinking of using the "lsn at the end of the last vacuum", is
> that it seems to be more adapative to the frequency of vacuuming.
Yes, but I think it's *too* adaptive. The frequency of vacuuming can
plausibly be multiple times per minute or not even annually. That's
too big a range of variation. The threshold for freezing can vary by
how actively the table or the page is updated, but I don't think it
should vary by six orders of magnitude. Under what theory does it make
sense to say that say "this row in table hasn't been modified in 20
seconds, so let's freeze it, but this row in table B hasn't been
modified in 8 months, so let's not freeze it because it might be
modified again soon"? If you use the LSN at the end of the last
vacuum, you're going to end up making decisions exactly like that,
which seems wrong to me.
> Perhaps we can mix both approaches. We can use the LSN and time of the last
> vacuum to establish an LSN->time mapping that's reasonably accurate for a
> relation. For infrequently vacuumed tables we can use the time between
> checkpoints to establish a *more aggressive* cutoff for freezing then what a
> percent-of-time-since-last-vacuum appach would provide. If e.g. a table gets
> vacuumed every 100 hours and checkpoint timeout is 1 hour, no realistic
> percent-of-time-since-last-vacuum setting will allow freezing, as all dirty
> pages will be too new. To allow freezing a decent proportion of those, we
> could allow freezing pages that lived longer than ~20%
> time-between-recent-checkpoints.
Yeah, I don't know if that's exactly the right idea, but I think it's
in the direction that I was thinking about. I'd even be happy with
100% of the time-between-recent checkpoints, maybe even 200% of
time-between-recent checkpoints. But I think there probably should be
some threshold beyond which we say "look, this doesn't look like it
gets touched that much, let's just freeze it so we don't have to come
back to it again later."
I think part of the calculus here should probably be that when the
freeze threshold is long, the potential gains from making it even
longer are not that much. If I change the freeze threshold on a table
from 1 minute to 1 hour, I can potentially save uselessly freezing
that page 59 times per hour, every hour, forever, if the page always
gets modified right after I touch it. If I change the freeze threshold
on a table from 1 hour to 1 day, I can only save 23 unnecessary
freezes per day. Percentage-wise, the overhead of being wrong is the
same in both cases: I can have as many extra freeze operations as I
have page modifications, if I pick the worst possible times to freeze
in every case. But in absolute terms, the savings in the second
scenario are a lot less. I think if a user is accessing a table
frequently, the overhead of jamming a useless freeze in between every
table access is going to be a lot more noticeable then when the table
is only accessed every once in a while. And I also think it's a lot
less likely that we'll reliably get it wrong. Workloads that touch a
page and then touch it again ~N seconds later can exist for all values
of N, but I bet they're way more common for small values of N than
large ones.
Is there also a need for a similar guard in the other direction? Let's
say that autovacuum_naptime=15s and on some particular table it
triggers every time. I've actually seen this on small queue tables. Do
you think that, in such tables, we should freeze pages that haven't
been modified in 15s?
> Hm, possibly stupid idea: What about using shared_buffers residency as a
> factor? If vacuum had to read in a page to vacuum it, a) we would need read IO
> to freeze it later, as we'll soon evict the page via the ringbuffer b)
> non-residency indicates the page isn't constantly being modified?
This doesn't seem completely stupid, but I fear it would behave
dramatically differently on a workload a little smaller than s_b vs.
one a little larger than s_b, and that doesn't seem good.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-25 18:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-25 21:16 ` Peter Geoghegan <[email protected]>
2023-09-26 15:19 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Peter Geoghegan @ 2023-09-25 21:16 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Andres Freund <[email protected]>; Melanie Plageman <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Mon, Sep 25, 2023 at 11:45 AM Robert Haas <[email protected]> wrote:
> > The reason I was thinking of using the "lsn at the end of the last vacuum", is
> > that it seems to be more adapative to the frequency of vacuuming.
>
> Yes, but I think it's *too* adaptive. The frequency of vacuuming can
> plausibly be multiple times per minute or not even annually. That's
> too big a range of variation.
+1. The risk of VACUUM chasing its own tail seems very real. We want
VACUUM to be adaptive to the workload, not adaptive to itself.
> Yeah, I don't know if that's exactly the right idea, but I think it's
> in the direction that I was thinking about. I'd even be happy with
> 100% of the time-between-recent checkpoints, maybe even 200% of
> time-between-recent checkpoints. But I think there probably should be
> some threshold beyond which we say "look, this doesn't look like it
> gets touched that much, let's just freeze it so we don't have to come
> back to it again later."
The sole justification for any strategy that freezes lazily is that it
can avoid useless freezing when freezing turns out to be unnecessary
-- that's it. So I find it more natural to think of freezing as the
default action, and *not freezing* as the thing that requires
justification. Thinking about it "backwards" like that just seems
simpler to me. There is only one possible reason to not freeze, but
several reasons to freeze.
> I think part of the calculus here should probably be that when the
> freeze threshold is long, the potential gains from making it even
> longer are not that much. If I change the freeze threshold on a table
> from 1 minute to 1 hour, I can potentially save uselessly freezing
> that page 59 times per hour, every hour, forever, if the page always
> gets modified right after I touch it. If I change the freeze threshold
> on a table from 1 hour to 1 day, I can only save 23 unnecessary
> freezes per day.
I totally agree with you on this point. It seems related to my point
about "freezing being the conceptual default action" in VACUUM.
Generally speaking, over-freezing is a problem when we reach the same
wrong conclusion (freeze the page) about the same relatively few pages
over and over -- senselessly repeating those mistakes really adds up
when you're vacuuming the same table very frequently. On the other
hand, under-freezing is typically a problem when we reach the same
wrong conclusion (don't freeze the page) about lots of pages only once
in a very long while. I strongly suspect that there is very little
gray area between the two, across the full spectrum of application
characteristics.
Most individual pages have very little chance of being modified in the
short to medium term. In a perfect world, with a perfect algorithm,
we'd almost certainly be freezing most pages at the earliest
opportunity. It is nevertheless also true that a freezing policy that
is only somewhat more aggressive than this ideal oracle algorithm will
freeze way too aggressive (by at least some measures). There isn't
much of a paradox to resolve here: it's all down to the cadence of
vacuuming, and of rows subject to constant churn.
As you point out, the "same policy" can produce dramatically different
outcomes when you actually consider what the consequences of the
policy are over time, when applied by VACUUM under a variety of
different workload conditions. So any freezing policy must be designed
with due consideration for those sorts of things. If VACUUM doesn't
freeze the page now, then when will it freeze it? For most individual
pages, that time will come (again, pages that benefit from lazy
vacuuming are the exception rather than the rule). Right now, VACUUM
almost behaves as if it thought "that's not my problem, it's a problem
for future me!".
Trying to differentiate between pages that we must not over freeze and
pages that we must not under freeze seems important. Generational
garbage collection (as used by managed VM runtimes) does something
that seems a little like this. It's based on the empirical observation
that "most objects die young". What the precise definition of "young"
really is varies significantly, but that turns out to be less of a
problem than you might think -- it can be derived through feedback
cycles. If you look at memory lifetimes on a logarithmic scale, very
different sorts of applications tend to look like they have remarkably
similar memory allocation characteristics.
> Percentage-wise, the overhead of being wrong is the
> same in both cases: I can have as many extra freeze operations as I
> have page modifications, if I pick the worst possible times to freeze
> in every case. But in absolute terms, the savings in the second
> scenario are a lot less.
Very true.
I'm surprised that there hasn't been any discussion of the absolute
amount of system-wide freeze debt on this thread. If 90% of the pages
in the entire database are frozen, it'll generally be okay if we make
the wrong call by freezing lazily when we shouldn't. This is doubly
true within small to medium sized tables, where the cost of catching
up on freezing cannot ever be too bad (concentrations of unfrozen
pages in one big table are what really hurt users).
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-25 18:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-25 21:16 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
@ 2023-09-26 15:19 ` Andres Freund <[email protected]>
2023-09-26 16:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Andres Freund @ 2023-09-26 15:19 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Robert Haas <[email protected]>; Melanie Plageman <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi,
On 2023-09-25 14:16:46 -0700, Peter Geoghegan wrote:
> On Mon, Sep 25, 2023 at 11:45 AM Robert Haas <[email protected]> wrote:
> I'm surprised that there hasn't been any discussion of the absolute
> amount of system-wide freeze debt on this thread. If 90% of the pages
> in the entire database are frozen, it'll generally be okay if we make
> the wrong call by freezing lazily when we shouldn't. This is doubly
> true within small to medium sized tables, where the cost of catching
> up on freezing cannot ever be too bad (concentrations of unfrozen
> pages in one big table are what really hurt users).
I generally agree with the idea of using "freeze debt" as an input - IIRC I
proposed using the number of frozen pages vs number of pages as the input to
the heuristic in one of the other threads about the topic a while back. I also
think we should read a portion of all-visible-but-not-frozen pages during
non-aggressive vacuums to manage the freeze debt.
However, I'm not at all convinced doing this on a system wide level is a good
idea. Databases do often contain multiple types of workloads at the same
time. E.g., we want to freeze aggressively in a database that has the bulk of
its size in archival partitions but has lots of unfrozen data in an active
partition. And databases have often loads of data that's going to change
frequently / isn't long lived, and we don't want to super aggressively freeze
that, just because it's a large portion of the data.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-25 18:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-25 21:16 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-26 15:19 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
@ 2023-09-26 16:07 ` Peter Geoghegan <[email protected]>
0 siblings, 0 replies; 36+ messages in thread
From: Peter Geoghegan @ 2023-09-26 16:07 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Robert Haas <[email protected]>; Melanie Plageman <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Tue, Sep 26, 2023 at 8:19 AM Andres Freund <[email protected]> wrote:
> However, I'm not at all convinced doing this on a system wide level is a good
> idea. Databases do often contain multiple types of workloads at the same
> time. E.g., we want to freeze aggressively in a database that has the bulk of
> its size in archival partitions but has lots of unfrozen data in an active
> partition. And databases have often loads of data that's going to change
> frequently / isn't long lived, and we don't want to super aggressively freeze
> that, just because it's a large portion of the data.
I didn't say that we should always have most of the data in the
database frozen, though. Just that we can reasonably be more lazy
about freezing the remainder of pages if we observe that most pages
are already frozen. How they got that way is another discussion.
I also think that the absolute amount of debt (measured in physical
units such as unfrozen pages) should be kept under control. But that
isn't something that can ever be expected to work on the basis of a
simple threshold -- if only because autovacuum scheduling just doesn't
work that way, and can't really be adapted to work that way.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
@ 2023-09-27 18:36 ` Melanie Plageman <[email protected]>
2023-09-27 18:57 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-27 19:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
1 sibling, 2 replies; 36+ messages in thread
From: Melanie Plageman @ 2023-09-27 18:36 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Fri, Sep 8, 2023 at 12:07 AM Andres Freund <[email protected]> wrote:
>
> Hi,
>
> On 2023-09-06 10:35:17 -0400, Robert Haas wrote:
> > On Wed, Sep 6, 2023 at 1:09 AM Andres Freund <[email protected]> wrote:
> > > Yea, it'd be useful to have a reasonably approximate wall clock time for the
> > > last modification of a page. We just don't have infrastructure for determining
> > > that. We'd need an LSN->time mapping (xid->time wouldn't be particularly
> > > useful, I think).
> > >
> > > A very rough approximate modification time can be computed by assuming an even
> > > rate of WAL generation, and using the LSN at the time of the last vacuum and
> > > the time of the last vacuum, to compute the approximate age.
> > >
> > > For a while I thought that'd not give us anything that just using LSNs gives
> > > us, but I think it might allow coming up with a better cutoff logic: Instead
> > > of using a cutoff like "page LSN is older than 10% of the LSNs since the last
> > > vacuum of the table", it would allow us to approximate "page has not been
> > > modified in the last 15 seconds" or such. I think that might help avoid
> > > unnecessary freezing on tables with very frequent vacuuming.
> >
> > Yes. I'm uncomfortable with the last-vacuum-LSN approach mostly
> > because of the impact on very frequently vacuumed tables, and
> > secondarily because of the impact on very infrequently vacuumed
> > tables.
> >
> > Downthread, I proposed using the RedoRecPtr of the latest checkpoint
> > rather than the LSN of the previou vacuum. I still like that idea.
>
> Assuming that "downthread" references
> https://postgr.es/m/CA%2BTgmoYb670VcDFbekjn2YQOKF9a7e-kBFoj2WJF1HtH7YPaWQ%40mail.gmail.com
> could you sketch out the logic you're imagining a bit more?
>
>
> > It's a value that we already have, with no additional bookkeeping. It
> > varies over a much narrower range than the interval between vacuums on
> > a table. The vacuum interval could be as short as tens of seconds as
> > long as years, while the checkpoint interval is almost always going to
> > be between a few minutes at the low end and some tens of minutes at
> > the high end, hours at the very most. That's quite appealing.
>
> The reason I was thinking of using the "lsn at the end of the last vacuum", is
> that it seems to be more adapative to the frequency of vacuuming.
>
> One the one hand, if a table is rarely autovacuumed because it is huge,
> (InsertLSN-RedoRecPtr) might or might not be representative of the workload
> over a longer time. On the other hand, if a page in a frequently vacuumed
> table has an LSN from around the last vacuum (or even before), it should be
> frozen, but will appear to be recent in RedoRecPtr based heuristics?
>
>
> Perhaps we can mix both approaches. We can use the LSN and time of the last
> vacuum to establish an LSN->time mapping that's reasonably accurate for a
> relation. For infrequently vacuumed tables we can use the time between
> checkpoints to establish a *more aggressive* cutoff for freezing then what a
> percent-of-time-since-last-vacuum appach would provide. If e.g. a table gets
> vacuumed every 100 hours and checkpoint timeout is 1 hour, no realistic
> percent-of-time-since-last-vacuum setting will allow freezing, as all dirty
> pages will be too new. To allow freezing a decent proportion of those, we
> could allow freezing pages that lived longer than ~20%
> time-between-recent-checkpoints.
One big sticking point for me (brought up elsewhere in this thread, but,
AFAICT never resolved) is that it seems like the fact that we mark pages
all-visible even when not freezing them means that no matter what
heuristic we use, we won't have the opportunity to freeze the pages we
most want to freeze.
Getting to the specific proposal here -- having two+ heuristics and
using them based on table characteristics:
Take the append-only case. Let's say that we had a way to determine if
an insert-only table should use the vacuum LSN heuristic or older than X
checkpoints heuristic. Given the default
autovacuum_vacuum_insert_threshold, every 1000 tuples inserted,
autovacuum will vacuum the table. If you insert to the table often, then
it is a frequently vacuumed table and, if I'm understanding the proposal
correctly, you would want to use a vacuum LSN-based threshold to
determine which pages are old enough to freeze. Using only the
page-older-than-X%-of-LSNs-since-last-vacuum heuristic and assuming no
concurrent workloads, each autovacuum will leave X% of the pages
unfrozen. However, those pages will likely be marked all visible. That
means that the next non-aggressive autovacuum will not even scan those
pages (assuming that run of pages exceeds the skip pages threshold). So
some chunk of pages will get left behind on every autovacuum.
On the other hand, let's say that the table is not frequently inserted
to, so we consider it an infrequently updated table and want to use the
checkpoint-based heuristic. If the page hasn't been modified for
multiple checkpoints, then the next modification will require an FPI.
Because there aren't dead tuples on the page, we can't expect pruning to
emit the FPI. So, freezing the page will always emit an FPI in this
case.
It seems like the ideal freeze pattern for an insert-only table would be
to freeze as soon as the page is full before any checkpoints which could
force you to emit an FPI.
- Melanie
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-27 18:36 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-09-27 18:57 ` Peter Geoghegan <[email protected]>
1 sibling, 0 replies; 36+ messages in thread
From: Peter Geoghegan @ 2023-09-27 18:57 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Wed, Sep 27, 2023 at 11:36 AM Melanie Plageman
<[email protected]> wrote:
> One big sticking point for me (brought up elsewhere in this thread, but,
> AFAICT never resolved) is that it seems like the fact that we mark pages
> all-visible even when not freezing them means that no matter what
> heuristic we use, we won't have the opportunity to freeze the pages we
> most want to freeze.
I think of it as being a bit like an absorbing state from a Markov
chain. It's a perfect recipe for weird path dependent behavior, which
seems to make your task just about impossible. It literally makes
vacuuming more frequently lead to less useful work being performed! It
does so *reliably*, in the simplest of cases.
Ideally, you'd be designing an algorithm for a system where we could
expect to get approximately the desired outcome whenever we have
approximately the right idea. A system where mistakes tend to "cancel
each other out" over time. As long as you have "set all-visible but
don't freeze" behavior as your starting point, then ISTM that your
algorithm almost has to make no mistakes at all to really improve on
what we have right now. As things stand, when your algorithm decides
to not freeze (for pages that are still set all-visible in the VM),
you really can't afford to be wrong. (I think that you get this
already, but it's a point worth emphasizing.)
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-27 18:36 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-09-27 19:45 ` Robert Haas <[email protected]>
2023-09-27 20:03 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-09-27 19:45 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Wed, Sep 27, 2023 at 2:36 PM Melanie Plageman
<[email protected]> wrote:
> It seems like the ideal freeze pattern for an insert-only table would be
> to freeze as soon as the page is full before any checkpoints which could
> force you to emit an FPI.
Yes. So imagine we have two freeze criteria:
1. Do not ever opportunistically freeze pages.
2. Opportunistically freeze pages whenever we can completely freeze the page.
For a table where the same rows are frequently updated or where the
table is frequently truncated, we want to apply (1). For an
insert-only table, we want to apply (2). Maybe I'm all wet here, but
it's starting to seem to me that in almost all other cases, we also
want to apply (2). If that's true, then the only thing we need to do
here is identify the special cases where (1) is correct.
It'd be even better if we could adapt this behavior down to the page
level - imagine a large mostly cold table with a hot tail. In a
perfect world we apply (1) to the tail and (2) to the rest. But this
might be too much of a stretch to actually get right.
> One big sticking point for me (brought up elsewhere in this thread, but,
> AFAICT never resolved) is that it seems like the fact that we mark pages
> all-visible even when not freezing them means that no matter what
> heuristic we use, we won't have the opportunity to freeze the pages we
> most want to freeze.
The only solution to this problem that I can see is what Peter
proposed earlier: if we're not prepared to freeze the page, then don't
mark it all-visible either. This might be the right thing to do, and
if it is, we could even go further and get rid of the two as separate
concepts completely. However, I think it would be OK to leave all of
that to one side for the moment, *especially* if we adopt some
proposal that does a lot more opportunistic freezing than we do
currently. Because then the problem just wouldn't come up nearly as
much as it does now. One patch can't fix everything, and shouldn't
try.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-27 18:36 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-27 19:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-09-27 20:03 ` Andres Freund <[email protected]>
2023-09-27 20:14 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Andres Freund @ 2023-09-27 20:03 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Melanie Plageman <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi,
On 2023-09-27 15:45:06 -0400, Robert Haas wrote:
> > One big sticking point for me (brought up elsewhere in this thread, but,
> > AFAICT never resolved) is that it seems like the fact that we mark pages
> > all-visible even when not freezing them means that no matter what
> > heuristic we use, we won't have the opportunity to freeze the pages we
> > most want to freeze.
>
> The only solution to this problem that I can see is what Peter
> proposed earlier: if we're not prepared to freeze the page, then don't
> mark it all-visible either. This might be the right thing to do, and
> if it is, we could even go further and get rid of the two as separate
> concepts completely.
I suspect that medium term the better approach would be to be much more
aggressive about setting all-visible, including as part of page-level
visibility checks, and to deal with the concern of vacuum not processing such
pages soon by not just looking at unmarked pages, but also a portion of the
all-visible-but-not-frozen pages (the more all-visible-but-not-frozen pages
there are, the more of them each vacuum should process). I think all-visible
is too important for index only scans, for us to be able to remove it, or
delay setting it until freezing makes sense.
My confidence in my gut feeling isn't all that high here, though.
> However, I think it would be OK to leave all of
> that to one side for the moment, *especially* if we adopt some
> proposal that does a lot more opportunistic freezing than we do
> currently. Because then the problem just wouldn't come up nearly as
> much as it does now. One patch can't fix everything, and shouldn't
> try.
Agreed.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-27 18:36 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-27 19:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-27 20:03 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
@ 2023-09-27 20:14 ` Peter Geoghegan <[email protected]>
0 siblings, 0 replies; 36+ messages in thread
From: Peter Geoghegan @ 2023-09-27 20:14 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Robert Haas <[email protected]>; Melanie Plageman <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
On Wed, Sep 27, 2023 at 1:03 PM Andres Freund <[email protected]> wrote:
> I suspect that medium term the better approach would be to be much more
> aggressive about setting all-visible, including as part of page-level
> visibility checks, and to deal with the concern of vacuum not processing such
> pages soon by not just looking at unmarked pages, but also a portion of the
> all-visible-but-not-frozen pages (the more all-visible-but-not-frozen pages
> there are, the more of them each vacuum should process). I think all-visible
> is too important for index only scans, for us to be able to remove it, or
> delay setting it until freezing makes sense.
>
> My confidence in my gut feeling isn't all that high here, though.
I think that this is a bad idea. I see two main problems with
"splitting the difference" like this:
1. If we randomly scan some all-visible pages in non-aggressive
VACUUMs, then we're sure to get FPIs in order to be able to freeze.
As a general rule, I think that we're better of gambling against
future FPIs, and then pulling back if we go too far. The fact that we
went one VACUUM operation without the workload unsetting an
all-visible page isn't that much of a signal about what might happen
to that page.
2. Large tables (i.e. the tables where it really matters) just don't
have that many VACUUM operations, relative to everything else. Who
says we'll get more than one opportunity per page with these tables,
even with this behavior of scanning all-visible pages in
non-aggressive VACUUMs?
Big append-only tables simply won't get the opportunity to catch up in
the next non-aggressive VACUUM if there simply isn't one.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-08-28 19:09 ` Peter Geoghegan <[email protected]>
2023-08-28 20:17 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Peter Geoghegan @ 2023-08-28 19:09 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
On Mon, Aug 28, 2023 at 7:00 AM Melanie Plageman
<[email protected]> wrote:
> To do this, we need to save that insert LSN
> somewhere. In the attached WIP patch, I saved it in the table stats, for
> now -- knowing that those are not crash-safe.
What patch? You didn't actually attach one.
> Other discarded heuristic ideas included comparing the next transaction
> ID at the end of the vacuum of a relation to the visibility cutoff xid
> in the page -- but that wasn't effective for freezing data from bulk
> loads.
I've long emphasized the importance of designs that just try to avoid
disaster. With that in mind, I wonder: have you thought about
conditioning page freezing on whether or not there are already some
frozen tuples on the page? You could perhaps give some weight to
whether or not the page already has at least one or two preexisting
frozen tuples when deciding on whether to freeze it once again now.
You'd be more eager about freezing pages that have no frozen tuples
whatsoever, compared to what you'd do with an otherwise equivalent
page that has no unfrozen tuples.
Small mistakes are inevitable here. They have to be okay. What's not
okay is a small mistake that effectively becomes a big mistake because
it gets repeated across each VACUUM operation, again and again,
ceaselessly. You can probably be quite aggressive about freezing, to
good effect -- provided you can be sure that the downside when it
turns out to have been a bad idea is self-limiting, in whatever way.
Making more small mistakes might actually be a good thing --
especially if it can dramatically lower the chances of ever making any
really big mistakes.
VACUUM is not a passive observer of the system. It's just another
component in the system. So what VACUUM sees in the table depends in
no small part on what previous VACUUMs actually did. It follows that
VACUUM should be concerned about how what it does might either help or
hinder future VACUUMs. My preexisting frozen tuples suggestion is
really just an example of how that principle might be applied. Many
variations on the same general idea are possible.
There are already various extremely weird feedback loops where what
VACUUM did last time affects what it'll do this time. These are
vicious circles. So ISTM that there is a lot to be said for disrupting
those vicious circles, and even deliberately engineering heuristics
that have the potential to create virtuous circles for the right
workload.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 19:09 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
@ 2023-08-28 20:17 ` Robert Haas <[email protected]>
2023-08-28 21:05 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Robert Haas @ 2023-08-28 20:17 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 3:09 PM Peter Geoghegan <[email protected]> wrote:
> I've long emphasized the importance of designs that just try to avoid
> disaster. With that in mind, I wonder: have you thought about
> conditioning page freezing on whether or not there are already some
> frozen tuples on the page? You could perhaps give some weight to
> whether or not the page already has at least one or two preexisting
> frozen tuples when deciding on whether to freeze it once again now.
> You'd be more eager about freezing pages that have no frozen tuples
> whatsoever, compared to what you'd do with an otherwise equivalent
> page that has no unfrozen tuples.
I'm sure this could be implemented, but it's unclear to me why you
would expect it to perform well. Freezing a page that has no frozen
tuples yet isn't cheaper than freezing one that does, so for this idea
to be a win, the presence of frozen tuples on the page would have to
be a signal that the page is likely to be modified again in the near
future. In general, I don't see any reason why we should expect that
to be the case. One could easily construct a workload where it is the
case -- for instance, set up one table T1 where 90% of the tuples are
repeatedly updated and the other 10% are never touched, and another
table T2 that is insert-only. Once frozen, the never-updated tuples in
T1 become sentinels that we can use to know that the table isn't
insert-only. But I don't think that's very interesting: you can
construct a test case like this for any proposed criterion, just by
structuring the test workload so that whatever criterion is being
tested is a perfect predictor of whether the page will be modified
soon.
What really matters here is finding a criterion that is likely to
perform well in general, on a test case not known to us beforehand.
This isn't an entirely feasible goal, because just as you can
construct a test case where any given criterion performs well, so you
can also construct one where any given criterion performs poorly. But
I think a rule that has a clear theory of operation must be preferable
to one that doesn't. The theory that Melanie and Andres are advancing
is that a page that has been modified recently (in insert-LSN-time) is
more likely to be modified again soon than one that has not i.e. the
near future will be like the recent past. I'm not sure what the theory
behind the rule you propose here might be; if you articulated it
somewhere in your email, I seem to have missed it.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 19:09 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 20:17 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
@ 2023-08-28 21:05 ` Peter Geoghegan <[email protected]>
2023-08-28 23:15 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Peter Geoghegan @ 2023-08-28 21:05 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 1:17 PM Robert Haas <[email protected]> wrote:
> I'm sure this could be implemented, but it's unclear to me why you
> would expect it to perform well. Freezing a page that has no frozen
> tuples yet isn't cheaper than freezing one that does, so for this idea
> to be a win, the presence of frozen tuples on the page would have to
> be a signal that the page is likely to be modified again in the near
> future. In general, I don't see any reason why we should expect that
> to be the case.
What I've described in a scheme for deciding to not freeze where that
would usually happen -- a scheme for *vetoing* page freezing -- rather
than a scheme for deciding to freeze. On its own, what I suggested
cannot help at all. It assumes a world in which we're already deciding
to freeze much more frequently, based on whatever other criteria. It's
intended to complement something like the LSN scheme.
> What really matters here is finding a criterion that is likely to
> perform well in general, on a test case not known to us beforehand.
> This isn't an entirely feasible goal, because just as you can
> construct a test case where any given criterion performs well, so you
> can also construct one where any given criterion performs poorly. But
> I think a rule that has a clear theory of operation must be preferable
> to one that doesn't. The theory that Melanie and Andres are advancing
> is that a page that has been modified recently (in insert-LSN-time) is
> more likely to be modified again soon than one that has not i.e. the
> near future will be like the recent past.
I don't think that it's all that useful on its own. You just cannot
ignore the fact that the choice to not freeze now doesn't necessarily
mean that you get to rereview that choice in the near future.
Particularly with large tables, the opportunities to freeze at all are
few and far between -- if for no other reason than the general design
of autovacuum scheduling. Worse still, any unfrozen all-visible pages
can just accumulate as all-visible pages, until the next aggressive
VACUUM happens whenever. How can that not be extremely important?
That isn't an argument against a scheme that uses LSNs (many kinds of
information might be weighed) -- it's an argument in favor of paying
attention to the high level cadence of VACUUM. That much seems
essential. I think that there might well be room for having several
complementary schemes like the LSN scheme. Or one big scheme that
weighs multiple factors together, if you prefer. That all seems
basically reasonable to me.
Adaptive behavior is important with something as complicated as this.
Adaptive schemes all seem to involve trial and error. The cost of
freezing too much is relatively well understood, and can be managed
sensibly. So we should err in that direction -- a direction that is
relatively easy to understand, to notice, and to pull back from having
gone too far. Putting off freezing for a very long time is a source of
much of the seemingly intractable complexity in this area.
Another way of addressing that is getting rid of aggressive VACUUM as
a concept. But I'm not going to revisit that topic now, or likely
ever.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 19:09 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 20:17 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 21:05 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
@ 2023-08-28 23:15 ` Melanie Plageman <[email protected]>
2023-08-29 01:43 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 36+ messages in thread
From: Melanie Plageman @ 2023-08-28 23:15 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 5:06 PM Peter Geoghegan <[email protected]> wrote:
>
> On Mon, Aug 28, 2023 at 1:17 PM Robert Haas <[email protected]> wrote:
> > I'm sure this could be implemented, but it's unclear to me why you
> > would expect it to perform well. Freezing a page that has no frozen
> > tuples yet isn't cheaper than freezing one that does, so for this idea
> > to be a win, the presence of frozen tuples on the page would have to
> > be a signal that the page is likely to be modified again in the near
> > future. In general, I don't see any reason why we should expect that
> > to be the case.
>
> What I've described in a scheme for deciding to not freeze where that
> would usually happen -- a scheme for *vetoing* page freezing -- rather
> than a scheme for deciding to freeze. On its own, what I suggested
> cannot help at all. It assumes a world in which we're already deciding
> to freeze much more frequently, based on whatever other criteria. It's
> intended to complement something like the LSN scheme.
I like the direction of this idea. It could avoid repeatedly freezing
a page that is being modified over and over. I tried it out with a
short-running version of workload I (approximating a work queue) --
and it prevents unnecessary freezing.
The problem with this criterion is that if you freeze a page and then
ever modify it again -- even once, vacuum will not be allowed to
freeze it again until it is forced to. Perhaps we could use a very
strict LSN cutoff for pages which contain any frozen tuples -- for
example: only freeze a page containing a mix of frozen and unfrozen
tuples if it has not been modified since before the last vacuum of the
relation ended.
- Melanie
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 19:09 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 20:17 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 21:05 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 23:15 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-08-29 01:43 ` Peter Geoghegan <[email protected]>
0 siblings, 0 replies; 36+ messages in thread
From: Peter Geoghegan @ 2023-08-29 01:43 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers; Andres Freund <[email protected]>; Jeff Davis <[email protected]>
On Mon, Aug 28, 2023 at 4:15 PM Melanie Plageman
<[email protected]> wrote:
> On Mon, Aug 28, 2023 at 5:06 PM Peter Geoghegan <[email protected]> wrote:
> > What I've described in a scheme for deciding to not freeze where that
> > would usually happen -- a scheme for *vetoing* page freezing -- rather
> > than a scheme for deciding to freeze. On its own, what I suggested
> > cannot help at all. It assumes a world in which we're already deciding
> > to freeze much more frequently, based on whatever other criteria. It's
> > intended to complement something like the LSN scheme.
>
> I like the direction of this idea. It could avoid repeatedly freezing
> a page that is being modified over and over. I tried it out with a
> short-running version of workload I (approximating a work queue) --
> and it prevents unnecessary freezing.
I see a lot of value in it as an enabler of freezing at the earliest
opportunity, which is usually the way to go.
> The problem with this criterion is that if you freeze a page and then
> ever modify it again -- even once, vacuum will not be allowed to
> freeze it again until it is forced to.
Right. Like you, I was thinking of something that dampened VACUUM's
newfound enthusiasm for freezing, in whatever way -- not a discrete
rule. A mechanism with a sense of proportion about what it meant for
the page to look a certain way. The strength of the signal would
perhaps be highest (i.e. most discouraging of further freezing) on a
page that has only a few relatively recent unfrozen tuples. XID age
could actually be useful here!
> Perhaps we could use a very
> strict LSN cutoff for pages which contain any frozen tuples -- for
> example: only freeze a page containing a mix of frozen and unfrozen
> tuples if it has not been modified since before the last vacuum of the
> relation ended.
You might also need to account for things like interactions with the
FSM. If the new unfrozen tuple packs the page to the brim, and the new
row is from an insert, then maybe the mechanism shouldn't dampen our
enthusiasm for freezing the page at all. Similarly, on a page that has
no unfrozen tuples at all just yet, it would make sense for the
algorithm to be most enthusiastic about freezing those pages that can
fit no more tuples. Perhaps we'd be willing to accept the cost of an
extra FPI with such a page, for example.
It will take time to validate these sorts of ideas thoroughly, of
course. I agree with what you said upthread about it also being a
question of values and priorities.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
@ 2023-10-12 00:43 ` Andres Freund <[email protected]>
2023-10-12 14:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
1 sibling, 1 reply; 36+ messages in thread
From: Andres Freund @ 2023-10-12 00:43 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Peter Geoghegan <[email protected]>; Jeff Davis <[email protected]>; Robert Haas <[email protected]>
Hi,
Robert, Melanie and I spent an evening discussing this topic around
pgconf.nyc. Here are, mildly revised, notes from that:
First a few random points that didn't fit with the sketch of an approach
below:
- Are unlogged tables a problem for using LSN based heuristics for freezing?
We concluded, no, not a problem, because aggressively freezing does not
increase overhead meaningfully, as we would already dirty both the heap and VM
page to set the all-visible flag.
- "Unfreezing" pages that were frozen hours / days ago aren't too bad and can
be desirable.
The main thing we are worried about is repeated freezing / unfreezing of
pages within a relatively short time period.
- Computing an average "modification distance" as I (Andres) proposed efor
each page is complicated / "fuzzy"
The main problem is that it's not clear how to come up with a good number
for workloads that have many more inserts into new pages than modifications
of existing pages.
It's also hard to use average for this kind of thing, e.g. in cases where
new pages are frequently updated, but also some old data is updated, it's
easy for the updates to the old data to completely skew the average, even
though that shouldn't prevent us from freezing.
- We also discussed an idea by Robert to track the number of times we need to
dirty a page when unfreezing and to compare that to the number of pages
dirtied overall (IIRC), but I don't think we really came to a conclusion
around that - and I didn't write down anything so this is purely from
memory.
A rough sketch of a freezing heuristic:
- We concluded that to intelligently control opportunistic freezing we need
statistics about the number of freezes and unfreezes
- We should track page freezes / unfreezes in shared memory stats on a
per-relation basis
- To use such statistics to control heuristics, we need to turn them into
rates. For that we need to keep snapshots of absolute values at certain
times (when vacuuming), allowing us to compute a rate.
- If we snapshot some stats, we need to limit the amount of data that occupies
- evict based on wall clock time (we don't care about unfreezing pages
frozen a month ago)
- "thin out" data when exceeding limited amount of stats per relation
using random sampling or such
- need a smarter approach than just keeping N last vacuums, as there are
situations where a table is (auto-) vacuumed at a high frequency
- only looking at recent-ish table stats is fine, because we
- a) don't want to look at too old data, as we need to deal with changing
workloads
- b) if there aren't recent vacuums, falsely freezing is of bounded cost
- shared memory stats being lost on crash-restart/failover might be a problem
- we certainly don't want to immediate store these stats in a table, due
to the xid consumption that'd imply
- Attributing "unfreezes" to specific vacuums would be powerful:
- "Number of pages frozen during vacuum" and "Number of pages unfrozen that
were frozen during the same vacuum" provides numerator / denominator for
an "error rate"
- We can perform this attribution by comparing the page LSN with recorded
start/end LSNs of recent vacuums
- If the freezing error rate of recent vacuums is low, freeze more
aggressively. This is important to deal with insert mostly workloads.
- If old data is "unfrozen", that's fine, we can ignore such unfreezes when
controlling "freezing aggressiveness"
- Ignoring unfreezing of old pages is important to e.g. deal with
workloads that delete old data
- This approach could provide "goals" for opportunistic freezing in a
somewhat understandable way. E.g. aiming to rarely unfreeze data that has
been frozen within 1h/1d/...
Around this point my laptop unfortunately ran out of battery. Possibly the
attendees of this mini summit also ran out of steam (and tea).
We had a few "disagreements" or "unresolved issues":
- How aggressive should we be when we have no stats?
- Should the freezing heuristic take into account whether freezing would
require an FPI? Or whether page was not in s_b, or ...
I likely mangled this substantially, both when taking notes during the lively
discussion, and when revising them to make them a bit more readable.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Eager page freeze criteria clarification
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-10-12 00:43 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
@ 2023-10-12 14:26 ` Robert Haas <[email protected]>
0 siblings, 0 replies; 36+ messages in thread
From: Robert Haas @ 2023-10-12 14:26 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>; Jeff Davis <[email protected]>
Thanks for these notes.
On Wed, Oct 11, 2023 at 8:43 PM Andres Freund <[email protected]> wrote:
> - We also discussed an idea by Robert to track the number of times we need to
> dirty a page when unfreezing and to compare that to the number of pages
> dirtied overall (IIRC), but I don't think we really came to a conclusion
> around that - and I didn't write down anything so this is purely from
> memory.
See http://postgr.es/m/CA+TgmoYcWisxLBL-pXu13OevThLOXm20oJqjNRZtKkhXsY92XA@mail.gmail.com
for my on-list discussion of this.
> - Attributing "unfreezes" to specific vacuums would be powerful:
>
> - "Number of pages frozen during vacuum" and "Number of pages unfrozen that
> were frozen during the same vacuum" provides numerator / denominator for
> an "error rate"
I want to highlight the denominator issue here. I think we all have
the intuition that if we count the number of times that a
recently-frozen page gets unfrozen, and that's a big number, that's
bad, and a sign that we need to freeze less aggressively. But a lot of
the struggle has been around answering the question "big compared to
what". A lot of the obvious candidates fail to behave nicely in corner
cases, as discussed in the above email. I think this is one of the
better candidates so far proposed, possibly the best.
> - This approach could provide "goals" for opportunistic freezing in a
> somewhat understandable way. E.g. aiming to rarely unfreeze data that has
> been frozen within 1h/1d/...
This strikes me as another important point. Making the behavior
understandable to users is going to be important, because sometimes
whatever system we might craft will misbehave, and then people are
going to need to be able to understand why it's misbehaving and how to
tune/fix it so it works.
> Around this point my laptop unfortunately ran out of battery. Possibly the
> attendees of this mini summit also ran out of steam (and tea).
When the tea is gone, there's little point in continuing.
> I likely mangled this substantially, both when taking notes during the lively
> discussion, and when revising them to make them a bit more readable.
I think it's quite a good summary, actually. Thanks!
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 36+ messages in thread
end of thread, other threads:[~2023-10-12 14:26 UTC | newest]
Thread overview: 36+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-13 05:11 [PATCH v5 2/5] Introduce a new syntax to add/drop publications Japin Li <[email protected]>
2023-07-28 15:12 Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 18:59 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-07-28 19:27 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-07-28 19:45 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 14:00 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-28 16:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 20:30 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 14:22 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-01 19:34 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-02 01:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-06 14:46 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 16:20 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-06 20:21 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-23 19:53 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-24 16:45 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-25 20:19 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-06 05:09 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-06 14:35 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-08 04:07 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-25 18:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-25 21:16 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-26 15:19 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-26 16:07 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-27 18:36 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-09-27 18:57 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-09-27 19:45 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-09-27 20:03 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-09-27 20:14 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 19:09 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 20:17 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
2023-08-28 21:05 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-08-28 23:15 ` Re: Eager page freeze criteria clarification Melanie Plageman <[email protected]>
2023-08-29 01:43 ` Re: Eager page freeze criteria clarification Peter Geoghegan <[email protected]>
2023-10-12 00:43 ` Re: Eager page freeze criteria clarification Andres Freund <[email protected]>
2023-10-12 14:26 ` Re: Eager page freeze criteria clarification Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox