1

I 'm using Postgres 10

And I would like to limit connections via pooling. I use this syntax:

"my conexion": "Host=myhost;
Port=5432;
Database=mydb;
Username=postgres;
Password=XXX;
Persist Security Info=True;
Pooling=true;
MinPoolSize=1;
MaxPoolSize=20;
ConnectionLifeTime=15",

After deploying app with this connection string, I have more processes than expected:

postgres=# select state, count(1) from  pg_stat_activity  group by state;
 state  | count 
--------+-------
        |     5
 active |     1
 idle   |    47

MaxPoolSize=20 , so in IDLE column I should have 20 max no? What is wrong? Someone can help me?

Luis Diaz
  • 11
  • 5
  • 1
    Which programming language and pool are you using? How do you know those other connections are from your application? –  Jan 30 '20 at 12:34
  • Hello thanks for your attention. Well the programming language is C# .NET And the pool is npgsql Then, I check with ps -aux command from unix shell and I can see idle conexion for 2 databases (that is normal) the pool have been deployed for both DB ps -ef|grep postg | grep -v grep | wc -l 65 And they (developpers) just told me that they see this error: System.ArgumentException: 'Couldn't set connectionlifetime Parameter name: connectionlifetime' – Luis Diaz Jan 30 '20 at 12:55
  • Are you sure you don't have multiple client processes connecting to this database? Because the Npgsql pooling setting only holds for that specific process (the pool is internal, in-process). – Shay Rojansky Jan 30 '20 at 13:44
  • Hello Process can be defined by Port=5432 and Database=mydb write? – Luis Diaz Jan 30 '20 at 14:43
  • I have this: "ConnectionStrings": { "db1": "Host=myhost;Port=5432;Database=db1;Username=user1;Password=XXXXXX;Persist Security Info=True;Pooling=true;MinPoolSize=1;MaxPoolSize=20", "db2": "Host=myhost;Port=5432;Database=db2;Username=user2;Password=XXXXXX;Persist Security Info=True"Pooling=true;MinPoolSize=1;MaxPoolSize=20 } And only this 2 database connects – Luis Diaz Jan 30 '20 at 15:14
  • Then I check from psql: idle | StoreBillbackRequestHangFire | 21 idle | BillbackRequestHangFire | 52 This one BillbackRequestHangFire looks wrong, here is the conexion string: "HangfireConnection": "Host=myhost;Port=5432;Database=BillbackRequestHangFire;Username=postgres;Password=xxx;Persist Security Info=True;Pooling=true;MinPoolSize=1;MaxPoolSize=20" – Luis Diaz Jan 30 '20 at 15:25
  • Lock at the other columns in pg_stat_activity to figure out where the connections are coming from. – jjanes Jan 30 '20 at 15:58
  • Hello. For example application_name and client_hostname ? state | datname | application_name | client_hostname | count --------+------------------------------+------------------+-----------------+------- idle | StoreBillbackRequestHangFire | | | 43 idle | billback | | | 2 idle | StoreBillbackRequest | | | 3 idle | BillbackRequestHangFire | | | 22 Empty... – Luis Diaz Jan 31 '20 at 08:48
  • Hello. The idle process in the server never destroyed. Is this a "normal" behavior of postgres db? – Luis Diaz Jan 31 '20 at 08:50
  • Hello With this query: select datname, client_addr, count(1) from pg_stat_activity group by datname,client_addr; I have this output: datname;client_addr;count BillbackRequestHangFire;10.50.34.41;22 billback;10.50.34.41;2 StoreBillbackRequestHangFire;10.50.34.41;22 BillbackRequestHangFire;172.19.19.137;22 StoreBillbackRequest;10.50.34.41;3 postgres;172.17.100.38;1 StoreBillbackRequestHangFire;172.19.19.59;20 StoreBillbackRequest;172.19.19.59;1 – Luis Diaz Jan 31 '20 at 09:14
  • Seem to be OK,no? if we consider that pooling apply to the couple: IP/DB...I'll change pooling to max pool= 10 to see the new behavior – Luis Diaz Jan 31 '20 at 09:19
  • yes it's Ok, thanks for all your advises. I see that conexion are well ordered, by IP we can see the repartition. I change max pool size to 30 and I have this: StoreBillbackRequestHangFire 10.50.34.41 26 StoreBillbackRequestHangFire 172.19.19.59 24 Many thanks and have a nice week end ! – Luis Diaz Jan 31 '20 at 10:39

0 Answers0