Subject: Re: Invulnerable VACUUM process thrashing everything
From: "Jeffrey W. Baker" <jwbaker@acm.org>
To: Russ Garrett <russ@garrett.co.uk>
Cc: pgperf <pgsql-performance@postgresql.org>
In-Reply-To: <43B468D7.2050806@garrett.co.uk>
References: <1135894162.2223.4.camel@toonses.gghcwest.com>
	<6.2.5.6.0.20051229173718.01dbfff0@earthlink.net>
	<43B468D7.2050806@garrett.co.uk>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Thu, 29 Dec 2005 18:16:02 -0800
Message-Id: <1135908962.2908.3.camel@toonses.gghcwest.com>
Mime-Version: 1.0

On Thu, 2005-12-29 at 22:53 +0000, Russ Garrett wrote:
> In my experience a kill -9 has never resulted in any data loss in this 
> situation (it will cause postgres to detect that the process died, shut 
> down, then recover), and most of the time it only causes a 5-10sec 
> outage. I'd definitely hesitate to recommend it in a production context 
> though, especially since I think there are some known race-condition 
> bugs in 7.4.
> 
> VACUUM *will* respond to a SIGTERM, but it doesn't check very often - 
> I've often had to wait hours for it to determine that it's been killed, 
> and my tables aren't anywhere near 1TB. Maybe this is a place where 
> things could be improved...

FWIW, I murdered this process with SIGKILL, and the recovery was very
short.


> Incidentally, I have to kill -9 some of our MySQL instances quite 
> regularly because they do odd things. Not something you want to be 
> doing, especially when MySQL takes 30mins to recover.

Agreed.  After mysql shutdown with MyISAM, all tables must be checked
and usually many need to be repaired.  This takes a reallllllly long
time.

-jwb

> Russ Garrett
> Last.fm Ltd.
> russ@last.fm
> 
> Ron wrote:
> 
> > 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.
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>