Re: [GENERAL] NOTIFY command impact
Hi ! Indeed :) Thanks for taking the time to explain ! Benoit.
Re: [GENERAL] NOTIFY command impact
Hi Benoit, Your SQL works fine as long as there are never any communication problems between the master and slave. But if your slave loses communication with the master, then the SQL you provided will not report any lag despite the potential that the slave could be very far behind the master. This is because that SQL is asking the slave how far it is behind the master based on logs it has received from the master. However if the slave has not received logs from the master it cannot tell if the master has simply gone idle or if it has lost communication and is unable to send updates. By introducing a mechanism to manually force the replication stream to be sent periodically, which is what I use the NOTIFY command to do on the master, I can ensure that the slave is updating pg_last_xact_replay_timestamp at least as often as the NOTIFY is executed on the master (say every 30 seconds) and is successfully replayed on the slave. This enables an accurate measurement of replication lag whatever the circumstances. If the master has simply gone idle because there is no update activity, the NOTIFY command will still force the replication stream to replay on the slave every 30 seconds and the replay timestamp will be updated. But if communication to the master is lost, for whatever reason, the replay timestamp will not be updated on the slave and then lag can be accurately measured and alerted. I hope this helps! --Rob From: Benoit Lobréau Date: Thursday, February 23, 2017 at 9:23 AM To: "David G. Johnston" Cc: Rob Brucks , Tom Lane , François Beaulieu , Adrian Klaver , "pgsql-general@postgresql.org" Subject: Re: [GENERAL] NOTIFY command impact Hi, I might have missed something. This should be enough to solve the problem no ? SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER END AS replication_lag; Benoit.
Re: [GENERAL] NOTIFY command impact
Hi, I might have missed something. This should be enough to solve the problem no ? SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER END AS replication_lag; Benoit.
Re: [GENERAL] NOTIFY command impact
On Wed, Feb 22, 2017 at 3:05 PM, Rob Brucks wrote: > So, long-term, you don't see any negative impacts to the master cluster? > > I just don't want to implement this as a streaming "push" mechanism and > then have my cluster crash in 12 months because it hit some obscure problem > with notifications. > The only concern related to "xid/transactions" that I can think of is: https://www.postgresql.org/docs/current/static/routine-vacuuming.html "To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions." Probably not appreciably but you will hit the 2B mark more quickly if you are burning off transaction ids in this manner. David J.
Re: [GENERAL] NOTIFY command impact
Thank you Tom. So, long-term, you don't see any negative impacts to the master cluster? I just don't want to implement this as a streaming "push" mechanism and then have my cluster crash in 12 months because it hit some obscure problem with notifications. This turned out to be a really simple/elegant way to force WAL stream movement without having to modify the database in any way, since I am heavily restricting the permissions granted to my monitoring role. And, on a side note, it would be EXTREMELY useful to include a default monitoring role in PG that has permission to query all columns of all pg_stat* views on any DB, yet has no ability to modify *anything* (not even create any objects of its own). I won't grant super-user to my monitoring role since it is administered remotely, it is too much of a security risk. Thanks! Rob On 2/22/17, 3:19 PM, "Tom Lane" wrote: =?utf-8?Q?Fran=C3=A7ois_Beaulieu?= writes: >> On Feb 21, 2017, at 6:03 PM, Tom Lane wrote: >> (The reason it goes into the WAL stream is so that you can have listeners >> on replication slaves, not for recovery purposes.) > Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message: > "ERROR: cannot execute LISTEN during recovery" Hmm ... you know what, my remark above is full of it. NOTIFY traffic *doesn't* go into the WAL stream. I think I was remembering some discussions about how that would be a good idea so that you could put listeners on slaves; but that hasn't actually happened yet, as a look through async.c will show. After thinking a bit more, I believe what the OP is seeing is that NOTIFY does result in an XID being assigned to the transaction (so that the message it sticks into the pg_notify queue can be correctly labeled). That therefore results in a transaction commit message being sent to WAL, even though this transaction did nothing that would actually change any persistent database state. There are other ways of forcing XID assignment without doing much real work, but this one is probably about as cheap as any. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
=?utf-8?Q?Fran=C3=A7ois_Beaulieu?= writes: >> On Feb 21, 2017, at 6:03 PM, Tom Lane wrote: >> (The reason it goes into the WAL stream is so that you can have listeners >> on replication slaves, not for recovery purposes.) > Are we sure that replication slaves can have listeners? When I tried it on > 9.4.10, I got the following message: > "ERROR: cannot execute LISTEN during recovery" Hmm ... you know what, my remark above is full of it. NOTIFY traffic *doesn't* go into the WAL stream. I think I was remembering some discussions about how that would be a good idea so that you could put listeners on slaves; but that hasn't actually happened yet, as a look through async.c will show. After thinking a bit more, I believe what the OP is seeing is that NOTIFY does result in an XID being assigned to the transaction (so that the message it sticks into the pg_notify queue can be correctly labeled). That therefore results in a transaction commit message being sent to WAL, even though this transaction did nothing that would actually change any persistent database state. There are other ways of forcing XID assignment without doing much real work, but this one is probably about as cheap as any. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
> On Feb 21, 2017, at 6:03 PM, Tom Lane wrote: > > Rob Brucks writes: >> If a notify is sent and then PG is immediately shut down, wouldn't PG want >> to save that message for processing after startup? >> Or is the message just discarded? > > NOTIFY data is not saved across a shutdown or crash. > > (The reason it goes into the WAL stream is so that you can have listeners > on replication slaves, not for recovery purposes.) > >regards, tom lane > Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message: "ERROR: cannot execute LISTEN during recovery" -=Frank -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
Thanks Tom, Can you answer my original question too: If I am issuing NOTIFY commands every 30 seconds (and immediately committing) and there are no listeners, will that have a negative impact on the cluster? I'm using the NOTIFY to force streaming replication to update the "pg_last_xact_replay_timestamp" on the slaves so we can more accurately monitor replication lag on quiet systems. Thanks, Rob On 2/21/17, 5:03 PM, "Tom Lane" wrote: Rob Brucks writes: > If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup? > Or is the message just discarded? NOTIFY data is not saved across a shutdown or crash. (The reason it goes into the WAL stream is so that you can have listeners on replication slaves, not for recovery purposes.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
> On Feb 21, 2017, at 6:03 PM, Tom Lane wrote: > > Rob Brucks writes: >> If a notify is sent and then PG is immediately shut down, wouldn't PG want >> to save that message for processing after startup? >> Or is the message just discarded? > > NOTIFY data is not saved across a shutdown or crash. > > (The reason it goes into the WAL stream is so that you can have listeners > on replication slaves, not for recovery purposes.) > > regards, tom lane > Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message: "ERROR: cannot execute LISTEN during recovery" -=Frank -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
On Tue, Feb 21, 2017 at 3:47 PM, Rob Brucks wrote: > If a notify is sent and then PG is immediately shut down, wouldn't PG want > to save that message for processing after startup? > > > > Or is the message just discarded? > > > Adrian gave details but trying to figure out notification semantics across a system reboot seems like a headache for little benefit. Its likely assumed that upon first connection the client would eagerly load the relevant data - capturing the static state of the system at that time - and then only perform additional queries upon notification. David J.
Re: [GENERAL] NOTIFY command impact
Rob Brucks writes: > If a notify is sent and then PG is immediately shut down, wouldn't PG want to > save that message for processing after startup? > Or is the message just discarded? NOTIFY data is not saved across a shutdown or crash. (The reason it goes into the WAL stream is so that you can have listeners on replication slaves, not for recovery purposes.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
On 02/21/2017 02:43 PM, Rob Brucks wrote: That's a great thought! Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one 8k file named "". If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I wonder if that file is only used to store notify commands during shutdown/startup? From the source of async.c: During start or reboot, clean out the pg_notify directory. I would guess the file is like a WAL file it is a pre-initialized file filled with 0(?)'s Or if there are any considerations for memory usage… --Rob On 2/21/17, 4:38 PM, "Adrian Klaver" wrote: On 02/21/2017 02:19 PM, Rob Brucks wrote: > I did find a post a while back saying they were discarded, but I wanted > to double-check. > > > > I performed some tests to see if listens worked AFTER the notify was > issued, they were not. This leads me to believe that the messages are > discarded when a listen does not yet exist. Seems the thing to do would be to monitor the size of : $PG_DATA/pg_notify/ > > > > --Rob > > > > *From: *"David G. Johnston" > *Date: *Tuesday, February 21, 2017 at 3:38 PM > *To: *Adrian Klaver > *Cc: *Rob Brucks , > "pgsql-general@postgresql.org" > *Subject: *Re: [GENERAL] NOTIFY command impact > > > > On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver > mailto:adrian.kla...@aklaver.com>> wrote: > > On 02/21/2017 01:07 PM, Rob Brucks wrote: > > > Do you see any long-term problems with constantly issuing "NOTIFY" > commands every 30 seconds without an associated "LISTEN" command? > > > > Depending on how long 'long term' is: > > https://www.postgresql.org/docs/9.6/static/sql-notify.html > > "There is a queue that holds notifications that have been sent but > not yet processed by all listening sessions > > > > Its not clear in the OP that this is the case (it seems to be) but the > documentation is non-specific as to what happens when "# of listeners" = > 0; I suspect that said messages are created and then immediately > discarded - though apparently they do make it over to the standby server > too - and likely also immediately discarded there as well. > > > > David J. > > > -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup? Or is the message just discarded? --Rob From: "David G. Johnston" Date: Tuesday, February 21, 2017 at 4:45 PM To: Rob Brucks Cc: Adrian Klaver , "pgsql-general@postgresql.org" Subject: Re: [GENERAL] NOTIFY command impact On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks mailto:rob.bru...@rackspace.com>> wrote: But I wonder if that file is only used to store notify commands during shutdown/startup? huh? David J.
Re: [GENERAL] NOTIFY command impact
On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks wrote: > But I wonder if that file is only used to store notify commands during > shutdown/startup? > huh? David J.
Re: [GENERAL] NOTIFY command impact
That's a great thought! Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one 8k file named "". If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I wonder if that file is only used to store notify commands during shutdown/startup? Or if there are any considerations for memory usage… --Rob On 2/21/17, 4:38 PM, "Adrian Klaver" wrote: On 02/21/2017 02:19 PM, Rob Brucks wrote: > I did find a post a while back saying they were discarded, but I wanted > to double-check. > > > > I performed some tests to see if listens worked AFTER the notify was > issued, they were not. This leads me to believe that the messages are > discarded when a listen does not yet exist. Seems the thing to do would be to monitor the size of : $PG_DATA/pg_notify/ > > > > --Rob > > > > *From: *"David G. Johnston" > *Date: *Tuesday, February 21, 2017 at 3:38 PM > *To: *Adrian Klaver > *Cc: *Rob Brucks , > "pgsql-general@postgresql.org" > *Subject: *Re: [GENERAL] NOTIFY command impact > > > > On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver > mailto:adrian.kla...@aklaver.com>> wrote: > > On 02/21/2017 01:07 PM, Rob Brucks wrote: > > > Do you see any long-term problems with constantly issuing "NOTIFY" > commands every 30 seconds without an associated "LISTEN" command? > > > > Depending on how long 'long term' is: > > https://www.postgresql.org/docs/9.6/static/sql-notify.html > > "There is a queue that holds notifications that have been sent but > not yet processed by all listening sessions > > > > Its not clear in the OP that this is the case (it seems to be) but the > documentation is non-specific as to what happens when "# of listeners" = > 0; I suspect that said messages are created and then immediately > discarded - though apparently they do make it over to the standby server > too - and likely also immediately discarded there as well. > > > > David J. > > > -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
On 02/21/2017 02:19 PM, Rob Brucks wrote: I did find a post a while back saying they were discarded, but I wanted to double-check. I performed some tests to see if listens worked AFTER the notify was issued, they were not. This leads me to believe that the messages are discarded when a listen does not yet exist. Seems the thing to do would be to monitor the size of : $PG_DATA/pg_notify/ --Rob *From: *"David G. Johnston" *Date: *Tuesday, February 21, 2017 at 3:38 PM *To: *Adrian Klaver *Cc: *Rob Brucks , "pgsql-general@postgresql.org" *Subject: *Re: [GENERAL] NOTIFY command impact On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 02/21/2017 01:07 PM, Rob Brucks wrote: Do you see any long-term problems with constantly issuing "NOTIFY" commands every 30 seconds without an associated "LISTEN" command? Depending on how long 'long term' is: https://www.postgresql.org/docs/9.6/static/sql-notify.html "There is a queue that holds notifications that have been sent but not yet processed by all listening sessions Its not clear in the OP that this is the case (it seems to be) but the documentation is non-specific as to what happens when "# of listeners" = 0; I suspect that said messages are created and then immediately discarded - though apparently they do make it over to the standby server too - and likely also immediately discarded there as well. David J. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
I did find a post a while back saying they were discarded, but I wanted to double-check. I performed some tests to see if listens worked AFTER the notify was issued, they were not. This leads me to believe that the messages are discarded when a listen does not yet exist. --Rob From: "David G. Johnston" Date: Tuesday, February 21, 2017 at 3:38 PM To: Adrian Klaver Cc: Rob Brucks , "pgsql-general@postgresql.org" Subject: Re: [GENERAL] NOTIFY command impact On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 02/21/2017 01:07 PM, Rob Brucks wrote: Do you see any long-term problems with constantly issuing "NOTIFY" commands every 30 seconds without an associated "LISTEN" command? Depending on how long 'long term' is: https://www.postgresql.org/docs/9.6/static/sql-notify.html "There is a queue that holds notifications that have been sent but not yet processed by all listening sessions Its not clear in the OP that this is the case (it seems to be) but the documentation is non-specific as to what happens when "# of listeners" = 0; I suspect that said messages are created and then immediately discarded - though apparently they do make it over to the standby server too - and likely also immediately discarded there as well. David J.
Re: [GENERAL] NOTIFY command impact
In my case the monitoring user will be connecting, issuing the notify, then immediately disconnecting. And we don't have any systems using listen/notify. So I'm hoping there won't be a problem. That's why I'm asking ☺ --Rob On 2/21/17, 3:17 PM, "Adrian Klaver" wrote: On 02/21/2017 01:07 PM, Rob Brucks wrote: > Hi All, > > > > I just wanted to check with you guys to make sure that constantly > issuing "NOTIFY" commands without corresponding "LISTEN" commands will > not cause any harm or excessive work for the PostgreSQL cluster. Nothing > that would put my cluster at risk. > > > > The reason I ask is because I was trying to implement a reliable method > of monitoring replication lag for streaming replication on 9.2+ systems > using the following SQL on slaves: > > select extract(epoch from now() - pg_last_xact_replay_timestamp()); > > > > This SQL provides me with a time-based measure of replication lag > instead of a byte-based measure. Time-based lag measurement is more > meaningful for us in time-sensitive applications. > > > > During my testing I noticed that if the database went "quiet" (no update > activity on the master) for a period of time, then the last replay > timestamp remained unchanged. Having little or no update activity > after-hours is very common on our smaller systems. > > > > This made the monitoring of replication lag inconsistent because, > despite the slave being "caught up" with the master, it was reporting an > increasing time lag. And I didn't want our DBAs to get false alerts > from our monitoring. > > > > So I went on the hunt for a method of forcing replay to occur without > actually performing any database updates. I also did not want to grant > any kind of update capability on the database to my monitoring role, for > tighter security. > > > > I discovered that the monitoring role, despite not having any update > permissions, could successfully issue a "NOTIFY" command to a bogus > channel and that this command actually forced the log to replay on the > slave, updating the replay timestamp. This seems like a viable solution > to my problem. > > > > My plan is to have the monitoring role issue a "NOTIFY" every 30 seconds > to ensure the timestamp is updated at least that frequently. But there > will not be an associated "LISTEN" for these notifications. > > > > However, I don't want to cause any problems for the PostgreSQL cluster > itself by having messages inserted with nobody to listen for them, which > is why I'm posting here. > > > > Do you see any long-term problems with constantly issuing "NOTIFY" > commands every 30 seconds without an associated "LISTEN" command? Depending on how long 'long term' is: https://www.postgresql.org/docs/9.6/static/sql-notify.html "There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. However, no cleanup can take place if a session executes LISTEN and then enters a transaction for a very long time. Once the queue is half full you will see warnings in the log file pointing you to the session that is preventing cleanup. In this case you should make sure that this session ends its current transaction so that cleanup can proceed." > > > > Thank you, > > Rob Brucks > -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY command impact
On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver wrote: > On 02/21/2017 01:07 PM, Rob Brucks wrote: > >> >> Do you see any long-term problems with constantly issuing "NOTIFY" >> commands every 30 seconds without an associated "LISTEN" command? >> > > Depending on how long 'long term' is: > > https://www.postgresql.org/docs/9.6/static/sql-notify.html > > "There is a queue that holds notifications that have been sent but not yet > processed by all listening sessions Its not clear in the OP that this is the case (it seems to be) but the documentation is non-specific as to what happens when "# of listeners" = 0; I suspect that said messages are created and then immediately discarded - though apparently they do make it over to the standby server too - and likely also immediately discarded there as well. David J.
Re: [GENERAL] NOTIFY command impact
On 02/21/2017 01:07 PM, Rob Brucks wrote: Hi All, I just wanted to check with you guys to make sure that constantly issuing "NOTIFY" commands without corresponding "LISTEN" commands will not cause any harm or excessive work for the PostgreSQL cluster. Nothing that would put my cluster at risk. The reason I ask is because I was trying to implement a reliable method of monitoring replication lag for streaming replication on 9.2+ systems using the following SQL on slaves: select extract(epoch from now() - pg_last_xact_replay_timestamp()); This SQL provides me with a time-based measure of replication lag instead of a byte-based measure. Time-based lag measurement is more meaningful for us in time-sensitive applications. During my testing I noticed that if the database went "quiet" (no update activity on the master) for a period of time, then the last replay timestamp remained unchanged. Having little or no update activity after-hours is very common on our smaller systems. This made the monitoring of replication lag inconsistent because, despite the slave being "caught up" with the master, it was reporting an increasing time lag. And I didn't want our DBAs to get false alerts from our monitoring. So I went on the hunt for a method of forcing replay to occur without actually performing any database updates. I also did not want to grant any kind of update capability on the database to my monitoring role, for tighter security. I discovered that the monitoring role, despite not having any update permissions, could successfully issue a "NOTIFY" command to a bogus channel and that this command actually forced the log to replay on the slave, updating the replay timestamp. This seems like a viable solution to my problem. My plan is to have the monitoring role issue a "NOTIFY" every 30 seconds to ensure the timestamp is updated at least that frequently. But there will not be an associated "LISTEN" for these notifications. However, I don't want to cause any problems for the PostgreSQL cluster itself by having messages inserted with nobody to listen for them, which is why I'm posting here. Do you see any long-term problems with constantly issuing "NOTIFY" commands every 30 seconds without an associated "LISTEN" command? Depending on how long 'long term' is: https://www.postgresql.org/docs/9.6/static/sql-notify.html "There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. However, no cleanup can take place if a session executes LISTEN and then enters a transaction for a very long time. Once the queue is half full you will see warnings in the log file pointing you to the session that is preventing cleanup. In this case you should make sure that this session ends its current transaction so that cleanup can proceed." Thank you, Rob Brucks -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general