Thanks for your reply. Greg wrote: > On Tue, 2 Sep 2008, Duan Ligong wrote: > > - Does Vacuum delete the old clog files? > > Yes, if those transactions are all done. One possibility here is that > you've got some really long-running transaction floating around that is > keeping normal clog cleanup from happening. Take a look at the output > from "select * from pg_stat_activity" and see if there are any really old > transactions floating around.
Well, we could not wait so long and just moved the old clog files. The postgresql system is running well. But now the size of pg_clog has exceeded 50MB and there are 457 clog files. - - - - [EMAIL PROTECTED]:AN0101 hydragui]# du -sh pgdata/pg_clog 117M pgdata/pg_clog - - - - My question is: - How to determine whether there is a long-running transactions or not based on the output of "select * from pg_stat_activity"? It seems there is not the start time of transactions. - How should we deal with it if there is a long-running transactin? Can we do something to avoid long-running transactions? The following is the output of "select * from pg_stat_activity". #It seems there are no query_start time information. - - - - xxxdb=> select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start -------+-------------+---------+----------+----------+---------------+------------- 92406 | xxxdb | 17856 | 100 | myname | | 92406 | xxxdb | 31052 | 100 | myname | | (2 rows) - - - - After about 6minutes, I execute it again and the output is - - - - xxxdb=> select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start -------+-------------+---------+----------+----------+---------------+------------- 92406 | xxxdb | 5060 | 100 |myname | | 92406 | xxxdb | 5626 | 100 |myname | | (2 rows) - - - - #my postgresql version is 8.1 > > - Can we controll the maximum number of the clog files? > > The reference Alvaro suggested at > http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND > > goes over that. If you reduce autovacuum_freeze_max_age, that reduces the > expected maximum size of the clog files. "The default, 200 million > transactions, translates to about 50MB of pg_clog storage." If you've got > significantly more than 50MB of files in there, normally that means that > either you're not running vacuum to clean things up usefully, or there's > an old open transaction still floating around. > > > - When, or in what case is a new clog file produced? > > Every 32K transactions. See http://wiki.postgresql.org/wiki/Hint_Bits for > some clarification about what the clog files actually do. I recently > collected some notes from this list and from the source code README files > to give a high-level view there of what actually goes on under the hood in > this area. It seems that one transaction occupies 2bit and 32K transactions should occupy 8K, which is the size of one page. The size of each clog file is 256KB. Is there other information which is in clog files except the Hint_bits? And Do the other information and Hint_bit of 32K transactions occupy 256KB? > > - Is there a mechanism that the clog files are recycled? If there is , > > what is the mechanism? > > The old ones should get wiped out by vacuum's freezing mechanism. Does Wiping out clog files has something to do with the configuration except Vacuum? Does we have to set some parameter to enable the function of Vacuum's wipping out old clog files? The following is my postgresql.conf file: - - - - tcpip_socket = true max_connections = 500 port = 5432 shared_buffers = 1000 syslog = 2 log_min_messages = fatal - - - - #my postgresql database is very small. Thanks Duan > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
