From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Massive table (500M rows) update nightmare
Date: Thu, 7 Jan 2010 16:48:19 -0500
Message-ID: <hi5kpr$184c$1@news.hub.org> (raw)
In-Reply-To: <b1b9fac61001071305vf182f3ajff6827f92c943c68@mail.gmail.com>
References: <hi41os$eiv$1@news.hub.org>
<b1b9fac61001071305vf182f3ajff6827f92c943c68@mail.gmail.com>
> What is the rationale behind this? How about doing 10k rows in 1
> update, and committing every time?
When we did 10K updates, the application would sometimes appear to have
frozen, and we were concerned that there was a deadlock condition because of
the number of locked rows. While we may have the patience to sit around and
wait five minutes to see if the update would continue, we couldn't risk
having other applications appear frozen if that was the case. In fact, there
is no reason for any user or application to write to the same records we are
writing to - but the audit table is read frequently. We are not explicitly
locking anything, or writing any additional code to arbitrate the lcoking
model anywhere -it's all default UPDATE and SELECT syntax.
Doing the updates in smaller chunks resolved these apparent freezes - or,
more specifically, when the application DID freeze, it didn't do it for more
than 30 seconds. In all likelyhood, this is the OS and the DB thrashing.
We have since modified the updates to process 1000 rows at a time with a
commit every 10 pages. Just this morning, though, the IS manager asked me to
stop the backfill because of the load affect on other processes.
> You could try making the condition on the ctid column, to not have to
> use the index on ID, and process the rows in physical order.
An interesting idea, if I can confirm that the performance problem is
because of the WHERE clause, not the UPDATE.
>'where new_column is null' to the conditions.
Already being done, albeit with a coalesce(val, '') = '' - it's quite
possible that this is hurting the WHERE clause; the EXPLAIN shows the table
using the pkey and then filtering on the COALESCE as one would expect.
Carlo
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: stonec.register@sympatico.ca
Subject: Re: Massive table (500M rows) update nightmare
In-Reply-To: <hi5kpr$184c$1@news.hub.org>
* 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