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

On Thu, Apr 3, 2008 at 10:02 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:

> 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?
> >
>
> If you couldn't shutdown using fast, that's something that deserves some
> investigation. That shouldn't happen unless there's a bad situation.

that is what I intended to write. I tried both smart and fast mode. and it
says 'database wont shutdown', and immediate is the only mode that shutsdown
the database.
-> is shutting down in immediate mode a safe thing to do?
-> how do i investigate why fast mode doesnt work?

stats with new settings are below..
-> but even with this sometimes update queries take more than coupla
seconds sometimes...
-> now i have a few '<IDLE> in transaction' statements which I did not
have before.. is this OK?
-> sometimes INSERT statements are also slow. is there any settings I can
tweak to make INSERT statements fast?

bgwriter_lru_maxpages = 200 # 0-1000 max buffers written/round
checkpoint_segments = 96 # in logfile segments, min 1, 16MB
each
checkpoint_timeout = 20min # range 30s-1h
checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 -
1.0

postgres=# select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
67 | 0 | 4012010 | 330679
| 570 | 184569 | 5379667
(1 row)