Re: Raid 10 chunksize - Mailing list pgsql-performance

-----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-----