X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 781AE9DC883 for ; Thu, 23 Feb 2006 14:32:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44983-05 for ; Thu, 23 Feb 2006 14:32:00 -0400 (AST) X-Greylist: delayed 09:59:34.209961 by SQLgrey- Received: from blaster.systems.pipex.net (blaster.systems.pipex.net [62.241.163.7]) by postgresql.org (Postfix) with ESMTP id 193609DC806 for ; Thu, 23 Feb 2006 14:31:57 -0400 (AST) Received: from flange.edesix.com (81-86-64-236.dsl.pipex.com [81.86.64.236]) by blaster.systems.pipex.net (Postfix) with ESMTP id 8013EE000103 for ; Thu, 23 Feb 2006 18:31:55 +0000 (GMT) Received: from visitor4.edesix.com ([192.168.1.154]) by flange.edesix.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.50) id 1FCLFq-00065D-Uu for pgsql-admin@postgresql.org; Thu, 23 Feb 2006 18:31:54 +0000 Message-ID: <43FDFF9A.90603@edesix.com> Date: Thu, 23 Feb 2006 18:31:54 +0000 From: Robin Iddon User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table? References: <43FD7312.5080701@edesix.com> <43FDF046.2050609@zeut.net> In-Reply-To: <43FDF046.2050609@zeut.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/281 X-Sequence-Number: 20702 Hi Matt, Thanks for the reply. Matthew T. O'Connor wrote: > What do you mean by stats table entries? Are you saying there is > bloat in a specific table, or just the whole database? > The table in question is pg_statistic. It's pages as reported by pg_class.relpages grow without bound until I perform a vacuum full. I do a "vacuum full" and it drops in size to next to nothing. I have a standby database server configured in the same way, with the same data set, but almost completely idle; I did the vacuum full on it ~1-2 months back when I started to look into this issue; since then it has accumulated 215K relpages (which is, I think, around 1.7G!) in the pg_statistic table. Chasing through pg_database.oid and pg_class.relfilenode I compute that pg_statistic should be stored in $PGDATA/base/17230/16408. Here is what du reports for those files: 1049600 17230/16408 1049600 17230/16408.1 1049600 17230/16408.2 15912 17230/16408.3 Which is a total of just over 3G (this implies to me that the relpages count in the pg_class table is out of date). Looking at another (tiny) database that is definitely only used for testing and I know hasn't been used in months it's pg_statistic has also grown to 1.1G on disk. > Second, I think you need to make autovacuum more aggressive. I think > most people find that the vacuum scaling factor should be more like > 0.3 or so. With the settings you have a 1000 tuple table would have > to see 3000 invalidated tuples before it does a vacuum that is > probably much more slack space than you want. > Valuable advice, but as it happens we delete next to nothing and I think the disk space in use by the database is reasonable for the data stored. > Third: Are you seeing in the autovacuum log that autovacuum is > actually trying to vacuum tables? Turn up the debugging to -d2 that > should give you some more info as to why autovac is (or is not) doing > what it's doing. > I will turn it on and take a look. I am guessing it will tell me that for whatever reason it isn't actually doing anything to the pg_catalog tables ... > Fourth: there will always be some slack space. Under the best of > circumstances, a database running with autovac will be somewhat bigger > than the database right after a VACUUM FULL, probably not 2.5G, but some. > Understood - I am happy for there to be 50% slack - I just don't understand why pg_statistic grows without bound and I am concerned that the size of the stats table might be causing me some performance issues (this is fear rather than fact). Thanks again, Robin