Re: Optimizing bulk update performance

We're using Postgresql 9.1.9 on Ubuntu 12.04 on EBS volumes on
m1.xlarge instances, which have:

15 GiB memory
8 EC2 Compute Units (4 virtual cores with 2 EC2 Compute Units each)
64-bit platform

(Yes, we're moving to EBS Optimized instances + Provisioned IOPS
volumes, but prelim. benchmarks suggest this won't get us enough of a
boost as much as possibly refactoring the way we're executing these
bulk updates in our application.)

On Fri, Apr 26, 2013 at 5:27 PM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
> On 27/04/13 12:14, Yang Zhang wrote:
>
> It currently takes up to 24h for us to run a large set of UPDATE
> statements on a database, which are of the form:
>
> UPDATE table SET field1 = constant1, field2 = constant2, ... WHERE
> id = constid
>
> (We're just overwriting fields of objects identified by ID.)
>
> The tables have handfuls of indices each and no foreign key constraints.
> No COMMIT is made till the end.
>
> It takes 2h to import a `pg_dump` of the entire DB. This seems like a
> baseline we should reasonably target.
>
> Short of producing a custom program that somehow reconstructs a dataset
> for Postgresql to re-import, is there anything we can do to bring the
> bulk UPDATE performance closer to that of the import? (This is an area
> that we believe log-structured merge trees handle well, but we're
> wondering if there's anything we can do within Postgresql.)
>
> Some ideas:
>
> - dropping all non-ID indices and rebuilding afterward?
> - increasing checkpoint_segments, but does this actually help sustained
> long-term throughput?
> - using the techniques mentioned here? (Load new data as table, then
> "merge in" old data where ID is not found in new data)
>
> <http://www.postgresql.org/message-id/3a0028490809301807j59498370m1442d8f5867e9668@mail.gmail.com>
>
> Basically there's a bunch of things to try and we're not sure what the
> most effective are or if we're overlooking other things. We'll be
> spending the next few days experimenting, but we thought we'd ask here
> as well.
>
> Thanks.
>
>
> People will need to know your version of Postgres & which Operating System
> etc. plus details of CPU RAM, and Disks... AS well as what changes you have
> made to postgresql.conf...
>
> I would be inclined to DROP all indexes and reCREATE them later.
>
> Updating a row might lead to new row being added in a new disk page, so I
> suspect that updates will hit every index associated with the table with the
> (possible exception of partial indexes).
>
> Running too many updates in one transaction, may mean that Postgres may need
> to use disk work files.
>
> Depending on RAM etc, it may pay to increase some variables tat affect how
> Postgres uses RAM, some of these are per session.
>
>
> Cheers,
> Gavin

--
Yang Zhang
http://yz.mit.edu/