[pgpool-general: 642] Re: pool functions seems not working properly

[Tatsuo Ishii]

> Hello every one in the list. Acording to this question written in the FAQ, and treated in past mails:
> 
> Why max_connection must satisfy this formula max_connection >= (num_init_children * max_pool) and not max_connection >= num_init_children?
> 
>     Probably you need to understand how pgpool uses these variables. Here is internal processing inside pgpool.
> 
>     1. Wait for connection request from clients.
>     2. pgpool child receives connection request from a client.
>     3. The pgpool child looks for existing connection in the pool which has requested database/user pair up to max_pool.
>     4. If found, reuse it.
>     5. If not found, opens a new connection to PostgreSQL and registers to the pool. If the pool has no empty slot, closes the oldest connection to PostgreSQL and reuse the slot.
>     6. Do some query processing until the client sends session close request.
>     7. Close the connection to client but keeps the connection to PostgreSQL for future use.
>     8. Go to #1
> 
> It says that connection existing in the pool are reused; but for my enviroment it seems not working properly, attached is a report that confirm this behavior from pgpoolAdmin.

Yes, pgpool is working as expected in your environment. Probably you
think that the connection pool is shared among all pgpool children in
#3, right? Actually the connection pool is in pgpool's process private
memory and is not shared by other pgpool.

Suppose pgpool process 12345 has connection cache for database A/user
B but process 12346 does not have connection cache for database A/user
B and both 12345 and 12346 are in idle state(no client is connecting
at this point). If client connects to pgpool process 12345 with
database A/user B, then the exisiting connection of 12345 is
reused. On the other hand, If client connects to pgpool process 12346,
12346 needs to create new connection.  Whether 12345 or 12346 is
chosen, is not under control of pgpool. However in the long run, each
pgpool child process will be equally chosen and it is expected that
each process's pool will be resued equally.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp