Pgpool-II max connections

Explanation and demonstration of Pgpool-II pooling and configuration.

• This article follows article Pgpool-II-introduction.
• This article relates to Pgpool-II's pooling functionality.
• Here we detail num_init_children and max_pool behaviour.

• num_init_children

• Number of preforked Pgpool-II server child processes.

• It is also the maximum number of concurrent client connections that Pgpool-II will handle at any time.

• All new connections after num_init_children number of active concurrent connections get queued in the listen queue of Pgpool-II and eventually they can fail with connection timeout depending on client settings.

• Default 32.

• max_pool

• Maximum number of unique database + user pair connections that each child process can keep cached to the database as idle connections (after the process has facilitated those pair connections)

• These cached connections can be potentially reused, depending on database + user pair occurrences for new connections (IF a new connection uses a database + user pair that is cached AND that same child process picks up the new connection)

• max number of database connections (active & idle) = num_init_children * max_pool

• these values should be kept in line with the following formula

• num_init_children * max_pool <= (max_connections - superuser_reserved_connections) (no query canceling needed)

• Consider you have num_init_children = 3 as before, a single database mydb with user1 and user2:
• 1- Establish 3 connections with user1, then disconnect.
• This will cause all 3 processes to connect to Postgres using the pair mydb + user1, using 1 connection slot each, and that connection will be kept open to the Postgres database.
• The maximum concurrent connections reached during this test at any moment were:
• Client (frontend): 3.
• Database (backend): 3 (+1 which is the healthcheck).
• 2- Establish another 3 connections with user2, then disconnect.
• 2.1 - If max_pool = 1, then all previously established connections using user1 will be disconnected and replaced with 1 connection using user2 on each one of the 3 processes. The maximum concurrent connections reached during this test at any moment were:
• Client (frontend): 3.
• Database (backend): 3 (+1).
• 2.2 - If max_pool = 2, then each of the 3 connections established with user1 in step (1) will be kept, and new connections will be opened for the clients using user2. The maximum concurrent connections reached during this test at any moment were:
• Client (frontend): 3.
• Database (backend): 6 (+1).
• Then at this point if you disconnect one of the user2 clients and open a new connection with user1, it will be handled by one of the processes which will reuse the previously established connection using user1.

The behavior reproduced above can be corroborated by the PgPool-II wiki:

max_pool parameter configures how many connections to cache _per child_. So if
num_init_children is configured to 100, and max_pool is configured to 3, then
Pgpool-II can potentially open 300 (=3*100) connections to the backend database.

A child process opens a new backend connection only if the requested 
[user,database] pair is not already in the cache. So if the application uses
only one user to connect to only one database, say [pguser1,pgdb1], then each
child will continue to reuse the first connection and will never open a second
connection, so in effect Pgpool-II will open no more than 100 backend connections
even though max_pool is set to 3.

But if the application uses more than one pair of [user,database] connection
parameters, then each child will cache the connection it might already have for
another pair, and open a new backend connection for the requested pair.

• If your application uses a single pair database + user, then you can set max_pool = 1, as the process won't open a second connection slot.
• If on the other hand your application uses multiple database + user connections, then you can set max_pool to a higher number, knowing that on Postgres side, you can get up to num_init_children * max_pool connections at the same time.
• e.g if you want to keep the same 250 concurrent client connections but fit them into max_connections = 600, then you need to set max_pool = 2, because then 250 * 2 = 500 < 600.
• You can see all connection slots per process when connected to the Pgpool-II db: show pool_pools.

docker run -it --name pgpooltest rockylinux:8 bash
su - root
dnf -y install https://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm && \
sed -i "s@<username>:<password>@enterusernamehere:enterpasswordhere@" /etc/yum.repos.d/edb.repo && \
dnf -y install epel-release && \
dnf -y install dnf-plugins-core && \
dnf config-manager --set-enabled powertools && \
dnf -qy module disable postgresql && \
dnf -y install edb-as12-server && \
dnf -y install procps-ng net-tools && \
su - enterprisedb

/usr/edb/as12/bin/initdb -D /var/lib/edb/as12/data/ -E UTF8 --locale=C.UTF8 && \
/usr/edb/as12/bin/pg_ctl -D /var/lib/edb/as12/data/ -l logfile start && \
psql edb -c "ALTER USER enterprisedb WITH PASSWORD 'pass'" && \
psql edb -c "CREATE USER testuser WITH PASSWORD 'pass'" && \
psql edb -c "CREATE DATABASE testdb OWNER testuser" && \
psql testdb -c "CREATE TABLE testtbl (id INTEGER PRIMARY KEY)" && \
exit

dnf -y install edb-pgpool42 && \
cd /etc/sysconfig/edb/pgpool4.2/ && \
cp pgpool.conf.sample pgpool.conf && \
chown enterprisedb:enterprisedb pgpool.conf && \
sed -i "s@listen_addresses = 'localhost'@listen_addresses = '127.0.0.1'@" pgpool.conf && \
sed -i "s@backend_hostname0 = 'host1'@backend_hostname0 = '127.0.0.1'@" pgpool.conf && \
sed -i "s@backend_data_directory0 = '/var/lib/edb/as13/data'@backend_data_directory0 = '/var/lib/edb/as12/data/'@" pgpool.conf && \
sed -i "s@enable_pool_hba = off@enable_pool_hba = on@" pgpool.conf && \
sed -i "s@nobody@enterprisedb@" pgpool.conf && \
sed -i "s@sr_check_password = ''@sr_check_password = 'enterprisedb'@" pgpool.conf && \
sed -i "s@health_check_database = ''@health_check_database = 'edb'@" pgpool.conf && \
sed -i "s@health_check_password = ''@health_check_password = 'enterprisedb'@" pgpool.conf && \
sed -i "s@pcp_listen_addresses = '\*'@pcp_listen_addresses = '127.0.0.1'@" pgpool.conf && \
cp pool_hba.conf.sample pool_hba.conf && \
sed -i "s@num_init_children = 32@num_init_children = 3@" pgpool.conf && \
sed -i "s@max_pool = 4@max_pool = 2@" pgpool.conf && \
su - enterprisedb

/usr/edb/pgpool4.2/bin/pgpool -dn

# open another terminal - connect to pg pool admin db 
docker exec -it pgpooltest
su - enterprisedb
psql -d edb -p 9999 
show pool_pools;

# open another terminal - connect to edb db 
docker exec -it pgpooltest
su - enterprisedb
psql -d edb