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 0AB49528A9 for ; Wed, 18 May 2005 18:12:33 -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 74716-04 for ; Wed, 18 May 2005 21:12:27 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 4DF0E5289F for ; Wed, 18 May 2005 18:12:27 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j4ILCRQj075445 for ; Wed, 18 May 2005 21:12:27 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j4IL403s073226 for pgsql-admin@postgresql.org; Wed, 18 May 2005 21:04:00 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: Sidetracking pg_autovacuum Date: Wed, 18 May 2005 16:15:37 -0400 Organization: cbbrowne Computing Inc Lines: 55 Message-ID: <60hdh0qoie.fsf@dba2.int.libertyrms.com> References: <428B8A27.7050402@nexcerpt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:8y2BjSFr4XhfA33A7Gmn+hmKtmk= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200505/249 X-Sequence-Number: 17678 jboes@nexcerpt.com (Jeff Boes) writes: > (Environment: Pg 7.4.1 on Linux; yes, I know it's past time to > upgrade.) If you were running 7.2, I'd be pestering you ;-). We didn't get rid of our last 7.1 system until last December, and I think the backend is probably still running, albeit with nothing hooking up to it :-). > (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.) Oops. Change that to a VACUUM ANALYZE (in your script), and things should work out better. > 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.] That's not a bad idea, all in all. > 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?) Yes, that doubtless would be it. There's a new sampling scheme in version 8 that may do better. > [And here's the remaining question in my puzzled mind: ANALYZE would > not change the reltuples value, but VACUUM FULL ANALYZE > did. Er-wha?] VACUUM ANALYZE would also change the reltuples value... -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"