General queston on getting rid of unused WAL files

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux