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 CC8CAD1C47A for ; Mon, 11 Aug 2003 22:56:31 +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 54129-05 for ; Mon, 11 Aug 2003 19:55:00 -0300 (ADT) Received: from djinn.gene.com (djinn-open.gene.COM [192.12.78.2]) by svr1.postgresql.org (Postfix) with ESMTP id 74214D1C4DF for ; Mon, 11 Aug 2003 19:56:17 -0300 (ADT) Received: from tallac.gene.com (tallac.gene.com [128.137.116.213]) by djinn.gene.com (8.12.9/8.12.9) with ESMTP id h7BMu8De103167; Mon, 11 Aug 2003 15:56:11 -0700 (PDT) Received: by tallac.gene.com (Postfix, from userid 13922) id B7E404B9BD; Mon, 11 Aug 2003 15:56:07 -0700 (PDT) Subject: Re: Perfomance Tuning From: Reece Hart To: Bruce Momjian Cc: "pgsql-performance@postgresql.org" In-Reply-To: <200308112216.h7BMGiF06162@candle.pha.pa.us> References: <200308112216.h7BMGiF06162@candle.pha.pa.us> Content-Type: multipart/alternative; boundary="=-DAkHNnrjCBmUJ+YtkGfN" Organization: Genentech, Inc. Message-Id: <1060642567.15483.220.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Mon, 11 Aug 2003 15:56:07 -0700 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/180 X-Sequence-Number: 2997 --=-DAkHNnrjCBmUJ+YtkGfN Content-Type: text/plain Content-Transfer-Encoding: 7bit On Mon, 2003-08-11 at 15:16, Bruce Momjian wrote: > That _would_ work if ext2 was a reliable file system --- it is not. Bruce- I'd like to know your evidence for this. I'm not refuting it, but I'm a >7 year linux user (including several clusters, all of which have run ext2 or ext3) and keep a fairly close ear to kernel newsgroups, announcements, and changelogs. I am aware that there have very occasionally been corruption problems, but my understanding is that these are fixed (and quickly). In any case, I'd say that your assertion is not widely known and I'd appreciate some data or references. As for PostgreSQL on ext2 and ext3, I recently switched from ext3 to ext2 (Stephen Tweedy was insightful to facilitate this backward compatibility). I did this because I had a 45M row update on one table that was taking inordinate time (killed after 10 hours), even though creating the database from backup takes ~4 hours including indexing (see pgsql-perform post on 2003/07/22). CPU usage was ~2% on an otherwise unloaded, fast, SCSI160 machine. vmstat io suggested that PostgreSQL was writing something on the order of 100x as many blocks as being read. My untested interpretation was that the update bookkeeping as well as data update were all getting journalled, the journal space would fill, get sync'd, then repeat. In effect, all blocks were being written TWICE just for the journalling, never mind the overhead for PostgreSQL transactions. This emphasizes that journals probably work best with short burst writes and syncing during lulls rather than sustained writes. I ended up solving the update issue without really updating, so ext2 timings aren't known. So, you may want to test this yourself if you're concerned. -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 --=-DAkHNnrjCBmUJ+YtkGfN Content-Type: text/html; charset=utf-8 On Mon, 2003-08-11 at 15:16, Bruce Momjian wrote:
That _would_ work if ext2 was a reliable file system --- it is not.

Bruce-

I'd like to know your evidence for this. I'm not refuting it, but I'm a >7 year linux user (including several clusters, all of which have run ext2 or ext3) and keep a fairly close ear to kernel newsgroups, announcements, and changelogs. I am aware that there have very occasionally been corruption problems, but my understanding is that these are fixed (and quickly). In any case, I'd say that your assertion is not widely known and I'd appreciate some data or references.

As for PostgreSQL on ext2 and ext3, I recently switched from ext3 to ext2 (Stephen Tweedy was insightful to facilitate this backward compatibility). I did this because I had a 45M row update on one table that was taking inordinate time (killed after 10 hours), even though creating the database from backup takes ~4 hours including indexing (see pgsql-perform post on 2003/07/22). CPU usage was ~2% on an otherwise unloaded, fast, SCSI160 machine. vmstat io suggested that PostgreSQL was writing something on the order of 100x as many blocks as being read. My untested interpretation was that the update bookkeeping as well as data update were all getting journalled, the journal space would fill, get sync'd, then repeat. In effect, all blocks were being written TWICE just for the journalling, never mind the overhead for PostgreSQL transactions. This emphasizes that journals probably work best with short burst writes and syncing during lulls rather than sustained writes.

I ended up solving the update issue without really updating, so ext2 timings aren't known. So, you may want to test this yourself if you're concerned.

-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
--=-DAkHNnrjCBmUJ+YtkGfN--