Re: Oddly slow queries - Mailing list pgsql-performance

On 16.04.2008, at 17:42, Chris Browne wrote:
> spreng@socket.ch (Thomas Spreng) writes:
>> On 16.04.2008, at 01:24, PFC wrote:
>>>
>>>> The queries in question (select's) occasionally take up to 5 mins
>>>> even if they take ~2-3 sec under "normal" conditions, there are no
>>>> sequencial scans done in those queries. There are not many users
>>>> connected (around 3, maybe) to this database usually since it's
>>>> still in a testing phase. I tried to hunt down the problem by
>>>> playing around with resource usage cfg options but it didn't really
>>>> made a difference.
>>>
>>>     Could that be caused by a CHECKPOINT ?
>>
>> actually there are a few log (around 12 per day) entries concerning
>> checkpoints:
>>
>> LOG:  checkpoints are occurring too frequently (10 seconds apart)
>> HINT:  Consider increasing the configuration parameter
>> "checkpoint_segments".
>>
>> But wouldn't that only affect write performance? The main problems
>> I'm
>> concerned about affect SELECT queries.
>
> No, that will certainly NOT just affect write performance; if the
> postmaster is busy writing out checkpoints, that will block SELECT
> queries that are accessing whatever is being checkpointed.

What I meant is if there are no INSERT's or UPDATE's going on it
shouldn't
affect SELECT queries, or am I wrong?

All the data modification tasks usually run at night, during the day
there
shouldn't be many INSERT's or UPDATE's going on.

> When we were on 7.4, we would *frequently* see SELECT queries that
> should be running Very Quick that would get blocked by the checkpoint
> flush.

How did you actually see they were blocked by the checkpoint flushes?
Do they show up as separate processes?

> There are two things worth considering:
>
> 1.  If the checkpoints are taking place "too frequently," then that is
> clear evidence that something is taking place that is injecting REALLY
> heavy update load on your database at those times.
>
> If the postmaster is checkpointing every 10s, that implies Rather
> Heavy Load, so it is pretty well guaranteed that performance of other
> activity will suck at least somewhat because this load is sucking up
> all the I/O bandwidth that it can.
>
> So, to a degree, there may be little to be done to improve on this.

I strongly assume that those log entries showed up at night when the
heavy insert routines are being run. I'm more concerned about the query
performance under "normal" conditions when there are very few
modifications
done.

> 2.  On the other hand, if you're on 8.1 or so, you may be able to
> configure the Background Writer to incrementally flush checkpoint data
> earlier, and avoid the condition of 1.
>
> Mind you, you'd have to set BgWr to be pretty aggressive, based on the
> "10s periodicity" that you describe; that may not be a nice
> configuration to have all the time :-(.

I've just seen that the daily vacuum tasks didn't run, apparently. The
DB
has almost doubled it's size since some days ago. I guess I'll have to
VACUUM FULL (dump/restore might be faster, though) and check if that
helps
anything.

Does a bloated DB affect the performance alot or does it only use up
disk
space?

Thanks for all the hints/help so far from both of you.

Cheers,

Tom