Re: Filesystem Direct I/O and WAL sync option

Gregory, thanks for good questions! :))
I got more lights on my throughput here :))

The running OS is Solaris9 (customer is still not ready to upgrade to
Sol10), and I think the main "sync" issue is coming from the old UFS
implementation... UFS mounted with 'forcedirectio' option uses
different "sync" logic as well accepting concurrent writing to the
same file which is giving here a higher performance level. I did not
expect really so big gain, so did not think to replay the same test
with direct I/O on and fsync=on too. For my big surprise - it also
reached 2800 tps as with fsync=off !!! So, initial question is no more
valid :))

As well my tests are executed just to validate server + storage
capabilities, and honestly it's really pity to see them used under old
Solaris version :))
but well, at least we know what kind of performance they may expect
currently, and think about migration before the end of this year...

Seeing at least 10.000 random writes/sec on storage sub-system during
live database test was very pleasant to customer and make feel them
comfortable for their production...

Thanks a lot for all your help!

Best regards!
-Dimitri

On 7/4/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>
> "Dimitri" <dimitrik(dot)fr(at)gmail(dot)com> writes:
>
> > Yes Gregory, that's why I'm asking, because from 1800 transactions/sec
> > I'm jumping to 2800 transactions/sec! and it's more than important
> > performance level increase :))
>
> wow. That's kind of suspicious though. Does the new configuration take
> advantage of the lack of the filesystem cache by increasing the size of
> shared_buffers? Even then I wouldn't expect such a big boost unless you got
> very lucky with the size of your working set compared to the two sizes of
> shared_buffers.
>
> It seems likely that somehow this change is not providing the same
> guarantees
> as fsync. Perhaps fsync is actually implementing IDE write barriers and the
> sync mode is just flushing buffers to the hard drive cache and then
> returning.
>
> What transaction rate do you get if you just have a single connection
> streaming inserts in autocommit mode? What kind of transaction rate do you
> get
> with both sync mode on and fsync=on in Postgres?
>
> And did you say this with a battery backed cache? In theory fsync=on/off and
> shouldn't make much difference at all with a battery backed cache. Stranger
> and stranger.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
>
>