[pgpool-general: 2070] PgPool Hang Issue

[Özhan Karaman]

Hi;
We are using pgpool II (3.2.5), memcached(1.2.6-5.17.1) and libmemcached(1.0.17) on updated SLES 11 SP2 server which is connected to Master/Slave 3 node Postgresql 9.1.9 Cluster. We use Php5+Apache 2.4 as application server.

Caching and Master/Slave Setup works good but sometimes we notice that Pgpool hangs and does not answer any query its receives them but do not response.. When we check the logs there are millions of "delete_cache_on_memcached: NOT FOUND" warning on Pgpool log. 

Aug 23 09:18:25 host6 pgpool[3542]: delete_cache_on_memcached: NOT FOUND

The only solution at this point is stop/start the application servers(stop/start client), then Pgpool continues to work without restart.

I checked pgpool server to see whats happening on server "ps -ef|grep SELECT" and i saw that there are active selects on Pgpool server then i check Postgresql Cluster side to see if there are some active SELECT's there but there are not any active selects on Postgresql Cluster. Somehow Pgpool hanged. It gets queries from clients but does not send them to Postgresql maybe there is a problem on memcached stack.

So from this point how could i solve this problem, where should i need to check on Pgpool. I checked PgPool logs to see if there is an special error line but there are only some error lines from Postgresql backends conflict recovery from streaming replication nothing special.

Regards
Özhan

##############################


listen_addresses = '192.168.0.1'
port = 5437
socket_dir = '/tmp'
# - pgpool Communication Manager Connection Settings -
pcp_port = 9898
pcp_socket_dir = '/tmp'

backend_hostname0 = '192.168.1.1'
backend_port0 = 5432
backend_weight0 = 2 
backend_data_directory0 = '/u02/pgdata91'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.2'
backend_port1 = 5432
backend_weight1 = 30
backend_data_directory1 = '/u02/pgdata91'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '192.168.1.3'
backend_port2 = 5432
backend_weight2 = 30
backend_data_directory2 = '/u02/pgdata91'
backend_flag2 = 'ALLOW_TO_FAILOVER'

# - Authentication -
enable_pool_hba = off
pool_passwd = 'pool_passwd'
authentication_timeout = 60
# - SSL Connections -
ssl = off
#ssl_key = './server.key'
#ssl_cert = './server.cert'
#ssl_ca_cert = ''
#ssl_ca_cert_dir = ''

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

# - Pool size -
num_init_children = 64
max_pool = 2
child_life_time = 300
child_max_connections = 1000
connection_life_time = 600
client_idle_limit = 900


#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------

# - Where to log -

log_destination = 'syslog'
print_timestamp = on
log_connections = off
log_hostname = off
log_statement = off
log_per_node_statement = on
log_standby_delay = 'none'
# - Syslog specific -
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'

# - Debug -
debug_level = 0
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/var/run/pgpool.pid'
logdir = '/tmp'

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

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
# - Degenerate handling -
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off


#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------

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

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
master_slave_sub_mode = 'stream'
# - Streaming -
sr_check_period = 0
sr_check_user = 'postgres'
sr_check_password = ''
delay_threshold = 0
# - Special commands -
follow_master_command = ''


#------------------------------------------------------------------------------
# PARALLEL MODE
#------------------------------------------------------------------------------
parallel_mode = off
pgpool2_hostname = ''

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------

health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgozi91'
health_check_password = ''
health_check_max_retries = 0
health_check_retry_delay = 1

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/usr/local/bin/failover.sh %d %H'
failback_command = ''
fail_over_on_backend_error = off

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0

#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = off 
trusted_servers = ''
delegate_IP = ''
wd_hostname = ''
wd_port = 9000
wd_interval = 10
ping_path = '/bin'
ifconfig_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'
arping_path = '/usr/sbin'           # arping command path
arping_cmd = 'arping -U $_IP_$ -w 1'
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'


#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
relcache_size = 256
check_temp_table = off

#------------------------------------------------------------------------------
# ON MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = on
memqcache_method = 'memcached'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''