Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id B4BD51337B34 for ; Mon, 7 Feb 2011 03:52:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 38676-07 for ; Mon, 7 Feb 2011 07:51:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9]) by mail.postgresql.org (Postfix) with ESMTP id 1345B13364DE for ; Mon, 7 Feb 2011 03:51:55 -0400 (AST) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis) id 0MN95A-1PoZsF1nQG-0078Jq; Mon, 07 Feb 2011 08:51:52 +0100 Message-ID: <4D4FA496.8060206@2ndquadrant.com> Date: Mon, 07 Feb 2011 02:51:50 -0500 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: felix CC: Scott Marlowe , sthomas@peak6.com, "pgsql-performance@postgresql.org" Subject: Re: Really really slow select count(*) References: <4D4C138C.7010304@2ndquadrant.com> <4D4C1555.40209@peak6.com> <4D4C2AC3.5020804@peak6.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:TGKG08vUPfLP0UFYnNDvpWNKX55N84yoMblt5tUo3mI CbWueLm3KtWLfjpozH8U1h9HazMeAz+fhQAYreSoatmMd/FL0q RVTlf8FUNvf7Ney7ORHx2mhBcmoY2TXAah5eYRTZxhV8qkd71S Nyg7QBSZcjvBHbYVP/KmWWu6zVOCN4fX5+X1Y3UA1g/X+yxBBF iJgDEAT1egw1o4qUtIRoZ6qFKYBRoS7Vm29kb/HtgA= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201102/281 X-Sequence-Number: 42419 felix wrote: > So is it normal for postgres to report that it failed to shut down, > operate for an hour and then go ahead and restart itself ? You've already gotten a few explanations for why waiting for connections can cause this. I'll only add that it is critical to be watching the database log file when doing work like this with PostgreSQL. Go back and check it if you still have the data from when your problematic restart attempt happened, normally you'll get some warnings about it starting to shutdown. Try to look for the actual server shutdown message and then the restart one after doing this sort of thing. If you don't see them when you do this again, you'll know something unexpected is happening, and then to look into what that is. Also, as a general downtime commentary born from years of being the receiving end of outages, I'd recommend against ever doing any server maintenance operation for the first time just before bedtime. While that may be convienent from a "less users are using the site" perspective, the downside is what you've seen here: mistakes can mean rather extended outages. Better to get up early and do this sort of thing instead, so you can watch the site afterwards for a few hours to make sure nothing is broken. For similar reasons I try to avoid ever doing major changes on a Friday. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books