Table size growing for no reason

Поиск
Список
Период
Сортировка
От Benjamin Krajmalnik
Тема Table size growing for no reason
Дата
Msg-id BF337097BDD9D849A2F4B818DDB27987029447@stash.stackdump.local
обсуждение исходный текст
Ответы Re: Table size growing for no reason
Список pgsql-admin
Дерево обсуждения
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. 
 
 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: corruption since 7.4.13 update ?
Следующее
От: "Rodrigo De Leon"
Дата:
Сообщение: Re: Table size growing for no reason