Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 8266C2E0031
	for <pgsql-interfaces-postgresql.org@developer.postgresql.org>;
	Fri, 16 May 2008 05:59:46 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 65972-01-10
	for <pgsql-interfaces-postgresql.org@developer.postgresql.org>;
	Fri, 16 May 2008 05:59:34 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from bay0-omc3-s40.bay0.hotmail.com (bay0-omc3-s40.bay0.hotmail.com
	[65.54.246.240])
	by developer.postgresql.org (Postfix) with ESMTP id 990132E008F
	for <pgsql-interfaces@postgresql.org>;
	Fri, 16 May 2008 05:58:18 -0300 (ADT)
Received: from BAY137-W50 ([64.4.48.85]) by bay0-omc3-s40.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Fri, 16 May 2008 01:58:15 -0700
Message-ID: <BAY137-W50DCFFB333D7318AD4F557D6C80@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_4869fada-8f61-4674-aa79-c8b5aa965f00_"
X-Originating-IP: [198.135.207.129]
From: Dennis Wang <dennis_02_2002@hotmail.com>
To: Postgresql Help <pgsql-interfaces@postgresql.org>
Subject: pg_subtrans directory grows over 1 GB, is there a way to control it?
Date: Fri, 16 May 2008 19:28:15 +1030
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 16 May 2008 08:58:15.0338 (UTC)
	FILETIME=[FA2A40A0:01C8B732]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200805/11
X-Sequence-Number: 6735

--_4869fada-8f61-4674-aa79-c8b5aa965f00_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


I am using PostgreSQL database in a location recognition application, the f=
iles under postgresql/cluster is growing and over a few days it become very=
 large as below.=20
    /var/opt/postgresql/cluster/data  166015 Kilobytes  (the actual databas=
e data)
    /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes  (transaction c=
ommit status data)
    /var/opt/postgresql/cluster/sys/pg_subtrans   1187032 Kilobytes (transa=
ction status data)

The pg_clog and pg_subtrans can grow when database contains more data and m=
ore queries come in. But these figures looks rather large to me because the=
 queries goes in the database are mainly read operation rather than write o=
ne. I got a few question to ask here:
     Is there a way to control the size of pg_clog and pg_subtrans?=20
     Is the file size still be normal for the database of this size (166MB =
for cluster/data)?
     Does the larger pg_subtrans indicate some bad transaction happens in t=
he database?

You expertise is very appreciated.

Thanks

Dennis



_________________________________________________________________
Are you paid what you're worth? Find out: SEEK Salary Centre
http://a.ninemsn.com.au/b.aspx?URL=3Dhttp%3A%2F%2Fninemsn%2Eseek%2Ecom%2Eau=
%2Fcareer%2Dresources%2Fsalary%2Dcentre%2F%3Ftracking%3Dsk%3Ahet%3Asc%3Anin=
e%3A0%3Ahot%3Atext&_t=3D764565661&_r=3DOCT07_endtext_salary&_m=3DEXT=

--_4869fada-8f61-4674-aa79-c8b5aa965f00_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class=3D'hmmessage'>
I am using PostgreSQL database in a location recognition application, the f=
iles under postgresql/cluster is growing and over a few days it become very=
 large as below. <br>&nbsp;&nbsp;&nbsp; /var/opt/postgresql/cluster/data&nb=
sp; 166015 Kilobytes&nbsp; (the actual database data)<br>&nbsp;&nbsp;&nbsp;=
 /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes&nbsp; (transaction=
 commit status data)<br>&nbsp;&nbsp;&nbsp; /var/opt/postgresql/cluster/sys/=
pg_subtrans&nbsp;&nbsp; 1187032 Kilobytes (transaction status data)<br><br>=
The pg_clog and pg_subtrans can grow when database contains more data and m=
ore queries come in. But these figures looks rather large to me because the=
 queries goes in the database are mainly read operation rather than write o=
ne. I got a few question to ask here:<br>&nbsp;&nbsp;&nbsp;&nbsp; Is there =
a way to control the size of pg_clog and pg_subtrans? <br>&nbsp;&nbsp;&nbsp=
;&nbsp; Is the file size still be normal for the database of this size (166=
MB for cluster/data)?<br>&nbsp; &nbsp;&nbsp; Does the larger pg_subtrans in=
dicate some bad transaction happens in the database?<br><br>You expertise i=
s very appreciated.<br><br>Thanks<br><br>Dennis<br><br><br><br /><hr />Find=
 out: SEEK Salary Centre <a href=3D'http://a.ninemsn.com.au/b.aspx?URL=3Dht=
tp%3A%2F%2Fninemsn%2Eseek%2Ecom%2Eau%2Fcareer%2Dresources%2Fsalary%2Dcentre=
%2F%3Ftracking%3Dsk%3Ahet%3Asc%3Anine%3A0%3Ahot%3Atext&_t=3D764565661&_r=3D=
OCT07_endtext_salary&_m=3DEXT' target=3D'_new'>Are you paid what you're wor=
th?</a></body>
</html>=

--_4869fada-8f61-4674-aa79-c8b5aa965f00_--