Re: Determining server load - Mailing list pgsql-general

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?

>
> 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.klaver@aklaver.com