Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 55AC19FBE72 for ; Thu, 8 Mar 2007 12:08:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 81635-05-5 for ; Thu, 8 Mar 2007 12:08:41 -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 1FB059FBDDD for ; Thu, 8 Mar 2007 12:08:40 -0400 (AST) thread-index: AcdhnBErFcgrlUVWQ3CaDizlehPgwg== 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 11:08:51 -0500 Content-Transfer-Encoding: 7bit Subject: log_autovacuum Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 From: "Simon Riggs" To: Cc: "Alvaro Herrera" Content-Type: multipart/mixed; boundary="=-GyXORkB7C5BJ44ARXf8N" Date: Thu, 08 Mar 2007 16:05:48 +0000 Message-ID: <1173369949.3641.132.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 X-OriginalArrivalTime: 08 Mar 2007 16:08:51.0875 (UTC) FILETIME=[103FAF30:01C7619C] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.065 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200703/126 X-Sequence-Number: 1136 This is a multi-part message in MIME format. --=-GyXORkB7C5BJ44ARXf8N Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit 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 --=-GyXORkB7C5BJ44ARXf8N Content-Disposition: attachment; filename="log_autovacuum.v1.patch" Content-Type: text/x-patch; charset="UTF-8"; name="log_autovacuum.v1.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 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); --=-GyXORkB7C5BJ44ARXf8N--