Re: [HACKERS] Restricting maximum keep segments by repslots
Oops! The previous patch is forgetting the default case and crashes. At Wed, 08 Nov 2017 13:14:31 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20171108.131431.170534842.horiguchi.kyot...@lab.ntt.co.jp> > > I don't think 'distance' is a good metric - that's going to continually > > change. Why not store the LSN that's available and provide a function > > that computes this? Or just rely on the lsn - lsn operator? > > It seems reasonable.,The 'secured minimum LSN' is common among > all slots so showing it in the view may look a bit stupid but I > don't find another suitable place for it. distance = 0 meant the > state that the slot is living but insecured in the previous patch > and that information is lost by changing 'distance' to > 'min_secure_lsn'. > > Thus I changed the 'live' column to 'status' and show that staus > in text representation. > > status: secured | insecured | broken > > So this looks like the following (max_slot_wal_keep_size = 8MB, > which is a half of the default segment size) > > -- slots that required WAL is surely available > select restart_lsn, status, min_secure_lsn, pg_current_wal_lsn() from > pg_replication_slots; > restart_lsn | status | min_recure_lsn | pg_current_wal_lsn > +-++ > 0/1A60 | secured | 0/1A00 | 0/1B42BC78 > > -- slots that required WAL is still available but insecured > restart_lsn | status| min_recure_lsn | pg_current_wal_lsn > +---++ > 0/1A60 | insecured | 0/1C00 | 0/1D76C948 > > -- slots that required WAL is lost > # We should have seen the log 'Some replication slots have lost...' > > restart_lsn | status | min_recure_lsn | pg_current_wal_lsn > +++ > 0/1A60 | broken | 0/1C00 | 0/1D76C9F0 > > > I noticed that I abandoned the segment fragment of > max_slot_wal_keep_size in calculating in the routines. The > current patch honors the frament part of max_slot_wal_keep_size. I changed IsLsnStillAvailable to return meaningful values regardless whether max_slot_wal_keep_size is set or not. # I had been forgetting to count the version for latestst several # patches. I give the version '4' - as the next of the last # numbered patch. -- Kyotaro Horiguchi NTT Open Source Software Center >From 109f056e257aba70dddc8d466767ed0a1db371e2 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 28 Feb 2017 11:39:48 +0900 Subject: [PATCH 1/2] Add WAL releaf vent for replication slots Adds a capability to limit the number of segments kept by replication slots by a GUC variable. --- src/backend/access/transam/xlog.c | 39 +++ src/backend/utils/misc/guc.c | 11 src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/xlog.h | 1 + 4 files changed, 52 insertions(+) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index dd028a1..cfdae39 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -105,6 +105,7 @@ int wal_level = WAL_LEVEL_MINIMAL; int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; +int max_slot_wal_keep_size_mb = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; @@ -9432,9 +9433,47 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) if (max_replication_slots > 0 && keep != InvalidXLogRecPtr) { XLogSegNo slotSegNo; + int slotlimitsegs; + uint64 recptroff; + uint64 slotlimitbytes; + uint64 slotlimitfragment; + + recptroff = XLogSegmentOffset(recptr, wal_segment_size); + slotlimitbytes = 1024 * 1024 * max_slot_wal_keep_size_mb; + slotlimitfragment = XLogSegmentOffset(slotlimitbytes, + wal_segment_size); + + /* calculate segments to keep by max_slot_wal_keep_size_mb */ + slotlimitsegs = ConvertToXSegs(max_slot_wal_keep_size_mb, + wal_segment_size); + /* honor the fragment */ + if (recptroff < slotlimitfragment) + slotlimitsegs++; XLByteToSeg(keep, slotSegNo, wal_segment_size); + /* + * ignore slots if too many wal segments are kept. + * max_slot_wal_keep_size is just accumulated on wal_keep_segments. + */ + if (max_slot_wal_keep_size_mb > 0 && slotSegNo + slotlimitsegs < segno) + { + segno = segno - slotlimitsegs; /* must be positive */ + + /* + * warn only if the checkpoint flushes the required segment. + * we assume here that *logSegNo is calculated keep location. + */ + if (slotSegNo < *logSegNo) +ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots have lost required WAL segnents to continue by up to %ld segments.", + (segno < *logSegNo ? segno : *logSegNo) - slotS
Re: [HACKERS] Restricting maximum keep segments by repslots
Hello, At Mon, 6 Nov 2017 05:20:50 -0800, Andres Freund wrote in <20171106132050.6apzynxrqrzgh...@alap3.anarazel.de> > Hi, > > On 2017-10-31 18:43:10 +0900, Kyotaro HORIGUCHI wrote: > > - distance: > > how many bytes LSN can advance before the margin defined by > > max_slot_wal_keep_size (and wal_keep_segments) is exhasuted, > > or how many bytes this slot have lost xlog from restart_lsn. > > I don't think 'distance' is a good metric - that's going to continually > change. Why not store the LSN that's available and provide a function > that computes this? Or just rely on the lsn - lsn operator? It seems reasonable.,The 'secured minimum LSN' is common among all slots so showing it in the view may look a bit stupid but I don't find another suitable place for it. distance = 0 meant the state that the slot is living but insecured in the previous patch and that information is lost by changing 'distance' to 'min_secure_lsn'. Thus I changed the 'live' column to 'status' and show that staus in text representation. status: secured | insecured | broken So this looks like the following (max_slot_wal_keep_size = 8MB, which is a half of the default segment size) -- slots that required WAL is surely available select restart_lsn, status, min_secure_lsn, pg_current_wal_lsn() from pg_replication_slots; restart_lsn | status | min_recure_lsn | pg_current_wal_lsn +-++ 0/1A60 | secured | 0/1A00 | 0/1B42BC78 -- slots that required WAL is still available but insecured restart_lsn | status| min_recure_lsn | pg_current_wal_lsn +---++ 0/1A60 | insecured | 0/1C00 | 0/1D76C948 -- slots that required WAL is lost # We should have seen the log 'Some replication slots have lost...' restart_lsn | status | min_recure_lsn | pg_current_wal_lsn +++ 0/1A60 | broken | 0/1C00 | 0/1D76C9F0 I noticed that I abandoned the segment fragment of max_slot_wal_keep_size in calculating in the routines. The current patch honors the frament part of max_slot_wal_keep_size. regards, -- Kyotaro Horiguchi NTT Open Source Software Center >From 109f056e257aba70dddc8d466767ed0a1db371e2 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 28 Feb 2017 11:39:48 +0900 Subject: [PATCH 1/2] Add WAL releaf vent for replication slots Adds a capability to limit the number of segments kept by replication slots by a GUC variable. --- src/backend/access/transam/xlog.c | 39 +++ src/backend/utils/misc/guc.c | 11 src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/xlog.h | 1 + 4 files changed, 52 insertions(+) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index dd028a1..cfdae39 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -105,6 +105,7 @@ int wal_level = WAL_LEVEL_MINIMAL; int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; +int max_slot_wal_keep_size_mb = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; @@ -9432,9 +9433,47 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) if (max_replication_slots > 0 && keep != InvalidXLogRecPtr) { XLogSegNo slotSegNo; + int slotlimitsegs; + uint64 recptroff; + uint64 slotlimitbytes; + uint64 slotlimitfragment; + + recptroff = XLogSegmentOffset(recptr, wal_segment_size); + slotlimitbytes = 1024 * 1024 * max_slot_wal_keep_size_mb; + slotlimitfragment = XLogSegmentOffset(slotlimitbytes, + wal_segment_size); + + /* calculate segments to keep by max_slot_wal_keep_size_mb */ + slotlimitsegs = ConvertToXSegs(max_slot_wal_keep_size_mb, + wal_segment_size); + /* honor the fragment */ + if (recptroff < slotlimitfragment) + slotlimitsegs++; XLByteToSeg(keep, slotSegNo, wal_segment_size); + /* + * ignore slots if too many wal segments are kept. + * max_slot_wal_keep_size is just accumulated on wal_keep_segments. + */ + if (max_slot_wal_keep_size_mb > 0 && slotSegNo + slotlimitsegs < segno) + { + segno = segno - slotlimitsegs; /* must be positive */ + + /* + * warn only if the checkpoint flushes the required segment. + * we assume here that *logSegNo is calculated keep location. + */ + if (slotSegNo < *logSegNo) +ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots have lost required WAL segnents to continue by up to %ld segments.", + (segno < *logSegNo ? segno : *logSegNo) - slotSegNo))); + + /* emergency vent */ + slotSegNo = segno; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) diff
Re: [HACKERS] Restricting maximum keep segments by repslots
Hi, On 2017-10-31 18:43:10 +0900, Kyotaro HORIGUCHI wrote: > - distance: > how many bytes LSN can advance before the margin defined by > max_slot_wal_keep_size (and wal_keep_segments) is exhasuted, > or how many bytes this slot have lost xlog from restart_lsn. I don't think 'distance' is a good metric - that's going to continually change. Why not store the LSN that's available and provide a function that computes this? Or just rely on the lsn - lsn operator? - Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 2017-11-06 11:07:04 +0800, Craig Ringer wrote: > Would it make sense to teach xlogreader how to fetch from WAL archive, > too? That way if there's an archive, slots could continue to be used > even after we purge from local pg_xlog, albeit at a performance cost. > > I'm thinking of this mainly for logical slots. That seems more like a page read callback's job than xlogreader's. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 31 October 2017 at 17:43, Kyotaro HORIGUCHI wrote: > Hello, this is a rebased version. > > It gets a change of the meaning of monitoring value along with > rebasing. > > In previous version, the "live" column mysteriously predicts the > necessary segments will be kept or lost by the next checkpoint > and the "distance" offered a still more mysterious value. Would it make sense to teach xlogreader how to fetch from WAL archive, too? That way if there's an archive, slots could continue to be used even after we purge from local pg_xlog, albeit at a performance cost. I'm thinking of this mainly for logical slots. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On Tue, Oct 31, 2017 at 10:43 PM, Kyotaro HORIGUCHI wrote: > Hello, this is a rebased version. Hello Horiguchi-san, I think the "ddl" test under contrib/test_decoding also needs to be updated because it looks at pg_replication_slots and doesn't expect your new columns. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
Hello, this is a rebased version. It gets a change of the meaning of monitoring value along with rebasing. In previous version, the "live" column mysteriously predicts the necessary segments will be kept or lost by the next checkpoint and the "distance" offered a still more mysterious value. In this version the meaning of the two columns became clear and informative. pg_replication_slots - live: true the slot have not lost necessary segments. - distance: how many bytes LSN can advance before the margin defined by max_slot_wal_keep_size (and wal_keep_segments) is exhasuted, or how many bytes this slot have lost xlog from restart_lsn. There is a case where live = t and distance = 0. The slot is currently having all the necessary segments but will start to lose them at most two checkpoint passes. regards, -- Kyotaro Horiguchi NTT Open Source Software Center >From 57eaa2b878d30bfcebb093cca0e772fe7a9bff0e Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 28 Feb 2017 11:39:48 +0900 Subject: [PATCH 1/2] Add WAL releaf vent for replication slots Adds a capability to limit the number of segments kept by replication slots by a GUC variable. --- src/backend/access/transam/xlog.c | 24 src/backend/utils/misc/guc.c | 11 +++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/xlog.h | 1 + 4 files changed, 37 insertions(+) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index dd028a1..f79cefb 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -105,6 +105,7 @@ int wal_level = WAL_LEVEL_MINIMAL; int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; +int max_slot_wal_keep_size_mb = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; @@ -9432,9 +9433,32 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) if (max_replication_slots > 0 && keep != InvalidXLogRecPtr) { XLogSegNo slotSegNo; + int slotlimitsegs = ConvertToXSegs(max_slot_wal_keep_size_mb); XLByteToSeg(keep, slotSegNo, wal_segment_size); + /* + * ignore slots if too many wal segments are kept. + * max_slot_wal_keep_size is just accumulated on wal_keep_segments. + */ + if (max_slot_wal_keep_size_mb > 0 && slotSegNo + slotlimitsegs < segno) + { + segno = segno - slotlimitsegs; /* must be positive */ + + /* + * warn only if the checkpoint flushes the required segment. + * we assume here that *logSegNo is calculated keep location. + */ + if (slotSegNo < *logSegNo) +ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots have lost required WAL segnents to continue by up to %ld segments.", + (segno < *logSegNo ? segno : *logSegNo) - slotSegNo))); + + /* emergency vent */ + slotSegNo = segno; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 65372d7..511023a 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2368,6 +2368,17 @@ static struct config_int ConfigureNamesInt[] = }, { + {"max_slot_wal_keep_size", PGC_SIGHUP, REPLICATION_SENDING, + gettext_noop("Sets the maximum size of extra WALs kept by replication slots."), + NULL, + GUC_UNIT_MB + }, + &max_slot_wal_keep_size_mb, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"wal_sender_timeout", PGC_SIGHUP, REPLICATION_SENDING, gettext_noop("Sets the maximum time to wait for WAL replication."), NULL, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 368b280..e76c73a 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -234,6 +234,7 @@ #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments; 0 disables +#max_slot_wal_keep_size = 0 # measured in bytes; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index 0f2b8bd..f0c0255 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -98,6 +98,7 @@ extern int wal_segment_size; extern int min_wal_size_mb; extern int max_wal_size_mb; extern int wal_keep_segments; +extern int max_slot_wal_keep_size_mb; extern int XLOGbuffers; extern int XLogArchiveTimeout; extern int wal_retrieve_retry_interval; -- 2.9.2 >From 37749d46ba97de38e4593f141bb8a82c67fc0af5 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Thu, 7 Sep 2017 19:13:22 +0900 Subject: [PATCH 2/2] Add monitoring a
Re: [HACKERS] Restricting maximum keep segments by repslots
At Wed, 13 Sep 2017 11:43:06 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170913.114306.67844218.horiguchi.kyot...@lab.ntt.co.jp> horiguchi.kyotaro> At Thu, 07 Sep 2017 21:59:56 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170907.215956.110216588.horiguchi.kyot...@lab.ntt.co.jp> > > Hello, > > > > At Thu, 07 Sep 2017 14:12:12 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI > > wrote in > > <20170907.141212.227032666.horiguchi.kyot...@lab.ntt.co.jp> > > > > I would like a flag in pg_replication_slots, and possibly also a > > > > numerical column that indicates how far away from the critical point > > > > each slot is. That would be great for a monitoring system. > > > > > > Great! I'll do that right now. > > > > Done. > > The CF status of this patch turned into "Waiting on Author". > This is because the second patch is posted separately from the > first patch. I repost them together after rebasing to the current > master. Hmm. I was unconsciously careless of regression test since it is in a tentative shape. This must pass the regression.. regards, -- Kyotaro Horiguchi NTT Open Source Software Center *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 105,110 int wal_level = WAL_LEVEL_MINIMAL; --- 105,111 int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; + int max_slot_wal_keep_size_mb = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; *** *** 9365,9373 KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) --- 9366,9397 if (max_replication_slots > 0 && keep != InvalidXLogRecPtr) { XLogSegNo slotSegNo; + int slotlimitsegs = ConvertToXSegs(max_slot_wal_keep_size_mb); XLByteToSeg(keep, slotSegNo); + /* + * ignore slots if too many wal segments are kept. + * max_slot_wal_keep_size is just accumulated on wal_keep_segments. + */ + if (max_slot_wal_keep_size_mb > 0 && slotSegNo + slotlimitsegs < segno) + { + segno = segno - slotlimitsegs; /* must be positive */ + + /* + * warn only if the checkpoint flushes the required segment. + * we assume here that *logSegNo is calculated keep location. + */ + if (slotSegNo < *logSegNo) + ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots have lost required WAL segnents to continue by up to %ld segments.", + (segno < *logSegNo ? segno : *logSegNo) - slotSegNo))); + + /* emergency vent */ + slotSegNo = segno; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *** *** 2371,2376 static struct config_int ConfigureNamesInt[] = --- 2371,2387 }, { + {"max_slot_wal_keep_size", PGC_SIGHUP, REPLICATION_SENDING, + gettext_noop("Sets the maximum size of extra WALs kept by replication slots."), + NULL, + GUC_UNIT_MB + }, + &max_slot_wal_keep_size_mb, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"wal_sender_timeout", PGC_SIGHUP, REPLICATION_SENDING, gettext_noop("Sets the maximum time to wait for WAL replication."), NULL, *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *** *** 235,240 --- 235,241 #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables + #max_slot_wal_keep_size = 0 # measured in bytes; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots *** a/src/include/access/xlog.h --- b/src/include/access/xlog.h *** *** 97,102 extern bool reachedConsistency; --- 97,103 extern int min_wal_size_mb; extern int max_wal_size_mb; extern int wal_keep_segments; + extern int max_slot_wal_keep_size_mb; extern int XLOGbuffers; extern int XLogArchiveTimeout; extern int wal_retrieve_retry_interval; *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 9336,9341 CreateRestartPoint(int flags) --- 9336,9420 } /* + * Check if the record on the given lsn will be preserved at the next + * checkpoint. + * + * Returns true if it will be preserved. If distance is given, the distance + * from origin to the beginning of the first segment kept at the next + * checkpoint. It means margin when this function returns true and gap of lost + * records when false. + * + * This function should return the consistent result with KeepLogSeg. + */ + bool + GetMarginToSlotSegmentLimit(XLogRecPtr restartLSN, uint64 *distance) + { + XLogRecPtr currpos; + XLogRecPtr tailpos;
Re: [HACKERS] Restricting maximum keep segments by repslots
At Thu, 07 Sep 2017 21:59:56 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170907.215956.110216588.horiguchi.kyot...@lab.ntt.co.jp> > Hello, > > At Thu, 07 Sep 2017 14:12:12 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI > wrote in > <20170907.141212.227032666.horiguchi.kyot...@lab.ntt.co.jp> > > > I would like a flag in pg_replication_slots, and possibly also a > > > numerical column that indicates how far away from the critical point > > > each slot is. That would be great for a monitoring system. > > > > Great! I'll do that right now. > > Done. The CF status of this patch turned into "Waiting on Author". This is because the second patch is posted separately from the first patch. I repost them together after rebasing to the current master. regards, -- Kyotaro Horiguchi NTT Open Source Software Center *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 105,110 int wal_level = WAL_LEVEL_MINIMAL; --- 105,111 int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; + int max_slot_wal_keep_size_mb = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; *** *** 9365,9373 KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) --- 9366,9397 if (max_replication_slots > 0 && keep != InvalidXLogRecPtr) { XLogSegNo slotSegNo; + int slotlimitsegs = ConvertToXSegs(max_slot_wal_keep_size_mb); XLByteToSeg(keep, slotSegNo); + /* + * ignore slots if too many wal segments are kept. + * max_slot_wal_keep_size is just accumulated on wal_keep_segments. + */ + if (max_slot_wal_keep_size_mb > 0 && slotSegNo + slotlimitsegs < segno) + { + segno = segno - slotlimitsegs; /* must be positive */ + + /* + * warn only if the checkpoint flushes the required segment. + * we assume here that *logSegNo is calculated keep location. + */ + if (slotSegNo < *logSegNo) + ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots have lost required WAL segnents to continue by up to %ld segments.", + (segno < *logSegNo ? segno : *logSegNo) - slotSegNo))); + + /* emergency vent */ + slotSegNo = segno; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *** *** 2371,2376 static struct config_int ConfigureNamesInt[] = --- 2371,2387 }, { + {"max_slot_wal_keep_size", PGC_SIGHUP, REPLICATION_SENDING, + gettext_noop("Sets the maximum size of extra WALs kept by replication slots."), + NULL, + GUC_UNIT_MB + }, + &max_slot_wal_keep_size_mb, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"wal_sender_timeout", PGC_SIGHUP, REPLICATION_SENDING, gettext_noop("Sets the maximum time to wait for WAL replication."), NULL, *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *** *** 235,240 --- 235,241 #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables + #max_slot_wal_keep_size = 0 # measured in bytes; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots *** a/src/include/access/xlog.h --- b/src/include/access/xlog.h *** *** 97,102 extern bool reachedConsistency; --- 97,103 extern int min_wal_size_mb; extern int max_wal_size_mb; extern int wal_keep_segments; + extern int max_slot_wal_keep_size_mb; extern int XLOGbuffers; extern int XLogArchiveTimeout; extern int wal_retrieve_retry_interval; *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 9336,9341 CreateRestartPoint(int flags) --- 9336,9420 } /* + * Check if the record on the given lsn will be preserved at the next + * checkpoint. + * + * Returns true if it will be preserved. If distance is given, the distance + * from origin to the beginning of the first segment kept at the next + * checkpoint. It means margin when this function returns true and gap of lost + * records when false. + * + * This function should return the consistent result with KeepLogSeg. + */ + bool + GetMarginToSlotSegmentLimit(XLogRecPtr restartLSN, uint64 *distance) + { + XLogRecPtr currpos; + XLogRecPtr tailpos; + uint64 currSeg; + uint64 restByteInSeg; + uint64 restartSeg; + uint64 tailSeg; + uint64 keepSegs; + + currpos = GetXLogWriteRecPtr(); + + LWLockAcquire(ControlFileLock, LW_SHARED); + tailpos = ControlFile->checkPointCopy.redo; + LWLockRelease(ControlFileLock); + + /* Move the pointer to the beginning of
Re: [HACKERS] Restricting maximum keep segments by repslots
Hello, At Thu, 07 Sep 2017 14:12:12 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170907.141212.227032666.horiguchi.kyot...@lab.ntt.co.jp> > > I would like a flag in pg_replication_slots, and possibly also a > > numerical column that indicates how far away from the critical point > > each slot is. That would be great for a monitoring system. > > Great! I'll do that right now. Done. In the attached patch on top of the previous patch, I added two columns in pg_replication_slots, "live" and "distance". The first indicates the slot will "live" after the next checkpoint. The second shows the how many bytes checkpoint lsn can advance before the slot will "die", or how many bytes the slot have lost after "death". Setting wal_keep_segments = 1 and max_slot_wal_keep_size = 16MB. =# select slot_name, restart_lsn, pg_current_wal_lsn(), live, distance from pg_replication_slots; slot_name | restart_lsn | pg_current_wal_lsn | live | distance ---+-++--+--- s1| 0/162D388 | 0/162D3C0 | t| 0/29D2CE8 This shows that checkpoint can advance 0x29d2ce8 bytes before the slot will die even if the connection stalls. s1| 0/4001180 | 0/6FFF2B8 | t| 0/DB8 Just before the slot loses sync. s1| 0/4001180 | 0/70008A8 | f| 0/FFEE80 The checkpoint after this removes some required segments. 2017-09-07 19:04:07.677 JST [13720] WARNING: restart LSN of replication slots is ignored by checkpoint 2017-09-07 19:04:07.677 JST [13720] DETAIL: Some replication slots have lost required WAL segnents to continue by up to 1 segments. If max_slot_wal_keep_size if not set (0), live is always true and distance is NULL. slot_name | restart_lsn | pg_current_wal_lsn | live | distance ---+-++--+--- s1| 0/4001180 | 0/73117A8 | t| - The name (or its content) of the new columns should be arguable. - pg_replication_slots view takes LWLock on ControlFile and spinlock on XLogCtl for every slot. But seems difficult to reduce it.. - distance seems mitakenly becomes 0/0 for certain condition.. - The result seems almost right but more precise check needed. (Anyway it cannot be perfectly exact.); regards, -- Kyotaro Horiguchi NTT Open Source Software Center >From ac47e250cf88b1279556e27c33e9f29806fdc04d Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Thu, 7 Sep 2017 19:13:22 +0900 Subject: [PATCH 2/2] Add monitoring aid for max_replication_slots. Adds two columns "live" and "distance" in pg_replication_slot. Setting max_slot_wal_keep_size, long-disconnected slots may lose sync. The two columns shows how long a slot can live on or how many bytes a slot have lost if max_slot_wal_keep_size is set. --- src/backend/access/transam/xlog.c| 80 src/backend/catalog/system_views.sql | 4 +- src/backend/replication/slotfuncs.c | 16 +++- src/include/access/xlog.h| 1 + src/include/catalog/pg_proc.h| 2 +- 5 files changed, 100 insertions(+), 3 deletions(-) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index ae70d7d..c4c8307 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -9324,6 +9324,86 @@ CreateRestartPoint(int flags) } /* + * Check if the record on the given lsn will be preserved at the next + * checkpoint. + * + * Returns true if it will be preserved. If distance is given, the distance + * from origin to the beginning of the first segment kept at the next + * checkpoint. It means margin when this function returns true and gap of lost + * records when false. + * + * This function should return the consistent result with KeepLogSeg. + */ +bool +GetMarginToSlotSegmentLimit(XLogRecPtr restartLSN, uint64 *distance) +{ + XLogRecPtr currpos; + XLogRecPtr tailpos; + uint64 currSeg; + uint64 restByteInSeg; + uint64 restartSeg; + uint64 tailSeg; + uint64 keepSegs; + + currpos = GetXLogWriteRecPtr(); + + LWLockAcquire(ControlFileLock, LW_SHARED); + tailpos = ControlFile->checkPointCopy.redo; + LWLockRelease(ControlFileLock); + + /* Move the pointer to the beginning of the segment*/ + XLByteToSeg(currpos, currSeg); + XLByteToSeg(restartLSN, restartSeg); + XLByteToSeg(tailpos, tailSeg); + restByteInSeg = 0; + + Assert(wal_keep_segments >= 0); + Assert(max_slot_wal_keep_size_mb >= 0); + + /* + * WAL are removed by the unit of segment. + */ + keepSegs = wal_keep_segments + ConvertToXSegs(max_slot_wal_keep_size_mb); + + /* + * If the latest checkpoint's redo point is older than the current head + * minus keep segments, the next checkpoint keeps the redo point's + * segment. Elsewise use current head minus number of segments to keep. + */ + if (currSeg < tailSeg + keepSegs) + { + if (currSeg < keepSegs) + tailSeg = 0; + else + tailSeg = currSeg - keepSegs; + + /* In this
Re: [HACKERS] Restricting maximum keep segments by repslots
Hello, At Fri, 1 Sep 2017 23:49:21 -0400, Peter Eisentraut wrote in <751e09c4-93e0-de57-edd2-e64c4950f...@2ndquadrant.com> > I'm still concerned about how the critical situation is handled. Your > patch just prints a warning to the log and then goes on -- doing what? > > The warning rolls off the log, and then you have no idea what happened, > or how to recover. The victims should be complaining in their log files, but, yes, I must admit that it's extremely resembles /dev/null. And the catastrophe comes suddenly. > I would like a flag in pg_replication_slots, and possibly also a > numerical column that indicates how far away from the critical point > each slot is. That would be great for a monitoring system. Great! I'll do that right now. > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > Thanks. -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
I'm still concerned about how the critical situation is handled. Your patch just prints a warning to the log and then goes on -- doing what? The warning rolls off the log, and then you have no idea what happened, or how to recover. I would like a flag in pg_replication_slots, and possibly also a numerical column that indicates how far away from the critical point each slot is. That would be great for a monitoring system. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
Hello, I'll add this to CF2017-09. At Mon, 06 Mar 2017 18:20:06 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20170306.182006.172683338.horiguchi.kyot...@lab.ntt.co.jp> > Thank you for the comment. > > At Fri, 3 Mar 2017 14:47:20 -0500, Peter Eisentraut > wrote in > > > On 3/1/17 19:54, Kyotaro HORIGUCHI wrote: > > >> Please measure it in size, not in number of segments. > > > It was difficult to dicide which is reaaonable but I named it > > > after wal_keep_segments because it has the similar effect. > > > > > > In bytes(or LSN) > > > max_wal_size > > > min_wal_size > > > wal_write_flush_after > > > > > > In segments > > > wal_keep_segments > > > > We have been moving away from measuring in segments. For example, > > checkpoint_segments was replaced by max_wal_size. > > > > Also, with the proposed patch that allows changing the segment size more > > easily, this will become more important. (I wonder if that will require > > wal_keep_segments to change somehow.) > > Agreed. It is 'max_slot_wal_keep_size' in the new version. > > wal_keep_segments might should be removed someday. - Following to min/max_wal_size, the variable was renamed to "max_slot_wal_keep_size_mb" and used as ConvertToXSegs(x)" - Stopped warning when checkpoint doesn't flush segments required by slots even if max_slot_wal_keep_size have worked. - Avoided subraction that may be negative. regards, *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 105,110 int wal_level = WAL_LEVEL_MINIMAL; --- 105,111 int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; + int max_slot_wal_keep_size_mb = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; *** *** 9353,9361 KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) --- 9354,9385 if (max_replication_slots > 0 && keep != InvalidXLogRecPtr) { XLogSegNo slotSegNo; + int slotlimitsegs = ConvertToXSegs(max_slot_wal_keep_size_mb); XLByteToSeg(keep, slotSegNo); + /* + * ignore slots if too many wal segments are kept. + * max_slot_wal_keep_size is just accumulated on wal_keep_segments. + */ + if (max_slot_wal_keep_size_mb > 0 && slotSegNo + slotlimitsegs < segno) + { + segno = segno - slotlimitsegs; /* must be positive */ + + /* + * warn only if the checkpoint flushes the required segment. + * we assume here that *logSegNo is calculated keep location. + */ + if (slotSegNo < *logSegNo) + ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots have lost required WAL segnents to continue by up to %ld segments.", + (segno < *logSegNo ? segno : *logSegNo) - slotSegNo))); + + /* emergency vent */ + slotSegNo = segno; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *** *** 2366,2371 static struct config_int ConfigureNamesInt[] = --- 2366,2382 }, { + {"max_slot_wal_keep_size", PGC_SIGHUP, REPLICATION_SENDING, + gettext_noop("Sets the maximum size of extra WALs kept by replication slots."), + NULL, + GUC_UNIT_MB + }, + &max_slot_wal_keep_size_mb, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"wal_sender_timeout", PGC_SIGHUP, REPLICATION_SENDING, gettext_noop("Sets the maximum time to wait for WAL replication."), NULL, *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *** *** 235,240 --- 235,241 #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables + #max_slot_wal_keep_size = 0 # measured in bytes; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots *** a/src/include/access/xlog.h --- b/src/include/access/xlog.h *** *** 97,102 extern bool reachedConsistency; --- 97,103 extern int min_wal_size_mb; extern int max_wal_size_mb; extern int wal_keep_segments; + extern int max_slot_wal_keep_size_mb; extern int XLOGbuffers; extern int XLogArchiveTimeout; extern int wal_retrieve_retry_interval; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 28 February 2017 at 12:27, Petr Jelinek wrote: >> This patch adds a GUC to put a limit to the number of segments >> that replication slots can keep. Hitting the limit during >> checkpoint shows a warining and the segments older than the limit >> are removed. >> >>> WARNING: restart LSN of replication slots is ignored by checkpoint >>> DETAIL: Some replication slots lose required WAL segnents to continue. >> > > However this is dangerous as logical replication slot does not consider > it error when too old LSN is requested so we'd continue replication, > hiding data loss. That skipping only happens if you request a startpoint older than confirmed_flush_lsn . It doesn't apply to this situation. The client cannot control where we start decoding, it's always restart_lsn, and if we can't find a needed WAL segment we'll ERROR. So this is safe, though the error will be something about being unable to find a wal segment that users might not directly associate with having set this option. It won't say "slot disabled because needed WAL has been discarded due to [setting]" or anything. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
Thank you for the comment. At Fri, 3 Mar 2017 14:47:20 -0500, Peter Eisentraut wrote in > On 3/1/17 19:54, Kyotaro HORIGUCHI wrote: > >> Please measure it in size, not in number of segments. > > It was difficult to dicide which is reaaonable but I named it > > after wal_keep_segments because it has the similar effect. > > > > In bytes(or LSN) > > max_wal_size > > min_wal_size > > wal_write_flush_after > > > > In segments > > wal_keep_segments > > We have been moving away from measuring in segments. For example, > checkpoint_segments was replaced by max_wal_size. > > Also, with the proposed patch that allows changing the segment size more > easily, this will become more important. (I wonder if that will require > wal_keep_segments to change somehow.) Agreed. It is 'max_slot_wal_keep_size' in the new version. wal_keep_segments might should be removed someday. regards, -- Kyotaro Horiguchi NTT Open Source Software Center >From a646db76ac71ba1bff1105d55b8042fb451022bf Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 28 Feb 2017 11:39:48 +0900 Subject: [PATCH] Add WAL releaf vent for replication slots Adds a capability to limit the number of segments kept by replication slots by a GUC variable. --- src/backend/access/transam/xlog.c | 12 src/backend/utils/misc/guc.c | 11 +++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/xlog.h | 1 + 4 files changed, 25 insertions(+) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 8973583..cb23fbc 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -104,6 +104,7 @@ int wal_level = WAL_LEVEL_MINIMAL; int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; +int max_slot_wal_keep_size = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; @@ -9267,6 +9268,17 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) XLByteToSeg(keep, slotSegNo); + /* emergency vent */ + if (max_slot_wal_keep_size > 0 && + segno - slotSegNo > max_slot_wal_keep_size) + { + ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots lose required WAL segnents to continue."))); + /* slotSegNo cannot be negative here */ + slotSegNo = segno - max_slot_wal_keep_size; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 0707f66..20fe29a 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2335,6 +2335,17 @@ static struct config_int ConfigureNamesInt[] = }, { + {"max_slot_wal_keep_size", PGC_SIGHUP, REPLICATION_SENDING, + gettext_noop("Sets the maximum size of extra WALs kept by replication slots."), + NULL, + GUC_UNIT_XSEGS + }, + &max_slot_wal_keep_size, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"wal_sender_timeout", PGC_SIGHUP, REPLICATION_SENDING, gettext_noop("Sets the maximum time to wait for WAL replication."), NULL, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 157d775..93e2f13 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -233,6 +233,7 @@ #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables +#max_slot_wal_keep_size = 0 # measured in bytes; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index 9f036c7..93ee819 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -97,6 +97,7 @@ extern bool reachedConsistency; extern int min_wal_size; extern int max_wal_size; extern int wal_keep_segments; +extern int max_slot_wal_keep_size; extern int XLOGbuffers; extern int XLogArchiveTimeout; extern int wal_retrieve_retry_interval; -- 2.9.2 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 3/1/17 19:54, Kyotaro HORIGUCHI wrote: >> Please measure it in size, not in number of segments. > It was difficult to dicide which is reaaonable but I named it > after wal_keep_segments because it has the similar effect. > > In bytes(or LSN) > max_wal_size > min_wal_size > wal_write_flush_after > > In segments > wal_keep_segments We have been moving away from measuring in segments. For example, checkpoint_segments was replaced by max_wal_size. Also, with the proposed patch that allows changing the segment size more easily, this will become more important. (I wonder if that will require wal_keep_segments to change somehow.) -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
At Wed, 1 Mar 2017 12:18:07 -0500, Peter Eisentraut wrote in <98538b00-42ae-6a6b-f852-50b3c937a...@2ndquadrant.com> > On 2/27/17 22:27, Kyotaro HORIGUCHI wrote: > > This patch adds a GUC to put a limit to the number of segments > > that replication slots can keep. > > Please measure it in size, not in number of segments. It was difficult to dicide which is reaaonable but I named it after wal_keep_segments because it has the similar effect. In bytes(or LSN) max_wal_size min_wal_size wal_write_flush_after In segments wal_keep_segments But surely max_slot_wal_keep_segments works to keep disk space so bytes would be reasonable. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
At Wed, 1 Mar 2017 12:17:43 -0500, Peter Eisentraut wrote in > On 2/27/17 23:27, Petr Jelinek wrote: > >>> WARNING: restart LSN of replication slots is ignored by checkpoint > >>> DETAIL: Some replication slots lose required WAL segnents to continue. > > However this is dangerous as logical replication slot does not consider > > it error when too old LSN is requested so we'd continue replication, > > hiding data loss. > > In general, we would need a much more evident and strict way to discover > when this condition is hit. Like a "full" column in > pg_stat_replication_slot, and refusing connections to the slot until it > is cleared. Anyway, if preserving WAL to replicate has priority to the master's health, this doesn't nothing by leaving 'max_wal_keep_segments' to 0. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
At Wed, 1 Mar 2017 08:06:10 -0800, Andres Freund wrote in <20170301160610.wc7ez3vihmial...@alap3.anarazel.de> > On 2017-02-28 12:42:32 +0900, Michael Paquier wrote: > > Please no. Replication slots are designed the current way because we > > don't want to have to use something like wal_keep_segments as it is a > > wart, and this applies as well for replication slots in my opinion. > > I think a per-slot option to limit the amount of retention would make > sense. I started from that but I found that all slots refer to the same location as the origin of the distance, that is, the last segment number that KeepLogSeg returns. As the result the whole logic became as the following. This is one reason of the proposed pach. - Take the maximum value of the maximum-retain-LSN-amount per slot. - Apply the maximum value during the calcuation in KeepLogSeg. - (These steps runs only when at least one slot exists) The another reason was, as Robert retold, I thought that this is a matter of system (or a DB cluster) wide health and works in a bit different way from what the name "max_wal_size_hard" suggests. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 2/27/17 23:27, Petr Jelinek wrote: >>> WARNING: restart LSN of replication slots is ignored by checkpoint >>> DETAIL: Some replication slots lose required WAL segnents to continue. > However this is dangerous as logical replication slot does not consider > it error when too old LSN is requested so we'd continue replication, > hiding data loss. In general, we would need a much more evident and strict way to discover when this condition is hit. Like a "full" column in pg_stat_replication_slot, and refusing connections to the slot until it is cleared. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 2/27/17 22:27, Kyotaro HORIGUCHI wrote: > This patch adds a GUC to put a limit to the number of segments > that replication slots can keep. Please measure it in size, not in number of segments. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
Hi, On 2017-02-28 12:42:32 +0900, Michael Paquier wrote: > Please no. Replication slots are designed the current way because we > don't want to have to use something like wal_keep_segments as it is a > wart, and this applies as well for replication slots in my opinion. I think a per-slot option to limit the amount of retention would make sense. - Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On Tue, Feb 28, 2017 at 10:04 AM, Michael Paquier wrote: > It would make more sense to just switch max_wal_size from a soft to a > hard limit. The current behavior is not cool with activity spikes. Having a hard limit on WAL size would be nice, but that's a different problem from the one being discussed here. If max_wal_size becomes a hard limit, and a standby with a replication slot dies, then the master eventually starts refusing all writes. I guess that's better than a PANIC, but it's not likely to make users very happy. I think it's entirely reasonable to want a behavior where the master is willing to retain up to X amount of extra WAL for the benefit of some standby, but after that the health of the master takes priority. You can't really get that behavior today. Either you can retain as much WAL as might be necessary through archiving or a slot, or you can retain a fixed amount of WAL whether it's actually needed or not. There's currently no approach that retains min(wal_needed, configured_value). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On Tue, Feb 28, 2017 at 1:16 PM, Kyotaro HORIGUCHI wrote: > It is doable without a plugin and currently we are planning to do > in the way (Maybe such plugin would be unacceptable..). Killing > walsender (which one?), removing the slot and if failed.. The PID and restart_lsn associated to each slot offer enough information for monitoring. > This is the 'steps rather complex' and fragile. The handling of slot drop is not complex. The insurance that WAL segments get recycled on time and avoid a full bloat is though. >> That's as well more flexible than having a parameter >> that basically is just a synonym of max_wal_size. > > I thought the same thing first, max_wal_size_hard, that limits > the wal size including extra (other than them for the two > checkpoig cycles) segments. It would make more sense to just switch max_wal_size from a soft to a hard limit. The current behavior is not cool with activity spikes. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On 28/02/17 04:27, Kyotaro HORIGUCHI wrote: > Hello. > > Although replication slot is helpful to avoid unwanted WAL > deletion, on the other hand it can cause a disastrous situation > by keeping WAL segments without a limit. Removing the causal > repslot will save this situation but it is not doable if the > standby is active. We should do a rather complex and forcible > steps to relieve the situation especially in an automatic > manner. (As for me, specifically in an HA cluster.) > I agree that that it should be possible to limit how much WAL slot keeps. > This patch adds a GUC to put a limit to the number of segments > that replication slots can keep. Hitting the limit during > checkpoint shows a warining and the segments older than the limit > are removed. > >> WARNING: restart LSN of replication slots is ignored by checkpoint >> DETAIL: Some replication slots lose required WAL segnents to continue. > However this is dangerous as logical replication slot does not consider it error when too old LSN is requested so we'd continue replication, hiding data loss. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
Thank you for the opinion. At Tue, 28 Feb 2017 12:42:32 +0900, Michael Paquier wrote in > Please no. Replication slots are designed the current way because we > don't want to have to use something like wal_keep_segments as it is a > wart, and this applies as well for replication slots in my opinion. If > a slot is bloating WAL and you care about your Postgres instance, I > would recommend instead that you use a background worker that does > monitoring of the situation based on max_wal_size for example, killing > the WAL sender associated to the slot if there is something connected > but it is frozen or it cannot keep up the pace of WAL generation, and > then dropping the slot. It is doable without a plugin and currently we are planning to do in the way (Maybe such plugin would be unacceptable..). Killing walsender (which one?), removing the slot and if failed.. This is the 'steps rather complex' and fragile. > You may want to issue a checkpoint in this > case as well to ensure that segments get recycled. But anyway, if you > reach this point of WAL bloat, perhaps that's for the best as users > would know about it because backups would get in danger. Yes, but at the end it is better than that a server just stops with a PANIC. > For some applications, that is acceptable, but you could always > rely on monitoring slots and kill them on sight if > needed. Another solution would be that removing a slot kills corresponding walsender. What do you think about this? pg_drop_replication_slot(name, *force*) force = true kills the walsender runs on the slot. > That's as well more flexible than having a parameter > that basically is just a synonym of max_wal_size. I thought the same thing first, max_wal_size_hard, that limits the wal size including extra (other than them for the two checkpoig cycles) segments. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restricting maximum keep segments by repslots
On Tue, Feb 28, 2017 at 12:27 PM, Kyotaro HORIGUCHI wrote: > Although replication slot is helpful to avoid unwanted WAL > deletion, on the other hand it can cause a disastrous situation > by keeping WAL segments without a limit. Removing the causal > repslot will save this situation but it is not doable if the > standby is active. We should do a rather complex and forcible > steps to relieve the situation especially in an automatic > manner. (As for me, specifically in an HA cluster.) > > This patch adds a GUC to put a limit to the number of segments > that replication slots can keep. Hitting the limit during > checkpoint shows a warining and the segments older than the limit > are removed. > >> WARNING: restart LSN of replication slots is ignored by checkpoint >> DETAIL: Some replication slots lose required WAL segnents to continue. > > Another measure would be automatic deletion or inactivation of > the culprit slot but it seems too complex for the problem. > > > As we have already postponed some patches by the triage for the > last commit fest, this might should be postponed to PG11. Please no. Replication slots are designed the current way because we don't want to have to use something like wal_keep_segments as it is a wart, and this applies as well for replication slots in my opinion. If a slot is bloating WAL and you care about your Postgres instance, I would recommend instead that you use a background worker that does monitoring of the situation based on max_wal_size for example, killing the WAL sender associated to the slot if there is something connected but it is frozen or it cannot keep up the pace of WAL generation, and then dropping the slot. You may want to issue a checkpoint in this case as well to ensure that segments get recycled. But anyway, if you reach this point of WAL bloat, perhaps that's for the best as users would know about it because backups would get in danger. For some applications, that is acceptable, but you could always rely on monitoring slots and kill them on sight if needed. That's as well more flexible than having a parameter that basically is just a synonym of max_wal_size. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Restricting maximum keep segments by repslots
Hello. Although replication slot is helpful to avoid unwanted WAL deletion, on the other hand it can cause a disastrous situation by keeping WAL segments without a limit. Removing the causal repslot will save this situation but it is not doable if the standby is active. We should do a rather complex and forcible steps to relieve the situation especially in an automatic manner. (As for me, specifically in an HA cluster.) This patch adds a GUC to put a limit to the number of segments that replication slots can keep. Hitting the limit during checkpoint shows a warining and the segments older than the limit are removed. > WARNING: restart LSN of replication slots is ignored by checkpoint > DETAIL: Some replication slots lose required WAL segnents to continue. Another measure would be automatic deletion or inactivation of the culprit slot but it seems too complex for the problem. As we have already postponed some patches by the triage for the last commit fest, this might should be postponed to PG11. regards, -- Kyotaro Horiguchi NTT Open Source Software Center >From 367205d51ef471defd0f9df76840dee1c4cd4036 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 28 Feb 2017 11:39:48 +0900 Subject: [PATCH] Add WAL releaf vent for replication slots Adds a capability to limit the number of segments kept by replication slots by a GUC variable. --- src/backend/access/transam/xlog.c | 12 src/backend/utils/misc/guc.c | 10 ++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/xlog.h | 1 + 4 files changed, 24 insertions(+) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 5016273..6c57e99 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -104,6 +104,7 @@ int wal_level = WAL_LEVEL_MINIMAL; int CommitDelay = 0; /* precommit delay in microseconds */ int CommitSiblings = 5; /* # concurrent xacts needed to sleep */ int wal_retrieve_retry_interval = 5000; +int max_slot_wal_keep_segments = 0; #ifdef WAL_DEBUG bool XLOG_DEBUG = false; @@ -9267,6 +9268,17 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo) XLByteToSeg(keep, slotSegNo); + /* emergency vent */ + if (max_slot_wal_keep_segments > 0 && + slotSegNo < segno - max_slot_wal_keep_segments) + { + ereport(WARNING, + (errmsg ("restart LSN of replication slots is ignored by checkpoint"), + errdetail("Some replication slots lose required WAL segnents to continue."))); + /* slotSegNo cannot be negative here */ + slotSegNo = segno - max_slot_wal_keep_segments; + } + if (slotSegNo <= 0) segno = 1; else if (slotSegNo < segno) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 0707f66..4ff1c2a 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2335,6 +2335,16 @@ static struct config_int ConfigureNamesInt[] = }, { + {"max_slot_wal_keep_segments", PGC_SIGHUP, REPLICATION_SENDING, + gettext_noop("Sets the maximum keep segments by replication slots."), + NULL + }, + &max_slot_wal_keep_segments, + 0, 0, INT_MAX, + NULL, NULL, NULL + }, + + { {"wal_sender_timeout", PGC_SIGHUP, REPLICATION_SENDING, gettext_noop("Sets the maximum time to wait for WAL replication."), NULL, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 157d775..7424a63 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -233,6 +233,7 @@ #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables +#max_slot_wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index 9f036c7..fae1b87 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -97,6 +97,7 @@ extern bool reachedConsistency; extern int min_wal_size; extern int max_wal_size; extern int wal_keep_segments; +extern int max_slot_wal_keep_segments; extern int XLOGbuffers; extern int XLogArchiveTimeout; extern int wal_retrieve_retry_interval; -- 2.9.2 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers