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 B518A9FB2DB for ; Thu, 13 Jul 2006 22:08:18 -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 94276-07 for ; Thu, 13 Jul 2006 22:08:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 2B4509FB2D5 for ; Thu, 13 Jul 2006 22:08:09 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so563386uge for ; Thu, 13 Jul 2006 18:08:08 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=PU5HFOa+BAtZQUujd0/Ilt7lJ11nxsVdMI6jY/v1kCBp8w78VGFcgOK8Bvs49rstzsxdRkXopewx+bVkGe0VjTEKgC5kqDZ+4U3wOsr27FXHRxesCebpmDJYury38Mtw0Fv/ZX5HLj3GbI2J6KqMmj61eD3NATI5mFRN+c068j4= Received: by 10.66.244.10 with SMTP id r10mr659908ugh; Thu, 13 Jul 2006 18:08:08 -0700 (PDT) Received: by 10.66.234.16 with HTTP; Thu, 13 Jul 2006 18:08:07 -0700 (PDT) Message-ID: Date: Thu, 13 Jul 2006 20:08:07 -0500 From: "Rodrigo De Leon" To: pgsql-admin@postgresql.org Subject: Re: Table size growing for no reason In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/106 X-Sequence-Number: 22352 On 7/13/06, Benjamin Krajmalnik wrote: > > > I am running PostgreSQL 8.1.4 on windows. > I have a table with the following structure: > > CREATE TABLE "public"."tblksaura" ( > "ksaurasysid" SERIAL, > "testtime" TIMESTAMP WITHOUT TIME ZONE, > "lasthouralive" DOUBLE PRECISION[], > "last24hrsalive" DOUBLE PRECISION[], > "last7daysalive" DOUBLE PRECISION[], > "last30daysalive" DOUBLE PRECISION[], > "sumalivelasthour" DOUBLE PRECISION, > "sumreplylasthour" DOUBLE PRECISION, > "sumalivelast24hrs" DOUBLE PRECISION, > "sumalivelast7days" DOUBLE PRECISION, > "sumalivelast30days" DOUBLE PRECISION, > "sumreplylast24hrs" DOUBLE PRECISION, > "sumreplylast7days" DOUBLE PRECISION, > "sumreplylast30days" DOUBLE PRECISION, > "lasthourreply" DOUBLE PRECISION[], > "last24hrsreply" DOUBLE PRECISION[], > "last7daysreply" DOUBLE PRECISION[], > "last30daysreply" DOUBLE PRECISION[], > "lasthourstatus" INTEGER[], > "totaltestslasthour" BIGINT[], > "totaltestslast24hrs" BIGINT[], > "totaltestslast7days" BIGINT[], > "totaltestslast30days" BIGINT[], > "kstestssysid" INTEGER, > CONSTRAINT "tblksaura_kstestssysid_key" UNIQUE("kstestssysid"), > CONSTRAINT "tblksaura_pkey" PRIMARY KEY("ksaurasysid"), > CONSTRAINT "tblksaura_fk_tblkstests" FOREIGN KEY ("kstestssysid") > REFERENCES "public"."tblkstests"("kstestssysid") > MATCH FULL > ON DELETE CASCADE > ON UPDATE NO ACTION > NOT DEFERRABLE > ) WITHOUT OIDS; > > CREATE UNIQUE INDEX "tblksaura_idx_kstestssysid" ON "public"."tblksaura" > USING btree ("kstestssysid", "testtime"); > > Running explain select * from tblkaura indicates that the width of the row > is 1859. > The table has 3297 rows. > > Initially, it was 6.8MB. All of a sudden it has started growing in size - > both on the main table size, the indices, and toast. > > I truncated the table and reloaded the data - it went back to 6.8 Mb. > > This table is updated at a rate of about 10-12 updates statements per > second, by a single connection (a monitoring agent). > > What can be causing this strange behavior? Is there anything which can be > done? The update is being performed by a pl/pgsql stored procedure. > > The SQL query which is updating it uses the primary key for retrieveing the > field. > > The query looks as follows: > > update tblksaura > set > testtime = t_mytesttime, > totaltestslasthour = ia_totaltestslasthour, > totaltestslast24hrs = ia_totaltestslast24hrs, > totaltestslast7days = ia_totaltestslast7days, > totaltestslast30days = ia_totaltestslast30days, > lasthourstatus = ia_lasthourstatus, > lasthourreply = fa_lasthourreply, > last24hrsreply = fa_last24hrsreply, > last7daysreply = fa_last7daysreply, > last30daysreply = fa_last30daysreply, > lasthouralive = fa_lasthouralive, > last24hrsalive = fa_last24hrsalive, > last7daysalive = fa_last7daysalive, > last30daysalive = fa_last30daysalive, > sumalivelasthour = r_aurarecord.sumalivelasthour, > sumreplylasthour = r_aurarecord.sumreplylasthour, > sumalivelast24hrs = r_aurarecord.sumalivelast24hrs, > sumreplylast24hrs = r_aurarecord.sumreplylast24hrs, > sumalivelast7days = r_aurarecord.sumalivelast7days, > sumreplylast7days = r_aurarecord.sumreplylast7days, > sumalivelast30days = > r_aurarecord.sumalivelast30days, > sumreplylast30days = r_aurarecord.sumreplylast30days > where ksaurasysid = r_aurarecord.ksaurasysid; > > > Any assistance in getting this tracked down will be deeply appreciated. > > Looking at the current running processes in pgadmin I cannot see anything > which could be causing this. > > After about 1 hour of running, the table is now over 450MB!!!!! > > I am out of ideas. VACUUM? http://www.postgresql.org/docs/8.1/static/maintenance.html#ROUTINE-VACUUMING