X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2EAC3528A3 for ; Wed, 18 May 2005 15:32:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30323-07 for ; Wed, 18 May 2005 18:32:33 +0000 (GMT) Received: from yapok.nexcerpt.com (mail.nexcerpt.com [64.147.202.2]) by svr1.postgresql.org (Postfix) with ESMTP id 16A1A528A0 for ; Wed, 18 May 2005 15:32:31 -0300 (ADT) Received: from [192.168.0.110] (DSL-NE-121.qtm.net [216.163.37.121]) (authenticated bits=0) by yapok.nexcerpt.com (8.12.8/8.12.8) with ESMTP id j4IIWR3d019887 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=OK) for ; Wed, 18 May 2005 14:32:29 -0400 Message-ID: <428B8A27.7050402@nexcerpt.com> Date: Wed, 18 May 2005 14:32:07 -0400 From: Jeff Boes Organization: Nexcerpt, Inc. User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Sidetracking pg_autovacuum Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Nexcerpt-MailScanner-Information: Please contact the SysAdmin for more information X-Nexcerpt-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/247 X-Sequence-Number: 17676 (Environment: Pg 7.4.1 on Linux; yes, I know it's past time to upgrade.) (Originally, I started to post this as a question about how pg_class.reltuples could get so far out of whack compared to the actual table size. After posting it, I found that (a) I had the mailing list address wrong [d'oh!], and (b) I think I solved my problem. In essence, by doing a VACUUM in a script after a sizeable DELETE, apparently I took the affected table out of the hands of pg_autovacuum so that it never, ever did anything with the table. Including ANALYZE. Thus, reltuples never got updated.) I started tracking the COUNT(*) (actual row count) versus the value in pg_class.reltuples for a number of our larger tables. Some of the tables see a lot of INSERTs and DELETEs over the course of a day; as much as 1/12th of the data will be deleted overnight, and new data inserted over the course of the day. I have pg_autovacuum running, and I also do regular VACUUMs, ANALYZEs, and even some CLUSTERs on these tables. [N.B.: In fact, I started doing a VACUUM explicitly after the big nightly DELETE.] One table in particular started to act "funny", which got me looking at the innards of it, and I found that on our production system (which has significantly higher throughput), the ratio of (pg_class.reltuples / count(*)) would climb to 2.0 or higher; that seemed to indicate that pg_autovacuum wasn't doing its job, at least not when I expected it to, so I started doing a VACUUM after the DELETEs overnight, and that made *that* problem go away. However, now the test system (with the lower throughput) is behaving oddly. The ratio has *fallen* to about 0.16, meaning there are *six times as many* actual rows in the table compared to what the optimizer thinks. I did a hand-analyze on the table, and the situation didn't change. Right now, row count is about 182,000 and reltuples is under 29,000. What would cause an otherwise well-behaved table to start doing this? Is this just a "dead spot" in the ANALYZE command? (By which I mean: ANALYZE randomly sampling rows, but my data is not terribly random, so it gets fooled?) [And here's the remaining question in my puzzled mind: ANALYZE would not change the reltuples value, but VACUUM FULL ANALYZE did. Er-wha?] -- Jeff Boes Vox 269-226-9550 x24 Director of Software Development Fax 269-349-9076 Exfacto! Exceptional Online Content http://www.exfacto.com Nexcerpt ...Extend Your Expertise... http://www.nexcerpt.com