X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BF7A7D9F25 for ; Mon, 3 Oct 2005 22:54:52 -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 17893-05 for ; Tue, 4 Oct 2005 01:54:48 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B5E65D9F42 for ; Mon, 3 Oct 2005 22:54:50 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j941spu6000984; Mon, 3 Oct 2005 21:54:51 -0400 (EDT) To: Alvaro Herrera , "Jim C. Nasby" , Kevin Grittner , Jeff Kirby , pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: Vacuum Full Analyze Stalled In-reply-to: <14993.1128372520@sss.pgh.pa.us> References: <20051003194114.GD40138@pervasive.com> <20051003201225.GA11527@surnet.cl> <14993.1128372520@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Mon, 03 Oct 2005 16:48:40 -0400" Date: Mon, 03 Oct 2005 21:54:51 -0400 Message-ID: <983.1128390891@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/49 X-Sequence-Number: 19121 [ I just noticed that this thread is happening on pgsql-admin, which is completely inappropriate for discussing bugs in a beta version. Please redirect followups to pgsql-hackers. ] I wrote: > ... The hypothesis I'm thinking about is that VACUUM is trying to do > LockBufferForCleanup() and for some reason it never finishes. I set up a simple-minded reproduction of Kevin's situation: I did create domain dint as int check (value > 0); create table manyd (f1 dint, f2 dint, f3 dint, f4 dint, f5 dint, f6 dint, f7 dint, f8 dint, f9 dint, f10 dint); and then ran ten concurrent clients doing this continuously: insert into manyd values(1,2,3,4,5,6,7,8,9,10); which should be enough to force a lot of indexscans on pg_constraint_contypid_index. I added an additional client doing create domain d1 as int check (value > 0); drop domain d1; to ensure that there were dead rows needing vacuuming in pg_constraint. (BTW, Tatsuo's new version of pg_bench lets me do all this without writing a line of code...) Finally, I added some debug printouts to LockBufferForCleanup so I could see if it was being executed or not. Then I tried both manual and autovacuum-driven vacuums of pg_constraint. I was able to see from the debug printouts that LockBufferForCleanup was sometimes forced to wait in both cases. But it never got "stuck". This eliminates one thing I was worrying about, which was the possibility that the LockBufferForCleanup waiting path was completely broken inside autovacuum for some reason. But it doesn't get us a whole lot closer to a solution. At this point I think we need more info from Kevin and Jeff before we can go further. There must be some additional special feature of their application that makes the problem appear, but what? A stack trace of the stuck process would definitely help... regards, tom lane