Thread: 9.2 documentation/configuration question

9.2 documentation/configuration question

From
John Scalia
Date:
Hi All,

I've been asked to assist another group in our company and their db configuration is a little confusing to me. The
databaseis not replicated, but it is running WAL archiving. I've  
been looking at http://www.postgresql.org/docs/9.2/static/wal-configuration.html and my confusion regards the 4th
paragraph:

"The server's checkpointer process automatically performs a checkpoint every so often. A checkpoint is created every
checkpoint_segmentslog segments, or every checkpoint_timeout  
seconds, whichever comes first. The default settings are 3 segments and 300 seconds (5 minutes), respectively. In cases
whereno WAL has been written since the previous checkpoint,  
new checkpoints will be skipped even if checkpoint_timeout has passed. If WAL archiving is being used and you want to
puta lower limit on how often files are archived in order to  
bound potential data loss, you should adjust archive_timeout parameter rather than the checkpoint parameters..."

So, are docs saying that checkpoint_timeout is ignored if you have a value for archive_timeout or is it still used?
Thisserver currently has: 
checkpoint_segments=32
checkpoint_timeout = 5min #default
archive_timeout=3600

and I'm not sure these make sense together in light of this paragraph.
--
Jay





Re: 9.2 documentation/configuration question

From
Igor Neyman
Date:

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of John Scalia
Sent: Tuesday, December 02, 2014 3:00 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] 9.2 documentation/configuration question

Hi All,

I've been asked to assist another group in our company and their db configuration is a little confusing to me. The
databaseis not replicated, but it is running WAL archiving. I've been looking at
http://www.postgresql.org/docs/9.2/static/wal-configuration.htmland my confusion regards the 4th paragraph: 

"The server's checkpointer process automatically performs a checkpoint every so often. A checkpoint is created every
checkpoint_segmentslog segments, or every checkpoint_timeout seconds, whichever comes first. The default settings are 3
segmentsand 300 seconds (5 minutes), respectively. In cases where no WAL has been written since the previous
checkpoint,new checkpoints will be skipped even if checkpoint_timeout has passed. If WAL archiving is being used and
youwant to put a lower limit on how often files are archived in order to bound potential data loss, you should adjust
archive_timeoutparameter rather than the checkpoint parameters..." 

So, are docs saying that checkpoint_timeout is ignored if you have a value for archive_timeout or is it still used?
Thisserver currently has: 
checkpoint_segments=32
checkpoint_timeout = 5min #default
archive_timeout=3600

and I'm not sure these make sense together in light of this paragraph.
--
Jay


No, documentation says that even if WAL was not written, WAL archive will be created on archive_timeout, in your case
atleast once per hour. 

Regards,
Igor Neyman


Re: 9.2 documentation/configuration question

From
David G Johnston
Date:
Jay at Verizon wrote
> checkpoint_timeout = 5min #default

This impacts the primary machine only and ensures that regardless of the
lack of activity a crash will only need to REDO that last five minutes of
history since everything prior to that will already have been persisted to
the data files.


> archive_timeout=3600

This impacts how far, in time, the archive can be behind the master in a
low-volume situation.  This is because only complete (16MB) WAL files are
shipped.  In a low volume situation it may take several active
checkpoint_timeout periods (not to mention zero activity periods) to
generate enough data to fill up a single 16MB file.

In a low volume situation a single WAL will have multiple checkpoint
records.  In a high volume situation there will be many WAL files that have
no checkpoint records.  This later situation being the reason for the
"checkpoint_segments" parameter.

So the archive will never be more than 16MB behind the master but the
corresponding time period is unbounded in the absence of archive_timeout.
The master will always be up-to-date but the amount of time needed for
recovery is bounded either by time or size - which ever one comes first.

David J.






--
View this message in context:
http://postgresql.nabble.com/9-2-documentation-configuration-question-tp5828964p5828990.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: 9.2 documentation/configuration question

From
David G Johnston
Date:
Jay at Verizon wrote
> and I'm not sure these make sense together in light of this paragraph.

While not a severe need this section would be well served if it were divided
into:

x.1: Primary - Checkpoint Configuration
x.2: Archive & Replication

Addressing the apparent confusion that people may have that the checkpoint
parameters impact the recency of the archive is necessary but probably
deserves more than a byline in a paragraph explaining exactly how the two
bounding checkpoint parameters function - a concept complex enough by
itself.

x.2 could also be separated into log shipping and warm/hot standby
subsections.  They probably do not warrant separation into their own
top-level sections but physically separating and framing them within this
section would introduce the needed mental boundaries between the pieces that
affect primary operations and those that are specific to whatever form of
replication/archive the user may be using.

I'll add this my own doc patches listing but I'm not expecting any immediate
attention right now.

David J.




--
View this message in context:
http://postgresql.nabble.com/9-2-documentation-configuration-question-tp5828964p5828994.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: 9.2 documentation/configuration question

From
Scott Ribe
Date:
On Dec 2, 2014, at 3:06 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
>
> x.2 could also be separated into log shipping and warm/hot standby
> subsections.  They probably do not warrant separation into their own
> top-level sections but physically separating and framing them within this
> section would introduce the needed mental boundaries between the pieces that
> affect primary operations and those that are specific to whatever form of
> replication/archive the user may be using.

I've had similar thoughts, that it seemed confusing based on log shipping and streaming, and the setup for each, being
notsufficiently distinguished. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice