Thread: Data Directory size increasing abnormally
Dear All,
I am using postgres 7.3 version on RHEL 4.0.
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)
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 origin al 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 los s or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no bin ding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL.
Attachment
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.
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
> 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 >