public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim Nasby <[email protected]>
To: Andres Freund <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations
Date: Thu, 14 Apr 2022 18:19:24 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAH2-WzkvYTo4rbUi_izHOvcHNw78Ur9xLPGvj1PmDgt-csc8QQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAH2-WzmN4qh04TcyNph8=1awdUN-nzza_6y+TRbEo-ECiCDV0w@mail.gmail.com>
<[email protected]>
<CAH2-WzkPc56XrkGqWD-zto=c_YoJCv6OD=UXqxtXRbKdFejMaw@mail.gmail.com>
<[email protected]>
<CAH2-WzmP0awh8d2+dPd7Umq+HyJ-28HxjELzcPu7gO0G7VAvcQ@mail.gmail.com>
<CAH2-WzkwUx++AcDByZV+TvoZddbWfvSEVGhrc4roQVGVqdHJ=w@mail.gmail.com>
<[email protected]>
On 4/3/22 12:05 PM, Andres Freund wrote:
> While I was writing the above I, again, realized that it'd be awfully nice to
> have some accumulated stats about (auto-)vacuum's effectiveness. For us to get
> feedback about improvements more easily and for users to know what aspects
> they need to tune.
>
> Knowing how many times a table was vacuumed doesn't really tell that much, and
> requiring to enable log_autovacuum_min_duration and then aggregating those
> results is pretty painful (and version dependent).
>
> If we just collected something like:
> - number of heap passes
> - time spent heap vacuuming
> - number of index scans
> - time spent index vacuuming
> - time spent delaying
The number of passes would let you know if maintenance_work_mem is too
small (or to stop killing 187M+ tuples in one go). The timing info would
give you an idea of the impact of throttling.
> - percentage of non-yet-removable vs removable tuples
This'd give you an idea how bad your long-running-transaction problem is.
Another metric I think would be useful is the average utilization of
your autovac workers. No spare workers means you almost certainly have
tables that need vacuuming but have to wait. As a single number, it'd
also be much easier for users to understand. I'm no stats expert, but
one way to handle that cheaply would be to maintain an
engineering-weighted-mean of the percentage of autovac workers that are
in use at the end of each autovac launcher cycle (though that would
probably not work great for people that have extreme values for launcher
delay, or constantly muck with launcher_delay).
>
> it'd start to be a heck of a lot easier to judge how well autovacuum is
> coping.
>
> If we tracked the related pieces above in the index stats (or perhaps
> additionally there), it'd also make it easier to judge the cost of different
> indexes.
>
> - Andres
>
>
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations
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