Thread: General queston on getting rid of unused WAL files

Yesyerday I had an issue where Postgres could not write to a fsm file
and started creating WAL files until it filled up the disk space on the
partition where the WAL files were located and panicked.  I moved the
WASL files to the data partition, restarted postgres, and after about an
hour everything became operational.  The system had created 43GB of WAL
files.  At the earliest opportunity I would like to move the WAL files
to their own set of spindles (since it is currently affecting
performance), but I still have an excessive number of WAL files which
leave me very little space available on the other spindle.
Looking at pg_xlogs, I see that postgres is sequentially reusing all of
the available WAL files, while looking at the log file, it is recycling
approximately 70-80 files every checkpoint, but it is not removing any
files.

2013-01-08 16:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 17:03:41 GMT LOG:  checkpoint complete: wrote 27167 buffers
(6.9%); 0 transaction log file(s) added, 0 removed, 70 recycled;
write=712.867 s, sync=1.534 s, total=714.489 s
2013-01-08 17:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 18:03:48 GMT LOG:  checkpoint complete: wrote 27278 buffers
(6.9%); 0 transaction log file(s) added, 0 removed, 71 recycled;
write=719.655 s, sync=1.689 s, total=721.399 s
2013-01-08 18:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 19:10:33 GMT LOG:  checkpoint complete: wrote 42856 buffers
(10.9%); 0 transaction log file(s) added, 0 removed, 69 recycled;
write=1124.766 s, sync=1.426 s, total=1126.232 s
2013-01-08 19:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 20:03:56 GMT LOG:  checkpoint complete: wrote 27759 buffers
(7.1%); 0 transaction log file(s) added, 0 removed, 81 recycled;
write=727.997 s, sync=1.197 s, total=729.258 s
2013-01-08 20:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 21:03:02 GMT LOG:  checkpoint complete: wrote 25799 buffers
(6.6%); 0 transaction log file(s) added, 0 removed, 70 recycled;
write=674.490 s, sync=1.191 s, total=675.757 s
2013-01-08 21:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 22:03:41 GMT LOG:  checkpoint complete: wrote 27177 buffers
(6.9%); 0 transaction log file(s) added, 0 removed, 66 recycled;
write=712.874 s, sync=1.458 s, total=714.391 s
2013-01-08 22:51:47 GMT LOG:  checkpoint starting: time
2013-01-08 23:02:48 GMT LOG:  checkpoint complete: wrote 25292 buffers
(6.4%); 0 transaction log file(s) added, 0 removed, 66 recycled;
write=660.326 s, sync=1.397 s, total=661.902 s

Can anything be done (even if it requires restarting the postgresql
server) to cause it to stop utilizing all the other WAL files (there are
over 2000 of them).  I need to contain the utilization of all these WAL
files so I can move pg_xlog to another spindle.

Thanks in advance.

Postgres 9.0.4, FreeBSD 8.1/amd64

"Benjamin Krajmalnik" <kraj@servoyant.com> writes:
> Looking at pg_xlogs, I see that postgres is sequentially reusing all of
> the available WAL files, while looking at the log file, it is recycling
> approximately 70-80 files every checkpoint, but it is not removing any
> files.

[ squint... ]  It should be removing those files until it gets under
approximately 2 * checkpoint_segments WAL files.  So, what have you
got checkpoint_segments set to?

            regards, tom lane

It is set to 1024 (I had set it to that level just in case it was needed
due to a peak in workload), and presently I have 2121 WAL files.
Thanks for the info - just reduced it to 256, and will go form there.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, January 08, 2013 7:17 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] General queston on getting rid of unused WAL files

"Benjamin Krajmalnik" <kraj@servoyant.com> writes:
> Looking at pg_xlogs, I see that postgres is sequentially reusing all
> of the available WAL files, while looking at the log file, it is
> recycling approximately 70-80 files every checkpoint, but it is not
> removing any files.

[ squint... ]  It should be removing those files until it gets under
approximately 2 * checkpoint_segments WAL files.  So, what have you got
checkpoint_segments set to?

            regards, tom lane