0

I recently installed a Pgbouncer because our database received lots of connexions. (web / api activities). The problem is : apparently, the pgb doesn't keep opened connexions in his pool to reuse them after. I think this is what's happen because :

  • when checking in the database (select .. from pg_stat_activity) I see that the number of connection grow with trafic, but just after a rush period it decrease a lot. (and the whole happen with the 1800 seconds I set for server_lifetime
  • when I query 'show pools' within pgbouncer database, I can see 'active sv' increase, but after working on them, I don't see lots of 'sv idle' , remaining there, until next trafic spike.

Could something explain why the connections are not kept in 'sv idle' ? how can I 'debug' / test it futherly to identify what could be wrong ?

I set the pbgouncer like this :

  • server_lifetime = 1800
  • server_idle_timeout = 1800
  • max_client_conn = 5000
  • default_pool_size = 1500
  • min_pool_size = 50
  • pool_mode = transaction

(I tried with pool_mode = session with same behaviour)

The script open connection like this :

$co = new PDO($dsn, $user, $pwd);
$co->exec('SET statement_timeout TO 10000;');
$co->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$co->setAttribute(PDO::ATTR_PERSISTENT, true);

and after querying the database, perform a PDO::closeCursor()

Enialis
  • 173
  • 1
  • 9
  • 1
    Not an answer to your question, but your settings are crazy. A pool size above a few dozen is too much. Set the minimal pool size equal to the maximal pool size to avoid fluctuation. – Laurenz Albe May 07 '21 at 16:03
  • Do you use some backend framework or using library, such as pg in nodejs or psycopg2 in python? – Alan Millirud May 07 '21 at 18:24
  • right, I should have detailed this : Developpers use Pdo with Php. They set pdo connexion with attributes 'persistent connexion' and perform a query right after opening the connexion : set statement_timeout = 10000 ; After querying the perform a closeCursor, but nothing else. – Enialis May 10 '21 at 10:29

0 Answers0