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 2FD3AD1B8FC for ; Thu, 4 Sep 2003 07:03:30 +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 02911-10 for ; Thu, 4 Sep 2003 04:02:59 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id DEE54D1B8D3 for ; Thu, 4 Sep 2003 04:02:52 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h8479KEU008575 for ; Thu, 4 Sep 2003 12:39:20 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h8479ICO008549 for ; Thu, 4 Sep 2003 12:39:18 +0530 From: "Shridhar Daithankar" To: PostgreSQL Performance Mailing List Date: Thu, 04 Sep 2003 12:34:51 +0530 MIME-Version: 1.0 Subject: Re: PostgreSQL Reliability when fsync = false on Linux-XFS Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3F57316B.21521.9DD1CF4@localhost> In-reply-to: <1062646595.84923.35.camel@jester> References: <20030904030404.GA32319@leathercollection.ph> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/52 X-Sequence-Number: 3418 On 3 Sep 2003 at 23:36, Rod Taylor wrote: > > - the way PostgreSQL expects data to be written to disk without the > > fsync calls for things not to get corrupted in the event of a crash, > > and > > If you want the filesystem to deal with this, I believe it is necessary > for it to write the data out in the same order the write requests are > supplied in between ALL PostgreSQL processes. If you can accomplish > this, you do not need WAL. > > There are shortcuts which can be taken in the above, which is where WAL > comes in. WAL writes are ordered between processes and WAL of a single > process always hits disk prior to commit -- fsync forces both of these. > Due to WAL being in place, data can be written at almost any time. The > benefit to WAL is a single file fsync rather than the entire database > requiring one (PostgreSQL pre-7.1 method). > > > I know that at the end of the day, if I value my data, I must (1) back > > it up regularly, and (2) keep fsync enabled in PostgreSQL. However given > > the significance performance hit (at least as far as massive INSERT or > > If you want good performance, invest in a SCSI controller that has > battery backed write cache. A few megs will do it. You will find > performance similar to fsync being off (you don't wait for disk > rotation) but without the whole dataloss issue. Another alternative is > to buy a small 15krpm disk dedicated for WAL. In theory you can achieve > one commit per rotation. Just wonderin. What if you symlink WAL to a directory which is on mounted USB RAM drive? Will that increase any throughput? I am sure a 256/512MB flash drive will cost lot less than a SCSI disk. May be even a GB on flash drive would do.. Just a thought.. Bye Shridhar -- Ambition, n: An overmastering desire to be vilified by enemies while living and made ridiculous by friends when dead. -- Ambrose Bierce