On Tue, Sep 27, 2016 at 2:25 PM, Israel Brewster <isr...@ravnalaska.net> wrote:
> On Sep 27, 2016, at 10:07 AM, Adrian Klaver <adrian.kla...@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 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.kla...@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 > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.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.
