[pgpool-general: 2072] Re: PgPool Hang Issue

Özhan Karaman ozhankaraman at yorenet.com
Fri Aug 23 17:36:48 JST 2013


Hi Tatsuo
Do we need any extra parameter for backtrace? or only below command is enough.

Regards
Özhan

gdb pgpool /tmp/core-file.dmp



On Aug 23, 2013, at 11:24 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Can you please attach gdb to hunged pgpool and take backtrace?
> 
> In the mean time,
> 
> 1) change cache method to shmem and see if the problem happens. If
>   not, the problem is in either pgpool's code in handling memcached
>   or memcached itself.
> 
> 2) we found a bug regarding memqcache after releasing 3.2.5.
>   http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=217a7b358f2b2ad448b2ee15cc701e7f9f6e3bae
>   This may or may not be related to your problem and you may want to apply the fix.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> 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 = ''
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general



More information about the pgpool-general mailing list