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. connection_cache (boolean) connection_cache configuration parameter 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) max_pool configuration parameter 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 and the same run-time parameters. 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) listen_backlog_multiplier configuration parameter 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 * . Some systems have the upper limit of the backlog parameter of listen() system call. See for more details. Default is 2. This parameter can only be set at server start. serialize_accept (boolean) serialize_accept configuration parameter 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 recommended to be used only with the larger values of . For the small number of , the serialize accept can degrade the performance because of serializing overhead. It is recommended to do a benchmark before deciding whether to use serialize_accept or not, because the correlation of and serialize_accept can be different on different environments. 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". When is enabled, serialize_accept has no effect. Make sure that you set 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 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) child_life_time configuration parameter Specifies the time in seconds to terminate a Pgpool-II child process if it remains idle because no client is connecting to it. 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. child_life_time does not apply to processes that have not accepted any connection yet. 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) client_idle_limit configuration parameter 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. 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 command to alter the value of this parameter for a current session. child_max_connections (integer) child_max_connections configuration parameter 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 and 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) connection_life_time configuration parameter Specifies the time in seconds to terminate the cached connections to the PostgreSQL backend. This serves as the cached connection expiration time. If a client connects to the process which holds the cached connections, a calculation on the connection_life_time is not performed until the client disconnects. Thus it is possible that the cached connections are kept for longer time than this value. To avoid this, it is recommended to set shorter value to . 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) reset_query_list configuration parameter 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. 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' "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. log_destination (string) log_destination configuration parameter Pgpool-II supports two destinations for logging the Pgpool-II messages. The supported log destinations are stderr and syslog. You can also set this parameter to a list of desired log destinations separated by commas if you want the log messages on the multiple destinations. #for example to log on both syslog and stderr log_destination = 'syslog,stderr' The default is to log to stderr only. On some systems you will need to alter the configuration of your system's syslog daemon in order to make use of the syslog option for log_destination. Pgpool-II can log to syslog facilities LOCAL0 through LOCAL7 (see ), but the default syslog configuration on most platforms will discard all such messages. You will need to add something like: local0.* /var/log/pgpool.log to the syslog daemon's configuration file to make it work. This parameter can be changed by reloading the Pgpool-II configurations. logging_collector (boolean) logging_collector configuration parameter This parameter enables the logging collector, which is a background process that captures log messages sent to stderr and redirects them into log files. It is possible to log to stderr without using the logging collector; the log messages will just go to wherever the server's stderr is directed. However, that method is only suitable for low log volumes, since it provides no convenient way to rotate log files. This parameter can only be set at the Pgpool-II start. logging_collector is not available prior to Pgpool-II V4.2. log_directory (string) log_directory configuration parameter When is enabled, this parameter determines the directory in which log files will be created. The default is /tmp/pgpool_logs. This parameter can be changed by reloading the Pgpool-II configurations. log_filename (string) log_filename configuration parameter When is enabled, this parameter sets the file names of the created log files. The value is treated as a strftime pattern, so %-escapes can be used to specify time-varying file names. The supported %-escapes are similar to those listed in the Open Group's strftimespecification. If you specify a file name without escapes, you should plan to use a log rotation utility to avoid eventually filling the entire disk. The default is pgpool-%Y-%m-%d_%H%M%S.log. This parameter can be changed by reloading the Pgpool-II configurations. log_file_mode (integer) log_file_mode configuration parameter This parameter sets the permissions for log files when is enabled. The parameter value is expected to be a numeric mode specified in the format accepted by the chmod and umask system calls. To use the customary octal format the number must start with a 0 (zero). This parameter can be changed by reloading the Pgpool-II configurations. log_rotation_age (integer) log_rotation_age configuration parameter When is enabled, this parameter determines the maximum amount of time to use an individual log file, after which a new log file will be created. If this value is specified without units, it is taken as minutes. The default is 24 hours. Set to zero to disable time-based creation of new log files. This parameter can be changed by reloading the Pgpool-II configurations. log_rotation_size (integer) log_rotation_size configuration parameter When is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files. This parameter can be changed by reloading the Pgpool-II configurations. log_truncate_on_rotation (boolean) log_truncate_on_rotation configuration parameter When is enabled, this parameter will cause Pgpool-II to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during the startup or size-based rotation. When off, pre-existing files will be appended to in all cases. For example, using this setting in combination with a like pgpool-%H.log would result in generating twenty-four hourly log files and then cyclically overwriting them. This parameter can be changed by reloading the Pgpool-II configurations. syslog_facility (enum) syslog_facility configuration parameter See also the documentation of your system's syslog daemon. When logging to syslog is enabled, this parameter determines the syslog "facility" to be used. You can choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the documentation of your system's syslog daemon. This parameter can be changed by reloading the Pgpool-II configurations. syslog_ident (string) syslog_ident configuration parameter When logging to syslog is enabled, this parameter determines the program name used to identify Pgpool-II messages in syslog logs. The default is pgpool. This parameter can be changed by reloading the Pgpool-II configurations. client_min_messages (enum) client_min_messages configuration parameter Controls which minimum message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING and ERROR. Each level includes all the levels that follow it. The default is NOTICE. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_min_messages (enum) log_min_messages configuration parameter The default is WARNING. Controls which minimum message levels are emitted to log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the levels that follow it. The default is WARNING. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_statement (boolean) log_statement configuration parameter Setting to on, prints all SQL statements to the log. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_per_node_statement (boolean) log_per_node_statement configuration parameter Similar to , except that it print the logs for each DB node separately. It can be useful to make sure that replication or load-balancing is working. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. notice_per_node_statement (boolean) notice_per_node_statement configuration parameter Similar to , except that it prints the statement logs for each DB node separately as a NOTICE message. With the default setting (that is NOTICE), the log message will be printed on client's terminal as well. This is convenient for clients because it does not need to access Pgpool-II log file. Note that unlike , does not print internal queries, (e.g., system catalog inquiry). This is because this feature is designed to be used for testing as well. As internal queries tend to be non-deterministic, printing them in testing is not helpful. For the same reason, backend process id is not printed. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_client_messages (boolean) log_client_messages configuration parameter Setting to on, prints client messages to the log. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_backend_messages (enum) log_backend_messages configuration parameter Setting to terse or verbose, prints backend messages to the log. With terse the number of same kind of messages are recorded and is printed when different kind of messages is sent. Below is an example. LOG: last DataRow message from backend 0 repeated 10 times Thus the log will not be printed if the process corresponding to the session is killed. If you want to print the log even in this case, use verbose option. Note that with the option each repeated message is printed and lots of log lines are printed. The default is none, which disables printing log messages from backend. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_hostname (boolean) log_hostname configuration parameter Setting to on, prints the hostname instead of IP address in the ps command result, and connection logs (when is on). This parameter can be changed by reloading the Pgpool-II configurations. log_connections (boolean) log_connections configuration parameter Setting to on, prints all client connections from to the log. This parameter can be changed by reloading the Pgpool-II configurations. log_disconnections (boolean) log_disconnections configuration parameter Setting to on, prints all client connection terminations to the log. This parameter can be changed by reloading the Pgpool-II configurations. log_pcp_processes (boolean) log_pcp_processes configuration parameter Setting to on, enable logging about normal PCP Process fork and exit status. Default is on. This parameter can be changed by reloading the Pgpool-II configurations. log_error_verbosity (enum) log_error_verbosity configuration parameter Controls the amount of detail emitted for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY and CONTEXT error information. This parameter can be changed by reloading the Pgpool-II configurations. You can also use command to alter the value of this parameter for a current session. log_line_prefix (string) log_line_prefix configuration parameter This is a printf-style string that is output at the beginning of each log line. % characters begin escape sequences that are replaced with information outlined below. All unrecognized escapes are ignored. Other characters are copied straight to the log line. Default is '%m: %a pid %p: ', which prints timestamp, application name and process id. Escape Effect %a Application name. The initial value for child (session process) is "child". If Clients set application name (either in the startup message or by using SET command), application name will be changed accordingly. In other types of process, application name is a hard coded string. see . %p Process ID (PID) %P Process name %t Time stamp without milliseconds %m Time stamp with milliseconds %d Database name %u User name %l Log line number for each process %% '%' character Process type application name main main child child streaming replication delay check worker sr_check_worker watchdog heart beat sender heart_beat_sender watchdog heart beat receiver heart_beat_receiver watchdog watchdog watchdog life check life_check follow primary child follow_child watchdog utility watchdog_utility pcp main pcp_main pcp child pcp_child health check process health_check%d (%d is replaced with backend node id) logger process logger (Note that the application name "logger" will not be output to the log file managed by logger process) This parameter can be changed by reloading the Pgpool-II configurations.