Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1je0Wz-0002Hw-UU for pgsql-docs@arkaria.postgresql.org; Wed, 27 May 2020 18:11:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1je0Wy-0007oW-LB for pgsql-docs@arkaria.postgresql.org; Wed, 27 May 2020 18:11:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1je0Wy-0007oP-C7 for pgsql-docs@lists.postgresql.org; Wed, 27 May 2020 18:11:56 +0000 Received: from mail-pg1-x532.google.com ([2607:f8b0:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1je0Ww-0006LJ-64 for pgsql-docs@lists.postgresql.org; Wed, 27 May 2020 18:11:55 +0000 Received: by mail-pg1-x532.google.com with SMTP id m1so5893867pgk.1 for ; Wed, 27 May 2020 11:11:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=rk10zWZCDQ0mDOOFfBTSC84pEh1NWrrmyNwwsOD8v8o=; b=eBrohYNwi6SgXk5fWYquLYHIwcqetYdTNiHAZCqSLnKmyDkbQ7iEc7l+VaLT7OQS1P I7akNRCgYbDZKZBcdmVfMrEboNQ8/gAMKRl3tiwyXoQllbjPWj1EJ8YcGDBFjfsxsBdK VwdmbJ2D9DJ0ssPaguosQ9BG9ZuUoZQYg0XxD7p0+Ea9saBRa6Xg+V45L4fedaEVj/ea O6SN9nvZlCsY26urmk53FUTzYjTUfrQS8t7CnxYHEIO1TamBbYVHLhNfHvBLfW7HSbv0 xQK0+9tKfUBi5izW/V4LU9xUIGlGBrEN4OlFCM2RmjW/enQRNCWuMiRLDypSpdmFiKJW yLhg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=rk10zWZCDQ0mDOOFfBTSC84pEh1NWrrmyNwwsOD8v8o=; b=bIlBmfGos0fU4Y2V0GP6diaSfoitRzqThIRoGpG3U6m5AmW6pIOVLLjXqzMjM1GQvT jWGXCghz4rh2qmBd7BcDc6Bs9y6YXv7HidKk65h9PF8fQSwDho5dBbMKSoFKl1xUF9+d rpfnMTV+kruKGh73vek3FhGd1qgKfK1Ep8D2VhDdDX3tpjkiHifE6XcIC0Rq78+1aTc5 oawBeseN/MQG56xc/GMl4/R7kVQLXgl/uc09jVHxrI4r+lvk3CJQdtpD2cuOGU5Ec77v /5JkdVh1VUJMgLgMnbUcuOJgmOiUrv7s1mlubuVlaEELqaT400Dd99UfYwUrAsnt0DUT OEbw== X-Gm-Message-State: AOAM531nYAVsEyEP8iZHFB0pCUblOocUHjbEOIHRR/eExWapNvhDetRp 0viCto9dRO+SRRZgDKKk/bUTJ7e99dT2dVEAix+7Rw== X-Google-Smtp-Source: ABdhPJxhcRRaQ7BuMpIUgPZO7v37MD7AxJ5ATSoYx1m3EU0KfpCLC40xUfn2Qalv4PuDEfUZvFp5tfFe+ou6sp+Gq/k= X-Received: by 2002:a05:6a00:134c:: with SMTP id k12mr5091159pfu.313.1590603111173; Wed, 27 May 2020 11:11:51 -0700 (PDT) MIME-Version: 1.0 References: <159057129673.7025.17016799863173731176@wrigleys.postgresql.org> <92491c6d07a3216ef2caee8ef55c02b5@postgrespro.ru> In-Reply-To: <92491c6d07a3216ef2caee8ef55c02b5@postgrespro.ru> From: "David G. Johnston" Date: Wed, 27 May 2020 11:11:33 -0700 Message-ID: Subject: Re: max_wal_size To: p.luzanov@postgrespro.ru Cc: pluzanov@postgrespro.ru, pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006b91ed05a6a525c8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000006b91ed05a6a525c8 Content-Type: text/plain; charset="UTF-8" On Wed, May 27, 2020 at 9:17 AM wrote: > David, > > > This setting is the indirect means to ensure that the WAL directory > > doesn't get too large by forcing a checkpoint thus allowing the > > corresponding WAL to be removed. > > > This is a soft limit, ok. > But the question is a little different. > > Suppose we have: version >= 11, no replication slots, archive_mode = > off. > Checkpoint_timeout is big enough, so checkpoints triggered only by > max_wal_size (1GB). > checkpoint_completion_target = 1. > > What size of WAL files will be generated between checkpoints? > 1GB or 0.5GB? > > As I understand the description of max_wal_size(Maximum size to let the > WAL grow to between automatic WAL checkpoints), the answer is 1GB. > But it seems that the right answer is 0.5GB. > > Given how long it took me to come up with the answer I'm not going to claim the documentation shouldn't be improved...or that the following is even correct...especially not having performed tests I see where you are coming from better now - in your example the system operates under the simultaneous constraints that the directory should not take up more than X amount of space and also that it wants zero wait time between the end of the last checkpoint and the start of the next one - where the next one will start at the X amount mark. The (unstated) goal is to minimize I/O throughput allocated to WAL. Thus it should write out half of the maximum data in exactly the same amount of time that it takes for a new half of the maximum data to accumulate. If it writes any slower it will have to wait at the end. For 0.5 you get 2/3rds consumption: ( n / ( 1 + 0.5 ) ) = n * 2/3 - though my head is starting to hurt at the moment to fully explain the timing pattern. Unlike the 1.0 case there is downtime where non-checkpoint induced writing is performed and the rate is chosen, combined with that, so that some time is left at the end of each cycle. David J. --0000000000006b91ed05a6a525c8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, May 27, 2020 at 9:17 AM <p.luzanov@postgrespro.ru> wrote:
David,

> This setting is the indirect means to ensure that the WAL directory > doesn't get too large by forcing a checkpoint thus allowing the > corresponding WAL to be removed.


This is a soft limit, ok.
But the question is a little different.

Suppose we have: version >=3D 11, no replication slots, archive_mode =3D=
off.
Checkpoint_timeout is big enough, so checkpoints triggered only by
max_wal_size (1GB).
checkpoint_completion_target =3D 1.

What size of WAL files will be generated between checkpoints?
1GB or 0.5GB?

As I understand the description of max_wal_size(Maximum size to let the WAL grow to between automatic WAL checkpoints), the answer is 1GB.
But it seems that the right answer is 0.5GB.


<= /div>
Given how long it took me to come up with the answer I'm n= ot going to claim the documentation shouldn't be improved...or that the= following is even correct...especially not having performed tests
I see where you are coming from better now - in your example the = system operates under the simultaneous constraints that the directory shoul= d not take up more than X amount of space and also that it wants zero wait = time between the end of the last checkpoint and the start of the next one -= where the next one will start at the X amount mark.=C2=A0 The (unstated) g= oal is to minimize I/O throughput allocated to WAL.=C2=A0 Thus it should wr= ite out half of the maximum data in exactly the same amount of time that it= takes for a new half of the maximum data to accumulate.=C2=A0 If it writes= any slower it will have to wait at the end.

For 0= .5 you get 2/3rds consumption: ( n / ( 1=C2=A0+ 0.5=C2=A0 ) ) =3D n * 2/3 -= though my head is starting to hurt at the moment to fully explain the timi= ng pattern.=C2=A0 Unlike the 1.0 case there is downtime where non-checkpoin= t induced writing is performed and the rate is chosen, combined with that, = so that some time is left at the end of each cycle.

Da= vid J.

--0000000000006b91ed05a6a525c8--