Thread: Reducing excess files in pg_xlog

Hi,

I noticed there's 66 files in my pg_xlog directory.  I changed my checkpoint_segments from 32 to 3 as I noticed it was too high, restarted PosgreSQL, but there are still 66 files in that directory and they're taking up about 1.1G.

How can I get this list of files down?

Thanks

Thom

Thom Brown <thombrown@gmail.com> writes:
> I noticed there's 66 files in my pg_xlog directory.  I changed my
> checkpoint_segments from 32 to 3 as I noticed it was too high, restarted
> PosgreSQL, but there are still 66 files in that directory and they're taking
> up about 1.1G.

> How can I get this list of files down?

Force a checkpoint, if one hasn't happened already.

            regards, tom lane

Hi Tom,

Yeah, I've run a CHECKPOINT too, but no joy.  Still the same number of files.

Thom

Thom Brown <thombrown@gmail.com> writes:
> On 22 March 2010 14:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Force a checkpoint, if one hasn't happened already.

> Yeah, I've run a CHECKPOINT too, but no joy.  Still the same number of
> files.

Hm, it works for me.  What PG version is this exactly?  Are you sure
the active value of checkpoint_segments really changed?  (Use SHOW)

            regards, tom lane

This is version 8.4.3

postgres=# show checkpoint_segments;
 checkpoint_segments
---------------------
 3
(1 row)

Not sure if it's of any relevance, but the file name sequence seems to go a bit weird.

Nov 25 19:49 0000000100000000000000FA
Nov 26 17:25 0000000100000000000000FB
Dec 16 14:50 0000000100000000000000FC
Dec 17 04:00 0000000100000000000000FD
Dec 17 15:26 0000000100000000000000FE
Dec 17 15:27 000000010000000100000000
Dec 17 15:29 000000010000000100000001
Mar 22 14:28 0000000100000000000000BF

And yes, this particular cluster has very low usage at the moment.

Thom

Thom Brown wrote:
> I noticed there's 66 files in my pg_xlog directory.  I changed my
> checkpoint_segments from 32 to 3 as I noticed it was too high,
> restarted PosgreSQL, but there are still 66 files in that directory
> and they're taking up about 1.1G.
> How can I get this list of files down?

A checkpoint after there's been some write activity in the database
should reduce this down to a reasonable number.  If the system has been
idle since the last checkpoint, it doesn't do anything when you ask for
another one, which includes skipping this cleanup; that may be why you
haven't seen it drop yet.

I'm assuming you don't have WAL shipping turned on by setting
archive_command.  There can also be an excess of these segments that
can't be cleaned up if your archiving scheme fails.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Hi Greg,

You're right, I don't have the archiving enabled.  All options are commented out.

I've just forced a new WAL file by writing lots of stuff to a table, then deleting it.  It appears to be recyling the filenames now.  The latest files end in DD and DE, and the earliest is DF.  Presumably these will slowly get eaten up until it's just down to the 3 files I've configured it for?

Thom

Thom Brown <thombrown@gmail.com> writes:
> I've just forced a new WAL file by writing lots of stuff to a table, then
> deleting it.  It appears to be recyling the filenames now.  The latest files
> end in DD and DE, and the earliest is DF.  Presumably these will slowly get
> eaten up until it's just down to the 3 files I've configured it for?

The expected steady state is something like 2*checkpoint_segments+1
files.  See docs.

            regards, tom lane