X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 454D652A5A for ; Thu, 24 Feb 2005 21:25:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69043-05 for ; Thu, 24 Feb 2005 21:25:43 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by svr1.postgresql.org (Postfix) with ESMTP id CEE4852A4F for ; Thu, 24 Feb 2005 21:25:42 +0000 (GMT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id j1OLPYF14343; Thu, 24 Feb 2005 16:25:34 -0500 (EST) From: Bruce Momjian Message-Id: <200502242125.j1OLPYF14343@candle.pha.pa.us> Subject: Re: PostgreSQL is extremely slow on Windows In-Reply-To: <421D0543.6020308@samurai.com> To: Neil Conway Date: Thu, 24 Feb 2005 16:25:34 -0500 (EST) Cc: Magnus Hagander , "Vig, Sandor (G/FI-2)" , pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=AWL, RISK_FREE X-Spam-Level: X-Archive-Number: 200502/354 X-Sequence-Number: 10683 Neil Conway wrote: > Magnus Hagander wrote: > > Yes, fsync=false is very good for bulk loading *IFF* you can live with > > data loss in case you get a crash during load. > > It's not merely data loss -- you could encounter potentially > unrecoverable database corruption. > > There is a TODO item about allowing the delaying of WAL writes. If we > maintain the WAL invariant (that is, a WAL record describing a change > must hit disk before the change itself does) but simply don't flush the > WAL at transaction commit, we should be able to get better performance > without the risk of database corruption (so we would need to keep pages > modified by the committed transaction pinned in memory until the WAL has > been flushed, which might be done on a periodic basis). > > Naturally, there is a risk of losing data in the period between > transaction commit and syncing the WAL, but no risk of database > corruption. This seems a reasonable approach to providing better > performance for people who don't need the strict guarantees provided by > fsync=true. Right. Just for clarity, you might lose the last 5 seconds of transactions, but all transactsions would be completely committed or aborted in your datbase. Right now with fsync off you can get transactions partially commited in your database, which is a serious problem (think moving money from one account to another). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073