Re: Raid 10 chunksize

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