log_autovacuum = on produces a single line of output from autovacuum, with additional useful stats. Patch is proving useful in performance testing. Not sure what is intended on logging for 8.3....
LOG: autovac "public.w" scans:1 pages:197(-0) tuples:2338(-7199) CPU 0.00s/0.00u sec elapsed 0.39 sec LOG: autovac "public.s" scans:1 pages:1926746(-0) tuples:37000611(-3461867) CPU 99.74s/53.37u sec elapsed 7977.20 sec No docs yet, but will do this if accepted. scans: N number of times indexes have been scanned pages: remaining(-removed) tuples: remaining(-removed) CPU elapsed -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Index: src/backend/commands/vacuumlazy.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v retrieving revision 1.85 diff -c -r1.85 vacuumlazy.c *** src/backend/commands/vacuumlazy.c 21 Feb 2007 22:47:45 -0000 1.85 --- src/backend/commands/vacuumlazy.c 8 Mar 2007 15:51:06 -0000 *************** *** 49,54 **** --- 49,55 ---- #include "access/transam.h" #include "commands/vacuum.h" #include "miscadmin.h" + #include "postmaster/autovacuum.h" #include "pgstat.h" #include "storage/freespace.h" #include "utils/lsyscache.h" *************** *** 90,95 **** --- 91,97 ---- 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; /* number of scans of index */ } LVRelStats; *************** *** 141,146 **** --- 143,151 ---- Relation *Irel; int nindexes; BlockNumber possibly_freeable; + PGRUsage ru0; + + pg_rusage_init(&ru0); if (vacstmt->verbose) elevel = INFO; *************** *** 156,161 **** --- 161,168 ---- /* 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); *************** *** 200,205 **** --- 207,220 ---- /* report results to the stats collector, too */ pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, vacstmt->analyze, vacrelstats->rel_tuples); + if (Log_autovacuum && IsAutoVacuumProcess()) + ereport(LOG, + (errmsg("autovac \"%s.%s\" scans:%d pages:%d(-%d) tuples:%.0f(-%.0f) %s", + get_namespace_name(RelationGetNamespace(onerel)), + RelationGetRelationName(onerel), vacrelstats->num_index_scans, + vacrelstats->rel_pages, vacrelstats->pages_removed, + vacrelstats->rel_tuples, vacrelstats->tuples_deleted, + pg_rusage_show(&ru0)))); } *************** *** 282,287 **** --- 297,303 ---- 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); *************** *** 490,495 **** --- 506,512 ---- 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: /projects/cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v retrieving revision 1.33 diff -c -r1.33 autovacuum.c *** src/backend/postmaster/autovacuum.c 7 Mar 2007 13:35:02 -0000 1.33 --- src/backend/postmaster/autovacuum.c 8 Mar 2007 15:51:07 -0000 *************** *** 69,74 **** --- 69,76 ---- int autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; + bool Log_autovacuum = false; + /* Flag to tell if we are in the autovacuum daemon process */ static bool am_autovacuum_launcher = false; static bool am_autovacuum_worker = false; Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.379 diff -c -r1.379 guc.c *** src/backend/utils/misc/guc.c 6 Mar 2007 02:06:14 -0000 1.379 --- src/backend/utils/misc/guc.c 8 Mar 2007 15:51:11 -0000 *************** *** 584,589 **** --- 584,597 ---- false, NULL, NULL }, { + {"log_autovacuum", PGC_BACKEND, LOGGING_WHAT, + gettext_noop("Logs end of a session, including duration."), + 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: /projects/cvsroot/pgsql/src/include/postmaster/autovacuum.h,v retrieving revision 1.8 diff -c -r1.8 autovacuum.h *** src/include/postmaster/autovacuum.h 15 Feb 2007 23:23:23 -0000 1.8 --- src/include/postmaster/autovacuum.h 8 Mar 2007 15:51:18 -0000 *************** *** 25,30 **** --- 25,32 ---- extern int autovacuum_vac_cost_delay; extern int autovacuum_vac_cost_limit; + extern bool Log_autovacuum; + /* Status inquiry functions */ extern bool AutoVacuumingActive(void); extern bool IsAutoVacuumLauncherProcess(void);
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly