X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B7DFE5299C for ; Wed, 3 Aug 2005 10:28:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38347-09 for ; Wed, 3 Aug 2005 13:28:21 +0000 (GMT) Received: from rwcrmhc12.comcast.net (rwcrmhc14.comcast.net [204.127.198.54]) by svr1.postgresql.org (Postfix) with ESMTP id A6AC7529C8 for ; Wed, 3 Aug 2005 10:28:19 -0300 (ADT) Received: from [192.168.110.100] (c-24-147-235-8.hsd1.ma.comcast.net[24.147.235.8]) by comcast.net (rwcrmhc14) with SMTP id <2005080313282301400rifcne>; Wed, 3 Aug 2005 13:28:23 +0000 Subject: Vacuum doesn't end From: Scott Barvick To: pgsql-admin@postgresql.org Content-Type: text/plain Message-Id: <1123075702.3469.14.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 03 Aug 2005 09:28:22 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.75 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200508/29 X-Sequence-Number: 18348 I have an application that may add a couple million rows per day so I vacuum nightly. The tables never get to more than about 10 million rows before I move off the interesting information to other media. Somewhat often, my vacuums don't complete as shown from this ps command (usually VACUUMs take a couple minutes) 4442 ? R 949:07 postgres: postgres tag 127.0.0.1(33420) VACUUM and the CPU for postmaster (hyperthreaded linux) will be in the high 90s. The locks look like: scouts=# select * from pg_locks; relation | database | transaction | pid | mode | granted ----------+----------+-------------+-------+--------------------------+--------- | | 12826125 | 11642 | ExclusiveLock | t 16839 | 17230 | | 11642 | AccessShareLock | t 17251 | 17230 | | 4442 | RowExclusiveLock | t 17251 | 17230 | | 4442 | ShareUpdateExclusiveLock | t 17246 | 17230 | | 4442 | ShareUpdateExclusiveLock | t 17246 | 17230 | | 4442 | ShareUpdateExclusiveLock | t | | 12817402 | 4442 | ExclusiveLock | t (7 rows) I all inserts and maintenance through JDBC and may have inserts going on while a different java thread calls the VACUUM command. Any thoughts? Can I recover without dropping the server? Thanks for any help, Scott