From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Childs <peterachilds@gmail.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Slow Inserts on large tables
Date: Fri, 03 Oct 2008 08:19:51 -0400
Message-ID: <22109.1223036391@sss.pgh.pa.us> (raw)
In-Reply-To: <a2de01dd0810030147m4b2d302aj49e1bb38146d0523@mail.gmail.com>
References: <a2de01dd0810030025x2b28fee4o9505bed1689a6060@mail.gmail.com>
<48E5D62D.7060400@gmx.net>
<a2de01dd0810030147m4b2d302aj49e1bb38146d0523@mail.gmail.com>
"Peter Childs" <peterachilds@gmail.com> writes:
> 2008/10/3 Peter Eisentraut <peter_e@gmx.net>:
>> Then show us your checkpointing-related parameters.
> I've currently got them set to
> checkpoint_segments = 3
> checkpoint_timeout = 180s
> checkpoint_completion_target = 0.5
> after reading that doing more smaller checkpoints might make each
> checkpoint work quicker and hence less of a performance hit when they
> actually happen.
That concept is actually pretty obsolete in 8.3: with spread-out
checkpoints it basically shouldn't hurt to increase the checkpoint
interval, and could actually help because the bgwriter doesn't have
such a tight deadline to finish the checkpoint. In any case you
*definitely* need to increase checkpoint_segments --- the value
you've got could be forcing a checkpoint every few seconds not
every few minutes.
What I would suggest is turning on log_checkpoints and then seeing
if there's any correlation between your slow insert commands and the
checkpoints. I'm suspicious that the problem is somewhere else.
(For instance, have you got anything that might take a lock on the
table? Maybe enabling log_lock_waits would be a good idea too.)
regards, tom lane
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: tgl@sss.pgh.pa.us, peterachilds@gmail.com
Subject: Re: Slow Inserts on large tables
In-Reply-To: <22109.1223036391@sss.pgh.pa.us>
* 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