From: Greg Smith <greg@2ndQuadrant.com>
To: Rob Emery <re-pgsql@codeweavers.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deleting Rows From Large Tables
Date: Sun, 19 May 2013 22:55:28 -0400
Message-ID: <519990A0.6030402@2ndQuadrant.com> (raw)
In-Reply-To: <CAPCETps_ur3KM=8z0fQovKG-eKDVfsp9Xjt-9PEQd5BVMusr4Q@mail.gmail.com>
References: <CAPCETps_ur3KM=8z0fQovKG-eKDVfsp9Xjt-9PEQd5BVMusr4Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 5/17/13 7:26 AM, Rob Emery wrote:
> I can keep decreasing the size of
> the window I'm deleting but I feel I must be doing something either
> fundamentally wrong or over-complicating this enormously.
I've had jobs like this where we ended up making the batch size cover
only 4 hours at a time. Once you've looked at the EXPLAIN plans for the
row selection criteria and they're reasonable, dropping the period
that's deleted per pass is really the only thing you can do. Do some
DELETEs, then pause to let the disk cache clear; repeat.
The other useful thing to do here is get very aggressive about settings
for shared_buffers, checkpoint_segments, and checkpoint_timeout. I'll
normally push for settings like 8GB/256/15 minutes when doing this sort
of thing. The usual situation with a checkpoint every 5 minutes may not
be feasible when you've got this type of work going on in the background.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-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: greg@2ndQuadrant.com, re-pgsql@codeweavers.net
Subject: Re: Deleting Rows From Large Tables
In-Reply-To: <519990A0.6030402@2ndQuadrant.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