Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BBEEA6323A9 for ; Wed, 1 Apr 2009 13:08:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 57046-05 for ; Wed, 1 Apr 2009 13:08:20 -0300 (ADT) X-Greylist: delayed 16:32:25.877207 by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by mail.postgresql.org (Postfix) with ESMTP id 1EEA46318F3 for ; Wed, 1 Apr 2009 13:08:20 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id n31G8F7h005645; Wed, 1 Apr 2009 12:08:15 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id n31G8Fwd005640; Wed, 1 Apr 2009 12:08:15 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Wed, 1 Apr 2009 12:08:15 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Stef Telford cc: Mark Kirkwood , Scott Carey , "pgsql-performance@postgresql.org" Subject: Re: Raid 10 chunksize In-Reply-To: <49D37CAA.6080100@ummon.com> Message-ID: References: <49D31E85.8050405@paradise.net.nz> <49D37CAA.6080100@ummon.com> User-Agent: Alpine 2.01 (GSO 1184 2008-12-16) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; format=flowed; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000 X-Spam-Level: X-Archive-Number: 200904/8 X-Sequence-Number: 33375 On Wed, 1 Apr 2009, Stef Telford wrote: > I have -explicitly- enabled sync in the conf...In fact, if I turn -off- > sync commit, it gets about 200 -slower- rather than faster. You should take a look at http://www.postgresql.org/docs/8.3/static/wal-reliability.html And check the output from "hdparm -I" as suggested there. If turning off fsync doesn't improve your performance, there's almost certainly something wrong with your setup. As suggested before, your drives probably have write caching turned on. PostgreSQL is incapable of knowing that, and will happily write in an unsafe manner even if the fsync parameter is turned on. There's a bunch more information on this topic at http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm Also: a run to run variation in pgbench results of +/-10% TPS is normal, so unless you saw a consistent 200 TPS gain during multiple tests my guess is that changing fsync for you is doing nothing, rather than you suggestion that it makes things slower. > Curiously, I think with SSD's there may have to be an 'off' flag > if you put the xlog onto an ssd. It seems to complain about 'too > frequent checkpoints'. You just need to increase checkpoint_segments from the tiny default if you want to push any reasonable numbers of transactions/second through pgbench without seeing this warning. Same thing happens with any high-performance disk setup, it's not specific to SSDs. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD