<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<html><body><div><span class="tlid-translation translation" lang="en"><span title="" class="">Continued problem:</span><br><span title="" class="">I have 2 clusters - 2xpgpool + 2xpostgres (stream replication) - one production and the other test (test server has much weaker server parameters).</span><br><span title="" class="">The configuration between them differs only in the number of max connections:</span><br><br><span title="">Production:</span><br><span title="">num_init_children = 300</span><br><span title="">max_pool = 5</span><br><span title="">child_life_time = 600</span><br><span title="">child_max_connections = 1510</span><br><span title="">connection_life_time = 500</span><br><span title="">client_idle_limit = 300</span><br><br><span title="">test</span><span title="">:</span><br><span title="">num_init_children = 50</span><br><span title="">max_pool = 4</span><br><span title="">child_life_time = 600</span><br><span title="">child_max_connections = 205</span><br><span title="">connection_life_time = 500</span><br><span title="">client_idle_limit = 300</span><br><br><span title="">I did another tests with that python script from previous message and here are the results:</span></span><br><br>1. <span class="tlid-translation translation" lang="en"><span title="" class="">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).</span> <span title="">I noticed that he created the same number of connections to the PG slave server (inserts ??).</span> <span title="" class="">The running script did not create more connections but only used the existing ones (I understand that this is how connection cache works).<br></span></span><br>2. <span class="tlid-translation translation" lang="en"><span title="">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.</span> <span title="">The same thing happened to the PG slave server.</span> <span title="" class="">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).<br><br></span></span><br><span class="tlid-translation translation" lang="en"><span title="" class=""><span class="tlid-translation translation" lang="en"><span title="" class="">Conclusion:<br></span></span></span></span><br><span class="tlid-translation translation" lang="en"><span title="" class=""><span class="tlid-translation translation" lang="en"><span title="">I don't know why the connecion_cache test cluster works great and I can't see it in production.</span> <span title="">I suspect it may be related to num_init_children.</span><br><span title="">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).</span><br><span title="">I still don't understand how to best adjust the num_init_children and max_pools parameters.</span> <span title="">The production cluster supports a lot of connections (not necessarily simultaneous).</span><br><span title="" class="">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).<br><br>Regards<br></span></span></span></span>Radoslaw Szczygiel<br><br><div class="inpl-collapsed">Temat: [pgpool-general: 6915] Re: Pgpool postgresql - Idle connections close problem<br>Data: 2020-03-05 12:37<br>Nadawca: "Radosław Szczygieł" <radoslaw.szczygiel@interia.pl><br>Adresat: "Bo Peng" <pengbo@sra<a href="http://oss.co.jp" target="_blank">oss.co.jp</a>>; <br>DW: "pgpool-general@pgpool.net" <pgpool-general@pgpool.net>; <br><br><blockquote style="margin: 0px; padding-left: 1ex; border-left: 1px solid #CCCCCC;" data-mce-style="margin: 0px; padding-left: 1ex; border-left: 1px solid #CCCCCC;"><div>This is simple python code that make insert 10 times each time making a new connection.<br>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.<br><br>script:<br><br>#!/usr/bin/python</div><div>import os<br>import sys<br>import psycopg2</div><div>for x in range(1,10):<br> #ustawienia polaczenia i nawiazanie polaczenia<br> conn = <a href="http://psycopg2.connect" data-mce-href="http://psycopg2.connect">psycopg2.connect</a>("host=<u><span style="color: #000120;" data-mce-style="color: #000120;"><a href="http://pgpool.imgw.ad" data-mce-href="http://pgpool.imgw.ad">pgpool</a> </span></u>port=9999 dbname=test user=test password=test")<br> cur = <a href="http://conn.cursor" data-mce-href="http://conn.cursor">conn.cursor</a>()<br> sql="INSERT INTO test1 values (1,1)"<br> try:<br> <a href="http://cur.execute" data-mce-href="http://cur.execute">cur.execute</a>(sql)<br> <a href="http://conn.commit" data-mce-href="http://conn.commit">conn.commit</a>()<br> except:<br> print("BLAD - Nie powiodlo sie wstawianie zapytania %s" % sql)<br> else:<br> print("Wstawiono rekord: %s" % sql)<br> <a href="http://conn.close" data-mce-href="http://conn.close">conn.close</a>()<br></div><br><br>--<br>Pozdrawiam <br>Radosław Szczygieł<div class="inpl-collapsed">Temat: Re: [pgpool-general: 6901] Pgpool postgresql - Idle connections close problem<br>Data: 2020-03-05 11:37<br>Nadawca: "Bo Peng" <br>Adresat: "Radosław Szczygieł" ; <br>DW: pgpool-general@pgpool.net; <br><br><blockquote class="nxt-original-message-notrevealed" style="margin: 0px; padding-left: 1ex; border-left: 1px solid #CCCCCC;" data-mce-style="margin: 0px; padding-left: 1ex; border-left: 1px solid #CCCCCC;"><pre>> Hello,
>
> I want to reproduce this issue.
> Could you share the test program?
>
> On Wed, 04 Mar 2020 10:43:56 +0100
> Radosław Szczygieł wrote:
>
> > 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. 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 -
> num_init_children = 300 # Number of concurrent sessions allowed # (change requires restart)max_pool = 5 # Number of connection pool caches per connection # (change requires
> restart)# - Life time -child_life_time = 600 # Pool exits after being idle for this many secondschild_max_connections = 1510 # Pool exits after receiving that many connections # 0 means no exitconnection_life_time = 500 # Connection to backend closes after being idle for this many seconds &
> nbsp; # 0 means no closeclient_idle_limit = 300 # Client is disconnected after being idle for that many seconds # (even inside an explicit transactions!) # 0 means no disconnectionRegardsRado
> slaw Szczygiel--PozdrawiamRadosław Szczygieł
>
>
> --
> Bo Peng
> SRA OSS, Inc. Japan
> </pre><br></blockquote><br><br></div><br></blockquote><br><br></div></div><style type="text/css">#icselection13 {width: 100% !important; height: 100% !important; padding-top: 0px !important; overflow: visible !important;}</style><style type="text/css">#icselection13 {width: 100% !important; height: 100% !important; padding-top: 0px !important; overflow: visible !important;}</style><style type="text/css">#icselection13 {width: 100% !important; height: 100% !important; padding-top: 0px !important; overflow: visible !important;}</style><style type="text/css">#icselection13 {width: 100% !important; height: 100% !important; padding-top: 0px !important; overflow: visible !important;}</style></body></html>