cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Total connections as percentage of max_connections

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 

Labels (2)
Version history
Revision #:
2 of 2
Last update:
‎08-03-2020 03:42 PM
Updated by: