-----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@xxxxxxxxxxxxx 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----- -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
