Re: Vacuum Full Analyze Stalled - Mailing list pgsql-admin

My goal is to avoid vacuum full in production.  My understanding is
that it is never necessary if vacuums are done aggressively enough,
but I felt that while we were in beta test mode, it was worthwhile for
me to have it run periodically, with the verbose option, to provide
information about where we might need to adjust our vacuum
schedule or fsm settings.

Since the long-term blocking on the constraint index occurred, I have
asked that we run these during non-peak loads, and I'm getting to
the point where I think I can be satisfied with the verbose results of
a normal vacuum for these purposes, even though it provides less
detail.  Are there any tools which provide the level of detail you get
from vacuum full verbose without the problems?

When our small table has bloated, we have tried vacuum full in an
attempt to eliminate the bloat, but we have had to resort to reindex
table to clean things up adequately.  We have tried cluster, which
also worked -- but there doesn't seem to me to be any real
advantage over vacuum followed by reindex for our small, frequently
updated table, since it is rarely accessed sequentially.  Am I missing
something there?

-Kevin


>>> "Jim C. Nasby" <jnasby@pervasive.com> 10/03/05 4:48 PM >>>
On Mon, Oct 03, 2005 at 04:37:17PM -0500, Kevin Grittner wrote:
> We will use gdb and strace the next time we see this.
>
> I've tried to be specific about which vacuum is running in all cases.  If
> the posts have been confusing on that issue, I apologize.  I'll try to be
> clear on this in future posts.
>
> To summarize past events, the case involving the constraint index
> was indeed a "vacuum full" of the entire database under heavy load.
> Autovacuum failed to keep the small, high-update table clean in that
> scenario, but I am not sure whether that caused the failure of the
> vacuum full, or was the result of it.  This weekend, it seemed like the
> first thing which failed (and the last) were autovacuum attempts.
> Vacuum full was run through psql during attempts to recover
> performance after the failure of autovacuum caused performance
> to slow noticably.  We didn't capture info which would tell us whether
> the explicit vacuum was blocked by an autovacuum process.

Keep in mind that vacuum full is *very* aggressive for use in a
production environment. It aquires exclusive locks on tables, which
means everything else will grind to a complete halt while it's running.
Unless you have a very specific reason to use vacuum full, you should
just use plain vacuum (not related to autovacuum). If you are going to
vacuum full, you should consider using the cluster command which has
some added benefits.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461