Обсуждение: Table size growing for no reason

On 7/13/06, Benjamin Krajmalnik <kraj@illumen.com> 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

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

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [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
>
> On 7/13/06, Benjamin Krajmalnik <kraj@illumen.com> 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#ROU
> TINE-VACUUMING
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org
> so that your
>        message can get through to the mailing list cleanly
>

"Benjamin Krajmalnik" <kraj@illumen.com> writes:
> 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.

Um ... changing those parameters should have affected the contents of
$PGDATA/pg_xlog, but not of the table per se.  What were you measuring
exactly?

            regards, tom lane