>> Things worth experimenting with (these are all untested, so please
>> report any successes):
>> 1) Try reformatting with a cluster size of 8Kb (the pg page size), if
>> you can.
>> 2) Disable the last access time (like noatime on linux). "fsutil
>> behavior set disablelastaccess 1"
>> 3) Disable 8.3 filenames "fsutil behavior set disable8dot3 1"
>>
>> 2 and 3 may require a reboot.
>>
>> (2 and 3 can be done on earlier windows through registry
>settings only,
>> in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\FileSystem)
>
>I've repeated the test under 2 and 3 - no noticeable difference. With
>disablelastaccess I got about 10% - 15% better results, but it is not
>too significant.

Actually, that's enough to care about in a real world deployment.


>Finally I tried
>
>fsync = false
>
>and got 580-620 tps. So, the short summary:
>
>WinXP fsync = true 20-28 tps
>WinXP fsync = false 600 tps
>Linux 800 tps

This Linux figure is really compared to the WinXP fsync=false, since you
have write cacheing on. The interesting one to compare with is the other
one you did:

Linux w/o write cache 80-90 tps

Which is still faster than windows, but not as much faster.


>The general question is - does PostgreSQL really need fsync? I
>suppose it
>is a question for design, not platform-specific one. It sounds
>like only
>one scenario, when fsync is useful, is to interprocess
>communication via
>open file. But PostgreSQL utilize IPC for this, so does fsync is really
>required?

No, fsync is used to make sure your data is committed to disk once you
commit a transaction. IPC is handled through shared memory and named
pipes.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

There are two different concerns here.

1. transactions loss because of unexpected power loss and/or system failure
2. inconsistent database state

For many application (1) is fairly acceptable, and (2) is not.

So I'd like to formulate my questions by another way.

- if PostgeSQL is running without fsync, and power loss occur, which kind
of damage is possible? 1, 2, or both?

- it looks like with proper fwrite/fflush policy it is possible to
guarantee that only transactions loss may occur, but database
keeps some consistent state as before (several) last transactions.
Is it true for PostgeSQL?

Regards,
E.R.
__________________________________________________ ______________________e
Evgeny Rodichev Sternberg Astronomical Institute
email: er@sai.msu.su Moscow State University
Phone: 007 (095) 939 2383
Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Evgeny Rodichev wrote:
> There are two different concerns here.
>
> 1. transactions loss because of unexpected power loss and/or system failure
> 2. inconsistent database state
>
> For many application (1) is fairly acceptable, and (2) is not.
>
> So I'd like to formulate my questions by another way.
>
> - if PostgeSQL is running without fsync, and power loss occur, which kind
> of damage is possible? 1, 2, or both?

Both. If 1 can happen then 2 can happen.

> - it looks like with proper fwrite/fflush policy it is possible to
> guarantee that only transactions loss may occur, but database
> keeps some consistent state as before (several) last transactions.
> Is it true for PostgeSQL?

No - if fsync is on and the transaction is reported as committed then it
should still be there when the power returns. Provided you don't suffer
hardware failure you should be able to rely on a committed transaction
actually being written to disk. That's what fsync does for you.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq