To: "pgsql-zh-general@postgresql.org" <pgsql-zh-general@postgresql.org>
From: Quan Zongliang <zongliang.quan@postgresdata.com>
Subject: =?UTF-8?B?W+WNmuaWh10g5Y+C5pWwbWF4X3dhbF9zaXpl5LiObWluX3dhbF9zaXpl?=
 =?UTF-8?B?55qE6K6h566X5LiO5b2x5ZON?=
Message-ID: <e0990ee6-6efa-8ca0-4bdc-9052add439e7@postgresdata.com>
Date: Sat, 4 Jun 2016 10:50:15 +0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-zh-general-owner@postgresql.org

有人问我现在 checkpoint_segments 怎么计算，所以写了一篇这个分想给大家。
希望我拙劣的文笔能够把问题说清楚。

原文链接：http://my.oschina.net/quanzl/blog/686932

1、GUC参数最大最小值的设置是一个开区间，我们看实数的设置

if (newval->realval < conf->min || newval->realval > conf->max)


2、checkpoint_completion_target 取值范围 (0.0, 1.0)

     {
         {"checkpoint_completion_target", PGC_SIGHUP, WAL_CHECKPOINTS,
             gettext_noop("Time spent flushing dirty buffers during 
checkpoint, as fraction of checkpoint interval."),
             NULL
         },
         &CheckPointCompletionTarget,
         0.5, 0.0, 1.0,
         NULL, NULL, NULL
     },


3、新参数 min_wal_size、max_wal_size
以前叫做 checkpoint_segments 已经消失，原意是 Maximum number of log file segments 
between automatic WAL checkpoints


4、max_wal_size 的定义和计算方式

     {
         {"max_wal_size", PGC_SIGHUP, WAL_CHECKPOINTS,
             gettext_noop("Sets the WAL size that triggers a checkpoint."),
             NULL,
             GUC_UNIT_XSEGS
         },
         &max_wal_size,
         64, 2, INT_MAX,
         NULL, assign_max_wal_size, NULL
     },
公式

     {"TB", GUC_UNIT_XSEGS, (1024 * 1024 * 1024) / (XLOG_SEG_SIZE / 1024)},
     {"GB", GUC_UNIT_XSEGS, (1024 * 1024) / (XLOG_SEG_SIZE / 1024)},
     {"MB", GUC_UNIT_XSEGS, -(XLOG_SEG_SIZE / (1024 * 1024))},
     {"kB", GUC_UNIT_XSEGS, -(XLOG_SEG_SIZE / 1024)},
结合 GUC代码知道，最后得到的值已经是段数


5、每次 checkpoint 涉及的最大段数，原来是由我们通过参数来直接指定的，而现在

     target = (double) max_wal_size / (2.0 + CheckPointCompletionTarget);

     /* round down */
     CheckPointSegments = (int) target;

     if (CheckPointSegments < 1)
         CheckPointSegments = 1;
我们知道 CheckPointCompletionTarget 取值范围是 (0.0, 
1.0)，所以最终CheckPointSegments得到的值范围是 max_wal_size 的 1/3 ～ 
1/2，最小为1。


--------------------------------------------
权宗亮


-- 
Sent via pgsql-zh-general mailing list (pgsql-zh-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-zh-general