PostgreSQL wal_buffers 自动计算算法

标签

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.