Thread: pg_subtrans directory grows over 1 GB, is there a way to control it?
I am using PostgreSQL database in a location recognition application, the files under postgresql/cluster is growing andover a few days it become very large as below. <br /> /var/opt/postgresql/cluster/data 166015 Kilobytes (the actualdatabase data)<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 />The pg_clogand 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 the file sizestill be normal for the database of this size (166MB for cluster/data)?<br /> Does the larger pg_subtrans indicatesome bad transaction happens in the database?<br /><br />You expertise is very appreciated.<br /><br />Thanks<br/><br />Dennis<br /><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>
Dennis Wang wrote: > > I am using PostgreSQL database in a location recognition application, the files under postgresql/cluster is growing andover 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_clog and pg_subtrans can grow when database contains more data and more queries come in. But these figures looksrather large to me because the queries goes in the database are mainly read operation rather than write one. I got afew question to ask here: > Is there a way to control the 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 database? I think it can be an indication that you're leaving transactions running for too long. Check pg_stat_activity, and if you see "<IDLE> in transaction", start worrying about that. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 andover 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