Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group