Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 minutes.

SELECT sum(numbackends) FROM pg_stat_database;

Still, this seems to miss a huge number of short-lived connections, so the statistics are far from the reality.

I tried to run the script manually and I observed big changes even between two connections made few seconds away one from another.

How could I get this information in a reliable way? like max(connectios) happened during a time interval.

share|improve this question
    
Aggregate stats over time for connections would be nice, but I don't think PostgreSQL currently collects them. Look at the docs postgresql.org/docs/current/static/monitoring-stats.html for details. –  Craig Ringer Feb 11 at 11:58
    
@CraigRinger maybe I could configure Postgres or the clients to keep connections open for a longer period of time, so I could measure them. Due to the current setup I had one case when the postgres started to refuse connections. Monitoring was not able to detect this because this happened inside the 5 minute interval, and it went from below warning level to above critical in less then 5 minutes. And this was not a DoS attack. –  sorin Feb 11 at 12:12
2  
Yeah, that's quite an interesting problem. I strongly recommend putting a PgBouncer in front of your PostgreSQL instance, it'll queue up connections when it's too busy instead of rejecting them. (Yes, it's stupid that PostgreSQL can't do that its self but it's not a simple fix; see the endless discussions on the mailing lists re built-in pooling). –  Craig Ringer Feb 11 at 12:26
3  
What about logging connections (using log_connections and log_disconnections) into the logfile (e.g. csvlog) and then use pgBadger or something similar to extract that from the logfile? –  a_horse_with_no_name Feb 11 at 12:29
2  
@a_horse_with_no_name Good point. You can even "tail" the logs with a client that just reads new log entries, integrating over disconnects and connects to get a near-real-time report of peak connections during a given timeframe. Frankly, it shouldn't be this complicated. One of my tasks for the AXLE project (axleproject.eu) is to implement some more auditing, and I might be able to fit this into it... –  Craig Ringer Feb 11 at 12:33
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.