Re: Oddly slow queries - Mailing list pgsql-performance

Martha Stewart called it a Good Thing when spreng@socket.ch (Thomas Spreng) wrote:
> 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?

Yes, that's right.  (Caveat: VACUUM would be a form of update, in this
context...)

> 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?

We inferred this based on observed consistency of behaviour, and based
on having highly observant people like Andrew Sullivan around :-).

It definitely wasn't blatantly obvious.  It *might* be easier to see
in more recent versions, although BgWr makes the issue go away ;-).

>> 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.

I rather thought as much.

You *do* have to accept that when you get heavy update load, there
will be a lot of I/O, and in the absence of "disk array fairies" that
magically make bits get to the disks via automated mental telepathy
;-), you have to live with the notion that there will be *some*
side-effects on activity taking place at such times.

Or you have to spend, spend, spend on heftier hardware.  Sometimes too
expensive...

>> 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.

If you're locking out users, then it's probably a better idea to use
CLUSTER to reorganize the tables, as that simultaneously eliminates
empty space on tables *and indices.*

In contrast, after running VACUUM FULL, you may discover you need to
reindex tables, because the reorganization of the *table* leads to
bloating of the indexes.

Pre-8.3 (I *think*), there's a transactional issue with CLUSTER where
it doesn't fully follow MVCC, so that "dead, but still accessible, to
certain transactions" tuples go away.  That can cause surprises
(e.g. - queries missing data) if applications are accessing the
database concurrently with the CLUSTER.  It's safe as long as the DBA
can take over the database and block out applications.  And at some
point, the MVCC bug got fixed.

Note that you should check the output of a VACUUM VERBOSE run, and/or
use the contrib function pgsstattuples() to check how sparse the
storage usage is.  There may only be a few tables that are behaving
badly, and cleaning up a few tables will be a lot less intrusive than
cleaning up the whole database.

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

It certainly can affect performance; if lots of pages are virtually
empty, then you have to read more pages to find the data you're
looking for, and in such cases, you're mostly loading blank space into
pages in memory, cluttering memory up with "mostly nothing."
--
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/spreadsheets.html
Rules of  the Evil  Overlord #138. "The  passageways to and  within my
domain will  be well-lit  with fluorescent lighting.  Regrettably, the
spooky atmosphere will  be lost, but my security  patrols will be more
effective."  <http://www.eviloverlord.com/>