Any hints on how to limit WAL file disk usage?
I have an embedded application where we use PostgreSQL to store
configuration data. For the past few years, we were on a 6.x system,
and are finally trying to update to 7.1.2. One of the issues I face is
that the WAL files occupy a pretty significant amount of disk space. We
have figured out how to reduce the size of the files (we are using 500K,
and it seems to be OK), but whats not clear is how we can limit the
number of files to some maximum value (say 3 or 4). The configuration
variables seem to provide a guideline for this, but I have seen the
actual number of files exceed these many times. We don't do large
updates/inserts as a rule, and for this application it would be better
to wait while the files are committed rather than overrun the maximum
number. The filesystems are all memory based, and we have a hard limit.
Anyone have any pointers? I've done some cursory examination of the
code, but was hoping I might get some pointers to speed my progress.
TIA,
Bob Crowe
RCrowe@stbernard.com
RCrowe@stbernard.com writes:
... One of the issues I face is
that the WAL files occupy a pretty significant amount of disk space.
Anyone have any pointers?
First off, install the patch depicted at
http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html
CVS tip includes some further hacking that limits the number of WAL
segment files to 2*CHECKPOINT_SEGMENTS + WAL_FILES + 1 --- ie, 112
megabytes with default settings. If that's still in the "whoa, no way"
range for you, I think the most appropriate attack would be to reduce
the WAL segment size to something less than the normal 16Mb. See
XLogSegSize in src/include/access/xlog.h. For a low-traffic
installation I suspect you could get away with 1Mb or so. (It wasn't
entirely clear from your message whether you'd already discovered this
setting.)
regards, tom lane
Thanks that will help a lot. I searched through July and August
archives, but should have gone back to June too :(
I'd like to keep the total space consumed by the WAL files to under
3 MB or so. Not sure if thats practical or not. I'll experiment with
the provided patch. We did figure out how to reduce the WAL segment
size, and that helped a lot.
Thanks again,
Bob Crowe.
Show quoted text
On 3 Aug, Tom Lane wrote:
RCrowe@stbernard.com writes:
... One of the issues I face is
that the WAL files occupy a pretty significant amount of disk space.
Anyone have any pointers?First off, install the patch depicted at
http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.htmlCVS tip includes some further hacking that limits the number of WAL
segment files to 2*CHECKPOINT_SEGMENTS + WAL_FILES + 1 --- ie, 112
megabytes with default settings. If that's still in the "whoa, no way"
range for you, I think the most appropriate attack would be to reduce
the WAL segment size to something less than the normal 16Mb. See
XLogSegSize in src/include/access/xlog.h. For a low-traffic
installation I suspect you could get away with 1Mb or so. (It wasn't
entirely clear from your message whether you'd already discovered this
setting.)regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)