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