Re: PgBouncer doubts

On 06/10/2014 07:37 AM, Christian Jauvin wrote:
> Hi,
>
> I'm trying PgBouncer for the first time with a simple Python web
> application (Flask + psycopg2), and I have a hard time interpreting
> the meaning of its log messages by simple googling. I'm basically
> seeing a bunch of:
>
> 2014-06-09 09:25:07.867 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432
> login attempt: db=vinum user=vinum
> 2014-06-09 09:25:07.867 20980 LOG S-0x1b38bf0:
> vinum/vinum@127.0.0.1:5432 new connection to server
> 2014-06-09 09:25:07.875 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432
> closing because: client close request (age=0)
> 2014-06-09 09:25:15.626 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432
> login attempt: db=vinum user=vinum
> 2014-06-09 09:25:16.058 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432
> closing because: client close request (age=0)
> 2014-06-09 09:25:16.762 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432
> login attempt: db=vinum user=vinum
> 2014-06-09 09:25:16.796 20980 LOG C-0x1b1b3a8: vinum/vinum@unix:6432
> login attempt: db=vinum user=vinum
> 2014-06-09 09:25:16.796 20980 LOG S-0x1b38d58:
> vinum/vinum@127.0.0.1:5432 new connection to server
> 2014-06-09 09:25:17.181 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432
> closing because: client close request (age=0)
> 2014-06-09 09:25:17.240 20980 LOG C-0x1b1b3a8: vinum/vinum@unix:6432
> closing because: client close request (age=0)
>
> I find these multiple closings with "age=0" worrying, do they mean
> that the connections are always recreated, thus never pooled?
>
> My Flask/psycopg2 connection handling code is super simple:
>
> @app.before_request
> def before_request():
>      rdc = psycopg2.extras.RealDictConnection
>      g.db = psycopg2.connect("dbname=%s user=%s port=%s" %
>                              (app.config['DATABASE_NAME'],
>                               app.config['DATABASE_USER'],
>                               app.config['PGBOUNCER_PORT']),
>                              connection_factory=rdc)
>
> @app.teardown_request
> def teardown_request(exception):
>      if hasattr(g, 'db'):
>          g.db.close()
>
> I'd greatly appreciate any explanation or guidance about this

The log is showing that the client is requesting that the connection be
closed. So my guess is that teardown_request is being run for a reason
you have not accounted for.

To make things clearer, what log are we looking at, the PgBouncer or
Postgres?

>
> Christian
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com