From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dennis Wang <dennis_02_2002@hotmail.com>
Cc: Postgresql Help <pgsql-interfaces@postgresql.org>
Subject: Re: pg_subtrans directory grows over 1 GB, is there a way to control it?
Date: Fri, 16 May 2008 10:44:31 -0400
Message-ID: <16996.1210949071@sss.pgh.pa.us> (raw)
In-Reply-To: <BAY137-W50DCFFB333D7318AD4F557D6C80@phx.gbl>
References: <BAY137-W50DCFFB333D7318AD4F557D6C80@phx.gbl>
Dennis Wang <dennis_02_2002@hotmail.com> writes:
> I am using PostgreSQL database in a location recognition application, the files under postgresql/cluster is growing and over a few days it become very large as below.
> /var/opt/postgresql/cluster/data 166015 Kilobytes (the actual database data)
> /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes (transaction commit status data)
> /var/opt/postgresql/cluster/sys/pg_subtrans 1187032 Kilobytes (transaction status data)
I think the only way pg_subtrans can get that large is if you have a
client that's been sitting holding an open transaction for a very long
time. Take a look in pg_stat_activity.
The expected size of pg_clog varies depending on what PG version you're
using. In recent releases you can alter autovacuum_freeze_max_age to
trade off the size of pg_clog against the frequency of forced vacuums.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, dennis_02_2002@hotmail.com
Subject: Re: pg_subtrans directory grows over 1 GB, is there a way to control it?
In-Reply-To: <16996.1210949071@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox