Thread: pgsql: Add per-index stats information in verbose logs of autovacuum
Add per-index stats information in verbose logs of autovacuum Once a relation's autovacuum is completed, the logs include more information about this relation state if the threshold of log_autovacuum_min_duration (or its relation option) is reached, with for example contents about the statistics of the VACUUM operation for the relation, WAL and system usage. This commit adds more information about the statistics of the relation's indexes, with one line of logs generated for each index. The index stats were already calculated, but not printed in the context of autovacuum yet. While on it, some refactoring is done to keep track of the index statistics directly within LVRelStats, simplifying some routines related to parallel VACUUMs. Author: Masahiko Sawada Reviewed-by: Michael Paquier, Euler Taveira Discussion: https://postgr.es/m/CAD21AoAy6SxHiTivh5yAPJSUE4S=QRPpSZUdafOSz0R+fRcM6Q@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/5aed6a1fc214913de9ac69c1717dc64a2483e16d Modified Files -------------- src/backend/access/heap/vacuumlazy.c | 133 +++++++++++++++++++++-------------- 1 file changed, 81 insertions(+), 52 deletions(-)
On Mon, Mar 22, 2021 at 9:28 PM Michael Paquier <michael@paquier.xyz> wrote:
> Add per-index stats information in verbose logs of autovacuum
I think that this is very helpful -- thanks for working on it.
However, it seems to me that "%u remain" is misleading. I am referring
to this line of output:
index "dups_idx": pages: 1431 remain, 1343 newly deleted, 1427
currently deleted, 84 reusable
It suggests that the index file can shrink. As if the remaining pages
are left now that the pages we deleted have been returned to the OS. I
think that this same line of output should look like this instead:
index "dups_idx": 1431 pages in total, 1343 newly deleted,
1427 currently deleted, 84 reusable
FWIW this is roughly the corresponding point of equivalent VACUUM
VERBOSE output:
INFO: index "dups_idx" now contains 0 row versions in 1431 pages
To be clear, I agree that it would not be sensible to make the
log_autovacuum_min_duration output 100% consistent with VACUUM
VERBOSE. I just think that "%u remain" is misleading. It's just that
one detail.
--
Peter Geoghegan
On Mon, Mar 22, 2021 at 10:36:03PM -0700, Peter Geoghegan wrote: > It suggests that the index file can shrink. As if the remaining pages > are left now that the pages we deleted have been returned to the OS. I > think that this same line of output should look like this instead: > > index "dups_idx": 1431 pages in total, 1343 newly deleted, > 1427 currently deleted, 84 reusable I like the consistency of using "pages:" at the beginning of the line as all those fields refer to pages, and your formulation makes inclear what the second, third and fourth items on the line refer to. What's newly-deleted? Tuples? Pages? Pages, obviously, if you know what this is about. > To be clear, I agree that it would not be sensible to make the > log_autovacuum_min_duration output 100% consistent with VACUUM > VERBOSE. I just think that "%u remain" is misleading. It's just that > one detail. Saying that, I like your suggestion of using "total" or "in total" instead of "remain" for the first item. -- Michael
Attachment
On Mon, Mar 22, 2021 at 11:22 PM Michael Paquier <michael@paquier.xyz> wrote: > > To be clear, I agree that it would not be sensible to make the > > log_autovacuum_min_duration output 100% consistent with VACUUM > > VERBOSE. I just think that "%u remain" is misleading. It's just that > > one detail. > > Saying that, I like your suggestion of using "total" or "in total" > instead of "remain" for the first item. I'm okay with preserving "pages:" if you prefer it that way. It's really just the word "remain" -- it reminds me of the old "pages_removed" field. That used to be a thing that we showed back when old-style VACUUM existed and could shrink the index -- this is of course no longer possible. The message should clearly convey that the first number shown is simply the total size of the index. -- Peter Geoghegan
On Mon, Mar 22, 2021 at 11:34:47PM -0700, Peter Geoghegan wrote: > I'm okay with preserving "pages:" if you prefer it that way. It's > really just the word "remain" -- it reminds me of the old > "pages_removed" field. That used to be a thing that we showed back > when old-style VACUUM existed and could shrink the index -- this is of > course no longer possible. > > The message should clearly convey that the first number shown is > simply the total size of the index. No arguments against that, makes sense. I have applied something that changes "remain" to "in total" for clarity. Thanks! -- Michael
Attachment
On Tue, Mar 23, 2021 at 5:38 PM Michael Paquier <michael@paquier.xyz> wrote: > No arguments against that, makes sense. I have applied something that > changes "remain" to "in total" for clarity. Thanks! Thanks! -- Peter Geoghegan