Data Directory size increasing abnormally

Dear All,

I am using postgres 7.3 version on RHEL 4.0.

I have a table pdbsynchtable with the following structure

caption varchar(1020) NOT NULL,
uid varchar(255) NOT NULL,
destination varchar(1020) NOT NULL,
commanddata text NOT NULL,
command varchar(50) NOT NULL,
transactionid varchar(10) NOT NULL,
statusflag char(2) DEFAULT '01',
requestcounter int4 DEFAULT 0,
requestdatetime timestamp DEFAULT now(),
requesttype char(1) DEFAULT 'A',
actiondatetime timestamp DEFAULT now(),
"comment" text DEFAULT 'Entered into the System',
source varchar(255),
sourceip varchar(50),
sourcebocode varchar(4),
sourcedocode int4,
destinationbocode varchar(4),
destinationdocode int4,
CONSTRAINT pk_key PRIMARY KEY (uid)

The commanddata field consists of binary data of a txt file whose size is between 1kb to 4kb and there is a high frequecy of updates on this table (approx twice in a sec)

A strange behaviour is observerd in the physical files with respect to this table. The size of the file is growing abnormally in GBs. Suppose the file name (oid of relation )with respect to the table is "18924" I could find entries of 1 GB files like 18924, 18924.1, 18924.2 , 18924.3......18924.40 in the data directory, though there are on 10k records in the table.
I have attached the of list command of the data directory.
Kindly help me in finding , why this is happening because, the server is running out of space.

Thanks and Regards
Utsav Turray

Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL.

utsav wrote:
> Dear All,
>
> I am using postgres 7.3 version on RHEL 4.0.
>
You should upgrade to a newer/the latest stable release
>
> The commanddata field consists of binary data of a txt file whose size
> is between 1kb to 4kb and there is a high frequecy of updates on this
> table (approx twice in a sec)
>
> A strange behaviour is observerd in the physical files with respect to
> this table. The size of the file is growing abnormally in GBs. Suppose
> the file name (/oid of relation /)with respect to the table is "18924" I
> could find entries of 1 GB files like 18924, 18924.1, 18924.2 ,
> 18924.3......18924.40 in the data directory, though there are on 10k
> records in the table.
> I have attached the of list command of the data directory.
> Kindly help me in finding , why this is happening because, the server is
> running out of space.

Are you vacuuming this DB, or any of it's tables, especially the table in question?
>
> Thanks and Regards
> Utsav Turray

utsav wrote:
> The commanddata field consists of binary data of a txt file whose size
> is between 1kb to 4kb and there is a high frequecy of updates on this
> table (approx twice in a sec)
>
> A strange behaviour is observerd in the physical files with respect to
> this table. The size of the file is growing abnormally in GBs. Suppose
> the file name (/oid of relation /)with respect to the table is
> "18924" I could find entries of 1 GB files like 18924, 18924.1,
> 18924.2 , 18924.3......18924.40 in the data directory, though there
> are on 10k records in the table.
> I have attached the of list command of the data directory.
> Kindly help me in finding , why this is happening because, the server
> is running out of space.
>

are you vacuuming frequently enough? in postgresql, UPDATE behaves
much like DELETE and INSERT, and the DELETE'd old tuples don't get freed
until the tables are vacuumed.

I will note, this app likely would perform 4-10X faster on 8.3 or 8.4
due to significant improvements in how UPDATE functions (and overall
performance improvements).

7.3 is a -really- old release now.

Note that with pgsql, the older the version you're running, the
greater your pgsql-fu must be to keep it happy.

With autovacuum and more efficient vacuuming all around in later
versions, your simple first step is to upgrade to 8.4.1 or 8.3.8 as
soon as possible. Until then, dump / reload that table and / or
vacuum full and reindex it and / or cluster that table (did we have
cluster in 7.3? Been too long, can't remember).

In simple terms, please stop torturing yourself with an old and pretty
much unsupported pg version.

> A strange behaviour is observerd in the physical files with respect to
> this table. The size of the file is growing abnormally in GBs. Suppose
> the file name (oid of relation )with respect to the table is "18924" I
> could find entries of 1 GB files like 18924, 18924.1, 18924.2 ,
> 18924.3......18924.40 in the data directory, though there are on 10k
> records in the table.

Hello,

your application is similar to mine and has similar problems.

In mine I have average 10 Updates per second with peaks of 100 updates
per second...

this kind of applications is known to cause un undesireable behaviour in
PostgresSQL called (I hope I'm not confusing things here) table bloat
and if you have indexes/primary keys, also index bloat;

the solution is somewhere in the correct balance of: autovacuum,
periodic vacuuming, vacuum full once in a while, and re-indexing; of
course upgrading to 8.3.x or higher is very very much recommended. but
with this kind of application (very frequent updates)
vacuum/autovacuum/reindex/cluster will always be good friends to have
around.

My best advice is to quickly read the documentation regarding those
commands and start applying them to your test database; when you get the
feeling of it configure autovacuum and plan weekly (to start with)
vacuum/reindex operations; vacuum full is typically not needed but let
the experience tell you how it goes in your case.

I'm not a postgres expert but my application, similar in behaviour to
yours, teached me these things. So I'm sorry if I'm not being totally
cientific regarding my suggestions.

cheers

Joao

>