|
|
(One intermediate revision by the same user not shown) |
Line 1: |
Line 1: |
− | __NOTITLE__
| |
| | | |
− | = 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.
| |