Reporting xmin from VACUUMs
We've added xmin info to pg_stat_activity and pg_stat_replication, but
VACUUM doesn't yet report which xmin value it used when it ran.
Small patch to add this info to VACUUM output.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
vacuum_report_oldestxmin.v1.patchapplication/octet-stream; name=vacuum_report_oldestxmin.v1.patchDownload
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 005440e..5d47f16 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -380,10 +380,11 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
vacrelstats->pinskipped_pages,
vacrelstats->frozenskipped_pages);
appendStringInfo(&buf,
- _("tuples: %.0f removed, %.0f remain, %.0f are dead but not yet removable\n"),
+ _("tuples: %.0f removed, %.0f remain, %.0f are dead but not yet removable, oldest xmin: %u\n"),
vacrelstats->tuples_deleted,
vacrelstats->new_rel_tuples,
- vacrelstats->new_dead_tuples);
+ vacrelstats->new_dead_tuples,
+ OldestXmin);
appendStringInfo(&buf,
_("buffer usage: %d hits, %d misses, %d dirtied\n"),
VacuumPageHit,
@@ -1329,8 +1330,8 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
*/
initStringInfo(&buf);
appendStringInfo(&buf,
- _("%.0f dead row versions cannot be removed yet.\n"),
- nkeep);
+ _("%.0f dead row versions cannot be removed yet, oldest xmin: %u\n"),
+ nkeep, OldestXmin);
appendStringInfo(&buf, _("There were %.0f unused item pointers.\n"),
nunused);
appendStringInfo(&buf, ngettext("Skipped %u page due to buffer pins.\n",
On Fri, Feb 10, 2017 at 3:30 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
We've added xmin info to pg_stat_activity and pg_stat_replication, but
VACUUM doesn't yet report which xmin value it used when it ran.Small patch to add this info to VACUUM output.
It seems sensible to me to do something like this. We already report
a lot of other fine details, so what's one more? And it could be
useful.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas wrote:
On Fri, Feb 10, 2017 at 3:30 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
We've added xmin info to pg_stat_activity and pg_stat_replication, but
VACUUM doesn't yet report which xmin value it used when it ran.Small patch to add this info to VACUUM output.
It seems sensible to me to do something like this. We already report
a lot of other fine details, so what's one more? And it could be
useful.
Yeah, I can see how this can be useful to debug some hard-to-track
problems. The patch looks sensible to me.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Feb 11, 2017 at 7:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Feb 10, 2017 at 3:30 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
We've added xmin info to pg_stat_activity and pg_stat_replication, but
VACUUM doesn't yet report which xmin value it used when it ran.Small patch to add this info to VACUUM output.
+1
It seems sensible to me to do something like this. We already report
a lot of other fine details, so what's one more? And it could be
useful.
Also, I've been proposing to report the number of skipped the frozen
pages even in manual vacuum verbose log.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Accidentally sent o--list.
On 2/14/17 12:57 PM, Jim Nasby wrote:
On 2/13/17 12:36 AM, Masahiko Sawada wrote:
It seems sensible to me to do something like this. We already report
a lot of other fine details, so what's one more? And it could be
useful.Also, I've been proposing to report the number of skipped the frozen
pages even in manual vacuum verbose log.Yes, please. We now do that for pages skipped because we couldn't get
the cleanup lock and skipping due to all-frozen is certainly more
user-visible than that.ISTR previous discussion of allowing more stats files; if that happened
I think having stats that were dedicated to (auto)vacuum would be very
useful. That's clearly a lot more work though.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: a30f9151-afcd-9f48-e2c0-4a50dddee5d0@BlueTreble.com
Jim Nasby wrote:
ISTR previous discussion of allowing more stats files; if that happened
I think having stats that were dedicated to (auto)vacuum would be very
useful. That's clearly a lot more work though.
What?
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/15/17 12:05 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
ISTR previous discussion of allowing more stats files; if that happened
I think having stats that were dedicated to (auto)vacuum would be very
useful. That's clearly a lot more work though.What?
There's a bunch of information reported by vacuum logging but not in
pg_stat*, such as all-visible/frozen skipping, unable to get cleanup
lock, last freeze scan, times autovac has been interrupted. There's been
resistance in the past to further bloating the existing stats files, but
if we allowed more than one stats file per database that bloat would be
less of a concern (since vacuum stats will see far less update traffic
than the main relation stats).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Feb 18, 2017 at 12:30 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
What?
There's a bunch of information reported by vacuum logging but not in
pg_stat*, such as all-visible/frozen skipping, unable to get cleanup lock,
last freeze scan, times autovac has been interrupted. There's been
resistance in the past to further bloating the existing stats files, but if
we allowed more than one stats file per database that bloat would be less of
a concern (since vacuum stats will see far less update traffic than the main
relation stats).
This is the kind of information that you really want to see once per
autovac, though, not just the most recent autovac or some kind of
cumulative total. Knowing that I've done 301 index scans in my last
300 vacuums is not nearly as useful as knowing which autovacuum did 2
index scans and what exactly was going on with that vacuum. So I'm
not sure including this sort of thing in the stats files would be very
useful, or at least you'd want to think carefully about how to do it.
As far as bloating the stats file is concerned, the big problem there
is that our current design for the stats file requires rewriting the
entire thing any time we want to update even a single byte of data.
We could fix that by splitting up the files more so that they are
smaller and faster to rewrite, but we could also fix it by coming up
with a way of rewriting just one part of a file instead of the whole
thing, or we could think about storing it in DSM so that you don't
have to rewrite anything at all. I think that last option is worth
some serious study now that we have DSA, but it's currently not very
high on my personal priority list.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/19/17 3:43 AM, Robert Haas wrote:
This is the kind of information that you really want to see once per
autovac, though, not just the most recent autovac or some kind of
cumulative total. Knowing that I've done 301 index scans in my last
300 vacuums is not nearly as useful as knowing which autovacuum did 2
index scans and what exactly was going on with that vacuum. So I'm
not sure including this sort of thing in the stats files would be very
useful, or at least you'd want to think carefully about how to do it.
Well, counters would be better than nothing I think, but I agree with
your concern. Really, that's a problem for the entire stats system to
varying degrees.
As far as bloating the stats file is concerned, the big problem there
is that our current design for the stats file requires rewriting the
entire thing any time we want to update even a single byte of data.
We could fix that by splitting up the files more so that they are
smaller and faster to rewrite, but we could also fix it by coming up
with a way of rewriting just one part of a file instead of the whole
thing, or we could think about storing it in DSM so that you don't
have to rewrite anything at all. I think that last option is worth
some serious study now that we have DSA, but it's currently not very
high on my personal priority list.
Hmm... so basically replace the temporary file with DSM?
Something else I think would be useful is a way to subscribe to stats
updates.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers