On Tue, 8 May 2007, Orhan Aglagul wrote:
> Time for 10000 inserts
> Pentium M 1.7
> ~17 sec fsync=on
> ~6 sec fsync=off
This is 588 inserts/second with fsync on. It's impossible to achieve that
without write caching at either the controller or hard drive. My bet
would be that your hard drive in this system is a regular IDE/SATA drive
that has write caching enabled, which is the normal case. That means this
system doesn't really do a fsync when you tell it to.
> Pentium 4 2.4
> ~13 sec fsync=on
> ~11 sec fsync=off
Same response here. Odds are good the fsync=on numbers here are a
fantasy; unless you have some serious disk hardware in this server, it
can't really be doing an fsync and giving this performance level.
> Dual Xeon
> ~65 sec fsync=on
> ~1.9 sec fsync=off
Now this looks reasonable. 5263/second with fsync off, 154/second with it
on. This system appears to have hard drives in it that correctly write
data out when asked to via the fsync mechanism. I would bet this one is a
server that has some number of 10,000 RPM SCSI drives in it. Such a drive
gives a theoretical maximum of 166.7 inserts/second if the inserts are
done one at a time.
If this all is confusing to you, I have written a long primer on this
subject that explains how the interaction between the PostgreSQL, fsync,
and the underlying drives work. If you have the patience to work your way
through it and follow the references along the way, I think you'll find
the results you've been seeing will make more sense, and you'll be in a
better position to figure out what you should do next:
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD