From: Scott Marlowe <scott.marlowe@gmail.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive table (500M rows) update nightmare
Date: Thu, 7 Jan 2010 23:21:31 -0700
Message-ID: <dcc563d11001072221g66d3db9cr3556d49b2bdee47e@mail.gmail.com> (raw)
In-Reply-To: <hi6ifm$1bi8$1@news.hub.org>
References: <hi41os$eiv$1@news.hub.org>
<b1b9fac61001071305vf182f3ajff6827f92c943c68@mail.gmail.com>
<hi5kpr$184c$1@news.hub.org>
<dcc563d11001071740q572cdae2re410788fe790dbf6@mail.gmail.com>
<hi6ifm$1bi8$1@news.hub.org>
On Thu, Jan 7, 2010 at 11:14 PM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
>> It might well be checkpoints. Have you tried cranking up checkpoint
>> segments to something like 100 or more and seeing how it behaves then?
>
> No I haven't, althugh it certainly make sense - watching the process run,
> you get this sense that the system occaisionally pauses to take a deep, long
> breath before returning to work frantically ;D
>
> Checkpoint_segments are currently set to 64. The DB is large and is on a
> constant state of receiving single-row updates as multiple ETL and
> refinement processes run continuously.
>
> Would you expect going to 100 or more to make an appreciable difference, or
> should I be more aggressive?
If you're already at 64 then not much. Probably wouldn't hurt to
crank it up more and delay the checkpoints as much as possible during
these updates. 64 segments is already 1024M. If you're changing a
lot more data than that in a single update / insert then cranking them
up more might help.
What you might need to do is to change your completion target to
something closer to 100% since it's likely that most of the updates /
inserts are not happening to the same rows over and over, but to
different rows for each one, the closer you can get to 100% completed
before the next checkpoint the better. This will cause some more IO
to happen, but will even it out more (hopefully) so that you don't get
checkpoint spikes. Look into the checkpoint logging options so you
can monitor how they're affecting system performance.
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: scott.marlowe@gmail.com, stonec.register@sympatico.ca
Subject: Re: Massive table (500M rows) update nightmare
In-Reply-To: <dcc563d11001072221g66d3db9cr3556d49b2bdee47e@mail.gmail.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