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 DD3C8D1B50E for ; Fri, 3 Oct 2003 22:20:27 +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 51284-10 for ; Fri, 3 Oct 2003 19:19:42 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 26720D1B506 for ; Fri, 3 Oct 2003 19:19:40 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h93MJdQl054653 for ; Fri, 3 Oct 2003 22:19:39 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h93M9xUb053627 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 22:09:59 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: reindex/vacuum locking/performance? Date: Fri, 03 Oct 2003 17:34:14 -0400 Organization: Hub.Org Networking Services Lines: 47 Message-ID: <60d6de9fah.fsf@dev6.int.libertyrms.info> References: <16253.56074.737161.470428@jump.bivio.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:PxzAec+YnnTgAP4sW75pZIs4aOc= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/119 X-Sequence-Number: 3867 nagler@bivio.biz (Rob Nagler) writes: > 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. VACUUM FULL certainly does lock. See the man page: INPUTS FULL Selects ``full'' vacuum, which may reclaim more space, but takes much longer and exclusively locks the table. The usual answer is that you probably _didn't_ want to VACUUM FULL. VACUUM ('no full') does NOT block updates. > 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. Not surprising either. While the reindex takes place, updates to that table have to be deferred. > 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). That seems a little surprising. > 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? Consider running pg_autovacuum, and thereby do a little bit of vacuuming here and there all the time. It DOESN'T block, so unless your system is really busy, it shouldn't slow things down to a major degree. -- "cbbrowne","@","libertyrms.info" Christopher Browne (416) 646 3304 x124 (land)