On Tue, 27 Jun 2017 18:41:25 -0400
Melvin Davidson <melvin6925@gmail.com> wrote:
> On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
> > On 06/27/2017 01:10 PM, DrakoRod wrote:
> >
> >> Hi folks.
> >>
> >> Today I had a problem with production's database PostgreSQL version
> >> 9.4.4.9.
> >> The server have max_connections set to 200, but today I reviewed
> >> pg_stat_activity and saw 199 active connections, obviously the server
> >> rejected any new connection and the production stopped.
> >>
> >> I saw another posts with a similar problems, but this was because the
> >> pg_xlog was full or disk does'nt write, but the directory and disk had no
> >> problems.
> >>
> >> I just canceled some SELECTs querys and the server returned to normality.
> >> Now a monitoring activity of server and I can see some backends like this:
> >>
> >> postgres 9737 23340 2 14:55 ? 00:00:15 postgres: dbname user
> >> 8.8.8.8[37082] idle in transaction
> >> postgres 9741 23340 9 14:55 ? 00:00:47 postgres: dbname user
> >> 8.8.8.8[54286] idle in transaction
> >>
> >> Any suggestions?
> >>
> >
> > https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> > html#PG-STAT-ACTIVITY-VIEW
> >
> > SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
> >
> > To get more detail on what is holding these transactions open.
> >
>
> *His problem is NOT 'idle in transaction' per se. It is all connections are
> used up.*
> *Hence the need for pg_bouncer for connection pooling.*
That assessment lacks enough information to be substantiated.
One of the things I've frequently seen happen is applications written with
ORMs will create a new connection because the existing connections in the
pool are already in a transaction. If his application is not properly
committing transactions, an additional pooler layer will not improve on
the problem. Hence, what he needs to do first is gather more information and
understand exactly what's going on.
Of course, if usage has just scaled up to the point where he doesn't have
any free connections, then your assessment might be correct. But he hasn't
provided enough information to be sure of that.
Regardless, lots of "idle in transaction" connections that stick around a
long time is a clear sign of application bugs. If they're not the cause
of his immediate problem, they will be the cause of problems at some point,
so he might as well track them down and fix them.
--
PT <wmoran@potentialtech.com>
