Pgpool-II Configuration Parameters - reserved_connections

Pgpool-II is a feature-rich PostgreSQL cluster management tool. To determine which configuration is best for your database cluster, you need to understand the purpose of the parameters. Since this blog, I will introduce several effective parameters to improve performance.

In this blog, I will explain reserved_connections parameter and how to configure this parameter.

This parameter is used to refuse the incoming client connections with error message "Sorry, too many clients already" rather than block it, when the number of client connections exceeds the value of "num_init_children - reserved_connections"

First, let me describe why this parameter is added.

Control of client connections in Pgpool-II 4.0 and earlier

At startup, Pgpool-II parent process preforks num_init_children child processes, and each child process is waiting for a client connection. If the maximum number of concurrent client connections (the value of num_init_children) has been reached, how Pgpool-II handles the new requests?

Pgpool-II will stacked up the new request in the request queue. When a child process is released and the process is ready to accept a new connection request again, OS will assign the connection request to that process. In other words, the connection request will wait for an available child process.

However, if the client connects to Pgpool-II and occupies the Pgpool-II's child process for a long time without doing anything, the OS queue will be full and new client requests will never be accepted. In addition, it may cause high server loads and unstable state.

Configure reserved_connections

Since Pgpool-II 4.1 you can use reserved_connections parameter to control the client connections. 

The default value is 0. 

reserved_connections = 0 

If reserved_connections is set to 1 or greater, the incoming client connections will be refused with error message "Sorry, too many clients already", rather than be waiting for an available process, when the number of client connections exceeds "num_init_children - reserved_connections".

For example, if we set the parameters like below:

reserved_connections = 1
num_init_children = 32

then the 32nd client request will be refused. 

We can use pgbench to verify this behavior. First, we create 31 concurrent connections to Pgpool-II.

$ pgbench -h localhost -U pengbo -p 11000 test -c 31 -T 60
starting vacuum...end.

Then the 32nd client request will be refused with the following error message. 

$ psql -h localhost -U pengbo -p 11000 test
psql: error: connection to server at "localhost" (::1), port 11000 failed: FATAL:  Sorry, too many clients already

This behavior is same as PostgreSQL.

If your server has enough processing power to handle the high load and the number of concurrent client connections rarely reaches the limit, it is recommended to set reserved_connections to 0 in order to maintain the same behavior as Pgpool-II 4.0 and earlier.

Comments

Popular posts from this blog

Query Load Balancing in Pgpool-II

Monitoring PostgreSQL Cluster via Pgpool-II with Prometheus

Connection Pooling in Pgpool-II