X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id AB8A3D1C4E3; Wed, 23 Jul 2003 08:49:18 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 05795-10; Wed, 23 Jul 2003 05:48:38 -0300 (ADT) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by svr1.postgresql.org (Postfix) with ESMTP id AB819D1C4D0; Wed, 23 Jul 2003 05:49:16 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 19fFJF-000GMt-0X; Wed, 23 Jul 2003 09:49:17 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id AF0681675C; Wed, 23 Jul 2003 09:49:15 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id EB07D1661A; Wed, 23 Jul 2003 09:49:11 +0100 (BST) From: Richard Huxton To: Reece Hart , "pgsql-performance@postgresql.org" , "pgsql-admin@postgresql.org" , SF PostgreSQL Subject: Re: slow table updates Date: Wed, 23 Jul 2003 09:49:09 +0100 User-Agent: KMail/1.5 References: <1058920801.7281.67.camel@tallac> In-Reply-To: <1058920801.7281.67.camel@tallac> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307230949.10028.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/388 X-Sequence-Number: 2681 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