Message-ID: <4683A39B.1030100@archonet.com>
Date: Thu, 28 Jun 2007 13:03:39 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Jean-David Beyer <jeandavid8@verizon.net>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
References: <46834124.5010600@gmail.com> <21072.1183009270@sss.pgh.pa.us>
	<46835252.5070901@gmail.com> <468357B2.1040205@archonet.com>
	<46835A16.3050602@gmail.com> <46835D14.6010507@archonet.com>
	<46836EC8.20807@gmail.com> <46839F39.5020402@verizon.net>
In-Reply-To: <46839F39.5020402@verizon.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jean-David Beyer wrote:
> 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?

Basically, it depends on the amount of updates you have and whether you 
want to minimise total writes or keep the load even. Lots of 
checkpointing means you'll do more writing, but in smaller chunks. The 
only way to find out the right value for you is to test on a realistic 
setup I'm afraid.

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

If your updates are large (rather than having lots of small ones) then 
increasing wal_buffers might be useful.

If you have a lot of updates, you'll want to increase 
checkpoint_segments at least. You'll see mention in the logs when PG 
thinks checkpoints are too close together (checkpoint_timeout/warning).

Of course, a lot of people will have PostgreSQL installed on a PC or 
laptop along with the rest of the Linux distro. They'll not want to 
allocate too many resources.

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

If DB2 was I/O saturated with its transaction log, I'd be surprised if 
PG isn't too.

-- 
   Richard Huxton
   Archonet Ltd