Date: Wed, 9 May 2007 00:14:42 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Orhan Aglagul <oaglagul@cittio.com>
cc: pgsql-performance@postgresql.org
Subject: Re: 
In-Reply-To: <868BCE5A6576F44A862F1FBBC3E14A01042559C3@ms17.mse9.exchange.ms>
Message-ID: <Pine.GSO.4.64.0705082355140.1773@westnet.com>
References: <868BCE5A6576F44A862F1FBBC3E14A01042559C3@ms17.mse9.exchange.ms>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

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