Thread: Re: [pgsql-hackers-win32] win32 performance - fsync question
I'd like to see this one also considered for 8.0.x, though I'd certainly like to see some more testing as well. Perhaps it's suitable for the "8.0.x with extended testing" that is planned for the ARC replacement code? It does make a huge difference on win32. While we definitly don't want to risk data, a 60% speedup in write intensive apps is a *lot*. //Magnus >-----Original Message----- >From: pgsql-hackers-win32-owner@postgresql.org >[mailto:pgsql-hackers-win32-owner@postgresql.org] On Behalf Of >Bruce Momjian >Sent: den 27 februari 2005 01:54 >To: Magnus Hagander >Cc: Tom Lane; pgsql-hackers@postgresql.org; >pgsql-hackers-win32@postgresql.org; Merlin Moncure >Subject: Re: [pgsql-hackers-win32] [HACKERS] win32 performance >- fsync question > > > >Patch applied. Thanks. > >I assume this is not approprate for 8.0.X. > >--------------------------------------------------------------- >------------ > > >Magnus Hagander wrote: >> > Magnus prepared a trivial patch which added the O_SYNC flag >> > for windows and mapped it to FILE_FLAG_WRITE_THROUGH in >> > win32_open.c. >> >> Attached is this trivial patch. As Merlin says, it needs some more >> reliability testing. But the numbers are at least reasonable - it >> *seems* like it's doing the right thing (as long as you turn >off write >> cache). And it's certainly a significant performance increase - it >> brings the speed almost up to the same as linux. >> >> >> //Magnus > >Content-Description: o_sync.patch > >[ Attachment, skipping... ] > >> >> ---------------------------(end of >broadcast)--------------------------- >> TIP 8: explain analyze is your friend > >-- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, >Pennsylvania 19073 > >---------------------------(end of >broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Magnus Hagander wrote:
> I'd like to see this one also considered for 8.0.x, though I'd certainly
> like to see some more testing as well. Perhaps it's suitable for the
> "8.0.x with extended testing" that is planned for the ARC replacement
> code?
>
> It does make a huge difference on win32. While we definitly don't want
> to risk data, a 60% speedup in write intensive apps is a *lot*.
While this patch has been applied to CVS HEAD, there are still two open
issues:
1. Should it be the default wal_sync_method for Win32?
Right now we do:
#if defined(OPEN_DATASYNC_FLAG)
#define DEFAULT_SYNC_METHOD_STR "open_datasync"
#define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN
#define DEFAULT_SYNC_FLAGBIT OPEN_DATASYNC_FLAG
#else
#if defined(HAVE_FDATASYNC)
#define DEFAULT_SYNC_METHOD_STR "fdatasync"
#define DEFAULT_SYNC_METHOD SYNC_METHOD_FDATASYNC
#define DEFAULT_SYNC_FLAGBIT 0
#else
#define DEFAULT_SYNC_METHOD_STR "fsync"
#define DEFAULT_SYNC_METHOD SYNC_METHOD_FSYNC
#define DEFAULT_SYNC_FLAGBIT 0
#endif
Basically we do open_datasync -> fdatasync -> fsync. This is
empirically what we found to be fastest on most operating systems, and
we default to the first one that exists on the operating system.
Notice we never default to open_sync. However, on Win32, Magnus got a
60% speedup by using open_sync, implemented using
FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I
think we should default to open_sync on Win32. The attached patch
implements this.
2. Another question is what to do with 8.0.X? Do we backpatch this for
Win32 performance? Can we test it enough to know it will work well?
8.0.2 is going to have a more rigorous testing cycle because of the
buffer manager changes.
---------------------------------------------------------------------------
>
> //Magnus
>
>
> >-----Original Message-----
> >From: pgsql-hackers-win32-owner@postgresql.org
> >[mailto:pgsql-hackers-win32-owner@postgresql.org] On Behalf Of
> >Bruce Momjian
> >Sent: den 27 februari 2005 01:54
> >To: Magnus Hagander
> >Cc: Tom Lane; pgsql-hackers@postgresql.org;
> >pgsql-hackers-win32@postgresql.org; Merlin Moncure
> >Subject: Re: [pgsql-hackers-win32] [HACKERS] win32 performance
> >- fsync question
> >
> >
> >
> >Patch applied. Thanks.
> >
> >I assume this is not approprate for 8.0.X.
> >
> >---------------------------------------------------------------
> >------------
> >
> >
> >Magnus Hagander wrote:
> >> > Magnus prepared a trivial patch which added the O_SYNC flag
> >> > for windows and mapped it to FILE_FLAG_WRITE_THROUGH in
> >> > win32_open.c.
> >>
> >> Attached is this trivial patch. As Merlin says, it needs some more
> >> reliability testing. But the numbers are at least reasonable - it
> >> *seems* like it's doing the right thing (as long as you turn
> >off write
> >> cache). And it's certainly a significant performance increase - it
> >> brings the speed almost up to the same as linux.
> >>
> >>
> >> //Magnus
> >
> >Content-Description: o_sync.patch
> >
> >[ Attachment, skipping... ]
> >
> >>
> >> ---------------------------(end of
> >broadcast)---------------------------
> >> TIP 8: explain analyze is your friend
> >
> >--
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman@candle.pha.pa.us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square,
> >Pennsylvania 19073
> >
> >---------------------------(end of
> >broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.181
diff -c -c -r1.181 xlog.c
*** src/backend/access/transam/xlog.c 12 Feb 2005 23:53:37 -0000 1.181
--- src/backend/access/transam/xlog.c 17 Mar 2005 04:07:44 -0000
***************
*** 69,78 ****
#endif
#endif
#if defined(OPEN_DATASYNC_FLAG)
#define DEFAULT_SYNC_METHOD_STR "open_datasync"
#define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN
! #define DEFAULT_SYNC_FLAGBIT OPEN_DATASYNC_FLAG
#else
#if defined(HAVE_FDATASYNC)
#define DEFAULT_SYNC_METHOD_STR "fdatasync"
--- 69,83 ----
#endif
#endif
+ #if defined(WIN32) /* Fastest on Win32 using FILE_FLAG_WRITE_THROUGH */
+ #define DEFAULT_SYNC_METHOD_STR "open_sync"
+ #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN
+ #define DEFAULT_SYNC_FLAGBIT OPEN_SYNC_FLAG
+ #else
#if defined(OPEN_DATASYNC_FLAG)
#define DEFAULT_SYNC_METHOD_STR "open_datasync"
#define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN
! #define DEFAULT_SYNC_FLAGBIT OPEN_SYNC_FLAG
#else
#if defined(HAVE_FDATASYNC)
#define DEFAULT_SYNC_METHOD_STR "fdatasync"
***************
*** 84,89 ****
--- 89,95 ----
#define DEFAULT_SYNC_FLAGBIT 0
#endif
#endif
+ #endif
/* User-settable parameters */
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Notice we never default to open_sync. However, on Win32, Magnus got a
> 60% speedup by using open_sync, implemented using
> FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I
> think we should default to open_sync on Win32. The attached patch
> implements this.
... and breaks open_datasync for all other platforms ...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Notice we never default to open_sync. However, on Win32, Magnus got a > > 60% speedup by using open_sync, implemented using > > FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I > > think we should default to open_sync on Win32. The attached patch > > implements this. > > ... and breaks open_datasync for all other platforms ... Oh, fixed. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: src/backend/access/transam/xlog.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.181 diff -c -c -r1.181 xlog.c *** src/backend/access/transam/xlog.c 12 Feb 2005 23:53:37 -0000 1.181 --- src/backend/access/transam/xlog.c 17 Mar 2005 04:31:32 -0000 *************** *** 69,74 **** --- 69,79 ---- #endif #endif + #if defined(WIN32) /* Fastest on Win32 using FILE_FLAG_WRITE_THROUGH */ + #define DEFAULT_SYNC_METHOD_STR "open_sync" + #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN + #define DEFAULT_SYNC_FLAGBIT OPEN_SYNC_FLAG + #else #if defined(OPEN_DATASYNC_FLAG) #define DEFAULT_SYNC_METHOD_STR "open_datasync" #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN *************** *** 84,89 **** --- 89,95 ---- #define DEFAULT_SYNC_FLAGBIT 0 #endif #endif + #endif /* User-settable parameters */
On Wed, 16 Mar 2005, Bruce Momjian wrote: > Magnus Hagander wrote: >> I'd like to see this one also considered for 8.0.x, though I'd certainly >> like to see some more testing as well. Perhaps it's suitable for the >> "8.0.x with extended testing" that is planned for the ARC replacement >> code? >> >> It does make a huge difference on win32. While we definitly don't want >> to risk data, a 60% speedup in write intensive apps is a *lot*. > > Notice we never default to open_sync. However, on Win32, Magnus got a > 60% speedup by using open_sync, implemented using > FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I > think we should default to open_sync on Win32. The attached patch > implements this. Considering how stable an Operating System Windows *isn't*, I think the first thing Magnus states very much goes against making this the default: "While we definitely don't want to risk data..." ... Setting something like this that increases the risk to data should never be 'the default behaviour', but a conscious decision on the part of the administrator of the individual system ... and even then, with a good skull-n-cross bones warning around it so that they understand the risks ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Wed, Mar 16, 2005 at 11:20:12PM -0500, Bruce Momjian wrote: > > Basically we do open_datasync -> fdatasync -> fsync. This is > empirically what we found to be fastest on most operating systems, and > we default to the first one that exists on the operating system. > > Notice we never default to open_sync. However, on Win32, Magnus got a > 60% speedup by using open_sync, implemented using > FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I > think we should default to open_sync on Win32. The attached patch > implements this. > > 2. Another question is what to do with 8.0.X? Do we backpatch this for > Win32 performance? Can we test it enough to know it will work well? > 8.0.2 is going to have a more rigorous testing cycle because of the > buffer manager changes. > My preference would be to back-patch to 8.0.1. I have some projects where the performance difference will decide whether or not they go with MSSQL or PostgreSQL. Ken