X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 859759DC849 for ; Thu, 16 Mar 2006 11:32:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28674-08 for ; Thu, 16 Mar 2006 11:32:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 34C369DC843 for ; Thu, 16 Mar 2006 11:32:31 -0400 (AST) 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 k2GFWZQr016726; Thu, 16 Mar 2006 10:32:35 -0500 (EST) To: Jerry Sievers cc: pgsql-admin@postgresql.org Subject: Re: Curious run-away index build on upgrade to 8.1.3 In-reply-to: References: Comments: In-reply-to Jerry Sievers message dated "16 Mar 2006 09:40:37 -0500" Date: Thu, 16 Mar 2006 10:32:35 -0500 Message-ID: <16725.1142523155@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200603/134 X-Sequence-Number: 20891 Jerry Sievers writes: > What happened was that for a couple minutes the CPU load would > steadily increase and disk activity decrease at the same time. Before > long, one CPU is 100% busy and we let this continue for 2 hours, a > 100x longer than this index usually takes to build. Disk IO dropped > to nothing and remained so. Hm, we were just doing some work in this area a week or two ago, and 8.2 should be materially faster than current releases ... but offhand I don't know why 8.1 would be any worse than earlier versions. Looking in the CVS history shows that the sort logic didn't change at all between 8.0 and 8.1. Are you sure index build on this index really performs differently than it did in 8.0? What platform is this on? > Worse is that the backend that was spinning would not respond to a > cancel nor SIGTERM. Stopping this activity required a -m immediate > shutdown of Postgres. Yeah, we also noticed last week that some of the major loops in btree index build were free of any CHECK_FOR_INTERRUPTS calls :-(. This is fixed for 8.1.4. > If it would be of interest to someone that I truss one of the spinning > processes, I can redo this in an R&D setting and submit the results. truss probably wouldn't show anything interesting; a gprof or oprofile profile could be useful though. regards, tom lane