Re: Really really slow select count(*)

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: felix <crucialfelix(at)gmail(dot)com>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, sthomas(at)peak6(dot)com, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Really really slow select count(*)
Date: 2011-02-07 07:51:50
Message-ID: 4D4FA496.8060206@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Smith 2011-02-07 08:31:52 Re: High load,
Previous Message Shaun Thomas 2011-02-07 05:05:12 Re: Really really slow select count(*)