Re: [GENERAL] Monitoring and insight into NOTIFY queue
I just ran into monitoring this and came up with the following 1 liner for
monitoring this in releases < 9.6 through the SQL layer. Once you are at 9.6
Jeff Janes solution is correct.
It does make the assumption that the queue size is 8GB. It can misjudge the
queue usage by up to one file segment size. If you are using this for nagios
monitoring, however, that error just disappears as a couple thousandths of a
percent of noise.
SELECT sum((pg_stat_file('pg_notify/' || pg_ls_dir)).size)/(8 * pow(1024, 3)) *
100 as notify_queue_usage_percent FROM pg_ls_dir('pg_notify');
--
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] Monitoring and insight into NOTIFY queue
On Fri, Feb 19, 2016 at 6:01 AM, Jiří Hlinka wrote: > Hello, > > I'd like to ask you whether there is a way how to monitor and log details > about NOTIFY queue? What do you use for this prupose? > > In the official documentation I'm unable to find a way how to monitor NOTIFY > queue in more detail, at least size of this queue, which is critical in case > of filling up the queue, which will lead to stopping the NOTIFY queue > functionality. It won't help you now, but version 9.6 will introduce a new function that should help: Date: Fri Jul 17 09:12:03 2015 -0400 Add new function pg_notification_queue_usage. This tells you what fraction of NOTIFY's queue is currently filled. Cheers, Jeff -- 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] Monitoring and insight into NOTIFY queue
On 02/19/2016 06:01 AM, Jiří Hlinka wrote:
Hello,
I'd like to ask you whether there is a way how to monitor and log
details about NOTIFY queue? What do you use for this prupose?
In the official documentation I'm unable to find a way how to monitor
NOTIFY queue in more detail, at least size of this queue, which is
critical in case of filling up the queue, which will lead to stopping
the NOTIFY queue functionality.
Should have added from:
http://www.postgresql.org/docs/9.5/interactive/sql-notify.html
"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"
Where the message is of form(from async.c):
(errmsg("NOTIFY queue is %.0f%% full", fillDegree * 100),
(minPid != InvalidPid ?
errdetail("The server process with
PID %d is among those with the oldest transactions.", minPid)
: 0),
(minPid != InvalidPid ?
errhint("The NOTIFY queue cannot be
emptied until that process ends its current transaction.")
: 0)));
Thanks,
Jiri
--
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] Monitoring and insight into NOTIFY queue
On 02/19/2016 06:01 AM, Jiří Hlinka wrote: Hello, I'd like to ask you whether there is a way how to monitor and log details about NOTIFY queue? What do you use for this prupose? In the official documentation I'm unable to find a way how to monitor NOTIFY queue in more detail, at least size of this queue, which is critical in case of filling up the queue, which will lead to stopping the NOTIFY queue functionality. Well from here: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/async.c;h=beef574076c257db0a868b39a946565185f6c53e;hb=refs/heads/REL9_5_STABLE "There is one central queue in disk-based storage (directory pg_notify/), with actively-used pages mapped into shared memory by the slru.c module. All notification messages are placed in the queue and later read out by listening backends." and from here: http://www.postgresql.org/docs/9.5/interactive/sql-notify.html "The queue is quite large (8GB in a standard installation) ..." I could see setting up file monitoring in pg_notify/ of the queue file size. Thanks, Jiri -- 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] Monitoring and insight into NOTIFY queue
On Fri, Feb 19, 2016 at 8:01 AM, Jiří Hlinka wrote: > Hello, > > I'd like to ask you whether there is a way how to monitor and log details > about NOTIFY queue? What do you use for this prupose? > > In the official documentation I'm unable to find a way how to monitor NOTIFY > queue in more detail, at least size of this queue, which is critical in case > of filling up the queue, which will lead to stopping the NOTIFY queue > functionality. It used to be possible to do this with the (slow, locky) table based notification mechanism. Unfortunately FWICT it isn't now and we really ought to expand functionality in that area. I will say though that async notification should thought of as 'best effort'...if you are laying essential communication processes on top of it you need to have the listener respond and be prepare to retry. Another thing, queue size paranoia aside (which is a good way to consider things) I've never had the queue bust on me and I've beat on it pretty hard. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Monitoring and insight into NOTIFY queue
Hello, I'd like to ask you whether there is a way how to monitor and log details about NOTIFY queue? What do you use for this prupose? In the official documentation I'm unable to find a way how to monitor NOTIFY queue in more detail, at least size of this queue, which is critical in case of filling up the queue, which will lead to stopping the NOTIFY queue functionality. Thanks, Jiri
