[pgpool-general: 4659] Re: Pgpool hanging

[Abel Martín]

2016-04-27 12:09 GMT+02:00 Dominic Binks <dominic.binks at audioanalytic.com>:
>
> Hi,
>
>
>
> I’m looking for some clue as to what’s going on with my configuration of pgpool.  I’m sure I’ve just got some of the parameters wrong but I’ve played for a bit and not got it working acceptably.
>
>
>
> My set up is based on AWS infrastructure:
>
>
>
> Two RDS database instances (in different regions) – one of which is nominated as primary, the other as secondary
>
> Pgpool running on hosts in each region talking to both servers
>
>
>
> In each region there are clients talking to their respective local pgpool.
>
>
>
> In order to build the infrastructure I run a few SQL scripts to initialise the database structures and load some data in to the databases.
>
>
>
> I then run our unit tests against the local pgpool instances on each of the two hosts (though not at the same time).
>
>
>
> What I find is that pgpool will service a number of connections and then hang.  I found some information on hanging and a command to dump the TCP stats which would show the number of requests queuing but I do not see this.
>
>
>
> The RDS instances have a limited number of user connections (16).  My two servers currently make 6 connections from each region (so I’m currently using 12 connections against a single RDS instance).
>
>
>
> I don’t mind if pgpool has the ability to server more connections but while it’s going into this hanging state I clearly can’t put it into production.
>
>
>
> Can anyone shed some light on why pgpool is hanging like this?  What precisely is the relationship between the number of backend connections to databases and the front end client connections?  Is it better to have the backend connections reuse existing connections or reconnect per client?
>
>
>
> Many thanks for any information you can give.
>
>
>
> Dominic
>
>
>
>

Hi,

Please, provide the following parameters of your setup:

*PGpool*
   num_init_children
   max_pool

*PostgreSQL*
   max_connections
   superuser_reserved_connections (default is 3)

Are you using streaming replication between master and slave? In that
case, what's the value of max_wal_senders?

First, you'll need satisfy the formula defined here:
http://www.pgpool.net/mediawiki/index.php/Relationship_between_max_pool,_num_init_children,_and_max_connections

Also, in order to get PGpool to serve N connections you need to
guarantee that num_init_children >= N. If your applications try to
establish N+M connections, PGpool will queue those exceeding M
connections indefinitely until the aforementioned condition is met.
You can conlude this from the definition for num_init_children in
PGpool docs:

"[...] The number of preforked pgpool-II server processes. Default is
32. num_init_children is also the concurrent connections limit to
pgpool-II from clients. If more than num_init_children clients try to
connect to pgpool-II, they are blocked (not rejected) until a
connection to any pgpool-II process is closed [...]"

Regards,
Abel