Обсуждение: how much disk space does a VACUUM FULL take?
Due to running low on disk space, we have recently removed a majority of rows from a table to an archival DB. Although VACUUM allows disk space to be re-used, VACUUM FULL is the only one that actively reclaims disk space for use bythe OS. http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html For a variety of reasons I would prefer disk usage to be as low as possible, thus I would like to run a VACUUM FULL duringsome maintenance cycle (since it exclusively locks the table). However, given the details of VACUUM FULL: > VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizesthe size of the table, but can take a long time. It also requires extra disk space for the new copy of the table,until the operation completes. Does this suggest that VACUUM FULL needs free disk space on the order of the full size of the table that it's vacuuming tobe able to complete? Or does it / can it write the filesystem files in the 1GB chunks stored in /base while removing thenew "unused" files at the same time, thus requiring only a few GB of free space? thanks, ~ john
John, > Due to running low on disk space, we have recently removed a majority > of rows from a table to an archival DB. > > Although VACUUM allows disk space to be re-used, VACUUM FULL is the > only one that actively reclaims disk space for use by the OS. > http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html > > For a variety of reasons I would prefer disk usage to be as low as > possible, thus I would like to run a VACUUM FULL during some > maintenance cycle (since it exclusively locks the table). However, you might want to consider setting fillfactor to 100 [1] to completely compact the table (before doing a VACUUM FULL). Though I'm not 100% sure but I assume that VACUUM FULL considers the fillfactor when rewriting the table (maybe someone cancomment on this?). > given the details of VACUUM FULL: > > > VACUUM FULL actively compacts tables by writing a complete new > > version of the table file with no dead space. This minimizes the > > size of the table, but can take a long time. It also requires > > extra disk space for the new copy of the table, until the > > operation completes. > > Does this suggest that VACUUM FULL needs free disk space on the order > of the full size of the table that it's vacuuming to be able to > complete? Or does it / can it write the filesystem files in the 1GB > chunks stored in /base while removing the new "unused" files at the > same time, thus requiring only a few GB of free space? AFAIK a VACUUM FULL frees the old data after having completely written the new version. So the size of the original tableis an upper bound for the space requirement and it can be much less (in case the original table is bloated a lot). Regards, Andreas [1] http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
On Dec 3, 2013, at 3:53 PM, Andreas Brandl <ml@3.141592654.de> wrote: > John, > >> ... >> For a variety of reasons I would prefer disk usage to be as low as >> possible, thus I would like to run a VACUUM FULL during some >> maintenance cycle (since it exclusively locks the table). However, > > you might want to consider setting fillfactor to 100 [1] to completely compact the table (before doing a VACUUM FULL). > > Though I'm not 100% sure but I assume that VACUUM FULL considers the fillfactor when rewriting the table (maybe someonecan comment on this?). In the 9.0 documentation (which we're on) it suggests fillfactor is 100 by default, and I don't believe the table creationoverrode these. This likely means that incoming data will attempt to re-use pages with deleted rows? It does seemthat our database growth has slowed (as reflected with on-disk space), but it never decreases, which is why I was hopingto VACUUM FULL. >> given the details of VACUUM FULL: >> >>> VACUUM FULL actively compacts tables by writing a complete new >>> version of the table file with no dead space. This minimizes the >>> size of the table, but can take a long time. It also requires >>> extra disk space for the new copy of the table, until the >>> operation completes. >> >> Does this suggest that VACUUM FULL needs free disk space on the order >> of the full size of the table that it's vacuuming to be able to >> complete? Or does it / can it write the filesystem files in the 1GB >> chunks stored in /base while removing the new "unused" files at the >> same time, thus requiring only a few GB of free space? > > AFAIK a VACUUM FULL frees the old data after having completely written the new version. So the size of the original tableis an upper bound for the space requirement and it can be much less (in case the original table is bloated a lot). That's what I'm afraid of. Thanks for the anecdote, ~ john
On Tue, Dec 3, 2013 at 4:04 PM, john gale <john@smadness.com> wrote:
Does this suggest that VACUUM FULL needs free disk space on the order of the full size of the table that it's vacuuming to be able to complete? Or does it / can it write the filesystem files in the 1GB chunks stored in /base while removing the new "unused" files at the same time, thus requiring only a few GB of free space?
There are at least two tools out there that compact your table "live" by arranging it such that the trailing pages of your table are empty (by issuing trivial updates), and letting the standard vacuum truncate the file.
On Tue, Dec 3, 2013 at 2:04 PM, john gale <john@smadness.com> wrote: > > Due to running low on disk space, we have recently removed a majority of rows from a table to an archival DB. > > Although VACUUM allows disk space to be re-used, VACUUM FULL is the only one that actively reclaims disk space for useby the OS. http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html > > For a variety of reasons I would prefer disk usage to be as low as possible, thus I would like to run a VACUUM FULL duringsome maintenance cycle (since it exclusively locks the table). However, given the details of VACUUM FULL: > >> VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizesthe size of the table, but can take a long time. It also requires extra disk space for the new copy of the table,until the operation completes. > > Does this suggest that VACUUM FULL needs free disk space on the order of the full size of the table that it's vacuumingto be able to complete? Or does it / can it write the filesystem files in the 1GB chunks stored in /base whileremoving the new "unused" files at the same time, thus requiring only a few GB of free space? Yes, starting with pgsql 9.0 vacuum full writes a new copy of the table out while holding the old copy locked. If the new copy is going to be small then it's not too big of a problem. If it will be large then you need the space to write it out. Note that sometimes making a tablespace on another drive with enough room for the table to fit on twice and using that can be helpful.