Re: pg_subtrans directory grows over 1 GB, is there a way to control it?

Поиск
Список
Период
Сортировка
От Dennis Wang
Тема Re: pg_subtrans directory grows over 1 GB, is there a way to control it?
Дата
Msg-id BAY137-W42F60F8835863ADC9C4202D6C50@phx.gbl
обсуждение исходный текст
Список pgsql-interfaces
Hi tom lane, Alvaro Herrera <br /> <br />Thanks for the good information. It is very helpful.<br /> <br />I checked
pg_stat_activityand found that I do have a client connection in '<IDLE> in transaction' state. I know this could
becaused by not commit a transaction at the end of execution. However, because the PostgreSQL
Server has AutoCommit=trueas default value, the client connection't transactions should be commited immedidately. It
seemsthe '<IDLE> in transaction' state is not caused by AutoCommit setting. Is there other reasons which could
causea transaction stay in the state and not get finished?<br /> <br />As for autovacuum_freeze_max_age setting, I have
notchanged its default value. The config value in my database server is as below. Which should create a pg_clog file
about50MB at maximum according to <a
href="http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html">http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html</a>.
<br/>    #autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum<br />    #vacuum_freeze_min_age
=100000000<br /> <br />My pg_clog file sometimes can reach to about 148MB, that is about three times bigger than the
expectedvalue. My understanding is that: 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?<br
/> <br/>An additional information may be useful here, I have two schemas in a single database. One is a backup schema
createdfrom a database backup dump file, another is the public schema (working schema). When restore the database, I
usedthe backup schema to replace the public schema, then the old public schem is deleted, and all client
connections arestopped and reconnect to the new public schema. Can this kind of operation could affect pg_clog and
pg_subrransfile size?<br /> <br />Thanks a lot for your time.<br /> <br />Dennis<br /> <br /><br />> To:
dennis_02_2002@hotmail.com<br/>> CC: pgsql-interfaces@postgresql.org<br />> Subject: Re: [INTERFACES] pg_subtrans
directorygrows over 1 GB, is there a way to control it? <br />> Date: Fri, 16 May 2008 10:44:31 -0400<br />>
From:tgl@sss.pgh.pa.us<br />> <br />> Dennis Wang <dennis_02_2002@hotmail.com> writes:<br />> > I am
usingPostgreSQL database in a location recognition application, the files under postgresql/cluster is growing and over
afew days it become very large as below. <br />> > /var/opt/postgresql/cluster/data 166015 Kilobytes (the actual
databasedata)<br />> > /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes (transaction commit status
data)<br/>> > /var/opt/postgresql/cluster/sys/pg_subtrans 1187032 Kilobytes (transaction status data)<br />>
<br/>> I think the only way pg_subtrans can get that large is if you have a<br />> client that's been sitting
holdingan open transaction for a very long<br />> time. Take a look in pg_stat_activity.<br />> <br />> The
expectedsize of pg_clog varies depending on what PG version you're<br />> using. In recent releases you can alter
autovacuum_freeze_max_ageto<br />> trade off the size of pg_clog against the frequency of forced vacuums.<br />>
<br/>> regards, tom lane<br /> <br />> Date: Fri, 16 May 2008 10:39:39 -0400<br />> From:
alvherre@commandprompt.com<br/>> To: dennis_02_2002@hotmail.com<br />> CC: pgsql-interfaces@postgresql.org<br
/>>Subject: Re: [INTERFACES] pg_subtrans directory grows over 1 GB, is there a way to control it?<br />> <br
/>>Dennis Wang wrote:<br />> > <br />> > 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. <br />>
>/var/opt/postgresql/cluster/data 166015 Kilobytes (the actual database data)<br />> >
/var/opt/postgresql/cluster/sys/pg_clog74188 Kilobytes (transaction commit status data)<br />> >
/var/opt/postgresql/cluster/sys/pg_subtrans1187032 Kilobytes (transaction status data)<br />> > <br />> >
Thepg_clog and pg_subtrans can grow when database contains more data and more queries come in. But these figures looks
ratherlarge to me because the queries goes in the database are mainly read operation rather than write one. I got a few
questionto ask here:<br />> > Is there a way to control the size of pg_clog and pg_subtrans? <br />> > Is
thefile size still be normal for the database of this size (166MB for cluster/data)?<br />> > Does the larger
pg_subtransindicate some bad transaction happens in the database?<br />> <br />> I think it can be an indication
thatyou're leaving transactions running<br />> for too long. Check pg_stat_activity, and if you see '<IDLE>
in<br/>> transaction', start worrying about that.<br />> <br />> -- <br />> Alvaro Herrera
http://www.CommandPrompt.com/<br/>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support<br /><br
/><br/><hr />Find out: SEEK Salary Centre <a
href="http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Eseek%2Ecom%2Eau%2Fcareer%2Dresources%2Fsalary%2Dcentre%2F%3Ftracking%3Dsk%3Ahet%3Asc%3Anine%3A0%3Ahot%3Atext&_t=764565661&_r=OCT07_endtext_salary&_m=EXT"
target="_new">Areyou paid what you're worth?</a> 

В списке pgsql-interfaces по дате отправления:

Предыдущее
От: "Jonah H. Harris"
Дата:
Сообщение: Re: Newbie problem with from database in C
Следующее
От: clarkhorse@clarktx.com
Дата:
Сообщение: Re: Newbie problem with from database in C