Re: Slow Inserts on large tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Peter Childs" <peterachilds(at)gmail(dot)com>
Cc: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow Inserts on large tables
Date: 2008-10-03 12:19:51
Message-ID: 22109.1223036391@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Peter Childs" <peterachilds(at)gmail(dot)com> writes:
> 2008/10/3 Peter Eisentraut <peter_e(at)gmx(dot)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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Simon Waters 2008-10-03 13:37:35 7.4 - basic tuning question
Previous Message Iulian Dragan 2008-10-03 10:09:37 Re: Slow Inserts on large tables