[Pgpool-general] understanding pgpool connection settings

[Heiko L.]

Hallo *,

It seems I does not understand configuration.

Szenario:
On a client-host running 100-1000 client-prozesses.
Each client-prozess open 1 connection to SQL-Server.
Result: ca. 100-1000 connections (with same dbuser,dbname).
Up to 3 client-host can be running. (s. layout)

Naturally, if open an new connection then postgres must be fork.
Result: Postgres alloc to many memory.

Anybody say: pgpool can solved this problem.

How to configure pgpool?
(see details)
With configuration "test 4", pgpool reject all connections after any hours.

regards Heiko


-----------
- test 3

$  egrep "^(num|max|child)" pgpool.conf
num_init_children = 64  ## default 32
max_pool = 16            ## default 4
child_life_time = 300
child_max_connections = 0

- pgpool accept and forwarding connections.
  But after 20-30 sec pgpool seen accept connection and not forwarding.
The client say:

  Warning: ACL "warn" statement skipped: condition test deferred: PGSQL
connection failed: could not connect to server: 5432

  (note: postmaster running on 5433)

  - It is not enough to lower the number of Clients.
    pgpool must be restarted.

-----------
- test 4

$  egrep "^(num|max|child)" pgpool.conf
num_init_children = 128
max_pool = 16
child_life_time = 300
child_max_connections = 0

- pgpool accept and forwarding connections.
  So far seen without problems. At the moment...

- But to many connections from pgpool to postmaster.

# netstat -an | grep :5432 | wc -l
98
# netstat -an | grep :5433 | wc -l
392
  - note: each connection  is twice indicated.

  -> summary connections:   client2pgpool = 2 x pgpool2postmaster

- after any hours (3-20h) pgpool seen accept connection, but not forwarding.

- README:
   note that the total number of connections to the PostgreSQL server can
be calculated by following:
    num_init_children*max_pool

  -> Why to configure "max clienthosts" and "max connectionperclient"?

-----------
- show pool_status

  postgres $ psql -p 5432 -c 'show pool_status' template1

    print runningconfig only, but not status/statistic (ex. conn/s, clients)

  - logfile seen unused

  -> Why to print status on running pgpool without "psql "?
     ex. to logfile.

---------------------------------------------------------------------------
- layout


   ---------          ---------          ---------
   |   C   |          |   C   |          |   C   |
   | c c c |          | c c c |          | c c c |
   ---------          ---------          ---------
     | | |              |
     | | -- -------------
     | |  | |                             child_max_connections
   ----------------------------
   |                          |:5432
   | pool pool  pool pool     |           max_pool
   |  pgpool      pgpool      |           num_init_children
   ----------------------------
      |
      |
   -----------------------
   |                     |:5433
   | postgres            |
   -----------------------

  C = Client-host
  c = Client-prozess


---------------------------------------------------------------------------