[Pgpool-general] Setting up connections config for load-balancing

[Tatsuo Ishii]

> All,
> 
> I have two servers which are being replicated by Slony-I.  I intend to
> use pgpool2 to do load-balancing between them.  The client application
> will request up to 600 connections, most of which are idle most of the
> time, but most of which are fairly long-lived even if they spend hours
> idle.  95% of connections are the same user/database.
> 
> Given this, how should I configure:
> num_init_children

If you want to allow all 600 connections conncted at the same time,
num_init_children must be greater or equal to 600. Of course
max_connections of your PostgreSQL must be greater or equal to
(600+superuser_reserved_connections). If you are planning to use
health checking, you should add one more to max_connections.

> max_pool

In this case max_pool = 1 is enough. You could increase it for
efficiency reason (keeping connection cache for your 5% connections),
you have to increase max_connections as well. But I dought it worth...

> child_max_connections

Not relevant to maxium connections to pgpool-II nor PostgreSQL. This
is used to avoid possible resource leaking in PostgreSQL.

> ... and more importantly, why?

pgpool-II forks num_init_children child processes and each process
accepts 1 connection from clients at the same time. Thus you need
about 600 num_init_children if you want to allow 600 concurrent
connections from clients at the same time.

> Also, the instructions on master/slave mode have this bit of information:
> 
> DB nodes' information must be set as the replication mode.
> 
> ... but where or how to set that isn't explained.  Please explain?

"DB nodes' information" means backend_hostname, backend_port,
backend_weight. It just says you need those information in
pgpool.conf. Yeah, the wording is a little bit poor:-) I would like to
change like this. Comments?

DB nodes' information(backend_hostname, backend_port,
backend_weight, backend_data_directory, only if you need to do the
online recovery) must be set as the replication mode.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp