Re: Very long execution time of "select nextval('..');"

Am Sonntag 27 Januar 2008 18:56:49 schrieb Tom Lane:
> mljv(at)planwerk6(dot)de writes:
> > we run postgresql-8.1 on a dedicated debian box 64bit, dual-core CPU, 8GB
> > RAM, RAID-1.
>
> 8.1.what?

8.1.11-0etch1

> > LOG: duration: 12636.746 ms statement: EXECUTE <unnamed> [PREPARE:
> > select nextval ('member_id_seq')]
>
> That's just bizarre, especially if your system isn't showing any other
> signs of stress.
>
> > Unfortunatly i can not tell at which time this happens as the log
> > doesn't show the time of day.
>
> See log_line_prefix. I think what you need to do is gather some
> evidence about what else is happening at the same time --- can you
> afford to enable log_statement = all? Also, you should try to correlate
> this with spikes in I/O demand (try running "vmstat 1" or similar).

i guess log_statement=all will use all IO of my disks as there are lots of
queries

> It could be that this is related to checkpointing, which you won't see
> in a log_statement trace. In 8.1 you'd have to crank up
> log_min_messages to DEBUG2 to get log entries for checkpoint start and
> end, which is going to result in a mighty verbose log, but you may have
> to do that to confirm or disprove the idea.

ok, at the moment i got some traffic and my load is at 1.5. But now with
logging the timestamp I have seen that the long durations are quite regular
at intervals of 10 minutes.

but what in hell can make nextval take so long? even if checkpointing is badly
configured. I always thought that nextval is one of the fastest operations.
So if it takes 500 ms, fine. things like this can always happen, but 20
seconds sounds more like a hardware failure. But i can't see any.

ok, i will do some research tomorrow as i 'll try to go to sleep now.

kind regards
Janning