Обсуждение: vacuum full table - internals in 8.3
Hi,
I'm trying to understand what is going on internally when doing a VACUUM FULL on a table in 8.3.
I have a table that is 1GB in size, 500M is used, and 500M is free space. When I do a vacuum full
on this table, will it either: -
1) Compact all of the used tuples into free space within the existing disk file and then shrink the file
to 500M. Therefore simply freeing up 500M in the disk file.
2) Rewrite the table data to a new disk file with no free space. Once it has finished, then removes
the old copy of the table. I took this from http://developer.postgresql.org/pgdocs/postgres/sql-vacuum.html
, but I don't know whether this is how it worked prior to version 9.
In the case of 2) I would therefore need at least 500M free space in the filesystem whist it writes out
the new table, and once complete, 1GB would then be freed up when the old copy of the table is
deleted.
Regards
Kieren
I'm trying to understand what is going on internally when doing a VACUUM FULL on a table in 8.3.
I have a table that is 1GB in size, 500M is used, and 500M is free space. When I do a vacuum full
on this table, will it either: -
1) Compact all of the used tuples into free space within the existing disk file and then shrink the file
to 500M. Therefore simply freeing up 500M in the disk file.
2) Rewrite the table data to a new disk file with no free space. Once it has finished, then removes
the old copy of the table. I took this from http://developer.postgresql.org/pgdocs/postgres/sql-vacuum.html
, but I don't know whether this is how it worked prior to version 9.
In the case of 2) I would therefore need at least 500M free space in the filesystem whist it writes out
the new table, and once complete, 1GB would then be freed up when the old copy of the table is
deleted.
Regards
Kieren
Kieren Scott <kierenscott@hotmail.com> wrote: > I'm trying to understand what is going on internally when doing a > VACUUM FULL on a table in 8.3. > > I have a table that is 1GB in size, 500M is used, and 500M is free > space. When I do a vacuum full on this table, will it either: - > > 1) Compact all of the used tuples into free space within the > existing disk file and then shrink the file to 500M. Therefore > simply freeing up 500M in the disk file. On 8.3, this is what it will do, although it can take a very long time. I've given up on this before completion (sometimes after leaving it cranking away for a couple days) every time I've tried it on a table with more than a few GB and any significant bloat. I don't know that I've ever tried it on a table as small as you describe, but I would bet that CLUSTER is going to be much faster if you have the half a GB free space. Another issue with VACUUM FULL is that it bloats indexes; so you generally need to follow it with a REINDEX on the table. -Kevin
Thanks Kevin. That confirms what I've seen on 8.3.
Could you explain what causes index bloat when running vacuum full? I've
read that index bloat can occur, but no quite sure what is going on internally.
Kieren
> Date: Tue, 5 Oct 2010 10:24:26 -0500
> From: Kevin.Grittner@wicourts.gov
> To: kierenscott@hotmail.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] vacuum full table - internals in 8.3
>
> Kieren Scott <kierenscott@hotmail.com> wrote:
>
> > I'm trying to understand what is going on internally when doing a
> > VACUUM FULL on a table in 8.3.
> >
> > I have a table that is 1GB in size, 500M is used, and 500M is free
> > space. When I do a vacuum full on this table, will it either: -
> >
> > 1) Compact all of the used tuples into free space within the
> > existing disk file and then shrink the file to 500M. Therefore
> > simply freeing up 500M in the disk file.
>
> On 8.3, this is what it will do, although it can take a very long
> time. I've given up on this before completion (sometimes after
> leaving it cranking away for a couple days) every time I've tried it
> on a table with more than a few GB and any significant bloat. I
> don't know that I've ever tried it on a table as small as you
> describe, but I would bet that CLUSTER is going to be much faster if
> you have the half a GB free space. Another issue with VACUUM FULL
> is that it bloats indexes; so you generally need to follow it with a
> REINDEX on the table.
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
Could you explain what causes index bloat when running vacuum full? I've
read that index bloat can occur, but no quite sure what is going on internally.
Kieren
> Date: Tue, 5 Oct 2010 10:24:26 -0500
> From: Kevin.Grittner@wicourts.gov
> To: kierenscott@hotmail.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] vacuum full table - internals in 8.3
>
> Kieren Scott <kierenscott@hotmail.com> wrote:
>
> > I'm trying to understand what is going on internally when doing a
> > VACUUM FULL on a table in 8.3.
> >
> > I have a table that is 1GB in size, 500M is used, and 500M is free
> > space. When I do a vacuum full on this table, will it either: -
> >
> > 1) Compact all of the used tuples into free space within the
> > existing disk file and then shrink the file to 500M. Therefore
> > simply freeing up 500M in the disk file.
>
> On 8.3, this is what it will do, although it can take a very long
> time. I've given up on this before completion (sometimes after
> leaving it cranking away for a couple days) every time I've tried it
> on a table with more than a few GB and any significant bloat. I
> don't know that I've ever tried it on a table as small as you
> describe, but I would bet that CLUSTER is going to be much faster if
> you have the half a GB free space. Another issue with VACUUM FULL
> is that it bloats indexes; so you generally need to follow it with a
> REINDEX on the table.
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
Kieren Scott <kierenscott@hotmail.com> wrote: > Could you explain what causes index bloat when running vacuum > full? To collapse the space, it copies tuples to locations closer to the front of the table. The index needs to contain references to the old and new tuple copies until the VACUUM FULL commits or rolls back. VACUUM FULL doesn't attempt to reorganize the index or free index space, so the space previously held by index entries pointing to the old tuples (if you commit) or the new tuples (if you roll back) will be dead space. It may be reused by later index insertions, but the empty space is likely to cause a lot of partially-filled blocks to bog down performance unless there is a REINDEX. -Kevin
Kieren Scott wrote:
The info you've gotten from Kevin is all correct, but you may find some of the additional trivia in this area collected at http://wiki.postgresql.org/wiki/VACUUM_FULL interesting as well.
I'm trying to understand what is going on internally when doing a VACUUM FULL on a table in 8.3.
The info you've gotten from Kevin is all correct, but you may find some of the additional trivia in this area collected at http://wiki.postgresql.org/wiki/VACUUM_FULL interesting as well.
-- Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance" Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book