[pgpool-general: 3948] Re: pgpool establish new connection tooslow

Tue Aug 11 12:28:55 JST 2015

I forgot an important point: I recommend to add "-C" option to pgbench
if you want to test more real world type load.  Web applications
frequetly connect/disconnect to database. -C simulates this (in this
case you should look into TPS with "including connections
establishing").

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi,Tatsuo Ishii
> The test server has enough resource (cpu,mem,io) to handle the test. (6vCPU， 32GB Mem， 200GB sata)
> I have postgresql and pgpool deployed as follow:
>  pgpool : 192.168.9.132 (master), 192.168.9.133(slaver)  postgresql: 192.168.9.132 (slaver), 192.168.9.133(master)
> The server load both 132 and 133 is very low ( 1 ) when test with pgpool. (Concurrent connections  512)
> My test command ( execute on server 192.168.9.203) :
> pgbench -l -c $CLIENTS -T $TIME -r pgbench -p9999 -h192.168.9.190 -Unopass
> pgbench -l -c $CLIENTS -T $TIME -r pgbench -p5432 -h192.168.9.203 -Unopass
> 
> 
> The test result is :
> pgp: is represent for pgbench with pgpool
> pg: is represent for pgbench with postgresql
> 
> 
> My Configuration and Env is :
> [postgres at dbk pgp]$ cat /proc/sys/kernel/sem
> 250 32000 32 128
> postgres.conf
> max_connections = 2500
> pgpool.conf
> listen_addresses = '*'
> port = 9999
> socket_dir = '/tmp'
> listen_backlog_multiplier = 2
> pcp_listen_addresses = '*'
> pcp_port = 9898
> pcp_socket_dir = '/tmp'
> backend_hostname0 = 'pgtest1.xrktest.com'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/data/pgdata'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> backend_hostname1 = 'pgtest2.xrktest.com'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/data/pgdata'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> enable_pool_hba = on
> pool_passwd = 'pool_passwd'
> authentication_timeout = 60
> ssl = off
> num_init_children = 1012
> max_pool = 4
> child_life_time = 300
> child_max_connections = 0
> connection_life_time = 0
> client_idle_limit = 0
> log_destination = 'stderr'
> log_line_prefix = '%t: pid %p: '  # printf-style string to output at beginning of each log line.
> log_connections = on
> log_hostname = on
> log_statement = on
> log_per_node_statement = off
> log_standby_delay = 'none'
> syslog_facility = 'LOCAL0'
> syslog_ident = 'pgpool'
> debug_level = 0
> pid_file_name = '/home/postgres/local/pgpool/var/pgpool.pid'
> logdir = '/home/postgres/local/pgpool/var/pgpool'
> connection_cache = on
> reset_query_list = 'ABORT; DISCARD ALL'
> replication_mode = off
> replicate_select = off
> insert_lock = on
> 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 = 'nextval,setval,nextval,setval'
> database_redirect_preference_list = ''
> app_name_redirect_preference_list = ''
> allow_sql_comments = off
> master_slave_mode = on
> master_slave_sub_mode = 'stream'
> sr_check_period = 0
> sr_check_user = 'repuser'
> sr_check_password = 'rep123'
> delay_threshold = 0
> follow_master_command = ''
> health_check_period = 0
> health_check_timeout = 20
> health_check_user = 'repuser'
> health_check_password = 'rep123'
> health_check_max_retries = 0
> health_check_retry_delay = 1
> connect_timeout = 10000
> failover_command = ''
> failback_command = ''
> fail_over_on_backend_error = on
> search_primary_node_timeout = 10
> recovery_user = 'repuser'
> recovery_password = 'rep123'
> recovery_1st_stage_command = ''
> recovery_2nd_stage_command = ''
> recovery_timeout = 90
> client_idle_limit_in_recovery = 0
> use_watchdog = on
> trusted_servers = ''
> ping_path = '/bin'
> wd_hostname = 'pgtest1.xrktest.com'
> wd_port = 9000
> wd_authkey = ''
> delegate_IP = '192.168.9.190'
> 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'
> clear_memqcache_on_escalation = on
> wd_escalation_command = ''
> wd_lifecheck_method = 'heartbeat'
> wd_interval = 10
> wd_heartbeat_port = 9694
> wd_heartbeat_keepalive = 2
> wd_heartbeat_deadtime = 30
> heartbeat_destination0 = 'pgtest2.xrktest.com'
> heartbeat_destination_port0 = 9694
> heartbeat_device0 = ''
> wd_life_point = 3
> wd_lifecheck_query = 'SELECT 1'
> wd_lifecheck_dbname = 'template1'
> wd_lifecheck_user = 'nobody'
> wd_lifecheck_password = ''
> other_pgpool_hostname1 = 'pgtest2.xrktest.com'
> other_pgpool_port1 = 5432
> other_wd_port1 = 9000
> relcache_expire = 0
> relcache_size = 256
> check_temp_table = on
> check_unlogged_table = on
> memory_cache_enabled = off
> memqcache_method = 'shmem'
> 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 = ''
> 
> 
> I realy don’t know where am i wrong.
> The test result is very bad. (Lost 70% of the tps)
> 
> 
> Is there something I can do to improve it?
> 
> 
> Phone：+86 137 5171 0549
> Email：liujinfei at xiangrikui.com
> Skype : scofier.liu
> 原始邮件
> 发件人:Tatsuo Ishiiishii at postgresql.org
> 收件人:liujinfeiliujinfei at xiangrikui.com
> 抄送:pgpool-generalpgpool-general at pgpool.net
> 发送时间:2015年8月10日(周一) 13:46
> 主题:Re: [pgpool-general: 3942] Re: pgpool establish new connection tooslow
> 
> 
>  pgpool : 192.168.9.132 (master), 192.168.9.133(slaver)  postgresql: 192.168.9.132 (slaver), 192.168.9.133(master)  pgbench 192.168.9.203      When i test with postgresql, it cost a little time ( 5 seconds) to establish new connection.   I assume you run pgbench on 192.168.9.203.   So one PostgreSQL server spends 5 seconds for opening 1024  connections. Because you have two PostgreSQL servers spending 10  seconds to open 1024 connections to both PostgreSQL. But still extra  10 seconds does not explain. Let me think it about... One possible explanation is, 192.168.9.132 does not have enough CPU/memory resource to handle 2048 process (1024 pgpool-II + 1024 PostgreSQL). For example 2048 process will produce significant context switches which in turn gives high CPU load. Can you add more resource to the server? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp