X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C2902D1C7EB; Wed, 23 Jul 2003 17:44:44 +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 47439-02; Wed, 23 Jul 2003 14:44:02 -0300 (ADT) Received: from djinn.gene.com (socks-open.gene.com [192.12.78.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7EC0AD1C515; Wed, 23 Jul 2003 14:44:42 -0300 (ADT) Received: from faraday.gene.com (faraday.gene.com [128.137.49.205]) by djinn.gene.com (8.12.9/8.12.9) with ESMTP id h6NHibDe216316; Wed, 23 Jul 2003 10:44:37 -0700 (PDT) Received: from tallac.gene.com (ldappxy.gene.com [128.137.55.36]) by faraday.gene.com (Switch-3.1.0/Switch-3.1.0) with ESMTP id h6NHiaca024400; Wed, 23 Jul 2003 10:44:36 -0700 (PDT) Subject: Re: [PERFORM] slow table updates From: Reece Hart To: Richard Huxton Cc: "pgsql-performance@postgresql.org" , "pgsql-admin@postgresql.org" , SF PostgreSQL In-Reply-To: <200307230949.10028.dev@archonet.com> References: <1058920801.7281.67.camel@tallac> <200307230949.10028.dev@archonet.com> Content-Type: multipart/alternative; boundary="=-9hmJexnaeMy5n3vdHDR2" Organization: Genentech, Inc. Message-Id: <1058982275.7281.83.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 23 Jul 2003 10:44:36 -0700 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/245 X-Sequence-Number: 9585 --=-9hmJexnaeMy5n3vdHDR2 Content-Type: text/plain Content-Transfer-Encoding: 7bit Richard- Thanks for the suggestions. I too had thought about the FK checks, even though the columns aren't getting updated. I'm flabbergasted that the update is still running (~22 hours elapsed). By comparison, the database takes only 4 hours to recreate from backup! Something funny is happening here. I just interrupted the update and will find another way. But hang on, what's this: PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 30164 compbio 25 0 6056 6056 3568 R 98.8 0.1 29:55 postgres: admin csb [local] COPY I am the only user and I'm not doing a copy... this must be part of the update process. Does anyone out there know whether updates do a table copy instead of in-table udpating (perhaps as a special case for whole-table updates)? Of course, I can't help but wonder whether I just killed it when it was nearly done... Thanks, Reece -- Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/ Genentech, Inc. 650/225-6133 (voice), -5389 (fax) Bioinformatics and Protein Engineering 1 DNA Way, MS-93 http://www.in-machina.com/~reece/ South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0 --=-9hmJexnaeMy5n3vdHDR2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Richard-

Thanks for the suggestions. I too had thought about the FK checks, even though the columns aren't getting updated.

I'm flabbergasted that the update is still running (~22 hours elapsed). By comparison, the database takes only 4 hours to recreate from backup! Something funny is happening here. I just interrupted the update and will find another way.

But hang on, what's this:
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
30164 compbio   25   0  6056 6056  3568 R    98.8  0.1  29:55 postgres: admin csb [local] COPY

I am the only user and I'm not doing a copy... this must be part of the update process. Does anyone out there know whether updates do a table copy instead of in-table udpating (perhaps as a special case for whole-table updates)?

Of course, I can't help but wonder whether I just killed it when it was nearly done...

Thanks,
Reece

-- 
Reece Hart, Ph.D.                       rkh@gene.com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece@in-machina.com, GPG: 0x25EC91A0
--=-9hmJexnaeMy5n3vdHDR2--