[Pgpool-general] [pgpool problem] about streaming replication and load balancing.

Fri Nov 18 05:03:46 UTC 2011

Look like this is not same as what you said in your previous mail:

>> > backend_hostname0='192.168.3.141'
>> > backend_weight0 = 1     # but not changed 'backend_weight0'
>> > backend_hostname1='192.168.3.160'
>> > backend_weight1 = 1000    # not changed 'backend_weight1' too.
>> > ...
>> > )
>> >
>> > then pgpool has always connected backend_hostname0(192.168.3.141).

192.168.3.141 is hostname1, not hostname0(see below). And because
weight of hostname1 is very large comparing with hostname0, it is most
likely 192.168.3.141 which receives all queries.

2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_hostname0
2011-11-18 13:23:05 DEBUG: pid 16892: value: '192.168.3.160' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_port0
2011-11-18 13:23:05 DEBUG: pid 16892: value: 5432 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: port slot number 0
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_weight0
2011-11-18 13:23:05 DEBUG: pid 16892: value: 1 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: weight slot number 0
weight: 1.000000
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_data_directory0
2011-11-18 13:23:05 DEBUG: pid 16892: value: '/DbCenter_master/data' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_flag0
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'ALLOW_TO_FAILOVER' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: extract_string_tokens: token:
ALLOW_TO_FAILOVER
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: allow_to_failover on
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: slot number 0 flag: 0000
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_hostname1
2011-11-18 13:23:05 DEBUG: pid 16892: value: '192.168.3.141' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_port1
2011-11-18 13:23:05 DEBUG: pid 16892: value: 5432 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: port slot number 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_weight1
2011-11-18 13:23:05 DEBUG: pid 16892: value: 1000 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: weight slot number 1
weight: 1000.000000

> thank you for help.
> this is it.
> 
> ------------------------------
> --------------------------------------------------------------------------------
> $ pgpool -d -n
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: listen_addresses
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: '*' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: port
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 9999 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: socket_dir
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: '/tmp' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: pcp_port
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 9898 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: pcp_socket_dir
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: '/tmp' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_hostname0
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: '192.168.3.160' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_port0
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 5432 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: port slot number 0
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_weight0
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 1 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: weight slot number 0
> weight: 1.000000
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_data_directory0
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: '/DbCenter_master/data' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_flag0
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 'ALLOW_TO_FAILOVER' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: extract_string_tokens: token:
> ALLOW_TO_FAILOVER
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: allow_to_failover on
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: slot number 0 flag: 0000
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_hostname1
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: '192.168.3.141' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_port1
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 5432 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: port slot number 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_weight1
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 1000 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: weight slot number 1
> weight: 1000.000000
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_flag1
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 'ALLOW_TO_FAILOVER' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: extract_string_tokens: token:
> ALLOW_TO_FAILOVER
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: allow_to_failover on
> 2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: slot number 1 flag: 0000
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: enable_pool_hba
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: authentication_timeout
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 60 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: ssl
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: off kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: num_init_children
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 32 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: max_pool
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 4 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: child_life_time
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 300 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: child_max_connections
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: connection_life_time
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: client_idle_limit
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: log_destination
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 'syslog' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: print_timestamp
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: log_connections
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: log_hostname
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: log_statement
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: log_per_node_statement
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: log_standby_delay
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 'if_over_threshold' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: syslog_facility
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 'LOCAL0' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: syslog_ident
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 'pgpool' kind: 4
> 2011-11-18 13:23:05 DEBUG: pid 16892: key: debug_level
> 2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
> pid file found but it seems bogus. Trying to start pgpool anyway...
> 
> --------------------------------------------------------------------------------------------------------------
> what do you have something idea about it?
> 
> 良かったら、irc://irc.freenode.net/pgpool で、相談させて頂きたいですが・・・
> 
> 
> 
> 
> 2011/11/16 Tatsuo Ishii <ishii at sraoss.co.jp>
> 
>> Can you show me debug out? I mean run pgpool with "-d -n" option.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > yes, i am sure.
>> >
>> > I tried it)
>> >
>> > I tried a test how to change backend_hostname0 and backend_hostname1.
>> > (like as
>> > ..
>> > backend_hostname0='192.168.3.141'
>> > backend_weight0 = 1     # but not changed 'backend_weight0'
>> > backend_hostname1='192.168.3.160'
>> > backend_weight1 = 1000    # not changed 'backend_weight1' too.
>> > ...
>> > )
>> >
>> > then pgpool has always connected backend_hostname0(192.168.3.141).
>> >
>> > i think that this mean is 'can not reading pgpool load balancing' or 'can
>> > not reading PG server after backend_hostname0'.
>> >
>> > what do you have a idea about something please?
>> >
>> >
>> > 2011/11/11 Martín Marqués <martin.marques at gmail.com>
>> >
>> >> Are you sure that pgpool can connect to the second PG server?
>> >>
>> >> 2011/11/10 イジュヨン <elijah9 at gmail.com>:
>> >> > hello, developers and anybody using pgpool.
>> >> >
>> >> > i have a something problem about 'streaming replication' and 'load
>> >> > balancing'.
>> >> > i don't know what mail address for to ask like this problem. then i
>> send
>> >> > this asking mail to you.
>> >> >
>> >> > (if i mistake this doing, please teach me that i can ask mailling
>> list.)
>> >> >
>> >> > well, my question is this)
>> >> >
>> >> > <server spac>
>> >> >  - my 1st postgresql server(os:ubuntu10.4, ip:192.168.3.160,
>> postgresql
>> >> > version 9.1) run normal operations.
>> >> >
>> >> >    (i checked that requested query success by use log)
>> >> >
>> >> >  - my 2nd postgresql server(os:ubuntu10.4, ip:192.168.3.141,
>> postgresql
>> >> > version 9.1) run normal operations too.
>> >> >    (i checked it too.)
>> >> >
>> >> >  - my pgpool server(ip:192.168.3.160 - with 1st postgresql server,
>> pgpool
>> >> > version 3.1 -
>> >> >
>> >>
>> http://ftp2.jp.postgresql.org/pub/postgresql/projects/pgFoundry/pgpool/pgpool-II-3.1.tar.gz
>> >> )
>> >> > run normal operations too.
>> >> >
>> >> >    (i checked that requested query success by use log too.)
>> >> >
>> >> >
>> >> > <pgpool conf information>
>> >> > # cat pgpool.conf
>> >> > listen_addresses = '*'
>> >> > port = 9999
>> >> > socket_dir = '/tmp'
>> >> > pcp_port = 9898
>> >> >
>> >> > pcp_socket_dir = '/tmp'
>> >> > backend_hostname0 = '192.168.3.160'
>> >> > backend_port0 = 5432
>> >> > backend_weight0 = 1
>> >> > backend_data_directory0 = '/DbCenter_master/data'
>> >> > backend_flag0 = 'ALLOW_TO_FAILOVER'
>> >> >
>> >> > backend_hostname1 = '192.168.3.141'
>> >> > backend_port1 = 5432
>> >> > backend_weight1 = 1000
>> >> > backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> > enable_pool_hba = on
>> >> > authentication_timeout = 60
>> >> > ssl = off
>> >> > num_init_children = 32
>> >> >
>> >> > max_pool = 4
>> >> > child_life_time = 300
>> >> > child_max_connections = 0
>> >> > connection_life_time = 0
>> >> > client_idle_limit = 0
>> >> > log_destination = 'syslog'
>> >> > print_timestamp = on
>> >> > log_connections = on
>> >> > log_hostname = on
>> >> >
>> >> > log_statement = on
>> >> > log_per_node_statement = on
>> >> > log_standby_delay = 'if_over_threshold'
>> >> > syslog_facility = 'LOCAL0'
>> >> > syslog_ident = 'pgpool'
>> >> > debug_level = 0
>> >> > pid_file_name = '/var/run/pgpool/pgpool.pid'
>> >> >
>> >> > logdir = '/tmp'
>> >> > connection_cache = off
>> >> > 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 = 'repl_user'
>> >> > sr_check_password = 'password'
>> >> > delay_threshold = 10000000
>> >> > follow_master_command = 'date > /DataCenter_nas/abc'
>> >> >
>> >> > 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 = 0
>> >> > health_check_timeout = 20
>> >> > health_check_user = 'nobody'
>> >> > health_check_password = ''
>> >> > failover_command = 'date > /DataCenter_nas/abc2'
>> >> >
>> >> > failback_command = 'date > /DataCenter_nas/abc3'
>> >> > fail_over_on_backend_error = on
>> >> > recovery_user = 'nobody'
>> >> > recovery_password = ''
>> >> > recovery_1st_stage_command = ''
>> >> > recovery_2nd_stage_command = ''
>> >> >
>> >> > recovery_timeout = 90
>> >> > client_idle_limit_in_recovery = 0
>> >> > relcache_expire = 0
>> >> > #-------------- the end of pgpool conf---------------
>> >> >
>> >> >
>> >> > <i tried it>
>> >> > i send query request to pgpool, and 1st postgresql catch it always.
>> >> >
>> >> > it is a wrong working
>> >> >  why i did set pgpools to 1:1000 about backend_weight0 and
>> >> backend_weight1
>> >> > values.(look my pgpool.conf file)
>> >> > i think that this case's good working is almost catch 2nd postgresql
>> >> server.
>> >> >
>> >> > i don't know this working reason(about wrong pgpool's load balancing
>> and
>> >> > after backend_hostname1 setting values).
>> >> >
>> >> > do you have to teach me about this problem or something idea?
>> >> > I hope your help. please help me.
>> >> >
>> >> >
>> >> > i waiting your mail.
>> >> > thanks.
>> >> >
>> >> >
>> >> > _______________________________________________
>> >> > Pgpool-general mailing list
>> >> > Pgpool-general at pgfoundry.org
>> >> > http://pgfoundry.org/mailman/listinfo/pgpool-general
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Martín Marqués
>> >> select 'martin.marques' || '@' || 'gmail.com'
>> >> DBA, Programador, Administrador
>> >>
>>