On 16.04.2008, at 17:42, Chris Browne wrote:
[EMAIL PROTECTED] (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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
