Re: simple update queries take a long time - postgres 8.3.1

On Wed, 2 Apr 2008, mark wrote:

> with no clients connected to the database when I try to shutdown the
> database [to apply new settings], it says database cant be shutdown..
> for a long time both in smart and normal mode... then i had to go to
> immediate mode to shut down.. but then when i start it again.. it goes
> into recovery mode and runs for a while..is this the long recover time
> you are talking about?

Exactly; doing an immediate shutdown is one way (probably the safest way
in fact) to force the database into recovery mode when it starts back up
again to see how long a recovery will take. As you increase
checkpoint_segments, the amount of time it will take to process all the
segments before the database comes up will increase about linearly.
Double checkpoint_segments and you will double the expected startup delay
after crash (and a bad shutdown qualifies as a crash of sorts). You need
to make sure you're not making that time unacceptable for your application
before going too crazy increasing the segments.

You should certainly look into how to get your database to shutdown
cleanly though. There's three modes here pg_ctl will use: smart, fast,
immediate. I consider smart just about useless because I always have
somebody connected. Immediate has the problem you already noticed,
recovery after startup. So fast is the most useful of the three modes.

You said "smart and normal mode"...the default is smart, so if you meant
"without specifying a mode" when you said normal then you just tried smart
twice--that matches your comment that it took "a long time" in both cases
(fast won't). If I'm right about that, try fast next time.

If you couldn't shutdown using fast, that's something that deserves some
investigation. That shouldn't happen unless there's a bad situation.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD