[pgpool-general: 3951] Re: pgpool establish new connectiontooslow

Tatsuo Ishii ishii at postgresql.org
Tue Aug 11 13:04:04 JST 2015


Here is my my test case.

With pgpool: pgbench -S -c 10 -T 10 -C -p 11000 test
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 10 s
number of transactions actually processed: 20141
latency average: 4.965 ms
tps = 2013.933850 (including connections establishing)
tps = 15311.447818 (excluding connections establishing)

Without pgpool:
t-ishii at localhost: pgbench -S -c 10 -T 10 -C -p 11002 test
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 10 s
number of transactions actually processed: 5514
latency average: 18.136 ms
tps = 551.208510 (including connections establishing)
tps = 29466.303994 (excluding connections establishing)

This is a laptop running Ubuntu 14.04. The OS is not running on
virtual machine. I don't know what kind of super visor you are using
but I vaguely recall that one of two users experienced severe
performance degradation with their virtual machine and pgpool
combo. You might want to run the test on a non virtual environment.

Also I suspect your pgbench sends all the query to only
master. Because by default pgbench starts transaction and pgpool will
not load balance the transaction. That's why I use "-S".

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

> Thanks for reply.
> 
> 
> I have change test command to :
> 
> 
> pgbench -l -C -c $CLIENTS -T $TIME -r pgbench -p9999 -h192.168.9.190 -Unopass
> 
> 
> But the test result is as before : Pgpool bench test tps is too low than direct test postgresql.
> 
> 
> 
> 
> I guess the reason is that:
> 
> 
> With pgpool , the active database connection is very low (avg 10,Concurrent clients connections  512).
> Direct bench test with Postgresql , the active database connection is very high (avg  60,Concurrent clients connections  512). 
> 
> 
> 
> 
> 地址:广州市珠江新城花城大道18号建滔广场2101室 (邮编:510000)
> 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月11日(周二) 11:28
> 主题:Re: [pgpool-general: 3942] Re: pgpool establish new connectiontooslow
> 
> 
> 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


More information about the pgpool-general mailing list