Re: auto-sizing wal_buffers - Mailing list pgsql-hackers

Fujii Masao wrote:
> +int            XLOGbuffersMin = 8;
>
> XLOGbuffersMin is a fixed value. I think that defining it as a macro
> rather than a variable seems better.
>
> +        if (XLOGbuffers > 2048)
> +            XLOGbuffers = 2048;
>
> Using "XLOG_SEG_SIZE/XLOG_BLCKSZ" rather than 2048 seems
> better.
>
> +#wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
>
> Typo: s/32kB/64kB
>

Thanks, I've fixed all these issues and attached a new full patch,
pushed to github, etc.  Tests give same results back, and it's nice that
it scale to reasonable behavior if someone changes their XLOG segment size.

It should be possible to set the value back to the older minimum value
of 32kB too.  That's doesn't actually seem to work though; when I try it
I get:

$ psql -c "SELECT name,unit,boot_val,setting,current_setting(name) FROM
pg_settings WHERE name IN ('wal_buffers','shared_buffers')"
      name      | unit | boot_val | setting | current_setting
----------------+------+----------+---------+-----------------
 shared_buffers | 8kB  | 1024     | 131072  | 1GB
 wal_buffers    | 8kB  | -1       | 8       | 64kB

Where I was expecting that setting to be "4" instead for 32kB.  So
there's probably some minor bug left in where I inserted this into the
initialization sequence.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8e2a2c5..1c13f20 100644
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*************** SET ENABLE_SEQSCAN TO OFF;
*** 1638,1649 ****
        </indexterm>
        <listitem>
         <para>
!         The amount of memory used in shared memory for WAL data.  The
!         default is 64 kilobytes (<literal>64kB</>).  The setting need only
!         be large enough to hold the amount of WAL data generated by one
!         typical transaction, since the data is written out to disk at
!         every transaction commit.  This parameter can only be set at server
!         start.
         </para>

         <para>
--- 1638,1659 ----
        </indexterm>
        <listitem>
         <para>
!         The amount of shared memory used for storing WAL data.  The
!         default setting of -1 adjusts this automatically based on the size
!         of <varname>shared_buffers</varname>, making it 1/32 (about 3%) of
!         the size of that normally larger shared memory block.  Automatically
!         set values are limited to a maximum sufficient to hold one WAL
!         segment worth of data, normally 16 megabytes (<literal>16MB</>).
!         The smallest allowable setting is 32 kilobytes (<literal>32kB</>).
!        </para>
!
!        <para>
!         Since the data is written out to disk at every transaction commit,
!         the setting many only need to be be large enough to hold the amount
!         of WAL data generated by one typical transaction.  Larger values,
!         typically at least a few megabytes, can improve write performance
!         on a busy server where many clients are committing at once.
!         Extremely large settings are unlikely to provide additional benefit.
         </para>

         <para>
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 5b6a230..d057773 100644
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 69,75 ****
  /* User-settable parameters */
  int            CheckPointSegments = 3;
  int            wal_keep_segments = 0;
! int            XLOGbuffers = 8;
  int            XLogArchiveTimeout = 0;
  bool        XLogArchiveMode = false;
  char       *XLogArchiveCommand = NULL;
--- 69,76 ----
  /* User-settable parameters */
  int            CheckPointSegments = 3;
  int            wal_keep_segments = 0;
! int            XLOGbuffers = -1;
! int            XLOGbuffersMin = 8;
  int            XLogArchiveTimeout = 0;
  bool        XLogArchiveMode = false;
  char       *XLogArchiveCommand = NULL;
*************** GetSystemIdentifier(void)
*** 4780,4790 ****
--- 4781,4819 ----
  /*
   * Initialization of shared memory for XLOG
   */
+
+ void XLOGTuneNumBuffers(void)
+ {
+     int one_segment = XLOG_SEG_SIZE / XLOG_BLCKSZ;
+
+     /*
+      * If automatic setting was requested, use about 3% as much memory as
+      * requested for the buffer cache.  Clamp the automatic maximum to the
+      * size of one XLOG segment, while still allowing a larger manual
+      * setting.  Don't go below the default setting in earlier versions:
+      * twice the size of the minimum, which at default build options is 64kB.
+      */
+     if (XLOGbuffers == -1)  /* set automatically */
+         {
+         XLOGbuffers = NBuffers / 32;
+         if (XLOGbuffers > one_segment)
+             XLOGbuffers = one_segment;
+         if (XLOGbuffers < (XLOGbuffersMin * 2))
+             XLOGbuffers = XLOGbuffersMin * 2;
+         }
+
+     /* Enforce a 32KB minimum in every case */
+     if (XLOGbuffers < XLOGbuffersMin)
+         XLOGbuffers = XLOGbuffersMin;
+ }
+
  Size
  XLOGShmemSize(void)
  {
      Size        size;

+     XLOGTuneNumBuffers();
+
      /* XLogCtl */
      size = sizeof(XLogCtlData);
      /* xlblocks array */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index e4dea31..7c014cb 100644
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*************** static struct config_int ConfigureNamesI
*** 1765,1771 ****
              GUC_UNIT_XBLOCKS
          },
          &XLOGbuffers,
!         8, 4, INT_MAX, NULL, NULL
      },

      {
--- 1765,1771 ----
              GUC_UNIT_XBLOCKS
          },
          &XLOGbuffers,
!         -1, -1, INT_MAX, NULL, NULL
      },

      {
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index f436b83..6c6f9a9 100644
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 162,168 ****
                      #   fsync_writethrough
                      #   open_sync
  #full_page_writes = on            # recover from partial page writes
! #wal_buffers = 64kB            # min 32kB
                      # (change requires restart)
  #wal_writer_delay = 200ms        # 1-10000 milliseconds

--- 162,168 ----
                      #   fsync_writethrough
                      #   open_sync
  #full_page_writes = on            # recover from partial page writes
! #wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
                      # (change requires restart)
  #wal_writer_delay = 200ms        # 1-10000 milliseconds

diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 74d3427..b475867 100644
*** a/src/include/access/xlog.h
--- b/src/include/access/xlog.h
*************** typedef struct XLogRecord
*** 85,90 ****
--- 85,93 ----
   */
  #define XLR_BKP_REMOVABLE        0x01

+ /* Minimum setting used for a lower bound on wal_buffers */
+ #define XLOG_BUFFER_MIN            4
+
  /* Sync methods */
  #define SYNC_METHOD_FSYNC        0
  #define SYNC_METHOD_FDATASYNC    1
*************** extern void GetXLogReceiptTime(Timestamp
*** 293,298 ****
--- 296,302 ----

  extern void UpdateControlFile(void);
  extern uint64 GetSystemIdentifier(void);
+ extern void XLOGTuneNumBuffers(void);
  extern Size XLOGShmemSize(void);
  extern void XLOGShmemInit(void);
  extern void BootStrapXLOG(void);