Message-ID: <49D3932D.3080808@ummon.com>
Date: Wed, 01 Apr 2009 12:15:41 -0400
From: Stef Telford <stef@ummon.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090319)
MIME-Version: 1.0
To: Greg Smith <gsmith@gregsmith.com>
CC: Mark Kirkwood <markir@paradise.net.nz>,
	Scott Carey <scott@richrelevance.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Raid 10 chunksize
References: <C5F14970.3CA1%scott@richrelevance.com>
	<49D31E85.8050405@paradise.net.nz> <49D37CAA.6080100@ummon.com>
	<alpine.GSO.2.01.0904011144270.23835@westnet.com>
In-Reply-To: <alpine.GSO.2.01.0904011144270.23835@westnet.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Greg Smith wrote:
> 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.
>
Hello Greg,
    Turning off fsync -does- increase the throughput noticeably,
- -however-, turning off synchronous_commit seemed to slow things down
for me. Your right though, when I toggled the sync_commit on the
system, there was a small variation with TPS coming out between 1100
and 1300. I guess I saw the initial run and thought that there was a
'loss' in sync_commit = off

     I do agree that the benefit is probably from write-caching, but I
think that this is a 'win' as long as you have a UPS or BBU adaptor,
and really, in a prod environment, not having a UPS is .. well. Crazy ?

>> 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.
>
Good to know, I thought it maybe was atypical behaviour due to the
nature of SSD's.
Regards
Stef
> -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com
> Baltimore, MD

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAknTky0ACgkQANG7uQ+9D9UuNwCghLLC96mj9zzZPUF4GLvBDlQk
fyIAn0V63YZJGzfm+4zPB9zjm8YKn42X
=A6x2
-----END PGP SIGNATURE-----