Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 38B7A9FBDA9 for ; Thu, 8 Mar 2007 14:34:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 05818-01-10 for ; Thu, 8 Mar 2007 14:34:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 4A5169FBDE6 for ; Thu, 8 Mar 2007 14:34:24 -0400 (AST) thread-index: AcdhsGtTGLYRVnocSa2o69tS96rx0Q== Received: from [172.24.32.16] ([62.232.55.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 8 Mar 2007 13:34:33 -0500 Content-Transfer-Encoding: 7bit Subject: Re: log_autovacuum Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 From: "Simon Riggs" To: "Alvaro Herrera" Cc: In-Reply-To: <20070308175330.GW4715@alvh.no-ip.org> References: <1173369949.3641.132.camel@silverbirch.site> <20070308175330.GW4715@alvh.no-ip.org> Content-Type: multipart/mixed; boundary="=-boFlJARcC9BDLItSAwuz" Date: Thu, 08 Mar 2007 18:31:36 +0000 Message-ID: <1173378696.3641.162.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 X-OriginalArrivalTime: 08 Mar 2007 18:34:33.0781 (UTC) FILETIME=[6AD4AE50:01C761B0] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.064 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200703/132 X-Sequence-Number: 1142 This is a multi-part message in MIME format. --=-boFlJARcC9BDLItSAwuz Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit 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 --=-boFlJARcC9BDLItSAwuz Content-Disposition: attachment; filename="log_autovacuum.v2.patch" Content-Type: text/x-patch; charset="UTF-8"; name="log_autovacuum.v2.patch" Content-Transfer-Encoding: 7bit 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); --=-boFlJARcC9BDLItSAwuz--