Re: Determining server load

On Tue, Sep 27, 2016 at 2:25 PM, Israel Brewster <israel(at)ravnalaska(dot)net>
wrote:

> On Sep 27, 2016, at 10:07 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
> >
> > On 09/27/2016 09:54 AM, Israel Brewster wrote:
> >> I have a Postgresql (9.4.6) cluster that hosts several databases, used
> >> by about half-a-dozen different in-house apps. I have two servers set up
> >> as master-slave with streaming replication. Lately I've been running
> >> into an issue where one of the apps periodically can't connect to the
> >> db. The problem is always extremely short lived (less than a minute),
> >> such that by the time I can look into it, there is no issue. My
> >> *suspicion* is that I am briefly hitting the max connection limit of my
> >> server (currently set at 100). If so, I can certainly *fix* the issue
> >> easily by increasing the connection limit, but I have two questions
> >> about this:
> >
> > What does your Postgres log show around this time?
>
> So in looking further, I realized the actual error I was getting was "no
> route to host", which is obviously a networking issue and not a postgres
> issue - could not connect was only the end result. The logs then, of
> course, show normal operation. That said, now that I am thinking about it,
> I'm still curious as to how I can track concurrent connections, with the
> revised goal of simply seeing how heavily loaded my server really is, and
> when tools such as pgpool or the pgbouncer that another user mentioned
> start making sense for the number of connections I am dealing with. Thanks.
> -----------------------------------------------
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293
> -----------------------------------------------
>
> >
> >>
> >> 1) Is there some way I can track concurrent connections to see if my
> >> theory is correct? I know I can do a count(*) on pg_stat_activity to get
> >> the current number of connections at any point (currently at 45 BTW),
> >> but aside from repeatedly querying this at short intervals, which I am
> >> afraid would put undue load on the server by the time it is frequent
> >> enough to be of use, I don't know how to track concurrent connections.
> >>
> >> I did look at pgbadger, which tells me I have gotten as high as 62
> >> connections/second, but given that most of those connections are
> >> probably very short lived that doesn't really tell me anything about
> >> concurrent connections.
> >>
> >> 2) Is increasing the connection limit even the "proper" fix for this, or
> >> am I at a load point where I need to start looking at tools like pgpool
> >> or something to distribute some of the load to my hot standby server? I
> >> do realize you may not be able to answer that directly, since I haven't
> >> given enough information about my server/hardware/load, etc, but answers
> >> that tell me how to better look at the load over time and figure out if
> >> I am overloaded are appreciated.
> >>
> >> For reference, the server is running on the following hardware:
> >>
> >> 2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower
> there)
> >> 32 GB Ram total, currently with 533144k showing as "free" and 370464k of
> >> swap used
> >> 371 GB SSD RAID 10 (currently only using 40GB of space)
> >> Dual Gigabit ethernet
> >>
> >> Thanks for any advice that can be provided!
> >> -----------------------------------------------
> >> Israel Brewster
> >> Systems Analyst II
> >> Ravn Alaska
> >> 5245 Airport Industrial Rd
> >> Fairbanks, AK 99709
> >> (907) 450-7293
> >> -----------------------------------------------
> >>
> >>
> >>
> >>
> >>
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>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.

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