标签

PostgreSQL , wal_buffers , shared_buffers

背景

当wal_buffers设置为-1时，PG会自动计算一个值，取决于几个因素，wal_segment_size, shared_buffer.

算法

src/backend/access/transam/xlog.c

/*   * GUC check_hook for wal_buffers   */  bool  check_wal_buffers(int *newval, void **extra, GucSource source)  {          /*           * -1 indicates a request for auto-tune.           */          if (*newval == -1)          {                  /*                   * If we haven't yet changed the boot_val default of -1, just let it                   * be.  We'll fix it when XLOGShmemSize is called.                   */                  if (XLOGbuffers == -1)                          return true;                    /* Otherwise, substitute the auto-tune value */                  *newval = XLOGChooseNumBuffers();          }            /*           * We clamp manually-set values to at least 4 blocks.  Prior to PostgreSQL           * 9.1, a minimum of 4 was enforced by guc.c, but since that is no longer           * the case, we just silently treat such values as a request for the           * minimum.  (We could throw an error instead, but that doesn't seem very           * helpful.)           */          if (*newval < 4)                  *newval = 4;            return true;  }      /*   * Auto-tune the number of XLOG buffers.   *   * The preferred setting for wal_buffers is about 3% of shared_buffers, with   * a maximum of one XLOG segment (there is little reason to think that more   * is helpful, at least so long as we force an fsync when switching log files)   * and a minimum of 8 blocks (which was the default value prior to PostgreSQL   * 9.1, when auto-tuning was added).   *   * This should not be called until NBuffers has received its final value.   */  static int  XLOGChooseNumBuffers(void)  {          int                     xbuffers;            xbuffers = NBuffers / 32;          if (xbuffers > (wal_segment_size / XLOG_BLCKSZ))                  xbuffers = (wal_segment_size / XLOG_BLCKSZ);          if (xbuffers < 8)                  xbuffers = 8;          return xbuffers;  }

算法

自动计算：shared_buffers/32

上限：wal_segment_size/XLOG_BLCKSZ

下限：8*XLOG_BLCKSZ

例子

postgres=# show wal_segment_size ;   wal_segment_size   ------------------   16MB  (1 row)    postgres=# show wal_block_size  ;   wal_block_size   ----------------   8192  (1 row)    postgres=# show shared_buffers ;   shared_buffers   ----------------   32GB  (1 row)

以上参数，如果wal_buffers设置为-1，那么自动计算得到的值为16MB.