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 710A29DC809 for ; Thu, 29 Dec 2005 18:09:24 -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 11538-02 for ; Thu, 29 Dec 2005 18:09:26 -0400 (AST) X-Greylist: delayed 00:47:22.988824 by SQLgrey- Received: from gghcwest.com (adsl-71-128-90-172.dsl.pltn13.pacbell.net [71.128.90.172]) by postgresql.org (Postfix) with ESMTP id 325FB9DC800 for ; Thu, 29 Dec 2005 18:09:21 -0400 (AST) Received: from toonses.gghcwest.com (toonses.gghcwest.com [192.168.168.115]) by gghcwest.com (8.12.10/8.12.9) with ESMTP id jBTM9Kmd032131 for ; Thu, 29 Dec 2005 14:09:21 -0800 Received: from jwb by toonses.gghcwest.com with local (Exim 4.52) id 1Es5xa-0000aZ-BI for pgsql-performance@postgresql.org; Thu, 29 Dec 2005 14:09:22 -0800 Subject: Invulnerable VACUUM process thrashing everything From: "Jeffrey W. Baker" To: pgperf Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Thu, 29 Dec 2005 14:09:22 -0800 Message-Id: <1135894162.2223.4.camel@toonses.gghcwest.com> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 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: 200512/651 X-Sequence-Number: 16472 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. -jwb