pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Reece Hart <rkh@gene.COM>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
To: SF PostgreSQL <sfpug@postgresql.org>
Subject: Re: slow table updates
Date: Wed, 23 Jul 2003 09:49:09 +0100
Message-ID: <200307230949.10028.dev@archonet.com> (raw)
In-Reply-To: <1058920801.7281.67.camel@tallac>
References: <1058920801.7281.67.camel@tallac>

On Wednesday 23 July 2003 01:40, Reece Hart wrote:
> I'm trying to update a table but it's taking a very long time. I would
> appreciate any tips folks may have about ways to speed it up.
[snip]
> paprospect2 contains ~40M rows. The goal now is to migrate the data to
> the supertable-inherited column with
>
>         update paprospect2 set run_id_new=run_id;
>
>
> The update's been running for 5 hours (unloaded dual 2.4 GHz Xeon w/2GB
> RAM, SCSI160 10K drive). There are no other jobs running. Load is ~1.2
> and the update's using ~3-5% of the CPU.
[snip]
> This suggests that the update is I/O bound (duh) and vmstat supports
> this:
[snip]
> Presumably the large number of blocks written (bo) versus blocks read
> (bi) reflects an enormous amount of bookkeeping that has to be done for
> MVCC, logging, perhaps rewriting a row for the new definition (a guess
> -- I don't know how this is handled), indicies, etc. There's no swapping
> and no processes are waiting. In short, it seems that this is ENTIRELY
> an I/O issue. Obviously, faster drives will help (but probably only by
> small factor).
>
> Any ideas how I might speed this up? Presumably this is all getting
> wrapped in a transaction -- does that hurt me for such a large update?

Well, it needs to keep enought bookkeeping to be able to rollback the whole 
transaction if it encounters a problem, or 40M rows in your case. Looks like 
you're right and it's an I/O issue. I must admit, I'm a bit puzzled that your 
CPU is quite so low, but I suppose you've got two fast CPUs so it shouldn't 
be high.

[note the following is more speculation than experience]
What might be happening is that the drive is spending all its time seeking 
between the WAL, index and table as it updates. I would also tend to be 
suspicious of the foreign keys - PG might be re-checking these, and obviously 
that would take time too.

What you might want to try in future:
1. begin transaction
2. drop indexes, foreign keys
3. update table
4. vacuum it
5. recreate indexes, foreign keys etc
6. commit

Now that's just moving the index updating/fk stuff to the end of the task, but 
it does seem to help sometimes.

HTH
-- 
  Richard Huxton
  Archonet Ltd



view thread (5+ messages)  latest in thread

Message-ID: <200307230949.10028.dev@archonet.com>
Permalink:  ../200307230949.10028.dev@archonet.com/
Also on:    postgresql.org/message-id/200307230949.10028.dev@archonet.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, rkh@gene.COM, pgsql-admin@postgresql.org, sfpug@postgresql.org
  Subject: Re: slow table updates
  In-Reply-To: <200307230949.10028.dev@archonet.com>
  References: <1058920801.7281.67.camel@tallac> <200307230949.10028.dev@archonet.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