From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Jean-David Beyer <jeandavid8@verizon.net>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
Date: Thu, 28 Jun 2007 13:01:03 +0100
Message-ID: <4683A2FF.1090404@enterprisedb.com> (raw)
In-Reply-To: <46839F39.5020402@verizon.net>
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>
Jean-David Beyer wrote:
> 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?
Welcome back.
Longer is better when the system is running. But on recovery, longer
checkpoint interval means a longer wait until the database is up again.
Longer checkpoint interval also means that more WAL needs to be kept
around, but that's not usually a concern on normal server hardware with
plenty of disk space.
> WAL_BUFFERS The default is 8.
Increasing this can increase the performance of bulk load operations but
it doesn't make much difference otherwise.
> 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.
You have to decide if you want to use checkpoint_timeout or
checkpoint_segments as the primary means of controlling your checkpoint
interval. checkpoint_timeout is easier to understand and tune, so I
would suggest using that. Depending on how long recovery times you can
live with, set it to something like 15 minutes - 60 minutes. Then set
checkpoint_segments to a high value; it's purpose in this scheme is
basically to just protect you from running out of disk space on the
filesystem WAL is located in.
Note that unlike on DB2, the size of your transactions isn't limited by
the amount of transaction log you keep around; this is all about
performance.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, jeandavid8@verizon.net
Subject: Re: update query taking too long
In-Reply-To: <4683A2FF.1090404@enterprisedb.com>
* 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