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 C95319FB2DB for ; Thu, 13 Jul 2006 22:02:25 -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 94054-05 for ; Thu, 13 Jul 2006 22:02:17 -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 258429FB2D5 for ; Thu, 13 Jul 2006 22:02:16 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6A6E1.24EB0BBF" Subject: Table size growing for no reason X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 13 Jul 2006 19:02:14 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Table size growing for no reason thread-index: Acam4SXa+Y/bHjeHSfWuNIxwK1SisQ== From: "Benjamin Krajmalnik" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/105 X-Sequence-Number: 22351 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6A6E1.24EB0BBF Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable I am running PostgreSQL 8.1.4 on windows. I have a table with the following structure: =20 CREATE TABLE "public"."tblksaura" ( "ksaurasysid" SERIAL,=20 "testtime" TIMESTAMP WITHOUT TIME ZONE,=20 "lasthouralive" DOUBLE PRECISION[],=20 "last24hrsalive" DOUBLE PRECISION[],=20 "last7daysalive" DOUBLE PRECISION[],=20 "last30daysalive" DOUBLE PRECISION[],=20 "sumalivelasthour" DOUBLE PRECISION,=20 "sumreplylasthour" DOUBLE PRECISION,=20 "sumalivelast24hrs" DOUBLE PRECISION,=20 "sumalivelast7days" DOUBLE PRECISION,=20 "sumalivelast30days" DOUBLE PRECISION,=20 "sumreplylast24hrs" DOUBLE PRECISION,=20 "sumreplylast7days" DOUBLE PRECISION,=20 "sumreplylast30days" DOUBLE PRECISION,=20 "lasthourreply" DOUBLE PRECISION[],=20 "last24hrsreply" DOUBLE PRECISION[],=20 "last7daysreply" DOUBLE PRECISION[],=20 "last30daysreply" DOUBLE PRECISION[],=20 "lasthourstatus" INTEGER[],=20 "totaltestslasthour" BIGINT[],=20 "totaltestslast24hrs" BIGINT[],=20 "totaltestslast7days" BIGINT[],=20 "totaltestslast30days" BIGINT[],=20 "kstestssysid" INTEGER,=20 CONSTRAINT "tblksaura_kstestssysid_key" UNIQUE("kstestssysid"),=20 CONSTRAINT "tblksaura_pkey" PRIMARY KEY("ksaurasysid"),=20 CONSTRAINT "tblksaura_fk_tblkstests" FOREIGN KEY ("kstestssysid") REFERENCES "public"."tblkstests"("kstestssysid") MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE ) WITHOUT OIDS; =20 CREATE UNIQUE INDEX "tblksaura_idx_kstestssysid" ON "public"."tblksaura" USING btree ("kstestssysid", "testtime"); =20 Running explain select * from tblkaura indicates that the width of the row is 1859. The table has 3297 rows. =20 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. =20 I truncated the table and reloaded the data - it went back to 6.8 Mb. =20 This table is updated at a rate of about 10-12 updates statements per second, by a single connection (a monitoring agent). =20 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. =20 The SQL query which is updating it uses the primary key for retrieveing the field. =20 The query looks as follows: =20 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; =20 Any assistance in getting this tracked down will be deeply appreciated. =20 Looking at the current running processes in pgadmin I cannot see anything which could be causing this. =20 After about 1 hour of running, the table is now over 450MB!!!!! =20 I am out of ideas. =20 =20 =20 ------_=_NextPart_001_01C6A6E1.24EB0BBF Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable
I am = running=20 PostgreSQL 8.1.4 on windows.
I have = a table with=20 the following structure:
 
CREATE = TABLE=20 "public"."tblksaura" (
  "ksaurasysid" SERIAL,
  = "testtime"=20 TIMESTAMP WITHOUT TIME ZONE,
  "lasthouralive" DOUBLE = PRECISION[],=20
  "last24hrsalive" DOUBLE PRECISION[],
  = "last7daysalive"=20 DOUBLE PRECISION[],
  "last30daysalive" DOUBLE PRECISION[], =
 =20 "sumalivelasthour" DOUBLE PRECISION,
  "sumreplylasthour" = DOUBLE=20 PRECISION,
  "sumalivelast24hrs" DOUBLE PRECISION,
 =20 "sumalivelast7days" DOUBLE PRECISION,
  "sumalivelast30days" = DOUBLE=20 PRECISION,
  "sumreplylast24hrs" DOUBLE PRECISION,
 =20 "sumreplylast7days" DOUBLE PRECISION,
  "sumreplylast30days" = DOUBLE=20 PRECISION,
  "lasthourreply" DOUBLE PRECISION[],
 =20 "last24hrsreply" DOUBLE PRECISION[],
  "last7daysreply" DOUBLE=20 PRECISION[],
  "last30daysreply" DOUBLE PRECISION[],
  = "lasthourstatus" INTEGER[],
  "totaltestslasthour" BIGINT[], =
 =20 "totaltestslast24hrs" BIGINT[],
  "totaltestslast7days" = BIGINT[],=20
  "totaltestslast30days" BIGINT[],
  "kstestssysid" = INTEGER,=20
  CONSTRAINT "tblksaura_kstestssysid_key" = UNIQUE("kstestssysid"),=20
  CONSTRAINT "tblksaura_pkey" PRIMARY KEY("ksaurasysid"), =
 =20 CONSTRAINT "tblksaura_fk_tblkstests" FOREIGN KEY=20 ("kstestssysid")
    REFERENCES=20 "public"."tblkstests"("kstestssysid")
    MATCH=20 FULL
    ON DELETE CASCADE
    ON = UPDATE NO=20 ACTION
    NOT DEFERRABLE
) WITHOUT=20 OIDS;
 
CREATE = UNIQUE INDEX=20 "tblksaura_idx_kstestssysid" ON "public"."tblksaura"
  USING = btree=20 ("kstestssysid", "testtime");
 
Running explain=20 select * from tblkaura indicates that the width of the row is=20 1859.
The = table has 3297=20 rows.
 
Initially, it was=20 6.8MB.  All of a sudden it has started growing in size - both on = the main=20 table size, the indices, and toast.
 
I = truncated the=20 table and reloaded the data - it went back to 6.8 = Mb.
 
This = table is=20 updated at a rate of about 10-12 updates statements per second, by a = single=20 connection (a monitoring agent).
 
What = can be causing=20 this strange behavior?  Is there anything which can be done?  = The=20 update is being performed by a pl/pgsql stored = procedure.
 
The = SQL query which=20 is updating it uses the primary key for retrieveing the=20 field.
 
The = query looks as=20 follows:
 
       update=20 tblksaura
        =20 set
           = testtime =3D=20 t_mytesttime,
         &n= bsp;=20 totaltestslasthour =3D=20 ia_totaltestslasthour,
        = ;  =20 totaltestslast24hrs =3D=20 ia_totaltestslast24hrs,
       &nbs= p;  =20 totaltestslast7days =3D=20 ia_totaltestslast7days,
       &nbs= p;  =20 totaltestslast30days =3D=20 ia_totaltestslast30days,
       &nb= sp;  =20 lasthourstatus =3D=20 ia_lasthourstatus,
        &nb= sp; =20 lasthourreply =3D=20 fa_lasthourreply,
        &nbs= p; =20 last24hrsreply =3D=20 fa_last24hrsreply,
        &nb= sp; =20 last7daysreply =3D=20 fa_last7daysreply,
        &nb= sp; =20 last30daysreply =3D=20 fa_last30daysreply,
        &n= bsp; =20 lasthouralive =3D=20 fa_lasthouralive,
        &nbs= p; =20 last24hrsalive =3D=20 fa_last24hrsalive,
        &nb= sp; =20 last7daysalive =3D=20 fa_last7daysalive,
        &nb= sp; =20 last30daysalive =3D=20 fa_last30daysalive,
        &n= bsp; =20 sumalivelasthour =3D=20 r_aurarecord.sumalivelasthour,
      &nb= sp;   =20 sumreplylasthour =3D=20 r_aurarecord.sumreplylasthour,
      &nb= sp;   =20 sumalivelast24hrs =3D=20 r_aurarecord.sumalivelast24hrs,
      &n= bsp;   =20 sumreplylast24hrs =3D=20 r_aurarecord.sumreplylast24hrs,
      &n= bsp;   =20 sumalivelast7days =3D=20 r_aurarecord.sumalivelast7days,
      &n= bsp;   =20 sumreplylast7days =3D=20 r_aurarecord.sumreplylast7days,
      &n= bsp;   =20 sumalivelast30days =3D=20 r_aurarecord.sumalivelast30days,
      &= nbsp;   =20 sumreplylast30days =3D=20 r_aurarecord.sumreplylast30days
      &n= bsp;=20 where ksaurasysid =3D r_aurarecord.ksaurasysid;
 
Any = assistance in=20 getting this tracked down will be deeply = appreciated.
 
Looking at the=20 current running processes in pgadmin I cannot see anything which could = be=20 causing this.
 
After = about 1 hour=20 of running, the table is now over 450MB!!!!!
 
I am = out of=20 ideas. 
 
 
------_=_NextPart_001_01C6A6E1.24EB0BBF--