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,
