Thread: Determining server load
Attachment
On Tue, Sep 27, 2016 at 2:25 PM, Israel Brewster <israel@ravnalaska.net> wrote:
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 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.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
--
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
>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=onIt 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 youto 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.
Attachment
On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster <israel@ravnalaska.net> 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=onIt 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 youto 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 BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709----------------------------------------------- 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;
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;
--
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Sep 27, 2016, at 10:55 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster <israel@ravnalaska.net> 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=onIt 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 youto 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 BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709----------------------------------------------- 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 connectionsSELECT 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 userSELECT 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
-----------------------------------------------