X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id F3C949FA15C for ; Tue, 16 May 2006 15:31:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 54062-05 for ; Tue, 16 May 2006 15:31:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7DD519F9C9E for ; Tue, 16 May 2006 15:31:04 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id C406F5643A; Tue, 16 May 2006 13:31:02 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 16 May 2006 13:31:01 -0500 Date: Tue, 16 May 2006 13:31:01 -0500 From: "Jim C. Nasby" To: Mark Liberman Cc: Chris Hoover , pgsql-admin@postgresql.org Subject: Re: Autovacuum Question Message-ID: <20060516183101.GJ26212@pervasive.com> References: <9D938282F8C6EE43B748B910386DE93E0138B449@srvgpimail1.GPI.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9D938282F8C6EE43B748B910386DE93E0138B449@srvgpimail1.GPI.local> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060516:mliberman@mixedsignals.com::c9siBMKwexfFxICB:00000000000 00000000000000000000000002AD X-Hashcash: 1:20:060516:revoohc@gmail.com::jsgbNkJL2kyujtSg:00000000000000000000 0000000000000000000000004LpG X-Hashcash: 1:20:060516:pgsql-admin@postgresql.org::RtlyvRdm4QdoDV43:00000000000 00000000000000000000000037kJ X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/227 X-Sequence-Number: 21759 On Tue, May 16, 2006 at 10:30:26AM -0700, Mark Liberman wrote: > >Does this mean that each run of autovacuum will vacuum/analyze all tables > >that need work in the database that autovacuum is looking at? > > Yes, it will do all tables that satisfy the criteria. > > >Also, with over 200 databases, does this mean that a database will only get > >checked once about every 3.5 hours, or does autovacuum run on the database > >that needs the vacuuming the most? > > If, you have it set to 60 seconds, then yes. It hits one db each minute. Actually, to be more clear, it sleeps 1 minute between each check. So, if, on average, it takes 60 seconds to perform the necessary vacuum/analyzes, then it there will be, on average, 2 minutes between each db, or 400 minutes to get through all 200 dbs. The sleep time is so that the daemon doesn't just sit there hammering the database, checking to see if it needs to vacuum anything. If you've got 200 tables in one cluster, you should probably drop the sleep time way down; probably closer to 10 or even 1. Ideally, every time the daemon runs, there would be exactly one database that needed vacuuming. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461