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 C3106D1B4ED for ; Fri, 10 Oct 2003 17:25:27 +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 55574-10 for ; Fri, 10 Oct 2003 14:24:41 -0300 (ADT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 6C022D1B55D for ; Fri, 10 Oct 2003 14:24:39 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id h9AHOTs24892; Fri, 10 Oct 2003 13:24:29 -0400 (EDT) From: Bruce Momjian Message-Id: <200310101724.h9AHOTs24892@candle.pha.pa.us> Subject: Re: further testing on IDE drives In-Reply-To: To: "scott.marlowe" Date: Fri, 10 Oct 2003 13:24:29 -0400 (EDT) Cc: Postgresql Performance X-Mailer: ELM [version 2.4ME+ PL106 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/349 X-Sequence-Number: 4097 scott.marlowe wrote: > On Thu, 9 Oct 2003, Bruce Momjian wrote: > > > scott.marlowe wrote: > > > I was testing to get some idea of how to speed up the speed of pgbench > > > with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 > > > /dev/hdx). > > > > > > The only parameter that seems to make a noticeable difference was setting > > > wal_sync_method = open_sync. With it set to either fsync, or fdatasync, > > > the speed with pgbench -c 5 -t 1000 ran from 11 to 17 tps. With open_sync > > > it jumped to the range of 45 to 52 tps. with write cache on I was getting > > > 280 to 320 tps. so, not instead of being 20 to 30 times slower, I'm only > > > about 5 times slower, much better. > > > > > > Now I'm off to start a "pgbench -c 10 -t 10000" and pull the power cord > > > and see if the data gets corrupted with write caching turned on, i.e. do > > > my hard drives have the ability to write at least some of their cache > > > during spin down. > > > > Is this a reason we should switch to open_sync as a default, if it is > > availble, rather than fsync? I think we are doing a single write before > > fsync a lot more often than we are doing multiple writes before fsync. > > Sounds reasonable to me. Are there many / any scenarios where a plain > fsync would be faster than open_sync? Yes. If you were doing multiple WAL writes before transaction fsync, you would be fsyncing every write, rather than doing two writes and fsync'ing them both. I wonder if larger transactions would find open_sync slower? -- 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