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

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: # ...
Read more

Authentication in Pgpool-II

-
Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authen...
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize...
Read more