Обсуждение: 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.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
-----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
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.
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.
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