Re: Very slow planning performance on partition table

On Tue, Jul 29, 2014 at 1:21 AM, Rural Hunter <ruralhunter(at)gmail(dot)com> wrote:

> More information found. After the hang connection appears, I noticed there
> were several hundreds of connections of the same user. Since I use
> pgbouncer and I only set the pool size to 50 for each user, this is very
> strange. I checked the pgbouncer side, 'show pools' showed the active
> server connection count is less than 50(only 35 actually). I also checked
> the client port which is shown in pg process list. It is not used at
> pgbouncer side when I did the check. So I stopped pgbouncer then the
> connection count from the user drops slowly. Finally all those connections
> disappeared. After that I restarted pgbouncer and it looks good again.
> With this solution, I at least don't have to kill pg when the problem
> happens. But anyone has a clue why this happens?

It sounds like someone is bypassing your pgbouncer and connecting directly
to your database. Maybe they tried to create their own parallelization and
have a master connection going through pgbouncer and create many auxiliary
connections that go directly to the database (probably because pgbouncer
wouldn't let them create as many connections as they wanted through it).
That would explain why the connections slowly drain away once pgbouncer is
shut down.

Can you change your pg_hba.conf file so that it only allows connections
from pgbouncer's IP address? This should flush out the culprit pretty
quickly.

Cheers,

Jeff