Changing the default wal_sync_method to open_sync for Win32? - Mailing list pgsql-hackers

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 */