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: /messages/by-id/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
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