From: Greg Smith <greg@2ndquadrant.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive table (500M rows) update nightmare
Date: Fri, 08 Jan 2010 13:12:00 -0500
Message-ID: <4B477570.4080804@2ndquadrant.com> (raw)
In-Reply-To: <hi7qho$2v37$1@news.hub.org>
References: <hi41os$eiv$1@news.hub.org>
<b1b9fac61001071305vf182f3ajff6827f92c943c68@mail.gmail.com>
<hi5kpr$184c$1@news.hub.org>
<4B462563020000250002DFA3@gw.wicourts.gov>
<hi6ho6$17nn$1@news.hub.org>
<4B46E8A7020000250002E012@gw.wicourts.gov>
<hi7qho$2v37$1@news.hub.org>
Carlo Stonebanks wrote:
> This is hosted on a new server the client set up so I am waiting for
> the exact OS and hardware config. PG Version is PostgreSQL 8.3.6,
> compiled by Visual C++ build 1400, OS appears to be Windows 2003 x64
> Server.
>
> More than anything, I am more concerned with the long-term use of the
> system. This particular challenge with the 500M row update is one
> thing, but I am concerned about the exceptional effort required to do
> this. Is it REALLY this exceptional to want to update 500M rows of
> data in this day and age? Or is the fact that we are considering
> dumping and restoring and dropping indexes, etc to do all an early
> warning that we don't have a solution that is scaled to the problem?
It's certainly not common or easy to handle. If someone told me I had
to make that task well perform well and the tools at hand were anything
other than a largish UNIX-ish server with a properly designed disk
subsystem, I'd tell them it's unlikely to work well. An UPDATE is the
most intensive single operation you can do in PostgreSQL; the full
lifecycle of executing it requires:
-Creating a whole new row
-Updating all the indexes to point to the new row
-Marking the original row dead
-Pruning the original row out of the database once it's no longer
visible anywhere (VACUUM)
As a rule, if you can't fit a large chunk of the indexes involved in
shared_buffers on your system, this is probably going to have terrible
performance. And you're on a platform where that's very hard to do,
even if there's a lot of RAM around. It sounds like your system spends
all its time swapping index blocks in and out of the database buffer
cache here. That suggests there's a design problem here either with
those indexes (they're too big) or with the provisioned
hardware/software combination (needs more RAM, faster disks, or a
platform where large amounts of RAM work better).
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
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, stonec.register@sympatico.ca
Subject: Re: Massive table (500M rows) update nightmare
In-Reply-To: <4B477570.4080804@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