From: Benjamin Krajmalnik <kraj@servoyant.com>
To: pgsql-admin@postgresql.org
Subject: General queston on getting rid of unused WAL files
Date: Tue, 8 Jan 2013 18:55:33 -0700
Message-ID: <F4E6A2751A2823418A21D4A160B6898892B7EC@fletch.stackdump.local> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kraj@servoyant.com
Subject: Re: General queston on getting rid of unused WAL files
In-Reply-To: <F4E6A2751A2823418A21D4A160B6898892B7EC@fletch.stackdump.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox