Thread: Function to get size of notification queue?
Hi folks, I have a project which uses Postgres asynchronous notifications pretty heavily. It has a particularly Fun failure mode which causes the notification queue to fill up. To better debug this problem I'd like to be able to monitor the size of the notification queue over time. It doesn't look like we have a pg_notify_queue_size() or equivalent. Should we? Or would I be better off just watching the size of the pg_notify/ directory on disk? Cheers, BJ
Hi BJ, What approach did you end up using? Thanks, Kevin -- View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Hi Kevin,
I never found a direct solution to this problem. I still feel that a function to find the size of the notification queue would be a handy feature to have, and I would be willing to take a shot at writing such a feature. However, given the <tumbleweed/> response to my original email, it's likely that effort would be a waste of time.On Tue, 16 Jun 2015 at 03:40 kjsteuer <kjsteuer@gmail.com> wrote:
Hi BJ,
What approach did you end up using?
Thanks,
Kevin
--
View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Thanks for the timely response!
On Mon, Jun 15, 2015 at 2:07 PM Brendan Jurd [via PostgreSQL] <[hidden email]> wrote:
BJCheers,Hi Kevin,I never found a direct solution to this problem. I still feel that a function to find the size of the notification queue would be a handy feature to have, and I would be willing to take a shot at writing such a feature. However, given the <tumbleweed/> response to my original email, it's likely that effort would be a waste of time.On Tue, 16 Jun 2015 at 03:40 kjsteuer <[hidden email]> wrote:Hi BJ,
What approach did you end up using?
Thanks,
Kevin
--
View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5853923.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list ([hidden email])
If you reply to this email, your message will be added to the discussion below:
View this message in context: Re: Function to get size of notification queue?
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Brendan Jurd wrote: > Hi Kevin, > > I never found a direct solution to this problem. I still feel that a > function to find the size of the notification queue would be a handy > feature to have, and I would be willing to take a shot at writing such a > feature. However, given the <tumbleweed/> response to my original email, > it's likely that effort would be a waste of time. I think tumbleweed responses are more in line with "hmm, this guy might well be right, but I don't know right now. <next email>". When people come up with really useless proposals, they tend to figure out pretty quickly. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Jun 15, 2015 at 2:12 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Brendan Jurd wrote: >> Hi Kevin, >> >> I never found a direct solution to this problem. I still feel that a >> function to find the size of the notification queue would be a handy >> feature to have, and I would be willing to take a shot at writing such a >> feature. However, given the <tumbleweed/> response to my original email, >> it's likely that effort would be a waste of time. > > I think tumbleweed responses are more in line with "hmm, this guy might > well be right, but I don't know right now. <next email>". When people > come up with really useless proposals, they tend to figure out pretty > quickly. +1 It took me a lot longer than it should have to figure this out, but lack of comment does not in any way indicate a response is bad. Most commonly it means, "interesting idea, why don't you code it up and see what happens?". Suggestions, even very good ones (except when related to bona fide bugs) are remarkably unlikely to elicit, "good idea, let's do that!". A lot of this has to do with years of micro-optimization in terms of handling email and some gentle subtle nudges to do more of the homework yourself. merlin
On Tue, 16 Jun 2015 at 05:36 Merlin Moncure <mmoncure@gmail.com> wrote:
On Mon, Jun 15, 2015 at 2:12 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> Brendan Jurd wrote:
>> However, given the <tumbleweed/> response to my original email,
>> it's likely that effort would be a waste of time.
>
> I think tumbleweed responses are more in line with "hmm, this guy might
> well be right, but I don't know right now. <next email>". When people
> come up with really useless proposals, they tend to figure out pretty
> quickly.
+1
It took me a lot longer than it should have to figure this out, but
lack of comment does not in any way indicate a response is bad. Most
commonly it means, "interesting idea, why don't you code it up and see
what happens?". Suggestions, even very good ones (except when related
to bona fide bugs) are remarkably unlikely to elicit, "good idea,
let's do that!".
Álvaro, Merlin,
Thanks for your comments. I understand what you're saying, and I do agree for the most part. However I've also seen the downside of this, where nobody comments much on the original proposal, and only after sinking substantial effort into creating a patch do others appear to forcefully oppose the idea that led to the patch. I do understand why it happens this way, but that doesn't make it any less of a deterrent.
If you see a proposal on the list and you think "interesting idea, why don't you code it up and see what happens", I would humbly and respectfully encourage you to type exactly those words in to your email client and let the author of the proposal know. None of us are telepaths, silence is ambiguous, and sometimes even a very small encouragement is all that is needed to provoke action.
Back to the $subject at hand -- I have had a quick look into async.c and can see that the logic to test for queue size in asyncQueueFillWarning() could easily be factored out and exposed via an SQL function. My original idea was to have the function return the number of notifications in the queue, but in fact given the way notifications are stored, it would be much easier to return a float showing the fraction of the maximum queue size that is currently occupied. This would actually be more useful for the use-case I described, where I am wanting to monitor for rogue processes filling up the queue.
I will take Merlin's advice, code something up and see what happens.
Cheers,
BJ
On Mon, Jun 15, 2015 at 3:16 PM, Brendan Jurd <direvus@gmail.com> wrote: > On Tue, 16 Jun 2015 at 05:36 Merlin Moncure <mmoncure@gmail.com> wrote: >> >> On Mon, Jun 15, 2015 at 2:12 PM, Alvaro Herrera >> <alvherre@2ndquadrant.com> wrote: >> > Brendan Jurd wrote: >> >> However, given the <tumbleweed/> response to my original email, >> >> it's likely that effort would be a waste of time. >> > >> > I think tumbleweed responses are more in line with "hmm, this guy might >> > well be right, but I don't know right now. <next email>". When people >> > come up with really useless proposals, they tend to figure out pretty >> > quickly. >> >> +1 >> >> It took me a lot longer than it should have to figure this out, but >> lack of comment does not in any way indicate a response is bad. Most >> commonly it means, "interesting idea, why don't you code it up and see >> what happens?". Suggestions, even very good ones (except when related >> to bona fide bugs) are remarkably unlikely to elicit, "good idea, >> let's do that!". > > > Álvaro, Merlin, > > Thanks for your comments. I understand what you're saying, and I do agree > for the most part. However I've also seen the downside of this, where > nobody comments much on the original proposal, and only after sinking > substantial effort into creating a patch do others appear to forcefully > oppose the idea that led to the patch. I do understand why it happens this > way, but that doesn't make it any less of a deterrent. > > If you see a proposal on the list and you think "interesting idea, why don't > you code it up and see what happens", I would humbly and respectfully > encourage you to type exactly those words in to your email client and let > the author of the proposal know. None of us are telepaths, silence is > ambiguous, and sometimes even a very small encouragement is all that is > needed to provoke action. It goes back to the adage, 'Everyone wants to be an author but nobody wants to write'. -hackers are busy with release schedules, multi-xact bugs, bidirectional replication and who knows what else. It's definitely upon you to do the homework getting patch together, and you absolutely must be prepared to do that understanding the tough road most patches have in order to get accepted. The archives clearly note your suggestion; even if the work gets shelved it can be referred to by future coders or used as evidence by others to advance work. For posterity, I think your idea is pretty good, especially if the current slru based implementation supports it without a lot of extra work. Adding a new built-in function is not free though so I think to move forwards with this you'd also have to show some more justification. Perhaps a real world example demonstrating the problem reduced down to an executable case. merlin
On Tue, 16 Jun 2015 at 07:52 Merlin Moncure <mmoncure@gmail.com> wrote:
It goes back to the adage, 'Everyone wants to be an author but nobody
wants to write'.
A more accurate version would be "Everyone wants to be an author, some want to write, but nobody likes being rejected by publishers".
For posterity, I think your idea is pretty good, especially if the
current slru based implementation supports it without a lot of extra
work.
Thank you for saying so, and yes, adding the function is pretty much trivial. I already have a patch that works, and will submit it once I've added docs and tests.
Adding a new built-in function is not free though so I think to
move forwards with this you'd also have to show some more
justification. Perhaps a real world example demonstrating the problem
reduced down to an executable case.
Well the docs already describe this situation. The notification queue is finite, listening clients with long-running transactions could cause it to blow out, and if it does blow out, Bad Things will ensue. At the moment, there is no good way to find out whether this is happening.
From SQL Commands / NOTIFY / Notes:
"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."
"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."
So, it's straightorward to simulate the problem scenario. Make two client connections A and B to the same server. Client A executes "LISTEN a;", then "BEGIN;". Client B submits some notifications on channel "a", e.g., "SELECT pg_notify('a', 'Test queue saturation ' || s::text) FROM generate_series(1, 10000) s;". The queue will start filling up, and will never reduce unless and until client A ends its transaction. If client B keeps on submitting notifications, the queue will eventually fill completely and then client B's session will ERROR out.
Cheers,
BJ
Hi Brendan, Once the documentation/patch is ready can you pls share? I want to create an alarm off a periodic query on the queue size for say 4GB/some count that I tune. Thanks, Kevin -- View this message in context: http://postgresql.nabble.com/Function-to-get-size-of-notification-queue-tp5738461p5854035.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Hello hackers,
I present a patch to add a new built-in function pg_notify_queue_saturation().I am not convinced that pg_notify_queue_saturation is the best possible name for this function, and am very much open to other suggestions.
Attachment
I don't see this in the CF app; can you please add it there?
Best regards,
On Wed, Jun 17, 2015 at 3:31 AM, Brendan Jurd <direvus@gmail.com> wrote:
BJCheers,The patch includes documentation, a regression test and an isolation test.The patch has almost no new code. It makes use of the existing logic for the 50% warning. I simply refactored that logic into a separate function asyncQueueSaturation, and then added pg_notify_queue_saturation to make that available in SQL.The current documentation under LISTEN explains this possible gotcha, but doesn't really suggest a useful way to address it, except to mention that warnings will show up in the log once you get to 50% saturation of the queue. Unless you happen to be eyeballing the logs when it happens, that's not a huge help. The choice of 50% as a threshold is also very much arbitrary, and by the time you hit 50% the problem has likely been going on for quite a while. If you want your nagios (or whatever) to say, alert you when the queue goes over 5% or 1%, your options are limited and awkward.The purpose of the function is to allow users to monitor the health of their notification queue. In certain cases, a client connection listening for notifications might get stuck inside a transaction, and this would cause the queue to keep filling up, until finally it reaches capacity and further attempts to NOTIFY error out.Hello hackers,I present a patch to add a new built-in function pg_notify_queue_saturation().
I am not convinced that pg_notify_queue_saturation is the best possible name for this function, and am very much open to other suggestions.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Gurjeet Singh http://gurjeet.singh.im/
On Thu, 18 Jun 2015 at 03:06 Gurjeet Singh <gurjeet@singh.im> wrote:
I don't see this in the CF app; can you please add it there?
Done. I did try to add it when I posted the email, but for some reason I couldn't connect to commitfest.postgresql.org at all. Seems fine now, though.
Cheers,
BJ
On Wed, Jun 17, 2015 at 5:31 AM, Brendan Jurd <direvus@gmail.com> wrote: > Hello hackers, > > I present a patch to add a new built-in function > pg_notify_queue_saturation(). > > The purpose of the function is to allow users to monitor the health of their > notification queue. In certain cases, a client connection listening for > notifications might get stuck inside a transaction, and this would cause the > queue to keep filling up, until finally it reaches capacity and further > attempts to NOTIFY error out. > > The current documentation under LISTEN explains this possible gotcha, but > doesn't really suggest a useful way to address it, except to mention that > warnings will show up in the log once you get to 50% saturation of the > queue. Unless you happen to be eyeballing the logs when it happens, that's > not a huge help. The choice of 50% as a threshold is also very much > arbitrary, and by the time you hit 50% the problem has likely been going on > for quite a while. If you want your nagios (or whatever) to say, alert you > when the queue goes over 5% or 1%, your options are limited and awkward. > > The patch has almost no new code. It makes use of the existing logic for > the 50% warning. I simply refactored that logic into a separate function > asyncQueueSaturation, and then added pg_notify_queue_saturation to make that > available in SQL. > > I am not convinced that pg_notify_queue_saturation is the best possible name > for this function, and am very much open to other suggestions. > > The patch includes documentation, a regression test and an isolation test. *) The documentation should indicate what the range of values mean -- looks like value is returned on 0-1 scale. *) A note regarding the 50% (0.5) threshold raising warnings in the log might be appropriate here *) As you suspect, the name seems a little off to me. 'usage' seems preferable to 'saturation', I think. Perhaps, pg_notification_queue_usage()? merlin
On Wed, Jun 17, 2015 at 5:15 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > *) A note regarding the 50% (0.5) threshold raising warnings in the > log might be appropriate here scratch that. that note already exists in sql-notify.html. Instead, I'd modify that section to note that you can check queue usage with your new function. merlin
On Thu, 18 Jun 2015 at 08:19 Merlin Moncure <mmoncure@gmail.com> wrote:
scratch that. that note already exists in sql-notify.html. Instead,
I'd modify that section to note that you can check queue usage with
your new function.
I have already done so. Under the paragraph about the queue filling up, I have added:
"The function <function>pg_notify_queue_saturation</function> returns the proportion of the queue that is currently occupied by pending notifications."
"The function <function>pg_notify_queue_saturation</function> returns the proportion of the queue that is currently occupied by pending notifications."
A link from here back to the section in System Information Functions might be sensible?
I will rename the function with _usage as you suggest, and add the explanation of the return value in the docs.
Cheers,
BJ
Posting v2 of the patch, incorporating some helpful suggestions from Merlin.
Cheers,Attachment
On Wed, Jun 17, 2015 at 6:15 PM, Brendan Jurd <direvus@gmail.com> wrote: > Posting v2 of the patch, incorporating some helpful suggestions from Merlin. Based on perfunctory scan of the code, I think this is gonna make it, unless you draw some objections based on lack of necessity. merlin
Patch reviewed following the instructions on https://wiki.postgresql.org/wiki/Reviewing_a_Patch
# Submission review
- Is the patch in a patch format which has context?
Yes. Note to other reviewers: `git diff —patience` yields patch better suited for readability
- Does it apply cleanly to the current git master?
Yes.
- Does it include reasonable tests, necessary doc patches, etc?
Doc patch - Yes.
Tests - Yes.
# Usability review
- Does the patch actually implement the feature?
Yes.
- Do we want that?
Yes; see the discussion in mailing list.
- Do we already have it?
No.
- Does it follow SQL spec, or the community-agreed behavior?
Yes. It seems to implement the behavior agreed upon in the mailing list.
- Does it include pg_dump support (if applicable)?
N/A
- Are there dangers?
None that I could spot.
- Have all the bases been covered?
There’s room for an additional test which tests for non-zero return value.
# Feature test
- Does the feature work as advertised?
Yes. Build configured with '--enable-debug --enable-cassert CFLAGS=-O0’.
With a slightly aggressive notifications-in-a-loop script I was able to see non-zero return value:
Session 1:
listen ggg;
begin;
Session 2:
while sleep 0.1; do echo 'notify ggg; select pg_notification_queue_usage();' ; done | psql
- Are there corner cases the author has failed to consider?
No.
- Are there any assertion failures or crashes?
The patch exposes an already available function to the SQL interface, and rearranges code, so it doesn’t look like the patch can induce an assertion or a crash.
- Performance review
Not evaluated, since it’s not a performance patch, and it doesn’t seem to impact any performance critical code path, ,either.
Additional notes:
Patch updates the docs of another function (pg_listening_channels), but the update is an improvement so we can let it slide :)
+ proportion of the queue that is currently occupied by pending notifications.
s/proportion/fraction/
+ * The caller must hold (at least) shared AysncQueueLock.
A possibly better wording: The caller must hold AysncQueueLock in (at least) shared mode.
Unnecessary whitespace changes in pg_proc.h for existing functions.
+DESCR("get the current usage of the asynchronous notification queue");
A possibly better wording: get the fraction of the asynchronous notification queue currently in use
On Thu, Jun 18, 2015 at 10:47 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Wed, Jun 17, 2015 at 6:15 PM, Brendan Jurd <direvus@gmail.com> wrote:
> Posting v2 of the patch, incorporating some helpful suggestions from Merlin.
Based on perfunctory scan of the code, I think this is gonna make it,
unless you draw some objections based on lack of necessity.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Gurjeet Singh http://gurjeet.singh.im/
On Fri, 26 Jun 2015 at 06:03 Gurjeet Singh <gurjeet@singh.im> wrote:
Patch reviewed following the instructions on https://wiki.postgresql.org/wiki/Reviewing_a_Patch
Thank you for your review, Gurjeet.
s/proportion/fraction/
I think of these as synonymous -- do you have any particular reason to prefer "fraction"? I don't feel strongly about it either way, so I'm quite happy to go with fraction if folks find that more expressive.
+ * The caller must hold (at least) shared AysncQueueLock.A possibly better wording: The caller must hold AysncQueueLock in (at least) shared mode.
Yes, that is more accurate.
Unnecessary whitespace changes in pg_proc.h for existing functions.
I did group the asynchronous notification functions together, which seemed reasonable as there are now three of them, and changed the tabbing between the function name and namespace ID to match, as is done elsewhere in pg_proc.h. I think those changes improve readability, but again I don't feel strongly about it.
+DESCR("get the current usage of the asynchronous notification queue");A possibly better wording: get the fraction of the asynchronous notification queue currently in use
I have no objections to your wording.
Cheers,
BJ
On Thu, Jun 25, 2015 at 8:43 PM, Brendan Jurd <direvus@gmail.com> wrote:
On Fri, 26 Jun 2015 at 06:03 Gurjeet Singh <gurjeet@singh.im> wrote:s/proportion/fraction/I think of these as synonymous -- do you have any particular reason to prefer "fraction"? I don't feel strongly about it either way, so I'm quite happy to go with fraction if folks find that more expressive.
It just feels better to me in this context.
If the number of times used in Postgres code is any measure, 'fraction' wins hands down: "proportion" : 33, "fraction": 620.
I don't feel strongly about it, either. I can leave it up to the committer to decide.
+ * The caller must hold (at least) shared AysncQueueLock.A possibly better wording: The caller must hold AysncQueueLock in (at least) shared mode.Yes, that is more accurate.
OK.
Unnecessary whitespace changes in pg_proc.h for existing functions.I did group the asynchronous notification functions together, which seemed reasonable as there are now three of them, and changed the tabbing between the function name and namespace ID to match, as is done elsewhere in pg_proc.h. I think those changes improve readability, but again I don't feel strongly about it.
Fair enough.
+DESCR("get the current usage of the asynchronous notification queue");A possibly better wording: get the fraction of the asynchronous notification queue currently in useI have no objections to your wording.
OK. Please send a new patch with the changes you agree to, and I can mark it ready for committer.
Best regards,
-- Gurjeet Singh http://gurjeet.singh.im/
On Thu, 16 Jul 2015 at 08:37 Gurjeet Singh <gurjeet@singh.im> wrote:
OK. Please send a new patch with the changes you agree to, and I can mark it ready for committer.
Done. Please find attached patch v3. I have changed "proportion" to "fraction", and made other wording improvements per your suggestions.
Cheers,
BJ
Attachment
On Fri, Jul 17, 2015 at 4:23 AM, Brendan Jurd <direvus@gmail.com> wrote: > On Thu, 16 Jul 2015 at 08:37 Gurjeet Singh <gurjeet@singh.im> wrote: >> OK. Please send a new patch with the changes you agree to, and I can mark >> it ready for committer. > > Done. Please find attached patch v3. I have changed "proportion" to > "fraction", and made other wording improvements per your suggestions. Speaking as a man whose children just finished fifth-grade math, a proportion, technically speaking, is actually a relationship between two fractions or ratios. So I agree that "fraction" is the right word here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Jul 17, 2015 at 4:23 AM, Brendan Jurd <direvus@gmail.com> wrote: > On Thu, 16 Jul 2015 at 08:37 Gurjeet Singh <gurjeet@singh.im> wrote: >> OK. Please send a new patch with the changes you agree to, and I can mark >> it ready for committer. > > Done. Please find attached patch v3. I have changed "proportion" to > "fraction", and made other wording improvements per your suggestions. Committed. I changed one remaining use of "proportion" to "fraction", fixed an OID conflict, and reverted some unnecessary whitespace changes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, 17 Jul 2015 at 23:14 Robert Haas <robertmhaas@gmail.com> wrote:
Committed. I changed one remaining use of "proportion" to "fraction",
fixed an OID conflict, and reverted some unnecessary whitespace
changes.
Thanks Robert. Sorry I missed a "proportion" in my latest version, and thanks for catching it.
Cheers,