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