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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
