Simon Riggs wrote: > On Tue, 2007-04-17 at 14:06 -0400, Alvaro Herrera wrote:
> > I've tinkered with this patch a bit. Sample output: > > > > LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0 > > pages: removed 0, 11226 remain > > tuples: 1300683 removed, 1096236 remain > > system usage: CPU 0.29s/0.38u sec elapsed 2.56 sec > > > > Please comment. > > Well, 'tis great except when you have very very frequent autovacuums. > That was why I wanted it in 1 log line. > > Perhaps we need this as an integer, so we can log all vacuums that last > for longer in seconds than the setting, 0 logs all. That would > significantly reduce the volume if we set it to 5, say. That way you > would get your readability and I would get my reasonable size logs. It kinda smells funny to have a setting like that. Do we have a precedent? If other people is OK with it, I'll do that. Would it work to add a separate GUC var to control the minimum autovac time? Also, why do it by time and not by amount of tuples/pages removed? > Presumably you mean to have both removeds in the same order? > > pages: 0 removed, 11226 remain > > tuples: 1300683 removed, 1096236 remain Right, fixed. Also, here is the patch. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/vacuumlazy.c =================================================================== RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/vacuumlazy.c,v retrieving revision 1.85 diff -c -p -r1.85 vacuumlazy.c *** src/backend/commands/vacuumlazy.c 21 Feb 2007 22:47:45 -0000 1.85 --- src/backend/commands/vacuumlazy.c 17 Apr 2007 17:48:24 -0000 *************** *** 47,55 **** --- 47,57 ---- #include "access/genam.h" #include "access/heapam.h" #include "access/transam.h" + #include "commands/dbcommands.h" #include "commands/vacuum.h" #include "miscadmin.h" #include "pgstat.h" + #include "postmaster/autovacuum.h" #include "storage/freespace.h" #include "utils/lsyscache.h" #include "utils/memutils.h" *************** typedef struct LVRelStats *** 90,95 **** --- 92,98 ---- int max_free_pages; /* # slots allocated in array */ PageFreeSpaceInfo *free_pages; /* array or heap of blkno/avail */ BlockNumber tot_free_pages; /* total pages with >= threshold space */ + int num_index_scans; } LVRelStats; *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 141,146 **** --- 144,152 ---- Relation *Irel; int nindexes; BlockNumber possibly_freeable; + PGRUsage ru0; + + pg_rusage_init(&ru0); if (vacstmt->verbose) elevel = INFO; *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 156,161 **** --- 162,169 ---- /* XXX should we scale it up or down? Adjust vacuum.c too, if so */ vacrelstats->threshold = GetAvgFSMRequestSize(&onerel->rd_node); + vacrelstats->num_index_scans = 0; + /* Open all indexes of the relation */ vac_open_indexes(onerel, RowExclusiveLock, &nindexes, &Irel); vacrelstats->hasindex = (nindexes > 0); *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 200,205 **** --- 208,228 ---- /* report results to the stats collector, too */ pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, vacstmt->analyze, vacrelstats->rel_tuples); + + /* and log the action if appropriate */ + if (Log_autovacuum && IsAutoVacuumWorkerProcess()) + ereport(LOG, + (errmsg("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n" + "pages: removed %d, %d remain\n" + "tuples: %.0f removed, %.0f remain\n" + "system usage: %s", + get_database_name(MyDatabaseId), + get_namespace_name(RelationGetNamespace(onerel)), + RelationGetRelationName(onerel), + vacrelstats->num_index_scans, + vacrelstats->pages_removed, vacrelstats->rel_pages, + vacrelstats->tuples_deleted, vacrelstats->rel_tuples, + pg_rusage_show(&ru0)))); } *************** lazy_scan_heap(Relation onerel, LVRelSta *** 282,287 **** --- 305,311 ---- lazy_vacuum_heap(onerel, vacrelstats); /* Forget the now-vacuumed tuples, and press on */ vacrelstats->num_dead_tuples = 0; + vacrelstats->num_index_scans++; } buf = ReadBuffer(onerel, blkno); *************** lazy_scan_heap(Relation onerel, LVRelSta *** 490,495 **** --- 514,520 ---- vacrelstats); /* Remove tuples from heap */ lazy_vacuum_heap(onerel, vacrelstats); + vacrelstats->num_index_scans++; } /* Do post-vacuum cleanup and statistics update for each index */ Index: src/backend/postmaster/autovacuum.c =================================================================== RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v retrieving revision 1.41 diff -c -p -r1.41 autovacuum.c *** src/backend/postmaster/autovacuum.c 16 Apr 2007 18:29:52 -0000 1.41 --- src/backend/postmaster/autovacuum.c 17 Apr 2007 17:21:56 -0000 *************** int autovacuum_freeze_max_age; *** 71,76 **** --- 71,78 ---- int autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; + bool Log_autovacuum = false; + /* Flags to tell if we are in an autovacuum process */ static bool am_autovacuum_launcher = false; static bool am_autovacuum_worker = false; Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.385 diff -c -p -r1.385 guc.c *** src/backend/utils/misc/guc.c 16 Apr 2007 18:29:55 -0000 1.385 --- src/backend/utils/misc/guc.c 17 Apr 2007 17:33:47 -0000 *************** static struct config_bool ConfigureNames *** 593,598 **** --- 593,606 ---- false, NULL, NULL }, { + {"log_autovacuum", PGC_BACKEND, LOGGING_WHAT, + gettext_noop("Logs duration and statistics of a VACUUM run by the autovacuum daemon."), + NULL + }, + &Log_autovacuum, + false, NULL, NULL + }, + { {"debug_assertions", PGC_USERSET, DEVELOPER_OPTIONS, gettext_noop("Turns on various assertion checks."), gettext_noop("This is a debugging aid."), Index: src/include/postmaster/autovacuum.h =================================================================== RCS file: /home/alvherre/Code/cvs/pgsql/src/include/postmaster/autovacuum.h,v retrieving revision 1.9 diff -c -p -r1.9 autovacuum.h *** src/include/postmaster/autovacuum.h 16 Apr 2007 18:30:03 -0000 1.9 --- src/include/postmaster/autovacuum.h 17 Apr 2007 17:21:10 -0000 *************** extern int autovacuum_vac_cost_limit; *** 31,36 **** --- 31,38 ---- /* autovacuum launcher PID, only valid when worker is shutting down */ extern int AutovacuumLauncherPid; + extern bool Log_autovacuum; + /* Status inquiry functions */ extern bool AutoVacuumingActive(void); extern bool IsAutoVacuumLauncherProcess(void);
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org