Re: update query taking too long

From: Jean-David Beyer <jeandavid8(at)verizon(dot)net>
To: Pgsql performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: update query taking too long
Date: 2007-06-28 11:44:57
Message-ID: 46839F39.5020402@verizon.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Chris wrote (in part):

> I didn't have logging set up before but it's up and running now and I
> was getting
>
> LOG: checkpoints are occurring too frequently (26 seconds apart)
> HINT: Consider increasing the configuration parameter
> "checkpoint_segments".
>
> So I increased that from 10 to 30 and it finished:
>
> UPDATE 3500101
> Time: 146513.349 ms
>
I have not used postgreSQL since I tried it once in about 1998 (when I found
it unsatisfactory, but much has changed since then), but I am going to try
it again. What would be a good checkpointing interval? I would guess 26
seconds is too often. What considerations go into picking a checkpointing
interval?

I note, from the book "PostgreSQL" second edition by Douglas and Doublas,
the following parameters are available:

WAL_BUFFERS The default is 8.
CHECKPOINT_SEGMENTS The default is 3. This would have been too low for the
O.P. Would it make sense to start with a higher value
or is this a good value and just not appropriate for
the O.P.? Should CHECKPOINT_SEGMENTS be raised until
the checkpointing is about half CHECKPOINT_TIMEOUT,
e.g., 150 seconds while the dbms is running typical
work?
CHECKPOINT_TIMEOUT The default is 300 seconds.
CHECKPOINT_WARNING The default is 30 seconds.

My machine has 8 GBytes RAM and it worked perfectly well (very very little
paging) when it had 4 GBytes RAM. I doubled it because it was cheap at the
time and I was afraid it would become unavailable later. It is usually
between 2/3 and 3/4 used by the cache. When I run IBM DB2 on it, the choke
point is the IO time spent writing the logfiles.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 07:20:01 up 7 days, 14:55, 3 users, load average: 4.26, 4.15, 4.07

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Heikki Linnakangas 2007-06-28 12:01:03 Re: update query taking too long
Previous Message Richard Huxton 2007-06-28 09:16:00 Re: PostgreSQL 8.0 occasionally slow down