From: Jean-David Beyer <jeandavid8@verizon.net>
To: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
Date: Thu, 28 Jun 2007 07:44:57 -0400
Message-ID: <46839F39.5020402@verizon.net> (raw)
In-Reply-To: <46836EC8.20807@gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jeandavid8@verizon.net
Subject: Re: update query taking too long
In-Reply-To: <46839F39.5020402@verizon.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox