Thread: 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. 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.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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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. 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.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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)