[pgpool-general: 6927] Re: Pgpool postgresql - Idle connections close problem

Tue Mar 10 17:04:55 JST 2020

Continued problem:I have 2 clusters - 2xpgpool + 2xpostgres (stream replication) - one production and the other test (test server has much weaker server parameters).The configuration between them differs only in the number of max connections:Production:num_init_children = 300max_pool = 5child_life_time = 600child_max_connections = 1510connection_life_time = 500client_idle_limit = 300test:num_init_children = 50max_pool = 4child_life_time = 600child_max_connections = 205connection_life_time = 500client_idle_limit = 300I did another tests with that python script from previous message and here are the results:1. I increased the number of inserts to 40. The script running into the test cluster created about 8-10 connections on the PG master server (pg_stat_activity table). I noticed that he created the same number of connections to the PG slave server (inserts ??). The running script did not create more connections but only used the existing ones (I understand that this is how connection cache works).2. I increased the number of inserts to 40. I run script multiple times into the production cluster and it created further connections on the PG master server and there could have been even 100 or more (I was afraid that I would exceed some parameter and failover) and I don't run script again. The same thing happened to the PG slave server. In this case, he didn't use connection cache ... or I don't understand something in its operation (as a reminder on the production cluster there are higher parameters num_init_children and max_connections).Conclusion:I don't know why the connecion_cache test cluster works great and I can't see it in production. I suspect it may be related to num_init_children.The production server has on average about 700-800 connections to the PG master and a similar number of connections to the PG slave (load balancing works well).I still don't understand how to best adjust the num_init_children and max_pools parameters. The production cluster supports a lot of connections (not necessarily simultaneous).I also noticed, as I mentioned in previous messages, that on PG servers there are IDLE connections much older than times specified in PGPOOL to close them (even those having 1 or 2 days).RegardsRadoslaw SzczygielTemat: [pgpool-general: 6915] Re: Pgpool postgresql - Idle connections close problemData: 2020-03-05 12:37Nadawca: "Radosław Szczygieł" &lt;radoslaw.szczygiel at interia.pl&gt;Adresat: "Bo Peng" &lt;pengbo at sraoss.co.jp&gt;; DW: "pgpool-general at pgpool.net" &lt;pgpool-general at pgpool.net&gt;; This is simple python code that make insert 10 times each time making a new connection.Every use this script make 10 connections through pgpool in postgres. I thought that using pgpool connection pooling new connections use cached connecions than close it.script:#!/usr/bin/pythonimport osimport sysimport psycopg2for x in range(1,10):&nbsp;&nbsp;&nbsp; #ustawienia polaczenia i nawiazanie polaczenia&nbsp;&nbsp;&nbsp; conn = psycopg2.connect("host=pgpool port=9999 dbname=test user=test password=test")&nbsp;&nbsp;&nbsp; cur = conn.cursor()&nbsp;&nbsp;&nbsp; sql="INSERT INTO test1 values (1,1)"&nbsp;&nbsp;&nbsp; try:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur.execute(sql)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.commit()&nbsp;&nbsp;&nbsp; except:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; print("BLAD - Nie powiodlo sie wstawianie zapytania %s" % sql)&nbsp;&nbsp;&nbsp; else:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; print("Wstawiono rekord: %s" % sql)&nbsp;&nbsp;&nbsp; conn.close()--Pozdrawiam Radosław SzczygiełTemat: Re: [pgpool-general: 6901] Pgpool postgresql - Idle connections close problemData: 2020-03-05 11:37Nadawca: "Bo Peng" Adresat: "Radosław Szczygieł" ; DW: pgpool-general at pgpool.net; &gt; Hello,
&gt; 
&gt; I want to reproduce this issue.
&gt; Could you share the test program?
&gt; 
&gt; On Wed, 04 Mar 2020 10:43:56 +0100
&gt; Radosław Szczygieł  wrote:
&gt; 
&gt; &gt; Hi Pgpool team,I have a strange problem with hanging connections in postgresql or I don't know something. On postgres in pg_stat_activity I see many idle connections with "query_start" and "state_change" time from the previous day but they should be closed after 500 seconds (pgpool.conf settings).I made a test: create simply python code with loop making connect (using psycopg2), insert and close connection.&nbsp; Each code call creates a lot of postgres connections and does not close them. I understand that "connection cache" works, but according to pgpool documentation, if the same connection is used, using the same database, username and password should use the existing connection??Did I something misunderstand of connection pooling? Why these old connections in postgresql that are longer than the times specified in pgpool.conf not closed ??My cluster is : 2 pgpool 4.1 (watchdog) + 2 postgresql 12 (master slave replication)pgpool.conf# - Concurrent session and pool size -
&gt;  num_init_children = 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Number of concurrent sessions allowed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires restart)max_pool = 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Number of connection pool caches per connection&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (change requires 
&gt;  restart)# - Life time -child_life_time = 600&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; # Pool exits after being idle for this many secondschild_max_connections = 1510&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Pool exits after receiving that many connections&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0 means no exitconnection_life_time = 500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Connection to backend closes after being idle for this many seconds&nbsp;&nbsp;&amp;
&gt;  nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0 means no closeclient_idle_limit = 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # Client is disconnected after being idle for that many seconds&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # (even inside an explicit transactions!)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0 means no disconnectionRegardsRado
&gt;  slaw Szczygiel--PozdrawiamRadosław Szczygieł
&gt; 
&gt; 
&gt; -- 
&gt; Bo Peng 
&gt; SRA OSS, Inc. Japan
&gt; 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200310/e8b5e7ed/attachment.html>