Description Keeping your connection limits in check should lead to a much healthier performing database and application. PostgreSQL has a lot of metadata about both historical and current activity against a system. This custom alert utilized pg_stat_activity to help provide insight into how many total connections (in all states) are being used and then returns a percentage of max_connections found in pg_settings . This is just an example of how pg_stat_activity can be utilized to monitor the health of your PostgreSQL connections. Below are descriptions of each state : active – Currently running queries, or the number of connections you currently have idle – An application has a connection to the database, but nothing is happening. It's idle. A connection pooler like pgBouncer or pgPool-II is useful for managing these. idle in transaction – Sometimes you may have some application/query logic that has a BEGIN but is now waiting somewhere in transaction La La Land and not doing work. Alert Definition To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these: Alert Name: Total connections as percentage of max_connections Execution Interval : 10 minutes (default) -- change this according to your database workload. You may need to run this alert more frequently. Notification Text : Empty SQL Statement: select ROUND(CAST((FLOAT4(tc.total_connections)/FLOAT4(mc.max_connections))*100 AS NUMERIC),2) FROM (SELECT sum(numbackends) as total_connections FROM pg_stat_database) tc, (SELECT setting as max_connections from pg_settings where name = 'max_connections') mc WHERE tc.total_connections > 0 Execute Against: Monitored Instance Units: % High Threshold: Min 90, Max <blank> Medium Threshold: Min 85, Max 90 Low Threshold: Min 80, Max 85

This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - Total connections as percentage of max_connections

ashrecksiedler over 3 years ago

Description

Keeping your connection limits in check should lead to a much healthier performing database and application. PostgreSQL has a lot of metadata about both historical and current activity against a system. This custom alert utilized pg_stat_activity to help provide insight into how many total connections (in all states) are being used and then returns a percentage of max_connections found in pg_settings. This is just an example of how pg_stat_activity can be utilized to monitor the health of your PostgreSQL connections. Below are descriptions of each state:

active – Currently running queries, or the number of connections you currently have
idle – An application has a connection to the database, but nothing is happening. It's idle. A connection pooler like pgBouncer or pgPool-II is useful for managing these.
idle in transaction – Sometimes you may have some application/query logic that has a BEGIN but is now waiting somewhere in transaction La La Land and not doing work.

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:

Alert Name: Total connections as percentage of max_connections

Execution Interval: 10 minutes (default) -- change this according to your database workload. You may need to run this alert more frequently.

Notification Text: Empty

SQL Statement:

select ROUND(CAST((FLOAT4(tc.total_connections)/FLOAT4(mc.max_connections))*100 AS NUMERIC),2) FROM (SELECT sum(numbackends) as total_connections FROM pg_stat_database) tc, (SELECT setting as max_connections from pg_settings where name = 'max_connections') mc WHERE tc.total_connections > 0

Execute Against: Monitored Instance

Units: %

High Threshold: Min 90, Max <blank>

Medium Threshold: Min 85, Max 90

Low Threshold: Min 80, Max 85