Re: too many clog files

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.

> - 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.

> - 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.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD