[pgpool-general: 5569] Re: Pgpool stops handling connections
Thomas Höll
Thomas.Hoell at neos-it.de
Thu Jun 8 18:02:00 JST 2017
Unfortunately, client_idle_limit also killed connections from the first datasource's pool...
I will leave it as ist is, since it seems to be stable right now. The developers are aware of the issue and are working to replace activiti asap.
Thanks a lot for your help so far!
Thomas
On Wed, 2017-06-07 at 16:30 +0000, Pierre Timmermans wrote:
30 seconds is surely not too agressive I would believe...
Some people use a another connection pool on top of pgpool, it makes sense if the other pool can share connections at the session level. This is really dependent on the application though, you must be sure that your application does not use session state somehow. In the java/J2EE world it is very common to have such a connection pool at the web container level. Otherwise maybe pgBouncer can also be configured in front of pgpool (with session sharing mode)
Pierre
________________________________
On Wednesday, June 7, 2017, 10:16:53 AM GMT+2, Thomas Höll <Thomas.Hoell at neos-it.de> wrote:
Hi Pierre,
thanks for the input. The problem is indeed that the activiti engine doesn't seem to close the connections properly:
[service at db1<mailto:service at db1> ~]$ psql -h XXX.XXX.XXX.XXX -p 9999 -U postgres -c "select datname, count(*) from pg_stat_activity where datname != 'postgres' group by datname order by datname";
datname | count
------------------------------+-------
activiti_xxx_production | 128
activiti_xxx_qa | 14
activiti_yyy_production | 218
activiti_yyy_qa | 10
activiti_zzz_production | 16
activiti_zzz_qa | 18
app_yyy_production | 14
app_yyy_qa | 8
app_xxx_production | 17
app_xxx_qa | 6
app_zzz_production | 8
app_zzz_qa | 8
(12 rows)
What do you suggest for client_idle_limit? Is 30 too aggressive?
Meanwhile, I will talk to the developers about the issue.
Thomas
On Wed, 2017-06-07 at 07:20 +0000, Pierre Timmermans wrote:
Hi Thomas,
You have to check in the doc to be sure but I believe that connection_life_time is not going to help you: it means that after so many seconds pgpool will "refresh" the connection (destroy & recreate). This can help avoid memory leaks. The other settings is useful if your client do not disconnect : after client_idle_limit pgpool will give back the connection to the pool (make it available for another client)
If your client application is not using a connection pool on the client side (on top of pgpool, a connection pool with sharing at the session level), then it is very important that they explicitly disconnect each time so that the connection can be re-used. You can alleviate the issue with client_idle_limit but it is not enough.
Usually having 100 concurrent connections to postgres is already a lot, you cannot scale that way,
Pierre
________________________________
On Wednesday, June 7, 2017, 7:49:32 AM GMT+2, Thomas Höll <Thomas.Hoell at neos-it.de> wrote:
Pierre,
in my case the problem probably is that I cannot determine the amount of connections the application will spawn.
The activiti engine does not utilize a connection pull, but will spawn a new connection per user and process step. So, the more users using the application,
the more connections Pgpool needs to handle in parallel.
I've talked to the developers, they try to configure activiti to use connection pooling.
Also, do you have any suggestions regarding connection_life_time and client_idle_limit? I'm quite unsure how to configure that. At the moment, both are set to 0.
Does that mean that the connection will be cached forever unless the client closes it properly?
Thomas
On Tue, 2017-06-06 at 15:57 +0000, Pierre Timmermans wrote:
Thomas
Maybe you simply need to increase the value of num_init_children. Once the pool is full, i.e. once there are num_init_children active connections, then the next connection attempt will hang until a connection is free. So it will be exactly like the behavior you are seing.
You can do a select from pg_stat_activity (connect directly to postgres, not to pgpool, otherwise you will be queued): it the number of rows is equal to num_init_children then for sure you are hitting this limit
Pierre
________________________________
On Tuesday, June 6, 2017, 4:01:43 PM GMT+2, Thomas Höll <Thomas.Hoell at neos-it.de> wrote:
Hi all,
we're having a streaming replication setup as backend for one of our client's application we're hosting.
The backend consists of two virtual machines (Fedora Server 25) running PostgreSQL 9.6 and Pgpool 3.6.4 (this also happened with previous Pgpool versions).
Those machines are configured for streaming replication in master/slave mode. The Postgres-Instances are running fine, but Pgpool is giving us troubles.
After some time, Pgpool stops accepting connections. They are not rejected, the application trying to connect simply hangs. I have to stop the corresponding Pgpool-Instance
and the other takes over. The stopping itself takes forever. After some time, systemd simply kills the processes and I have to manually delete the leftover sockets.
I have absolutely no idea what's causing this. The Postgres-Instances are configured to accept up to 1000 connections, which should not be the problem.
About the application accessing the database:
There are multiple instances, each using a separate database. The application uses Activiti, which DOES NOT pool connections. So we're having a pretty high amount of
connections per second. The problem mostly occurs during peak hours, so load is most likely a factor.
I've tried plaing around with num_init_children and max_pool. Increasing max_pool and lowering num_init_children made things a lot worse.
Also, Pgpool still accepts pool join requests from other Pgpool instances, it's just that database connections aren't handled anymore.
You can find my pgpool.conf here: https://pastebin.com/4ee2bhhf
Regards,
Thomas
--
NeosIT GmbH
Wir schaffen IT, die die Welt ein Stück einfacher macht.
Schachtweg 1
38440 Wolfsburg
Tel. +49 5361 83494-23
Fax +49 5361 83494-94
mailto:thomas.hoell at neos-it.de<mailto:christoph.steindorff at neos-it.de>
http://www.neos-it.de/
http://www.facebook.com/neositgmbh
Handelsregister: Amtsgericht Braunschweig, HRB 203557
Geschäftsführer: Sebastian Schier
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
--
NeosIT GmbH
Wir schaffen IT, die die Welt ein Stück einfacher macht.
Schachtweg 1
38440 Wolfsburg
Tel. +49 5361 83494-23
Fax +49 5361 83494-94
mailto:thomas.hoell at neos-it.de<mailto:christoph.steindorff at neos-it.de>
http://www.neos-it.de/
http://www.facebook.com/neositgmbh
Handelsregister: Amtsgericht Braunschweig, HRB 203557
Geschäftsführer: Sebastian Schier
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
--
NeosIT GmbH
Wir schaffen IT, die die Welt ein Stück einfacher macht.
Schachtweg 1
38440 Wolfsburg
Tel. +49 5361 83494-23
Fax +49 5361 83494-94
mailto:thomas.hoell at neos-it.de<mailto:christoph.steindorff at neos-it.de>
http://www.neos-it.de/
http://www.facebook.com/neositgmbh
Handelsregister: Amtsgericht Braunschweig, HRB 203557
Geschäftsführer: Sebastian Schier
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
--
NeosIT GmbH
Wir schaffen IT, die die Welt ein Stück einfacher macht.
Schachtweg 1
38440 Wolfsburg
Tel. +49 5361 83494-23
Fax +49 5361 83494-94
mailto:thomas.hoell at neos-it.de<mailto:christoph.steindorff at neos-it.de>
http://www.neos-it.de/
http://www.facebook.com/neositgmbh
Handelsregister: Amtsgericht Braunschweig, HRB 203557
Geschäftsführer: Sebastian Schier
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170608/daa3f8dd/attachment-0001.html>
More information about the pgpool-general
mailing list