Re: Determining server load

[Israel Brewster <israel@xxxxxxxxxxxxxx>]

On Sep 27, 2016, at 10:55 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster <israel@xxxxxxxxxxxxxx> wrote:

>I'm still curious as to how I can track concurrent connections, ...

Have you considered enabling the following  in postgresql.conf?
log_connections=on
log_disconnections=on

It will put a bit of a bloat in you postgres log, but it will all allow you extract connects/disconnects over a time range. That should allow you

to determine concurrent connections during that that.

I do have those on, and I could write a parser that scans through the logs counting connections and disconnections to give a number of current connections at any given time. Trying to make it operate "in real time" would be interesting, though, as PG logs into different files by day-of-the-week (at least, with the settings I have), rather than into a single file that gets rotated out. I was kind of hoping such a tool, such as pgbadger (which, unfortunately, only seems to track connections per second and not consecutive connections), already existed, or that there was some way to have the database itself track this metric. If not, well, I guess that's another project :)

-----------------------------------------------

Israel Brewster

Systems Analyst II

Ravn Alaska

5245 Airport Industrial Rd

Fairbanks, AK 99709

(907) 450-7293

-----------------------------------------------

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Does this help?

--Total concurrent connections

SELECT COUNT(*)
  FROM pg_stat_activity;

--concurrent connections by user
SELECT usename,
       count(*)
 FROM pg_stat_activity
GROUP BY 1
ORDER BY 1;

--concurrent connections by database
SELECT datname,
       usename,
       count(*)
 FROM pg_stat_activity
GROUP BY 1, 2
ORDER BY 1, 2;

-- database connections by user

SELECT usename,
       datname,
       count(*)
 FROM pg_stat_activity
GROUP BY 1, 2
ORDER BY 1, 2;

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

That helps for one-time stat collection, but as I mentioned in my original message, since connections may not last long, I could be getting close to, or even hitting, my connection limit while still getting values back from those that show plenty of connections remaining, depending on how often I checked.

I guess what would be ideal in my mind is that whenever Postgresql logged an opened/closed connection, it also looked the *total* number of open connections at that time. I don't think that's possible, however :-)

-----------------------------------------------

Israel Brewster

Systems Analyst II

Ravn Alaska

5245 Airport Industrial Rd

Fairbanks, AK 99709

(907) 450-7293

-----------------------------------------------