Re: wal_init_zero and wal_recycle

[Thomas Munro]

On Wed, May 27, 2020 at 7:09 PM Simon Riggs  wrote:
> On Wed, 27 May 2020 at 04:27, Fujii Masao  wrote:
>>
>> Hi,
>>
>> The group of wal_init_zero and wal_recycle is WAL_SETTINGS in guc.c,
>> but their descriptions are located in "19.6. Replication"/"19.6.1. Sending
>> Servers" section. This seems a documentation bug. They should be located
>> in "19.5. Write Ahead Log"/"19.5.1. Settings". Thought?
>
>
> +1
>
> Why are there two settings for the same thing (COW support)?
> Would we really want to allow setting one but not the other?

In the long discussion[1] we decided to invent GUCs that tell you
precisely what they do, rather than trying to have a mysterious "COW
filesystem mode", given the apparent lack of COW filesystem expertise
in the thread (for example: claims, theories and data about ZFS were
presented, but do they also apply to BTRFS, APFS, ReFS, ...?  I dunno,
and no one showed up with more information on that.)

[1] 
https://www.postgresql.org/message-id/flat/CACPQ5Fo00QR7LNAcd1ZjgoBi4y97%2BK760YABs0vQHH5dLdkkMA%40mail.gmail.com

Re: max_wal_size

[David G. Johnston]

On Wed, May 27, 2020, 14:12  wrote:

> David,
>
> > For 0.5 you get 2/3rds consumption: ( n / ( 1 + 0.5  ) ) = n * 2/3
>
> Exactly, for checkpoint_completion_target=0.5 the distance between
> checkpoints is 2/3 of max_wal_size.
>
> But back to the documentation of max_wal_size.
> "Maximum size to let the WAL grow to between automatic WAL checkpoints."
>
> For me it looks like the distance between checkpoints is equal to
> max_wal_size.
>
> English is not my native language, so I admit that I misunderstand the
> exact meaning of this phrase.
>
>
I think part of the issue is your operating with a mental model of this
contrary to the one the documentation was written for.  Specifically if you
rely on a size trigger and set completion target to zero I believe you
should get something close to the full 10gb you are looking for.  But there
is a reason why the wording for completion target uses the word "time" -
the mental model is that time is the typical driving factor and that the
size component is simply there to handle unusually high peak volume by
setting a ceiling that will indeed immediately trigger an automatic WAL
checkpoint.

In any case "grow to" means that it doesn't matter whether at the end of
the previous checkpoint 1gb or 9gb were in use - once the usage goes "up
to" 10gp the automatic checkpoint will trigger.  If the word "to" wasn't
there your interpretation would be correct.  Its a fair argument to say
such an important distinction shouldn't be placed on the word "to" but it
isn't wrong.

David J.

Re: max_wal_size

[p . luzanov]

David,


For 0.5 you get 2/3rds consumption: ( n / ( 1 + 0.5  ) ) = n * 2/3


Exactly, for checkpoint_completion_target=0.5 the distance between 
checkpoints is 2/3 of max_wal_size.


But back to the documentation of max_wal_size.
"Maximum size to let the WAL grow to between automatic WAL checkpoints."

For me it looks like the distance between checkpoints is equal to 
max_wal_size.


English is not my native language, so I admit that I misunderstand the 
exact meaning of this phrase.


P.S. Sorry to post this topic twice.
https://www.postgresql.org/message-id/97af9ef7-d7d9-cfd0-a280-d400cf05c68a%40postgrespro.ru
-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: max_wal_size

[David G. Johnston]

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.

Re: max_wal_size

[p . luzanov]

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.

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: max_wal_size

[David G. Johnston]

On Wed, May 27, 2020 at 8:18 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/runtime-config-wal.html
> Description:
>
> About parameter max_wal_size the documentation says that "Maximum size to
> let the WAL grow to between automatic WAL checkpoints."
> Is it correct?
> The size between automatic WAL checkpoints or the size of the whole pg_wal
> directory?
>

Reading on... "This is a soft limit; WAL size can exceed max_wal_size under
special circumstances"

The database doesn't provide an option that would basically mean "make sure
the wal directory doesn't get larger than X even if that means that it will
become corrupted should it crash".

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.

David J.

max_wal_size

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/runtime-config-wal.html
Description:

About parameter max_wal_size the documentation says that "Maximum size to
let the WAL grow to between automatic WAL checkpoints."
Is it correct?
The size between automatic WAL checkpoints or the size of the whole pg_wal
directory?

Re: max_wal_size

[Egor Rogov]

On 27.05.2020 13:16, Andreas Kretschmer wrote:


Am 27.05.20 um 12:11 schrieb Pavel Luzanov:

Hello,

About parameter max_wal_size the documentation 
 
says: "Maximum size to let the WAL grow to between automatic WAL 
checkpoints."

Is it correct?

The size between automatic WAL checkpoints or the size of all WAL 
files in the pg_wal directory?


size between checkpoints, size of all wal's can be higher, see 
wal_keep_segments.




It is clear that total size can be higher due to wal_keep_segments, 
replications slots, archive_command etc.


But does max_wal_size relate to the (a) two subsequent checkpoints (as 
you may read the wording in the docs) or (b) to the past completed 
checkpoint AND the current yet-uncompleted checkpoint (which together 
may be up to TWO checkpoint intervals)?


If I read CalculateCheckpointSegments() in xlog.c correctly, the answer 
is (b), and hence the documentation is misleading.


Regards,
Egor.

Re: max_wal_size

[Pavel Luzanov]

Andreas,

size between checkpoints, size of all wal's can be higher, see 
wal_keep_segments.


Hm, I also thought, but after looking at the source 
, 
it seems like this is a restriction on all files in pg_wal.


-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: max_wal_size

[Andreas Kretschmer]

Am 27.05.20 um 12:11 schrieb Pavel Luzanov:

Hello,

About parameter max_wal_size the documentation 
 
says: "Maximum size to let the WAL grow to between automatic WAL 
checkpoints."

Is it correct?

The size between automatic WAL checkpoints or the size of all WAL 
files in the pg_wal directory?


size between checkpoints, size of all wal's can be higher, see 
wal_keep_segments.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

max_wal_size

[Pavel Luzanov]

Hello,

About parameter max_wal_size the documentation 
 
says: "Maximum size to let the WAL grow to between automatic WAL 
checkpoints."

Is it correct?

The size between automatic WAL checkpoints or the size of all WAL files 
in the pg_wal directory?


-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: Change JOIN tutorial to focus more on explicit joins

[Jürgen Purtz]

On 20.05.20 23:56, Thomas Munro wrote:

On Thu, May 21, 2020 at 1:37 AM PG Doc comments form
 wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/tutorial-join.html
Description:

The tutorial about joins makes the following statement about the explicit
JOIN operator:


This syntax is not as commonly used as the one above

I think in 2020 this claim is no longer true, and I would love to see the
manual prefer the "modern" explicit JOIN operator rather than sticking to
the ancient implicit joins in the WHERE clause.

+1

The "new" syntax is 28 years old, from SQL 92.  I don't see too many
SQL 86 joins.  Would you like to write a documentation patch?



The attached patch

- prefers the explicit join-syntax over the implicit one and explains 
the keywords of the explicit syntax


- uses a more accurate definition of 'join'

- separates  and  tags

- shifts  definitions outside of  to get a better 
rendering in PDF


- adds a note concerning IDs and foreign keys


--

J. Purtz


diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
index c0889743c4..6f8ea373b5 100644
--- a/doc/src/sgml/query.sgml
+++ b/doc/src/sgml/query.sgml
@@ -441,11 +441,11 @@ SELECT DISTINCT city

 Thus far, our queries have only accessed one table at a time.
 Queries can access multiple tables at once, or access the same
-table in such a way that multiple rows of the table are being
-processed at the same time.  A query that accesses multiple rows
-of the same or different tables at one time is called a
-join query.  As an example, say you wish to
-list all the weather records together with the location of the
+table several times. Such queries — they are called
+join queries — combine
+rows of one table in some way with rows of the other table
+and return a single row per combination. An example may be a
+list of all the weather records together with the location of the
 associated city.  To do that, we need to compare the city
 column of each row of the weather table with the
 name column of all rows in the cities
@@ -461,10 +461,17 @@ SELECT DISTINCT city
 
 
 SELECT *
-FROM weather, cities
-WHERE city = name;
+FROM   weather
+JOIN   cities ON (city = name);
 
 
+The keyword JOIN connects the two tables.
+Behind the keyword ON follows the
+definition how to compare their rows. In this case, the
+column city of table weather
+must be equal to the column name
+of table cities.
+
 
  city  | temp_lo | temp_hi | prcp |date| name  | location
 ---+-+-+--++---+---
@@ -476,8 +483,14 @@ SELECT *

 

-Observe two things about the result set:
+Observe some things about the result set:
 
+ 
+  
+   The resulting rows contain values from both tables.
+  
+ 
+
  
   
There is no result row for the city of Hayward.  This is
@@ -499,8 +512,8 @@ SELECT *
*:
 
 SELECT city, temp_lo, temp_hi, prcp, date, location
-FROM weather, cities
-WHERE city = name;
+FROM   weather
+JOIN   cities ON (city = name);
 
   
  
@@ -509,10 +522,9 @@ SELECT city, temp_lo, temp_hi, prcp, date, location
 

 Exercise:
-
 
  Attempt to determine the semantics of this query when the
- WHERE clause is omitted.
+ ON clause is omitted.
 

 
@@ -526,8 +538,8 @@ SELECT city, temp_lo, temp_hi, prcp, date, location
 
 SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
-FROM weather, cities
-WHERE cities.name = weather.city;
+FROM   weather
+JOIN   cities ON (cities.name = weather.city);
 
 
 It is widely considered good style to qualify all column names
@@ -537,20 +549,22 @@ SELECT weather.city, weather.temp_lo, weather.temp_hi,
 

 Join queries of the kind seen thus far can also be written in this
-alternative form:
+form:
 
 
 SELECT *
-FROM weather INNER JOIN cities ON (weather.city = cities.name);
+FROM   weather, cities
+WHERE  weather.city = cities.name;
 
 
-This syntax is not as commonly used as the one above, but we show
-it here to help you understand the following topics.
+This syntax is not as commonly used as the one above. It dates back
+to the very first times of SQL, avoids the JOIN
+keyword and uses the WHERE clause instead of the
+ON clause.

 
+   joinouter

-joinouter
-
 Now we will figure out how we can get the Hayward records back in.
 What we want the query to do is to scan the
 weather table and for each row to find the
@@ -563,15 +577,19 @@ SELECT *
 
 
 SELECT *
-FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
+FROM   weather
+LEFT OUTER JOIN cities ON (weather.city = cities.name);
 
+
+
+
  city  | temp_lo | temp_hi | prcp |  

Re: wal_init_zero and wal_recycle

[Simon Riggs]

On Wed, 27 May 2020 at 04:27, Fujii Masao 
wrote:

> Hi,
>
> The group of wal_init_zero and wal_recycle is WAL_SETTINGS in guc.c,
> but their descriptions are located in "19.6. Replication"/"19.6.1. Sending
> Servers" section. This seems a documentation bug. They should be located
> in "19.5. Write Ahead Log"/"19.5.1. Settings". Thought?
>

+1

Why are there two settings for the same thing (COW support)?
Would we really want to allow setting one but not the other?

-- 
Simon Riggshttp://www.2ndQuadrant.com/

Mission Critical Databases