[pgpool-general: 2853] Re: PgPool connection caching/pooling not working ?

[Tatsuo Ishii]

> Hello,
> 
> We have one postgres server and pgpool installed on the same server.
> Postgres (9.2) : listen on 5432
> Pgpool (3.3.3): listen on 5000
> 
> 
> num_init_children = 32
> max_pool = 4
> Means that 32 connections will be opened to the postgres server and pgpool
> will handle 32*4 connections?

No. 32 concurrent connections.

> We have done some tests with pgbench :
> /usr/pgsql-9.2/bin/pgbench -h 10.0.0.2 -p 5000 -U postgres -c 100 -t 100;
> 
> Benchmarking options:
>   -c NUM       number of concurrent database clients (default: 1) => 100
> 128-100=28 connections remaining
>   -t NUM       number of transactions each client runs (default: 10) => 100
> 
> And if I do a "show pool_pools;"
> 
> 
>  pool_pid |     start_time      | pool_id | backend_id | database |
> username |     create_time     | majorversion | minorversion | pool_counter
> | pool_backendpid | pool_connected
> ----------+---------------------+---------+------------+----------+----------+---------------------+--------------+--------------+--------------+-----------------+----------------
>  11306    | 2014-05-19 14:29:27 | 0       | 0          | postgres |
> postgres | 2014-05-19 14:37:14 | 3            | 0            | 1
> | 13865           | 1
>  11306    | 2014-05-19 14:29:27 | 1       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  11306    | 2014-05-19 14:29:27 | 2       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  11306    | 2014-05-19 14:29:27 | 3       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  11307    | 2014-05-19 14:29:27 | 0       | 0          | postgres |
> postgres | 2014-05-19 14:37:14 | 3            | 0            | 1
> | 13853           | 1
>  11307    | 2014-05-19 14:29:27 | 1       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  11307    | 2014-05-19 14:29:27 | 2       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  11307    | 2014-05-19 14:29:27 | 3       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
> ........
> 
> 
> There is only one connection cached by pool_id=0 (pool_connected=0 for
> pool_id 1,2,3). So we have the same number of connection on pgpool side and
> of on postgres side. It seems that connection pooling isn't working ?
> During the test the pooler doesn't accept new connection (timeout).

Database "postgres", "template1" and "regression" are treated to not
cache any time. Please use other database name for connection cache
testing purpose.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp