1

I have several clients (FreeRadius servers) that connect to a single central Pgbouncer. When I utilise one of these FreeRadius servers I can see that several database connections are created by Pgbouncer.

select *
from pg_stat_activity
where datname = 'master_db';

enter image description here

When I utilise the same freeradius server again, pgbouncer isn't re-using the existing open connections but keeps creating new ones. Once I reach over 30 connections the whole database comes to an halt.

PGbouncer.ini

server_idle_timeout = 10
max_client_conn = 500
default_pool_size = 30

postgresql.conf: (Postgres 13)

max_connections = 150

Based on my research Pgbouncer is supposed to allocate a single database connection to a client (from the default_pool_size) and then create as many internal connections the client needs (up to max_client_conn).

But what I observe here is the opposite. What am I missing, please?

UPDATE: The solution Laurenz suggested works but throws this error, when using asyncpg behind the scenes:

NOTE: pgbouncer with pool_mode set to "transaction" or "statement" does not support prepared statements properly. You have two options: * if you are using pgbouncer for connection pooling to a single server, switch to the connection pool functionality provided by asyncpg, it is a much better option for this purpose; * if you have no option of avoiding the use of pgbouncer, then you can set statement_cache_size to 0 when creating the asyncpg connection object.

Houman
  • 64,245
  • 87
  • 278
  • 460
  • Looks like you are using session pooling mode and never close sessions. – Laurenz Albe Jun 22 '21 at 07:09
  • Thanks, would you recommend `transaction` or `statement` type of pooling? – Houman Jun 22 '21 at 07:35
  • Transaction level is the best, but then you can get problems with temporary tables, prepared statements and other things that live longer than a transaction. – Laurenz Albe Jun 22 '21 at 07:40
  • 1
    It works! Thank you so much. Since I'm using asyncpg, it threw a new error, which I posted in my question. So I ended up using Pgbouncer in transaction mode for freeradius & asyncpg pool for all python connections. This works. Is it a problem of having two pools? Or do you think I should use just Pgbouncer as per error message's alternative solution? Thanks – Houman Jun 22 '21 at 08:18
  • @Houman So did you solved this problem? – Alex Yu Aug 09 '21 at 10:30
  • @AlexYu Yes. You need to switch to transaction level, so that it closes the connection after it's done. – Houman Aug 09 '21 at 14:22

1 Answers1

0

You will need to use the session option for POOL_MODE so that it is able to maintain connection sessions opened by asyncpg because of the asynchronous nature

You should the following in your

if using pgbouncer.ini file

[pgbouncer]
pool_mode = session
...
...
...

or if using env variable

POOL_MODE=session

extra source: https://magicstack.github.io/asyncpg/current/faq.html#why-am-i-getting-prepared-statement-errors

If you are getting intermittent prepared statement "asyncpg_stmt_xx" does not exist or prepared statement “asyncpg_stmt_xx” already exists errors, you are most likely not connecting to the PostgreSQL server directly, but via pgbouncer. pgbouncer, when in the "transaction" or "statement" pooling mode, does not support prepared statements. You have several options:

if you are using pgbouncer only to reduce the cost of new connections (as opposed to using pgbouncer for connection pooling from a large number of clients in the interest of better scalability), switch to the connection pool functionality provided by asyncpg, it is a much better option for this purpose;

disable automatic use of prepared statements by passing statement_cache_size=0 to asyncpg.connect() and asyncpg.create_pool() (and, obviously, avoid the use of Connection.prepare());

switch pgbouncer’s pool_mode to session.

uberrebu
  • 3,597
  • 9
  • 38
  • 73
  • This is incorrect. Session is by default and it won't work in this scenario as discussed. The solution is `pool_mode = transaction` – Houman Sep 02 '21 at 22:28
  • had similar error and i modified from `transaction` to `session` and it worked for me...using lastest pgbouncer and lastest asyncpg....maybe versions different then – uberrebu Sep 02 '21 at 22:33
  • also read the error message `NOTE: pgbouncer with pool_mode set to "transaction" or "statement" does not support prepared statements properly.` it clearly states that `transaction` does not work...maybe you had a different issue elsewhere then – uberrebu Sep 02 '21 at 22:51
  • The Default value is session. There is no need to set it in that case. Please see the comments of Laurenz Albe under my question. The session mode never closes the connections and this is exactly my problem, because the pool runs out eventually since I have so many FreeRadius servers connecting to the same database. I want the connections to close after Freeradius is done, hence Transaction is the correct setting. I'm using it on production since July this year without any issues. – Houman Sep 03 '21 at 09:32
  • well i had set mine to `transaction` and had to change to `session`...why does it matter that default is session? whether it is default or not...the error message clearly shows what the issue is...if it was left at default then why did the error message happen complaining it should be changed to `session`??? – uberrebu Sep 03 '21 at 09:34
  • I don't think you read my comment. pool_mode = session is keeping the connections alive. They accumulate, that's the problem. – Houman Sep 03 '21 at 13:09
  • so what was the solution for you? – uberrebu Sep 03 '21 at 13:12