X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D18B89FA5DD for ; Sun, 23 Jul 2006 09:50:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 32880-02 for ; Sun, 23 Jul 2006 12:50:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 1CE639FA2A7 for ; Sun, 23 Jul 2006 09:50:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mx11.cksoft.de (mx11.cksoft.de [62.111.66.11]) by svr4.postgresql.org (Postfix) with ESMTP id 18EF55AF042 for ; Sun, 23 Jul 2006 12:50:48 +0000 (GMT) Received: from vesihiisi.cksoft.de (unknown [192.168.64.10]) (using TLSv1 with cipher EDH-RSA-DES-CBC3-SHA (168/168 bits)) (No client certificate requested) by mx12.cksoft.de (Postfix) with ESMTP id 525C4B85B; Sun, 23 Jul 2006 14:50:44 +0200 (CEST) Received: from vesihiisi.cksoft.de (localhost [127.0.0.1]) by vesihiisi.cksoft.de (Postfix) with ESMTP id E10AA1EA5; Sun, 23 Jul 2006 14:50:42 +0200 (CEST) Received: by vesihiisi.cksoft.de (Postfix, from userid 1000) id 42BEF1EA4; Sun, 23 Jul 2006 14:50:37 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by vesihiisi.cksoft.de (Postfix) with ESMTP id 345BD1E9C; Sun, 23 Jul 2006 14:50:37 +0200 (CEST) Date: Sun, 23 Jul 2006 14:50:37 +0200 (CEST) From: Christian Kratzer X-X-Sender: ck@vesihiisi.cksoft.de Reply-To: Christian Kratzer To: Benjamin Krajmalnik Cc: pgsql-admin@postgresql.org Subject: Re: Unexplained growth of tables In-Reply-To: Message-ID: <20060723143850.P64773@vesihiisi.cksoft.de> References: X-Spammer-Kill-Ratio: 75% MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by AMaViS cksoft-s20020300-20031204bz on vesihiisi.cksoft.de X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.142 tagged_above=0 required=5 tests=SPF_FAIL X-Spam-Level: * X-Archive-Number: 200607/179 X-Sequence-Number: 22425 Hi, On Sun, 23 Jul 2006, Benjamin Krajmalnik wrote: > The actual number of physical rows in this table is 3404. Row width is > 361. Table size = 639 MB. > What can be causing this growth? Not vacuuming often enough? I hav > pg_autovacuum running every 60 seconds. These tables have 10-15 > insert/update statements per second. > > Any assistance or guidance will be deeply appreciated. I am pulling > hairs on this one. something might be stopping vacuum from reclaiming the dead rows. We had a similar situation with a DBD::Pg based perl script that was monitoring the database for new jobs to perform. The script started once, connceted to the db, prepared its statemens and then just slept waiting for notifies. We now have the script regularly exit and reopen a new db connection which magically allows vacuum to reclaim rows. Could be related to prepared staments and locks on system tables or something of that sort. I would recommend comparing the output of vacuum full with and without your application connected. Greetings Christian -- Christian Kratzer ck@cksoft.de CK Software GmbH http://www.cksoft.de/ Phone: +49 7452 889 135 Fax: +49 7452 889 136