Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 82F742E00DF for ; Mon, 19 May 2008 03:17:01 -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 54577-03 for ; Mon, 19 May 2008 03:15:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from BAY0-OMC3-S10.bay0.hotmail.com (bay0-omc3-s10.bay0.hotmail.com [65.54.246.210]) by developer.postgresql.org (Postfix) with ESMTP id 55C332E00E1 for ; Mon, 19 May 2008 02:54:00 -0300 (ADT) Received: from BAY137-W42 ([64.4.48.77]) by BAY0-OMC3-S10.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Sun, 18 May 2008 22:53:59 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_c79cf581-7bbb-4dee-923f-511a89d8a777_" X-Originating-IP: [198.135.207.129] From: Dennis Wang To: Tom Lane , Alvaro Herrera CC: Subject: Re: pg_subtrans directory grows over 1 GB, is there a way to control it? Date: Mon, 19 May 2008 16:23:58 +1030 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 19 May 2008 05:53:59.0164 (UTC) FILETIME=[BB693BC0:01C8B974] 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/16 X-Sequence-Number: 6740 --_c79cf581-7bbb-4dee-923f-511a89d8a777_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi tom lane, Alvaro Herrera Thanks for the good information. It is very he= lpful. I checked pg_stat_activity and found that I do have a client connect= ion in ' in transaction' state. I know this could be caused by not co= mmit a transaction at the end of execution. However, because the PostgreSQL= Server has AutoCommit=3Dtrue as default value, the client connection't tra= nsactions should be commited immedidately. It seems the ' in transact= ion' state is not caused by AutoCommit setting. Is there other reasons whic= h could cause a transaction stay in the state and not get finished? As for = autovacuum_freeze_max_age setting, I have not changed its default value. Th= e config value in my database server is as below. Which should create a pg_= clog file about 50MB at maximum according to http://www.postgresql.org/docs= /8.2/interactive/routine-vacuuming.html. #autovacuum_freeze_max_age =3D= 200000000 # maximum XID age before forced vacuum #vacuum_freeze_min_ag= e =3D 100000000 My pg_clog file sometimes can reach to about 148MB, that is= about three times bigger than the expected value. My understanding is that= : the pg_clog records transaction commit status data, not the content of th= e transaction. So a ' in transaction' connection should not cause the= pg_clog get too big, is that correct? An additional information may be use= ful here, I have two schemas in a single database. One is a backup schema c= reated from a database backup dump file, another is the public schema (work= ing schema). When restore the database, I used the backup schema to replace= the public schema, then the old public schem is deleted, and all client co= nnections are stopped and reconnect to the new public schema. Can this kind= of operation could affect pg_clog and pg_subrrans file size? Thanks a lot = for your time. Dennis > To: dennis_02_2002@hotmail.com> CC: pgsql-interface= s@postgresql.org> Subject: Re: [INTERFACES] pg_subtrans directory grows ove= r 1 GB, is there a way to control it? > Date: Fri, 16 May 2008 10:44:31 -04= 00> From: tgl@sss.pgh.pa.us> > Dennis Wang wri= tes:> > I am using PostgreSQL database in a location recognition applicatio= n, the files under postgresql/cluster is growing and over a few days it bec= ome very large as below. > > /var/opt/postgresql/cluster/data 166015 Kiloby= tes (the actual database data)> > /var/opt/postgresql/cluster/sys/pg_clog 7= 4188 Kilobytes (transaction commit status data)> > /var/opt/postgresql/clus= ter/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 loo= k 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 f= orced vacuums.> > regards, tom lane > Date: Fri, 16 May 2008 10:39:39 -0400= > From: alvherre@commandprompt.com> To: dennis_02_2002@hotmail.com> CC: pgs= ql-interfaces@postgresql.org> Subject: Re: [INTERFACES] pg_subtrans directo= ry grows over 1 GB, is there a way to control it?> > Dennis Wang wrote:> > = > > I am using PostgreSQL database in a location recognition application, t= he 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)> > > > The pg_c= log and pg_subtrans can grow when database contains more data and more quer= ies come in. But these figures looks rather large to me because the queries= goes in the database are mainly read operation rather than write one. I go= t a few question to ask here:> > Is there a way to control the size of pg_c= log and pg_subtrans? > > Is the file size still be normal for the database = of this size (166MB for cluster/data)?> > Does the larger pg_subtrans indic= ate some bad transaction happens in the database?> > I think it can be an i= ndication that you're leaving transactions running> for too long. Check pg_= stat_activity, and if you see ' in> transaction', start worrying abou= t that.> > -- > Alvaro Herrera http://www.CommandPrompt.com/> PostgreSQL Re= plication, Consulting, Custom Development, 24x7 support _________________________________________________________________ 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= --_c79cf581-7bbb-4dee-923f-511a89d8a777_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi tom lane, Alvaro Herrera

Thanks for the good informat= ion. It is very helpful.

I checked pg_stat_activity and found = that I do have a client connection in '<IDLE> in transaction' state.&= nbsp;I know this could be caused by not commit a transaction at the end of = execution. However, because the PostgreSQL Server has AutoCo= mmit=3Dtrue as default value, the client connection't transactions sho= uld be commited immedidately. It seems the '<IDLE> in transaction' st= ate is not caused by AutoCommit setting. Is there other reasons which could= cause a transaction stay in the state and not get finished?

A= s for autovacuum_freeze_max_age setting, I have not changed its defaul= t value. The config value in my database server is as below. Which should c= reate a pg_clog file about 50MB at maximum according to http://www.p= ostgresql.org/docs/8.2/interactive/routine-vacuuming.html.
&n= bsp; #autovacuum_freeze_max_age =3D 200000000 # maximum XID age= before forced vacuum
#vacuum_freeze_min_age =3D 1000= 00000

My pg_clog file sometimes can reach to about 148MB, that= is about three times bigger than the expected value. My understanding is t= hat: the pg_clog records transaction commit status data, not the content of= the transaction. So a '<IDLE> in transaction' connection should not = cause the pg_clog get too big, is that correct?

An additional = information may be useful here, I have two schemas in a single databas= e. One is a backup schema created from a database backup dump file, another= is the public schema (working schema). When restore the database, I u= sed the backup schema to replace the public schema, then the old public sch= em is deleted, and all client connections are stopped and reconne= ct to the new public schema. Can this kind of operation could affect p= g_clog and pg_subrrans file size?

Thanks a lot for your time.<= BR>
Dennis

> To: dennis_02_2002@hotmail.com> CC: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES]= pg_subtrans directory grows over 1 GB, is there a way to control it?
&= gt; Date: Fri, 16 May 2008 10:44:31 -0400
> From: tgl@sss.pgh.pa.us>
> Dennis Wang <dennis_02_2002@hotmail.com> writes:
&= gt; > I am using PostgreSQL database in a location recognition applicati= on, the files under postgresql/cluster is growing and over a few days it be= come very large as below.
> > /var/opt/postgresql/cluster/data 16= 6015 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 (trans= action status data)
>
> I think the only way pg_subtrans can g= et that large is if you have a
> client that's been sitting holding a= n open transaction for a very long
> time. Take a look in pg_stat_act= ivity.
>
> The expected size of pg_clog varies depending on wh= at PG version you're
> using. In recent releases you can alter autova= cuum_freeze_max_age to
> trade off the size of pg_clog against the fr= equency of forced vacuums.
>
> regards, tom lane

= > Date: Fri, 16 May 2008 10:39:39 -0400
> From: alvherre@commandpr= ompt.com
> To: dennis_02_2002@hotmail.com
> CC: pgsql-interface= s@postgresql.org
> Subject: Re: [INTERFACES] pg_subtrans directory gr= ows over 1 GB, is there a way to control it?
>
> Dennis Wang w= rote:
> >
> > I am using PostgreSQL database in a locati= on recognition application, the files under postgresql/cluster is growing a= nd over a few days it become very large as below.
> > /var/opt/po= stgresql/cluster/data 166015 Kilobytes (the actual database data)
> &= gt; /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes (transaction co= mmit status data)
> > /var/opt/postgresql/cluster/sys/pg_subtrans = 1187032 Kilobytes (transaction status data)
> >
> > The = pg_clog and pg_subtrans can grow when database contains more data and more = queries come in. But these figures looks rather large to me because the que= ries goes in the database are mainly read operation rather than write one. = I got a few question to ask here:
> > Is there a way to control th= e size of pg_clog and pg_subtrans?
> > 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 the d= atabase?
>
> I think it can be an indication that you're leavi= ng transactions running
> for too long. Check pg_stat_activity, and i= f you see '<IDLE> in
> transaction', start worrying about that.=
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/<= BR>> PostgreSQL Replication, Consulting, Custom Development, 24x7 suppor= t

Find out: SEEK Salary Centre Are= you paid what you're worth? = --_c79cf581-7bbb-4dee-923f-511a89d8a777_--