Postgresql reaching connection limit

[pnnagios]

Hello,

We experienced an issue with the postgresql service reaching its "max_connections" limit of 100, on two of our NaigosXI hosts. Once that was reached, a restart of the postgresql service corrected the issue. However, we would like to track down what caused the high number of connections. Since the postgresql service was restarted, the connections have been in the 25 - 30 range. Also, one host is running 2014r1.4, and the other is running 2012r2.9, yet they both had the same issue. We would like some direction, on where to start looking for the cause of this issue (logs to check etc.).

Thank you,

Paul

[tmcdonald]

The postgres db handles all user details such as dashlet configuration, interface preferences, authentication, etc. If you have many people logging in to the XI interface at once that could certainly do it. Similarly, if you have a lot of dashlets loading at once it could (just theorizing here, would need to confirm with a developer) bump up the number of connections as it tries to load them all.

[pnnagios]

We are using Nagios Fusion to provide an overall view for 7 NagiosXI hosts (5 running 2014r1.4 and 2 running 2012r2.9), along with a NOC view dashboard in fusion, that uses dashlets to show the host/service/warning status for each host. Could this combination have contributed to the postgresql connection spike? Also note, that we are using CentOS 6 - 64 bit.The two hosts that had the issue, are currently at 21 and 27 connections. We added a few more logins to fusion, and NagiosXi, but it didn't seem to increase the connection count. I am wondering if we are counting the connections correctly. We are executing:

Code: Select all

ps ax | grep postgres | wc -l

When the two servers had the postgresql issue, this returned 103, and we were unable to login to the NagiosXI host, until we restarted postgresql with:

Code: Select all

service postgresql restart

The process (connection?) count then returned to the 20 - 30 range.
Is there a way we can monitor the number of postgresql connections, and note where the connections are coming from, to track the connection count spikes?
If it turns out that we need to increase the maximum number of connections, does Nagios have recommended settings? From what I have read, there are other postgresql settings that may need adjusting if you increase the maximum number of connections?

Thank you,

Paul

[slansing]

I believe you can actually check the postgres connections with the postgresql wizard in XI, you could direct it at localhost to add them in. You can also run this to check # of connections:

Code: Select all

psql nagiosxi nagiosxi
select count(*) from pg_stat_activity ;
\q

Based on that we can better assist with what you may want to change, it's likely the dashboards/dashlets I believe, that are causing this, specifically the connection of the fusion server to the XI servers.

[pnnagios]

We have used the postgres monitoring wizard to add monitoring for the number of connections, on each NagiosXI host, as suggested. The postgres connection counts for each of the 7 NagiosXI hosts is currently, as follows: host1 (52), host2 (25), host3 (17), host4 (51), host5 (17), host6 (85) and host7 (15). The 85 for host6 seems rather high, and we will watch to see if it hits the connection limit of 100.

Code: Select all

[root@monitor6]# psql nagiosxi nagiosxi
psql (8.4.20)
Type "help" for help.

nagiosxi=> select count(*) from pg_stat_activity ;
 count
-------
    85
(1 row)

nagiosxi=> \q
[root@monitor6]#

Is there anything else, we can check on host6, to see why it has a lot more connections?

Thank you,

Paul

[lmiltchev]

Do you have many users logging into this particular server?

[pnnagios]

No, there are not many users (as in people) logging into this server. Also, the high number of connections seems to be persistent, rather than transient. See state history snapshot below, and note that I set the warning level at 70, and the critical level at 90. Note too, that the two servers that initially had the problem, have been ok (connections < 30), since I restarted the postgresql service. Is there a way to match up the connections with their source (process, script, server etc.). Perhaps some event causes a connection increase, and then the connections are not released properly, and then when the event happens again, the limit is reached, or something like that?

Thank you,

Paul

[abrist]

You can use the following ps command to get slightly more detailed information:

Code: Select all

ps auxww | grep ^postgres

Or the following queries:

Code: Select all

echo "SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;" | psql nagiosxi nagiosxi
echo "select * from pg_stat_activity;" | psql nagiosxi nagiosxi