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

adrien nayrat adrien.nayrat.axess at gmail.com
Mon May 19 23:15:10 JST 2014


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?

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

Here are an extract of the settings in the config fle :

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Pool size -

num_init_children = 32
max_pool = 4

child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0

#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off

#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = off

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = off

#------------------------------------------------------------------------------
# PARALLEL MODE
#------------------------------------------------------------------------------
parallel_mode = off

#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = off

#------------------------------------------------------------------------------
# ON MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = off


Any idea?
Thanks ;)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140519/6dc6eef7/attachment.html>


More information about the pgpool-general mailing list