X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4C610D1BADD for ; Fri, 3 Oct 2003 20:43:39 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 39688-06 for ; Fri, 3 Oct 2003 17:42:52 -0300 (ADT) Received: from jump.bivio.com (jump.bivio.com [216.87.82.232]) by svr1.postgresql.org (Postfix) with ESMTP id E62ACD1BA87 for ; Fri, 3 Oct 2003 17:24:46 -0300 (ADT) Received: (from nagler@localhost) by jump.bivio.com (8.11.6/8.11.6) id h93KOgL20962; Fri, 3 Oct 2003 14:24:42 -0600 X-Authentication-Warning: jump.bivio.com: nagler set sender to nagler@bivio.biz using -f From: Rob Nagler MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16253.56074.737161.470428@jump.bivio.com> Date: Fri, 3 Oct 2003 14:24:42 -0600 To: pgsql-performance@postgresql.org Subject: reindex/vacuum locking/performance? X-Mailer: VM 6.96 under Emacs 20.7.1 Organization: bivio Software Artisans, Inc. X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/105 X-Sequence-Number: 3853 I've read some posts that says vacuum doesn't lock, but my experience today indicates the opposite. It seemed that "vacuum full analyze" was locked waiting and so were other postmaster processes. It appeared to be deadlock, because all were in "WAITING" state according to ps. I let this go for about a 1/2 hour, and then killed the vacuum at which point all other processes completed normally. The same thing seemed to be happening with reindex on a table. It seems that the reindex locks the table and some other resource which then causes deadlock with other active processes. Another issue seems to be performance. A reindex on some indexes is taking 12 minutes or so. Vacuum seems to be slow, too. Way longer than the time it takes to reimport the entire database (30 mins). In summary, I suspect that it is better from a UI perspective to bring down the app on Sat at 3 a.m and reimport with a fixed time period than to live through reindexing/vacuuming which may deadlock. Am I missing something? Thanks, Rob