Connection Pooling

pgpool-II 4.0.7 Documentation
Prev	Up	Chapter 5. Server Configuration	Next

Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version) comes in. It reduces the connection overhead, and improves system's overall throughput.

5.5.1. Connection Pooling Settings

connection_cache ( boolean )

Caches connections to backends when set to on. Default is on. However, connections to template0 , template1 , postgres and regression databases are not cached even if connection_cache is on.

You need to restart Pgpool-II if you change this value.

max_pool ( integer )

The maximum number of cached connections in each Pgpool-II child process. Pgpool-II reuses the cached connection if an incoming connection is connecting to the same database with the same user name. If not, Pgpool-II creates a new connection to the backend. If the number of cached connections exceeds max_pool, the oldest connection will be discarded, and uses that slot for the new connection.

Default value is 4. Please be aware that the number of connections from Pgpool-II processes to the backends may reach num_init_children * max_pool in total.

This parameter can only be set at server start.

listen_backlog_multiplier ( integer )

Specifies the length of connection queue from frontend to Pgpool-II . The queue length (actually "backlog" parameter of listen() system call) is defined as listen_backlog_multiplier * num_init_children .

Note: Some systems have the upper limit of the backlog parameter of listen() system call. See num_init_children for more details.

Default is 2.

This parameter can only be set at server start.

serialize_accept ( boolean )

When set to on, Pgpool-II enables the serialization on incoming client connections. Without serialization the OS kernel wakes up all of the Pgpool-II children processes to execute accept() and one of them actually gets the incoming connection. The problem here is, because so my child process wake up at a same time, heavy context switching occurs and the performance is affected.

This phenomena is a well known classic problem called "the thundering herd problem". This can be solved by the serialization of the accept() calls, so that only one Pgpool-II process gets woken up for incoming connection to execute the accept() .

But serialization has its own overheads, and it is recomended to be used only with the larger values of num_init_children . For the small number of num_init_children , the serialize accept can degrade the performance because of serializing overhead.

Note: It is recomended to do a benchmark before deciding wether to use serialize_accept or not, because the corelation of num_init_children and serialize_accept can be different on different environments.

Example 5-1. Using pgbench to decide if serialize_accept should be used

To run the pgbench use the following command.

	pgbench -n -S -p 9999 -c 32 -C -S -T 300 test

Here, -C tells pgbench to connect to database each time a transaction gets executed. -c 32 specifies the number of the concurrent sessions to Pgpool-II . You should change this according to your system's requirement. After pgbench finishes, check the number from "including connections establishing".

Note: When child_life_time is enabled, serialize_accept has no effect. Make sure that you set child_life_time to 0 if you intend to turn on the serialize_accept . And if you are worried about Pgpool-II process memory leaks or whatever potential issue, you could use child_max_connections instead. This is purely an implementation limitation and may be removed in the future.

Default is off.

This parameter can only be set at server start.

child_life_time ( integer )

Specifies the time in seconds to terminate a Pgpool-II child process if it remains idle. The new child process is immediately spawned by Pgpool-II when it is terminated because of child_life_time . child_life_time is a measure to prevent the memory leaks and other unexpected errors in Pgpool-II children.

Note: child_life_time does not apply to processes that have not accepted any connection yet.

Note: serialize_accept becomes ineffective when child_life_time is enabled.

Default is 300 (5 minutes) and setting it to 0 disables the feature.

This parameter can only be set at server start.

client_idle_limit ( integer )

Specifies the time in seconds to disconnect a client if it remains idle since the last query. This is useful for preventing the Pgpool-II children from being occupied by a lazy clients or broken TCP/IP connection between client and Pgpool-II .

Note: client_idle_limit is ignored in the second stage of online recovery.

The default is 0, which turns off the feature.

This parameter can be changed by reloading the Pgpool-II configurations. You can also use PGPOOL SET command to alter the value of this parameter for a current session.

child_max_connections ( integer )

Specifies the lifetime of a Pgpool-II child process in terms of the number of client connections it can receive. Pgpool-II will terminate the child process after it has served child_max_connections client connections and will immediately spawn a new child process to take its place.

child_max_connections is useful on a very busy server, where child_life_time and connection_life_time never gets triggered. It is also useful to prevent the PostgreSQL servers from getting too big.

The default is 0, which turns off the feature.

This parameter can only be set at server start.

connection_life_time ( integer )

Specifies the time in seconds to terminate the cached connections to the PostgreSQL backend. This serves as the cached connection expiration time.

The default is 0, which means the cached connections will not be disconnected.

This parameter can only be set at server start.

reset_query_list ( string )

Specifies the SQL commands to be sent to reset the backend connection when exiting the user session. Multiple commands can be specified by delimiting each by ";" .

The available commands differ among PostgreSQL versions. Below are some recommended settings for reset_query_list on different PostgreSQL versions. Note, however, that ABORT command should be always included.

Table 5-4. Recommended setting for reset_query_list on different PostgreSQL versions

PostgreSQL version	reset_query_list
7.1 or earlier	`'ABORT'`
7.2 to 8.2	`'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'`
8.3 or later	`'ABORT; DISCARD ALL'`

Note: "ABORT" is not issued when not in a transaction block for 7.4 or later PostgreSQL versions.

Default is 'ABORT; DISCARD ALL' .

This parameter can be changed by reloading the Pgpool-II configurations.