public inbox for [email protected]
help / color / mirror / Atom feedFrom: Frédéric Yhuel <[email protected]>
To: Nathan Bossart <[email protected]>
To: wenhui qiu <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Imseih (AWS), Sami <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: Michael Banck <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: New GUC autovacuum_max_threshold ?
Date: Wed, 13 Nov 2024 11:03:25 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <Zy-G7u43r1siVkQ6@nathan>
References: <CA+TgmoYF3HqEUArbsqtCtP7YMJ62G3U8W1mSmZFZoCK8uBSi9g@mail.gmail.com>
<[email protected]>
<CA+TgmoaER+O40PA_CB8eSKm7KT37wDT+-y_mnsWXb9-Keivu1Q@mail.gmail.com>
<ZnD509Q1jROYKhz_@nathan>
<[email protected]>
<ZrPprRLBzi1YUBLn@nathan>
<[email protected]>
<CAGjGUALGtQ8MoshY6tEUPiAOjWNefBzq61gZ_5Dx-Uai1cC39Q@mail.gmail.com>
<Zy5N-M2jPIYzPdku@nathan>
<CAGjGUALGwH9LJUxe+ZbjnW_Cg4B4qyFrXFv+oWYCYnLcwggBJQ@mail.gmail.com>
<Zy-G7u43r1siVkQ6@nathan>
On 11/9/24 16:59, Nathan Bossart wrote:
> AFAICT the main advantage of these formulas is that you don't need another
> GUC, but they also makes the existing ones more difficult to configure.
I wouldn't say that's the main advantage. It doesn't seem very clean to
me to cap to a fixed value. Because you could take Robert's
demonstration with a bigger table, and come to the same conclusion:
Let's compare the current situation to the situation post-Nathan's-patch
with a cap of 100M. Consider a table 100 times larger than the one of
Robert's previous example, so pgbench scale factor 2_560_000, size on
disk 32TB.
Currently, that table will be vacuumed for bloat when the number of
dead tuples exceeds 20% of the table size, because that's the default
value of autovacuum_vacuum_scale_factor. The table has 256 billion
tuples, so that means that we're going to vacuum it when there are
more than 51 billion dead tuples. Post-patch, we will vacuum when we
have 100 million dead tuples. Suppose a uniform workload that slowly
updates rows in the table. If we were previously autovacuuming the
table once per day (1440 minutes) we're now going to try to vacuum it
almost every minute (1440 minutes / 512 = 168 seconds).
(compare with every 55 min with my formula)
Of course, this a theoretical example that is probably unrealistic. I
don't know, really. I don't know if Robert's example was realistic in
the first place.
In any case, we should do the tests that Robert suggested and/or come up
with a good mathematical model, because we are in the dark at the moment.
> Plus, there's no way to go back to the existing behavior.
I think we should indeed provide a retro-compatible behaviour (so maybe
another GUC after all).
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: New GUC autovacuum_max_threshold ?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox