Re: max_connections limit violation not showing in pg_stat_activity

On Wed, Nov 23, 2016 at 4:43 AM, Charles Clavadetscher
<clavadetscher(at)swisspug(dot)org> wrote:
> From: Kevin Grittner [mailto:kgrittn(at)gmail(dot)com]

>> Is it possible to upgrade? You are missing over a year's worth
>> of fixes for serious bugs and security vulnerabilities.
>
> Yes. Actually it is foreseen to move to 9.6 (and RedHat 7.x).
>
>> https://www.postgresql.org/support/versioning/

An upgrade from 9.3.x to 9.6.x is a major release upgrade, which
gets you new features and usually gets you improvements in
performance and scalability. The 9.3 major release will be
supported for almost 2 more years, so I wasn't so concerned about
that as being on 9.3.10 when the latest bug fix version of 9.3 is
9.3.15. To avoid hitting bugs that others have already hit and
reported, with fixes published, it is wise to try to upgrade to the
latest minor release fairly quickly. If the minor release fixes a
serious security vulnerability, I think it is a good idea to update
within a day or two of release.

> I will have to check with our hosting people how many cores we
> have or can have on the new environment. I have seen that there
> is pgBouncer and pgPool. Would you recommend one of those?

pgBouncer is more lightweight, so if you don't need any of the
features present only pgPool, I would go with pgBouncer. Depending
on your application software environment, it may be even better to
use a pool built into the application development framework. There
are several very good pools available to Java environments. I can
personally attest to the quality of Apache dbcp, but others have
mentioned that they like other Java connection pools even better.
You might want to search the archives, or maybe someone will
mention the others again on this thread.

>>> SELECT count(*) FROM pg_stat_activity; watch 1;
>>
>> At the times when the resources are overloaded by more
>> connections than the resources can efficiently service -- well
>> that's precisely the time that a sleeping "monitoring" process
>> is least likely to be given a time slice to run. If you can
>> manage to get pgbadger to run on your environment, and you turn
>> on logging of connections and disconnections, you will be able
>> to get far more accurate information.
>
> Yes, it sounds reasonable. I assumed that this kind of
> measurements have a higher priority or reserved slots for them.
> In those occasions is when they are most needed.

There is no such feature in PostgreSQL. It might be worthwhile,
although how that would be implemented is not obvious, short of a
sysadmin looking for the monitoring backend process and running
"nice" against it.

> And thank you for the hint to pgbadger. I will take a look into
> it, but an installation on the server completely depends on our
> hosting service people. I am not sure this is feasible, but I can
> imagine an alternative scenario, using a local installation for
> tuning and then pass the parameters to the hosters for
> implementation.

As long as you can control the PostgreSQL configuration (to set the
right logging options) and can retrieve the log files, you should
be able to use it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company