X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E97B89FA5E1 for ; Wed, 28 Jun 2006 01:45:36 -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 69774-04 for ; Wed, 28 Jun 2006 01:45:31 -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 566719FA5F0 for ; Wed, 28 Jun 2006 01:45:31 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 94B9B56484; Tue, 27 Jun 2006 23:45:30 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 27 Jun 2006 23:45:29 -0500 Date: Tue, 27 Jun 2006 23:45:29 -0500 From: "Jim C. Nasby" To: Jeff Frost Cc: pgsql-admin@postgresql.org Subject: Re: strange fsm issues Message-ID: <20060628044529.GO44573@pervasive.com> References: <9F858089-CCD2-4F88-BA18-8738ECEF5246@pervasive.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: 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:060628:jeff@frostconsultingllc.com::s5yUjYXxdEO/Z5Rg:0000000000 0000000000000000000000008TdK X-Hashcash: 1:20:060628:pgsql-admin@postgresql.org::8zszsTZUltO1eAVv:00000000000 0000000000000000000000006Kgf X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/273 X-Sequence-Number: 22235 On Tue, Jun 27, 2006 at 08:02:33PM -0700, Jeff Frost wrote: > On Tue, 27 Jun 2006, Jim Nasby wrote: > > >>What the heck? Why would it have shrunk down so much? The vacuum full > >>isn't scheduled until tonight. :-/ > > > >Take a look at the source for autovacuum... I'm guessing you're right and > >that it doesn't make any special considerations for toast tables, which it > >probably should (since AFAIK you have to first vacuum the base table, then > >commit, then vacuum the toast table). > > > >BTW, tracking pg_class.relpages or actual file size over time would > >probably be helpful. > > Actually, it says this: > > /* > * Scan pg_class and determine which tables to vacuum. > * > * The stats subsystem collects stats for toast tables independently of > * the stats for their parent tables. We need to check those stats since > * in cases with short, wide tables there might be proportionally much > * more activity in the toast table than in its parent. > * > * Since we can only issue VACUUM against the parent table, we need to > * transpose a decision to vacuum a toast table into a decision to vacuum > * its parent. There's no point in considering ANALYZE on a toast table, > * either. To support this, we keep a list of OIDs of toast tables that > * need vacuuming alongside the list of regular tables. Regular tables > * will be entered into the table list even if they appear not to need > * vacuuming; we go back and re-mark them after finding all the vacuumable > * toast tables. > */ > > So I guess it does take toast tables into account. Only if it issues 2 vacuums on the base tables... -- 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