DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327;
 d=earthlink.net;
	b=WhrXh+lkeSd3d5GgcOjXXQgwrMvK0r8vM+1cd19FYEmu42t8BC6DxDvYNAfFYPU9;
	h=Received:Message-Id:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type:X-ELNK-Trace:X-Originating-IP;
Message-Id: <6.2.5.6.0.20051229173718.01dbfff0@earthlink.net>
Date: Thu, 29 Dec 2005 17:42:51 -0500
To: "Jeffrey W. Baker" <jwbaker@acm.org>,
	pgperf <pgsql-performance@postgresql.org>
From: Ron <rjpeace@earthlink.net>
Subject: Re: Invulnerable VACUUM process thrashing everything
In-Reply-To: <1135894162.2223.4.camel@toonses.gghcwest.com>
References: <1135894162.2223.4.camel@toonses.gghcwest.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Ick.  Can you get users and foreign connections off that machine, 
lock them out for some period, and renice the VACUUM?

Shedding load and keeping it off while VACUUM runs high priority 
might allow it to finish in a reasonable amount of time.
Or
Shedding load and dropping the VACUUM priority might allow a kill 
signal to get through.

Hope this helps,
Ron


At 05:09 PM 12/29/2005, Jeffrey W. Baker wrote:
>A few WEEKS ago, the autovacuum on my instance of pg 7.4 unilaterally
>decided to VACUUM a table which has not been updated in over a year and
>is more than one terabyte on the disk.  Because of the very high
>transaction load on this database, this VACUUM has been ruining
>performance for almost a month.  Unfortunately is seems invulnerable to
>killing by signals:
>
># ps ax | grep VACUUM
>15308 ?        D    588:00 postgres: postgres skunk [local] VACUUM
># kill -HUP 15308
># ps ax | grep VACUUM
>15308 ?        D    588:00 postgres: postgres skunk [local] VACUUM
># kill -INT 15308
># ps ax | grep VACUUM
>15308 ?        D    588:00 postgres: postgres skunk [local] VACUUM
># kill -PIPE 15308
># ps ax | grep VACUUM
>15308 ?        D    588:00 postgres: postgres skunk [local] VACUUM
>
>o/~ But the cat came back, the very next day ...
>
>I assume that if I kill this with SIGKILL, that will bring down every
>other postgres process, so that should be avoided.  But surely there is
>a way to interrupt this.  If I had some reason to shut down the
>instance, I'd be screwed, it seems.