[PATCH] Addition of some trivial auto vacuum logging - Mailing list pgsql-hackers

From Royce Ausburn
Subject [PATCH] Addition of some trivial auto vacuum logging
Date
Msg-id BECA513D-A303-4B6B-A334-7B6842BFAA40@inomial.com
Whole thread Raw
Responses Re: [PATCH] Addition of some trivial auto vacuum logging
Re: [PATCH] Addition of some trivial auto vacuum logging
List 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 =)

--Royce





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,
                        ereport(LOG,
                                        (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",
                                                        get_database_name(MyDatabaseId),
                                                        get_namespace_name(RelationGetNamespace(onerel)),
@@ -266,6 +267,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
                                                        vacrelstats->rel_pages,
                                                        vacrelstats->tuples_deleted,
                                                        new_rel_tuples,
+                                                       vacrelstats->undeleteable_dead_tuples,
                                                        pg_rusage_show(&ru0))));
        }
 }
@@ -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:

Previous
From: Tom Lane
Date:
Subject: Re: Support UTF-8 files with BOM in COPY FROM
Next
From: Tom Lane
Date:
Subject: Re: [PATCH] Addition of some trivial auto vacuum logging