Обсуждение: Vacuum Full

Hello People,

I have some doubts about Vacuum Full. There We go:
1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean "dead space" on the disc, and reorganize the relation at the physical level?
If it's true, so doing this may speed up select's, while the Postgres will going to do less hit in the disc. I'm right?

2) Doing only Vacuum Analyze I have a enlargement of the parameter max_fsm_pages. Does it have any impact in the operation?
Should I increment the value in the conf to be so large than the Vacuum Verbose shows me?

3) There are differences in performace of Vacuum Full between versions 8.1.4 and 8.3.7?
As soon as possible we are going to migrate the Postgres to 8.3.7, just waiting the finish of the tests of the software in the new version.

4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or Reindex and Vacuum?
Running Vacuum I have a Index Bloat, right?! So I have to run Reindex afet Vacuum?!
If true I'll change the script to make first Vacuum and then Reindex.

5) Does have any way to see how much is left to finish vacuum?
Example:
select * from vacuum_finish;
Relation   Total Bytes    Total Bytes Done      Total Bytes Remaining
Table01    1000             600                          400

Or Something like that!


Thnks for responses,

Rafael Domiciano

On Tue, Mar 31, 2009 at 10:04 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hello People,
>
> I have some doubts about Vacuum Full. There We go:
> 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
> "dead space" on the disc, and reorganize the relation at the physical level?
> If it's true, so doing this may speed up select's, while the Postgres will
> going to do less hit in the disc. I'm right?

Generally speaking, vacuum full should not normally be needed.
Regular vacuuming should free up enough free space that the table
reaches an equilibrium where it has some small percentage of available
space (5 to 15% or so) and stay there.

> 2) Doing only Vacuum Analyze I have a enlargement of the parameter
> max_fsm_pages. Does it have any impact in the operation?
> Should I increment the value in the conf to be so large than the Vacuum
> Verbose shows me?

If the needed fsm settings need to keep increasing then something is wrong.

> 3) There are differences in performace of Vacuum Full between versions 8.1.4
> and 8.3.7?
> As soon as possible we are going to migrate the Postgres to 8.3.7, just
> waiting the finish of the tests of the software in the new version.

Yeah, 8.3 is faster.

> 4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or Reindex
> and Vacuum?
> Running Vacuum I have a Index Bloat, right?! So I have to run Reindex afet
> Vacuum?!
> If true I'll change the script to make first Vacuum and then Reindex.

yep, vacuum full, then reindex.

> 5) Does have any way to see how much is left to finish vacuum?

Not that I know of.

I notice you don't mention autovacuum.  Are you running it?  And if
not, why not?

No, I'm not running autovacuum in the 8.1, but in the 8.3 I'm going to autovacuum by default.

Thnks for Response

Hi Rafael,

On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote:

> > > I have some doubts about Vacuum Full. There We go:
> > > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
> > > "dead space" on the disc, and reorganize the relation at the physical
> > level?
> > > If it's true, so doing this may speed up select's, while the Postgres
> > will
> > > going to do less hit in the disc. I'm right?
> >
> > Generally speaking, vacuum full should not normally be needed.
> > Regular vacuuming should free up enough free space that the table
> > reaches an equilibrium where it has some small percentage of available
> > space (5 to 15% or so) and stay there.
>
> So, why Vacuum Full should not nomally be needed? Vacuum Analyze is enough
> for Maintenance Base?

You should normally be using autovacuum, possibly slightly tuned, to
keep everything nice.

> One more question, If I understood Vacuum Full it's similar to Defrag Tool
> like Defrag Windows. So, like defraging Windows, it's speed up a little (the
> disc is going to read the blocks faster) , Vacuuming Full Postgres should
> have the same behavior?!

VACUUM FULL should be renamed to "I totally bloated my database, please
fix it" or something like that. It is a very intrusive operation
(prevents write access to the table IIRC), so it's nothing for regular
operation. If you need VACUUM FULL, something went wrong before.

Maybe a command name like "UNBLOAT DATABASE" or "REBUILD TABLES"
would be more suited. @Developers: I'm serious. People perceive VACUUM
FULL as just a more intense version of VACUUM.

> > > 2) Doing only Vacuum Analyze I have a enlargement of the parameter
> > > max_fsm_pages. Does it have any impact in the operation?
> > > Should I increment the value in the conf to be so large than the Vacuum
> > > Verbose shows me?
> >
> > If the needed fsm settings need to keep increasing then something is wrong.
>
> The fsm_pages values incresead from 120000 to 320000, and now is around that
> every day.
> On more question, the postgres.conf max_fsm_pages is set to 150000, and
> every vacuum hint me to increase this parameter. Does it have any impact in
> the normal operation of Postgres?

Yes, it takes memory (6 bytes per page). If you have max_fsm_pages too
low, you'll get a bloated database because Postgres cannot keep track of
which pages are useable in a relation - it needs to append at the end!
Then you'll need VACUUM FULL sooner or later.

I've heard a rule of thumb: 65536 pages per GB of data.

> > > 3) There are differences in performace of Vacuum Full between versions
> > 8.1.4
> > > and 8.3.7?
> > > As soon as possible we are going to migrate the Postgres to 8.3.7, just
> > > waiting the finish of the tests of the software in the new version.
> >
> > Yeah, 8.3 is faster.
> >
> > > 4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or
> > Reindex
> > > and Vacuum?
> > > Running Vacuum I have a Index Bloat, right?! So I have to run Reindex
> > afet
> > > Vacuum?!
> > > If true I'll change the script to make first Vacuum and then Reindex.
> >
> > yep, vacuum full, then reindex.

If matters are really worse, it's faster to pg_dump, drop/create and
pg_restore. You'll get a nice fresh database afterwards with no bloat.
I've seen a database perform VACUUM FULL on pg_largeobject for two
weeks! (Only about 200 GB or so of data.)

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de

On Wed, Apr 1, 2009 at 4:10 PM, Tino Schwarze <postgresql@tisc.de> wrote:
> Hi Rafael,
>
> On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote:
>
>> > > I have some doubts about Vacuum Full. There We go:
>> > > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
>> > > "dead space" on the disc, and reorganize the relation at the physical
>> > level?
>> > > If it's true, so doing this may speed up select's, while the Postgres
>> > will
>> > > going to do less hit in the disc. I'm right?
>> >
>> > Generally speaking, vacuum full should not normally be needed.
>> > Regular vacuuming should free up enough free space that the table
>> > reaches an equilibrium where it has some small percentage of available
>> > space (5 to 15% or so) and stay there.
>>
>> So, why Vacuum Full should not nomally be needed? Vacuum Analyze is enough
>> for Maintenance Base?
>
> You should normally be using autovacuum, possibly slightly tuned, to
> keep everything nice.
>
>> One more question, If I understood Vacuum Full it's similar to Defrag Tool
>> like Defrag Windows. So, like defraging Windows, it's speed up a little (the
>> disc is going to read the blocks faster) , Vacuuming Full Postgres should
>> have the same behavior?!
>
> VACUUM FULL should be renamed to "I totally bloated my database, please
> fix it" or something like that. It is a very intrusive operation
> (prevents write access to the table IIRC), so it's nothing for regular
> operation. If you need VACUUM FULL, something went wrong before.
>
> Maybe a command name like "UNBLOAT DATABASE" or "REBUILD TABLES"
> would be more suited. @Developers: I'm serious. People perceive VACUUM
> FULL as just a more intense version of VACUUM.

Yeah, I think rebuild is a much better name for vacuum full.  Vacuum
full is just an historical name now, from the days when vacuum was ony
available in the full flavor.  After regular vacuum was introduced,
the old behaviour was renamed to vacuum full.  At the time this made a
lot of sense.  If you had been running vacuum before that, you just
ran it now, and in much less time and much less intrusively, it
cleaned up dead rows and made them available.  And if you needed the
old behaviour, you just amped up vacuum with full.

Nowadays, with many many new users, and no historical context, they do
just take it for "a better kind of vacuum" when in fact it is really
like reindex to indexes.  I'd vote for rebuild [table]; as the new way
to spell vacuum full;

Scott Marlowe <scott.marlowe@gmail.com> writes:
> Nowadays, with many many new users, and no historical context, they do
> just take it for "a better kind of vacuum" when in fact it is really
> like reindex to indexes.  I'd vote for rebuild [table]; as the new way
> to spell vacuum full;

Well, no, "rebuild" is a pretty lousy description for it.  I'd expect
"rebuild" to mean something like a no-op rewrite in ALTER TABLE.

It is true that VACUUM FULL's use case has decreased nearly to the
vanishing point, and the maintenance effort for it is way out of
proportion to the use case.  Maybe we should remove the code and make
VACUUM FULL do the table-rewrite thing.

            regards, tom lane

On Wed, Apr 01, 2009 at 08:09:31PM -0400, Tom Lane wrote:

> > Nowadays, with many many new users, and no historical context, they do
> > just take it for "a better kind of vacuum" when in fact it is really
> > like reindex to indexes.  I'd vote for rebuild [table]; as the new way
> > to spell vacuum full;
>
> Well, no, "rebuild" is a pretty lousy description for it.  I'd expect
> "rebuild" to mean something like a no-op rewrite in ALTER TABLE.
>
> It is true that VACUUM FULL's use case has decreased nearly to the
> vanishing point, and the maintenance effort for it is way out of
> proportion to the use case.  Maybe we should remove the code and make
> VACUUM FULL do the table-rewrite thing.

What do you mean with "the table-rewrite thing", exactly?

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de

Tino Schwarze <postgresql@tisc.de> writes:
>> Maybe we should remove the code and make
>> VACUUM FULL do the table-rewrite thing.

> What do you mean with "the table-rewrite thing", exactly?

Like CLUSTER, except not bothering to sort the rows: just seqscan the
table, enter all live tuples into a new relation file, then rebuild
the indexes from scratch.

The only real disadvantages I can see for this implementation are
that
(1) it won't work on those system catalogs whose relfilenode can't
be reassigned (pg_class and shared catalogs, at least).
(2) it requires extra disk space during the rebuild; although the
argument that VACUUM FULL works in-place is kinda shaky when you
consider its effect on indexes.

            regards, tom lane

Hi Tom,

On Wed, Apr 01, 2009 at 08:58:54PM -0400, Tom Lane wrote:

> >> Maybe we should remove the code and make
> >> VACUUM FULL do the table-rewrite thing.
>
> > What do you mean with "the table-rewrite thing", exactly?
>
> Like CLUSTER, except not bothering to sort the rows: just seqscan the
> table, enter all live tuples into a new relation file, then rebuild
> the indexes from scratch.
>
> The only real disadvantages I can see for this implementation are
> that
> (1) it won't work on those system catalogs whose relfilenode can't
> be reassigned (pg_class and shared catalogs, at least).
> (2) it requires extra disk space during the rebuild; although the
> argument that VACUUM FULL works in-place is kinda shaky when you
> consider its effect on indexes.

I remember several people (including myself) who used VACUUM FULL as a
last resort when their database was bloated and they run out of disk
space. But this seems to be moot, anyway, so it's probably better not to
offer something that might or might not work in such a situation (and
takes loooong) but tell them straight: "You need lots of disk space or
you need to dump/drop/restore your database".

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de

Tino Schwarze wrote:

> I remember several people (including myself) who used VACUUM FULL as a
> last resort when their database was bloated and they run out of disk
> space. But this seems to be moot, anyway, so it's probably better not to
> offer something that might or might not work in such a situation (and
> takes loooong) but tell them straight: "You need lots of disk space or
> you need to dump/drop/restore your database".

I guess this was an easier mistake to make back when autovacuum was
newer or inexistant.  Nowadays, you install a 8.3 cluster, you already
got some protection.  And in 8.4 with the dead space map it will be even
more difficult.

Not saying it'll be impossible to get in that situation, but should be
rare enough that we can suggest other workarounds when need arises.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

I'm planning to upgrade. Is there a updated release schedule for 8.4?


Thanks.

Ben Kim

Alvaro Herrera <alvherre@commandprompt.com> wrote:
> rare enough that we can suggest other workarounds when need arises.

Yeah, I can't remember ever being glad I tried a VACUUM FULL.  I think
I once allowed it a week and a half to try to finish in a situation
where there wasn't enough disk space for a rewrite (CLUSTER or the
clever side-effect trick with ALTER TABLE).  I even dropped all
indexes and the primary key first, since I figured I would just need
to rebuild them again if they were there, and it would slow down the
VACUUM FULL.  In the end, we killed it and used pg_dump and psql.
That ran in about a day.

-Kevin

Kevin Grittner wrote:

> Yeah, I can't remember ever being glad I tried a VACUUM FULL.  I think
> I once allowed it a week and a half to try to finish in a situation
> where there wasn't enough disk space for a rewrite (CLUSTER or the
> clever side-effect trick with ALTER TABLE).  I even dropped all
> indexes and the primary key first, since I figured I would just need
> to rebuild them again if they were there, and it would slow down the
> VACUUM FULL.  In the end, we killed it and used pg_dump and psql.
> That ran in about a day.

Yah.  The worst part about VACUUM FULL is that if you interrupt it in
the middle, it bloats the table even more and nothing is cleaned up at
all.  Worse, if you interrupt it at the wrong time (after the first
phase is finished I think), you can even get your server to PANIC, which
is not something that we allow any other command to do AFAIK.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, Apr 02, 2009 at 08:55:48AM -0500, Ben Kim wrote:
>
> I'm planning to upgrade. Is there a updated release schedule for 8.4?
>
>
> Thanks.
>
> Ben Kim
>
Ha, ha, ha, ha... That's a good one.

Seriously, we have not even started beta testing.

Cheers,
Ken

Ben Kim <bkim@tamu.edu> writes:
> I'm planning to upgrade. Is there a updated release schedule for 8.4?

[ all together now... ]  "When it's ready!"

At this point an informed guess would be "sometime this summer", but
it's strictly a guess.

            regards, tom lane

I guess I asked it the wrong way at the wrong time... (in the cross-fire of
jokes).

With today's post about the vacuum full ("in 8.4 with the dead space map
..." ), I was checking when 8.4 is coming out but these were all I could
find.

http://developer.postgresql.org/index.php/PostgreSQL_8.4_Development_Plan

http://www.postgresonline.com/journal/index.php?/archives/56-PostgreSQL-8.4-goodies-in-store.html

Both are about a year old articles and point to 3/1/2009 release.


Regards,

Ben Kim


On Thu, 2 Apr 2009, Tom Lane wrote:

> Ben Kim <bkim@tamu.edu> writes:
>> I'm planning to upgrade. Is there a updated release schedule for 8.4?
>
> [ all together now... ]  "When it's ready!"
>
> At this point an informed guess would be "sometime this summer", but
> it's strictly a guess.
>
>             regards, tom lane
>

On Thu, Apr 2, 2009 at 12:41 PM, Ben Kim <bkim@tamu.edu> wrote:
>
> I guess I asked it the wrong way at the wrong time... (in the cross-fire of
> jokes).

Best way to gauge this stuff is to trawl through the -hackers and
associated lists.