Difference between revisions of "Documentation/Relationship between max pool num init children and max connections"

From pgpool Wiki
Jump to: navigation, search
m
(Blanked the page)
 
Line 1: Line 1:
= Relationship between max_pool, num_init_children, and max_connections =
 
  
Gurjeet Sing (last updated: 22 August 2012)
 
 
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 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 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.
 
 
For eg., if the application now uses these 4 pairs: [user1,db1]
 
[user1,db2] [user2,db1] [user2,db2] to connect to pgpool, then each child
 
process can cache up to 3 connections for the first 3 different pairs it
 
receives connection requests for. But as soon as it receives a request for
 
the 4th pair that it does not yet have a connection for, then it will
 
disconnect the oldest connection in the cache and open a new connection for
 
the 4th pair.
 
 
As we already know that there's no guarantee as to which child process
 
will handle an incoming connection request, max_pool tries to improve the
 
performance a little bit by caching connections of different pairs, in the
 
hopes that an incoming connection request might match one of the
 
connections cached by the child process. But this also causes an explosion
 
in the number of connections that pgpool would request from the database.
 
 
So, in order to guarantee that the application connection requests are
 
never rejected, and that the connection requests wait until a database
 
connection is available, the following condition should be met:
 
 
max_pool*num_init_children <= (max_connections - superuser_reserved_connections)
 
 
If the application uses superuser connections (which is not
 
recommended), then the condition is reduced to:
 
 
max_pool*num_init_children <= max_connections
 
 
Setting max_pool to 1 will guarantee that the number of database
 
connections opened by pgpool child processes never exceeds the
 
num_init_children value. If for performance reasons, as explained above,
 
you do wish to set max_pool to more than 1, then max_connections will also
 
have to be increased accordingly so that application connection requests do
 
not get denied.
 

Latest revision as of 00:51, 25 August 2012