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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
