3

I've set up PGBouncer and configured it to connect to my postgres DB and it all connects just fine, however I'm not sure if its actually working.

I have a php script that runs as a daemon and picks up beanstalk jobs. The problem is for every distinct user/action on the system it opens a new connection to postgres and then leaves that connection idling because the daemon doesn't actually stop running so the connection is never terminated (a quick fix for this was to reset the connection at the end of the script loop, but that is going to be inefficient with many connects).

Anyway, this caused postgres to eventually run out of connections and lock up...

So PGBouncer seems like the answer.

But now when I run it, I see the same db connection multiple times when i do ps ax | grep postgres.

Isn't PGBouncer supposed to only ever have 1 connection open to the DB and route all traffic through that connection? Then open a new connection if that one is full?

At present I have 3 for one db connection (my access control system) and 2 for the other database (my client specific data).

To me, it feels like if I roll out these changes then I will just be faced with the same problem that the connections will just get eaten up again because they're not being released.

I hope that explains enough for someone to offer any advice.

TheStoneFox
  • 3,007
  • 3
  • 31
  • 47

1 Answers1

3

It sounds like an important step here is to fix the scripts so they release the connections when they're done. Once you do that, PgBouncer will help reduce the connection setup/teardown overhead, but in connection pooling mode it won't give you the ability to maintain more connections to Pg than you otherwise could.

However, you can also use PgBouncer in transaction-pooling mode. When used for transaction pooling, PgBouncer keeps a pool of idle backend transactions and only assigns them when a client does a BEGIN. Connections are returned to the pool after the client does a COMMIT or ROLLBACK. That means that in transaction pooling mode you can have large numbers of open connections to PgBouncer; they don't each need a corresponding connection to a PostgreSQL backend, so long as most of them are idle at any point in time and don't have any transaction open.

The only real downside of transaction pooling mode is that it breaks applications that expect to SET session-level variables, keep prepared statements across transactions, etc. Applications may need modification, e.g. to use SET LOCAL.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • The problem is because the "beanstalk workers" open a connection and sit in a loop waiting, the instance is never terminated. The underlying code stores the db handler in a static variable so it doesn't get recreated for every connection. But because the worker instance is never terminated then this variable is never reset and therefore the connection is never closed. I did add reset code to the worker so it would close the connection (and it does just fine) but now connections are open and closed for every worker request, which makes me sad :( – TheStoneFox Nov 14 '12 at 08:21
  • Craig, clarifying the excerpt about breaking applications that expect to `set` session-level variables, are you suggesting that, for example in a Django app, the ability to use the `request.session` dictionary to store and use variables (e.g. `request.session["foo"] = bar`) will be compromised? – Hassan Baig Sep 21 '17 at 18:20
  • @HassanBaig No, it relates to SQL-level `SET` – Craig Ringer Sep 22 '17 at 01:02