Custom Alert - Total connections in idle-in-transaction state, as a percentage of max_connections
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 ('idle in transaction') 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.
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
select ROUND(CAST((FLOAT4(tc.total_connections)/FLOAT4(mc.max_connections))*100 AS NUMERIC),2) FROM (select count(*) as total_connections from pg_stat_activity where state = 'idle in transaction') tc, (SELECT setting as max_connections from pg_settings where name = 'max_connections') mc WHERE tc.total_connections > 0
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.