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 628C79FB2DB for ; Thu, 13 Jul 2006 22:30:15 -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 95586-08 for ; Thu, 13 Jul 2006 22:30:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 62FC69FAE4E for ; Thu, 13 Jul 2006 22:30:07 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: Re: Table size growing for no reason X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 13 Jul 2006 19:30:05 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Table size growing for no reason thread-index: Acam4iejvInUNRP7QpWkYQ2WbxK70QAAS9fA From: "Benjamin Krajmalnik" To: "Rodrigo De Leon" , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/107 X-Sequence-Number: 22353 The table is vacuumed all the time. Pg_autovacuum runs. I started thinking - what did I change right before this happened? I was playing around trying to tweak the performance of the database. As part of the process, I increased the number of WAL buffers to 32 and the checkpoint segments to 16. That appears to have been the culprit! I set them back at the same levels I was previously using (16 and 4, respectively), and now the table is no longer misbehaving. That was quite interesting - can someone in the core team verify that too high a number for these 2 parameters could have caused this behavior? Thanks in advance. Benjamin=20 > -----Original Message----- > From: pgsql-admin-owner@postgresql.org=20 > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo De Leon > Sent: Thursday, July 13, 2006 7:08 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Table size growing for no reason >=20 > 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=20 > "public"."tblksaura" > > USING btree ("kstestssysid", "testtime"); > > > > Running explain select * from tblkaura indicates that the=20 > width of the=20 > > row is 1859. > > The table has 3297 rows. > > > > Initially, it was 6.8MB. All of a sudden it has started growing in=20 > > size - both on the main table size, the indices, and toast. > > > > I truncated the table and reloaded the data - it went back=20 > to 6.8 Mb. > > > > This table is updated at a rate of about 10-12 updates=20 > statements per=20 > > second, by a single connection (a monitoring agent). > > > > What can be causing this strange behavior? Is there anything which=20 > > can be done? The update is being performed by a pl/pgsql=20 > stored procedure. > > > > The SQL query which is updating it uses the primary key for=20 > > retrieveing the field. > > > > The query looks as follows: > > > > update tblksaura > > set > > testtime =3D t_mytesttime, > > totaltestslasthour =3D ia_totaltestslasthour, > > totaltestslast24hrs =3D ia_totaltestslast24hrs, > > totaltestslast7days =3D ia_totaltestslast7days, > > totaltestslast30days =3D ia_totaltestslast30days, > > lasthourstatus =3D ia_lasthourstatus, > > lasthourreply =3D fa_lasthourreply, > > last24hrsreply =3D fa_last24hrsreply, > > last7daysreply =3D fa_last7daysreply, > > last30daysreply =3D fa_last30daysreply, > > lasthouralive =3D fa_lasthouralive, > > last24hrsalive =3D fa_last24hrsalive, > > last7daysalive =3D fa_last7daysalive, > > last30daysalive =3D fa_last30daysalive, > > sumalivelasthour =3D r_aurarecord.sumalivelasthour, > > sumreplylasthour =3D r_aurarecord.sumreplylasthour, > > sumalivelast24hrs =3D r_aurarecord.sumalivelast24hrs, > > sumreplylast24hrs =3D r_aurarecord.sumreplylast24hrs, > > sumalivelast7days =3D r_aurarecord.sumalivelast7days, > > sumreplylast7days =3D r_aurarecord.sumreplylast7days, > > sumalivelast30days =3D > > r_aurarecord.sumalivelast30days, > > sumreplylast30days =3D r_aurarecord.sumreplylast30days > > where ksaurasysid =3D r_aurarecord.ksaurasysid; > > > > > > Any assistance in getting this tracked down will be deeply=20 > appreciated. > > > > Looking at the current running processes in pgadmin I cannot see=20 > > anything which could be causing this. > > > > After about 1 hour of running, the table is now over 450MB!!!!! > > > > I am out of ideas. >=20 > VACUUM? >=20 > http://www.postgresql.org/docs/8.1/static/maintenance.html#ROU > TINE-VACUUMING >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org=20 > so that your > message can get through to the mailing list cleanly >=20