CODEWITHSUNDEEP
postgresql-12pgpool
Suraj Sawant
Suraj Sawant

Reputation: 39

What's the Best connections settings for pgpool-4.4.0 with PostgreSQL-12.5

I'm new on the pgpool and I really wants to work on it but just stuck with handling the connections parameters setting with postgres db.

Problem: I'm seeing a huge number of idle connections and it's degrading my DB performance also it creating problem for new clients. ERR: Too many clients etc.

Current Pgpool Setting:

process_management_mode = dynamic
process_management_strategy = gentle
num_init_children = 750
min_spare_children = 10
max_spare_children = 20
max_pool = 2

child_life_time = 5min
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 1h

Postgres connections setting:

max_connections = 750
superuser_reserved_connections = 10

Note: The above parameters has been set by someone before me I dont know what was the intention.

Observation: I observed once the new connection comes in and completed its work but after that still its showing in idle state without any reason even my idle state limit = 1hr (client_idle_limit = 1h).

So what's the point for this limit or I'm reading this wrongly can't say right now.

Questions:

  1. Is it correct connection setting for volatile production server?
  2. What is the best practice to set the connection limit.
  3. is it compulsory to set pgpool num_init_children and postgres max_conncetions same values?
  4. how max_pool affects on number of connections & the performance
  5. confused between child_life_time and connection_life_time.

Upvotes: 1

Views: 968

Answers (1)

scapy
scapy

Reputation: 145

I'm new too with pgpool but checking your settings, I would say you need to decrease the max_pool to 1, instead of 2 or change the num_init_children to 375.

max_pool (integer) The maximum number of cached connections in each Pgpool-II child process. Pgpool-II reuses the cached connection if an incoming connection is connecting to the same database with the same user name and the same run-time parameters. If not, Pgpool-II creates a new connection to the backend. If the number of cached connections exceeds max_pool, the oldest connection will be discarded, and uses that slot for the new connection.

Default value is 4. Please be aware that the number of connections from Pgpool-II processes to the backends may reach num_init_children * max_pool in total.

You can check this info about both settings: Relationship between max pool, num init children, and max connections

Hope it helps

Upvotes: 2

Related Questions