[Pgpool-general] understanding pgpool connection settings

[Yoshiyuki Asaba]

Hi,

Sorry for the delay.

From: "Heiko L." <heikol at fh-lausitz.de>
Subject: [Pgpool-general] understanding pgpool connection settings
Date: Mon, 5 Nov 2007 07:41:46 +0100 (CET)

> 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.

As you know, postmaster calls fork(2) when it accepts a new
connection. pgpool is pre-fork architecture. So there is no penalty.

num_init_children is the number of child processes. One process can
handle one active session.

  client-1 <--> pgpool-1
  client-2 <--> pgpool-2
  ...


If all children are busy, a new client can't connect. Because no
process can accept a connection.


Each process has connection cache slot. Its size is set by
max_pool. For example, if max_pool is 3, a process holds three caches
which are identified by user name and database name.

          | <---> PostgreSQL (user: x, database: y)
  pgpool-1| <---> PostgreSQL (user: x, database: z)
          | <---> PostgreSQL (user: y, database: z)

So max_connections in postgresql.conf need to be equal or greater than
num_init_children * max_pool.


> Anybody say: pgpool can solved this problem.
> 
> How to configure pgpool?
> (see details)
> With configuration "test 4", pgpool reject all connections after any hours.

Do you use persistent connection (pconnect() in PHP)?

Regards,
--
Yoshiyuki Asaba
y-asaba at sraoss.co.jp


> 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
> 
> 
> ---------------------------------------------------------------------------
> 
> 
> 
> 
> 
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general