Skip site navigation (1) Skip section navigation (2)

[PATCH] Addition of some trivial auto vacuum logging

From: Royce Ausburn <royce(dot)ml(at)inomial(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [PATCH] Addition of some trivial auto vacuum logging
Date: 2011-09-27 17:51:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hi all,

I spent a bit of today diagnosing a problem where long held transactions were preventing auto vacuum from doing its job.  Eventually I had set log_autovacuum_min_duration to 0 to see what was going on.  Unfortunately it wasn't until I tried a VACUUM VERBOSE that I found there were dead tuples not being removed.  Had the unremoveable tuple count been included in the autovacuum log message life would have been a tiny bit easier.

I've been meaning for a while to dabble in postgres, so I thought this might be a good trivial thing for me to improve.  The attached patch adds extra detail the the existing autovacuum log message that is emitted when the log_autovacuum_min_duration threshold is met, exposing the unremovable dead tuple count similar to what you get from VACUUM VERBOSE.

Sample log output (my addition in bold):

LOG:  automatic vacuum of table "test.public.test": index scans: 0
	pages: 0 removed, 5 remain
	tuples: 0 removed, 1000 remain, 999 dead but not removable
	system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

My patch adds another member to the LVRelStats struct named undeleteable_dead_tuples.  lazy_scan_heap() sets undeleteable_dead_tuples to the value of lazy_scan_heap()'s local variable "nkeep", which is the same variable that is used to emit the VACUUM VERBOSE unremoveable dead row count.

As this is my first patch to postgresql, I'm expecting I've done something wrong.  Please if you want me to fix something up, or just go away please say so ;)  I appreciate that this is a trivial patch, and perhaps doesn't add value except for my very specific use caseā€¦ feel free to ignore it =)


diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index cf8337b..12f03d7 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -91,6 +91,7 @@ typedef struct LVRelStats
        double          scanned_tuples; /* counts only tuples on scanned pages */
        double          old_rel_tuples; /* previous value of pg_class.reltuples */
        double          new_rel_tuples; /* new estimated total # of tuples */
+       double          undeleteable_dead_tuples; /* count of dead tuples not yet removeable */
        BlockNumber pages_removed;
        double          tuples_deleted;
        BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
@@ -256,7 +257,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
                                        (errmsg("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n"
                                                        "pages: %d removed, %d remain\n"
-                                                       "tuples: %.0f removed, %.0f remain\n"
+                                                       "tuples: %.0f removed, %.0f remain, %.0f dead but not removable\n"
                                                        "system usage: %s",
@@ -266,6 +267,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
+                                                       vacrelstats->undeleteable_dead_tuples,
@@ -829,6 +831,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
        /* save stats for use later */
        vacrelstats->scanned_tuples = num_tuples;
        vacrelstats->tuples_deleted = tups_vacuumed;
+       vacrelstats->undeleteable_dead_tuples = nkeep;
        /* now we can compute the new value for pg_class.reltuples */
        vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,


pgsql-hackers by date

Next:From: Tom LaneDate: 2011-09-27 18:22:38
Subject: Re: [PATCH] Addition of some trivial auto vacuum logging
Previous:From: Tom LaneDate: 2011-09-27 14:32:03
Subject: Re: Support UTF-8 files with BOM in COPY FROM

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group