Re: too many clog files - Mailing list pgsql-performance

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.
- - - -
[root@set19: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 gsmith@gregsmith.com 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