Re: Determining server load

On Sep 27, 2016, at 10:07 AM, Adrian Klaver <adrian.klaver@aklaver.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
networkingissue and not a postgres issue - could not connect was only the end result. The logs then, of course, show
normaloperation. 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
pgpoolor 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.klaver@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general