Date: Thu, 28 Jun 2007 07:44:57 -0400
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: update query taking too long
In-reply-to: <46836EC8.20807@gmail.com>
To: Pgsql performance <pgsql-performance@postgresql.org>
Message-id: <46839F39.5020402@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
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>
User-Agent: Thunderbird 1.5 (X11/20051201)

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