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

Lazáro Rubén García Martínez lgarciam at vnz.uci.cu
Sat Jun 16 14:31:18 JST 2012


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.

I'm using postgresql 9.0.8 and pgpool-II 3.1.3

This is my pgpool.conf setting:

listen_addresses = '*'

port = 9999

socket_dir = '/tmp'

pcp_port = 9898

pcp_socket_dir = '/tmp'

backend_hostname0 = '10.4.15.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.4.15.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

enable_pool_hba = on
authentication_timeout = 30

ssl = on
ssl_key = '/opt/pgpool/etc/server.key'
ssl_cert = '/opt/pgpool/etc/server.crt'

num_init_children = 100
max_pool = 2

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

log_destination = 'stderr'
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = off
log_per_node_statement = off
log_standby_delay = 'always'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/var/log/pgpool'

connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'

replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off

load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'

master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'pgpoolmon'
sr_check_password = ''
delay_threshold = 1000

follow_master_command = ''

parallel_mode = off
enable_query_cache = off
pgpool2_hostname = ''

system_db_hostname  = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''

health_check_period = 30
health_check_timeout = 10
health_check_user = 'pgpoolmon'
health_check_password = ''

failover_command = '/opt/pgpool/bin/failover_stream.sh %d %P %H /tmp/trigger_file0'
failback_command = ''
fail_over_on_backend_error = on

recovery_user = 'pgpoolrec'
recovery_password = 'pgpoolrec'
recovery_1st_stage_command = 'basebackup.sh'
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = -1

relcache_expire = 0

Is there any way to solve this problem?


Regards and thank you very much for your time.


Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool_.rar
Type: application/octet-stream
Size: 142277 bytes
Desc: pgpool_.rar
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120616/d5851657/attachment-0001.obj>


More information about the pgpool-general mailing list