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

Mon Aug 10 19:14:47 JST 2015

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150810/a7056906/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: QQ20150810-8.png
Type: application/octet-stream
Size: 11430 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150810/a7056906/attachment-0001.obj>