On Thu, 2007-03-08 at 14:53 -0300, Alvaro Herrera wrote: > Maybe something like this is better: > > LOG: index passes: 1 pages: removed 0, 197 remain tuples: removed 7199, > 2338 remain CPU usage: whatever > CONTEXT: Automatic vacuuming of table "database.public.w"
Yours is better. I've implemented this: LOG: autovac "public.w" index passes: 1 pages: removed 0, 197 remain tuples: removed 7199, 2338 remain CPU usage: whatever I'm happy if this gets removed later, but I think it will help everybody understand how multi-vacuums are working and what the best way to specify the controls should be. Not sure about the CONTEXT bit. I think its verbose, plus I thought that was for ERRORs only. I will defer on this point, since I know y'all understand that better than I. -- 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 18:27:45 -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("autovacuum \"%s.%s.%s\" index scans:%d pages: removed %d, %d remain tuples:%.0f removed, -%.0f remain %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)))); } *************** *** 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 18:27:45 -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 18:27:48 -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 18:27:52 -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