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 7598652B95 for ; Thu, 4 Aug 2005 19:52:28 -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 85347-09 for ; Thu, 4 Aug 2005 22:52:15 +0000 (GMT) Received: from rwcrmhc12.comcast.net (rwcrmhc14.comcast.net [204.127.198.54]) by svr1.postgresql.org (Postfix) with ESMTP id 36CFB528B3 for ; Thu, 4 Aug 2005 19:52:14 -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 <2005080422520501400rl67ie>; Thu, 4 Aug 2005 22:52:06 +0000 Subject: Re: Vacuum doesn't end From: Scott Barvick To: Guido Barosio Cc: pgsql-admin@postgresql.org In-Reply-To: References: <1123075702.3469.14.camel@localhost.localdomain> Content-Type: text/plain Message-Id: <1123195925.3470.8.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 04 Aug 2005 18:52:05 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.575 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200508/46 X-Sequence-Number: 18365 strace did show what looked like it was stuck waiting, and it was the same vacuum proc as in the pg_Stat_activity. I had to restart the postmaster and eventually reload the data because no subsequent vacuum would finish properly. Was there anything else I could have done? On that note, if I am just filling tables, extracting the data off, dropping the tables, and then recreating additional tables, do I really need to do a VACUUM? Thanks, Scott On Wed, 2005-08-03 at 09:41, Guido Barosio wrote: > Usually, on this situations, you've got a deadlock or alike. > > What I do is to trace the pid of the vacuum (strace -p pid) and try to > guess if the vacuum > is working or expecting some semaphore to get green (that would be a > problem on the db side). > > If this is the problem, a quick look into the procs running in the > pg_Stat_activity table will give > you an idea of the situation. Prolly, nothing was going on when you > started your vacuum > but somehow, a proc acquired a lock somewhere and the vacuum reached > that somewhere causing > somekinda wait. > > At least, this is very often in my scenery, using 7.4.3 and linux > also. > > Regards, > Guido > > On 8/3/05, Scott Barvick wrote: > 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 > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an > appropriate > subscribe-nomail command to majordomo@postgresql.org so > that your > message can get through to the mailing list cleanly > > > > -- > "Adopting the position that you are smarter than an automatic > optimization algorithm is generally a good way to achieve less > performance, not more" - Tom Lane.