X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8B4879DC9CA for ; Thu, 5 Jan 2006 07:30:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57525-02 for ; Thu, 5 Jan 2006 07:30:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (serval.logix-tt.com [213.239.221.42]) by postgresql.org (Postfix) with ESMTP id 57AD09DC950 for ; Thu, 5 Jan 2006 07:30:00 -0400 (AST) Received: from kingfisher.intern.logix-tt.com (Mfe4e.m.pppool.de [89.49.254.78]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id 610BE24400F; Thu, 5 Jan 2006 12:36:19 +0100 (CET) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 2E48E180001F8; Thu, 5 Jan 2006 12:29:59 +0100 (CET) Message-ID: <43BD0337.60000@logix-tt.com> Date: Thu, 05 Jan 2006 12:29:59 +0100 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Jeffrey W. Baker" Cc: pgperf Subject: Re: Invulnerable VACUUM process thrashing everything References: <1135894162.2223.4.camel@toonses.gghcwest.com> In-Reply-To: <1135894162.2223.4.camel@toonses.gghcwest.com> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/20 X-Sequence-Number: 16498 Hi, Jeffrey, 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. Hmm, maybe this is the Transaction ID wraparound emerging, and VACUUM is freezing the rows. Did you VACUUM FREEZE the table after the last modifications? > # kill -HUP 15308 > # kill -INT 15308 > # kill -PIPE 15308 Did you try kill -TERM? This always cleanly ended VACUUMing backends on our machines within seconds. > 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. Yes, SIGKILL will make the postmaster shut down all running backend instances, the same as SIGSEGV and possibly a few others. The reason is that the postmaster assumes some internal data structure corruption in the shared memory pages is possible on an "unclean" backend abort, and thus quits immediately to minimize the possibility of those corruptions to propagate to the disks. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org