1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
#!@@PERL@@
# -*- cperl -*-
#
# Copyright (C) 2009 Magnus Hagander, Redpill Linpro AB
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; version 2 dated June,
# 1991.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 USA.
=head1 NAME
postgres_connections_ - Plugin to monitor PostgreSQL connections.
=head1 CONFIGURATION
Configuration is done through libpq environment variables, for example
PGUSER, PGDATABASE, etc. For more information, see L<Munin::Plugin::Pgsql>.
To monitor a specific database, link to postgres_connections_<databasename>.
To monitor all databases, link to postgres_connections_ALL.
=head1 SEE ALSO
L<Munin::Plugin::Pgsql>
=head1 MAGIC MARKERS
#%# family=auto
#%# capabilities=autoconf suggest
=head1 AUTHOR
Magnus Hagander <magnus@hagander.net>, Redpill Linpro AB
=head1 COPYRIGHT/License.
Copyright (c) 2009 Magnus Hagander, Redpill Linpro AB
All rights reserved. This program is free software; you can
redistribute it and/or modify it under the terms of the GNU General
Public License as published by the Free Software Foundation; version 2
dated June, 1991.
=cut
use strict;
use warnings;
use Munin::Plugin::Pgsql;
my $pg = Munin::Plugin::Pgsql->new(
basename => 'postgres_connections_',
title => 'PostgreSQL connections',
info => 'Number of connections',
vlabel => 'Connections',
basequery => [
"SELECT tmp.mstate AS state, COALESCE(count,0) FROM
(VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate)
LEFT JOIN
(SELECT CASE
WHEN a.wait_event_type IS NOT NULL AND a.locked AND state NOT LIKE 'idle in transaction%' THEN 'waiting'
WHEN state='idle' THEN 'idle'
WHEN state LIKE 'idle in transaction%' THEN 'idletransaction'
WHEN state='disabled' THEN 'unknown'
WHEN query='<insufficient privilege>' THEN 'unknown'
ELSE 'active' END AS mstate,
count(*) AS count
FROM (SELECT act.state, act.wait_event_type, EXISTS (SELECT FROM pg_locks AS l WHERE l.pid = act.pid) AS locked, act.query
FROM pg_stat_activity AS act
WHERE act.pid != pg_backend_pid() AND act.backend_type = 'client backend' %%FILTER%%)
AS a GROUP BY 1
) AS tmp2
ON tmp.mstate=tmp2.mstate
ORDER BY 1;
",
[ 9.6, "SELECT tmp.mstate AS state,COALESCE(count,0) FROM
(VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate)
LEFT JOIN
(SELECT CASE WHEN wait_event_type IS NOT NULL THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS mstate,
count(*) AS count
FROM pg_stat_activity WHERE pid != pg_backend_pid() %%FILTER%%
GROUP BY CASE WHEN wait_event_type IS NOT NULL THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END
) AS tmp2
ON tmp.mstate=tmp2.mstate
ORDER BY 1;
" ],
[ 9.5, "SELECT tmp.mstate AS state,COALESCE(count,0) FROM
(VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate)
LEFT JOIN
(SELECT CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS mstate,
count(*) AS count
FROM pg_stat_activity WHERE pid != pg_backend_pid() %%FILTER%%
GROUP BY CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END
) AS tmp2
ON tmp.mstate=tmp2.mstate
ORDER BY 1;
" ],
[ 9.1, "SELECT tmp.state,COALESCE(count,0) FROM
(VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(state)
LEFT JOIN
(SELECT CASE WHEN waiting THEN 'waiting' WHEN current_query='<IDLE>' THEN 'idle' WHEN current_query='<IDLE> in transaction' THEN 'idletransaction' WHEN current_query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS state,
count(*) AS count
FROM pg_stat_activity WHERE procpid != pg_backend_pid() %%FILTER%%
GROUP BY CASE WHEN waiting THEN 'waiting' WHEN current_query='<IDLE>' THEN 'idle' WHEN current_query='<IDLE> in transaction' THEN 'idletransaction' WHEN current_query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END
) AS tmp2
ON tmp.state=tmp2.state
ORDER BY 1
" ],
[ 8.1, "SELECT tmp.state,COALESCE(count,0) FROM
(SELECT 'active' UNION ALL SELECT 'idle' UNION ALL SELECT 'idletransaction' UNION ALL SELECT 'unknown') AS tmp(state)
LEFT JOIN
(SELECT CASE WHEN current_query='<IDLE>' THEN 'idle' WHEN current_query='<IDLE> in transaction' THEN 'idletransaction' WHEN current_query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS state,
count(*) AS count
FROM pg_stat_activity WHERE procpid != pg_backend_pid() %%FILTER%%
GROUP BY CASE WHEN current_query='<IDLE>' THEN 'idle' WHEN current_query='<IDLE> in transaction' THEN 'idletransaction' WHEN current_query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END
) AS tmp2
ON tmp.state=tmp2.state
ORDER BY 1" ],
],
wildcardfilter => " AND datname=?",
paramdatabase => 1,
configquery => [
"VALUES ('active','Active'),('waiting','Waiting for lock'),('idle','Idle'),('idletransaction','Idle in transaction'),('unknown','Unknown')",
[
8.1,
"SELECT 'active','Active' UNION ALL SELECT 'idle','Idle' UNION ALL SELECT 'idletransaction','Idle in transaction' UNION ALL SELECT 'unknown','Unknown'",
],
],
suggestquery =>
"SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate AND NOT datname='postgres' UNION ALL SELECT 'ALL' ORDER BY 1 LIMIT 10",
graphdraw => 'AREA',
stack => 1,
);
$pg->Process();
|
