Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TsktS-0006jW-5X for pgsql-admin@arkaria.postgresql.org; Wed, 09 Jan 2013 01:55:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TsktR-0007Ww-4u for pgsql-admin@arkaria.postgresql.org; Wed, 09 Jan 2013 01:55:49 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TsktQ-0007Wm-2c for pgsql-admin@postgresql.org; Wed, 09 Jan 2013 01:55:48 +0000 Received: from [64.207.29.137] (helo=mail.illumen.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TsktN-0002PG-UE for pgsql-admin@postgresql.org; Wed, 09 Jan 2013 01:55:46 +0000 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: General queston on getting rid of unused WAL files Date: Tue, 8 Jan 2013 18:55:33 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: General queston on getting rid of unused WAL files Thread-Index: Ac3uCtT2Zgtt+44jRlaBqEN+Xa/aig== From: "Benjamin Krajmalnik" To: X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.207.29.137 (failed) X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org 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. =20 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=3D712.867 s, sync=3D1.534 s, total=3D714.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=3D719.655 s, sync=3D1.689 s, total=3D721.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=3D1124.766 s, sync=3D1.426 s, total=3D1126.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=3D727.997 s, sync=3D1.197 s, total=3D729.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=3D674.490 s, sync=3D1.191 s, total=3D675.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=3D712.874 s, sync=3D1.458 s, total=3D714.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=3D660.326 s, sync=3D1.397 s, total=3D661.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 --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin