<div style="font:14px/1.5 'Lucida Grande', '微软雅黑';color:#333;"><p style="font:14px/1.5 'Lucida Grande';margin:0;">Thanks for reply. </p><p style="font:14px/1.5 'Lucida Grande';margin:0;"><br></p><p style="font:14px/1.5 'Lucida Grande';margin:0;">I have change test command to :</p><p style="font:14px/1.5 'Lucida Grande';margin:0;"><br></p><p style="font:14px/1.5 'Lucida Grande';margin:0;"> <span style="white-space: pre-wrap; font-family: 'Lucida Grande', 微软雅黑; line-height: 1.5;">pgbench -l -C -c $CLIENTS -T $TIME -r pgbench -p9999 -h192.168.9.190 -Unopass</span></p><p style="font:14px/1.5 'Lucida Grande';margin:0;"><span style="white-space: pre-wrap; font-family: 'Lucida Grande', 微软雅黑; line-height: 1.5;"><br></span></p><p style="font:14px/1.5 'Lucida Grande';margin:0;"><span style="white-space: pre-wrap; font-family: 'Lucida Grande', 微软雅黑; line-height: 1.5;">But the test result is as before : Pgpool bench test tps is too low than direct test postgresql.</span></p><div><br></div><div><br></div>I guess the reason is that:<div><br></div><div> With pgpool , the active database connection is very low (avg <10, <span style="font-family: 'Lucida Grande', 'Lucida Sans Unicode', sans-serif; widows: 1; color: rgb(34, 34, 34); font-size: 15px; line-height: 19px;">Concurrent clients connections > 512</span><span style="line-height: 1.5;">).</span></div><div> Direct bench test with Postgresql , the active database connection is very high (avg > 60, <span style="font-family: 'Lucida Grande', 'Lucida Sans Unicode', sans-serif; widows: 1; color: rgb(34, 34, 34); font-size: 15px; line-height: 19px;">Concurrent clients connections > 512</span><span style="line-height: 1.5;">). </span></div><div><div><div style="font: 14px/1.5 'Lucida Grande';"><div class="sign" style="color: rgb(51, 51, 51); widows: 1; padding: 20px 10px; line-height: 22px; font-family: 'Lucida Grande', 'Lucida Sans Unicode', sans-serif !important;"><br></div><div class="sign" style="color: rgb(51, 51, 51); widows: 1; padding: 20px 10px; line-height: 22px; font-family: 'Lucida Grande', 'Lucida Sans Unicode', sans-serif !important;"><br><div class="contact" style="border-bottom-color: rgb(229, 229, 229); padding: 15px 0px; margin-top: 18px; border-top-color: rgb(229, 229, 229); border-top-width: 1px; border-top-style: solid;"><p style="margin-top: 0px; margin-bottom: 10px; line-height: 18px; color: rgb(118, 116, 116); font-size: 12px;">地址：广州市珠江新城花城大道18号建滔广场2101室 （邮编：510000）</p><p style="margin-top: 0px; margin-bottom: 0px; line-height: 18px; color: rgb(118, 116, 116); font-size: 12px;">Phone：+86 137 5171 0549</p><p style="margin-top: 0px; margin-bottom: 0px; line-height: 18px; color: rgb(118, 116, 116); font-size: 12px;">Email：<a href="mailto:liujf3@ucweb.com" target="_blank" title="mailto:liujf3@ucweb.com" style="color: rgb(19, 109, 186); text-decoration: none; -webkit-user-drag: none;">liujinfei@xiangrikui.com</a></p><p style="margin-top: 0px; margin-bottom: 0px; line-height: 18px; color: rgb(118, 116, 116); font-size: 12px;">Skype : scofier.liu</p><p style="margin-top: 0px; margin-bottom: 0px; line-height: 18px; color: rgb(118, 116, 116); font-size: 12px;"><br></p></div></div></div></div><span style="font: 14px/1.5 'Lucida Grande';color:#333;"><br></span><div class="foxmail_blockquote_fromhere_element" style="font: 12px/1.5 'Lucida Grande';padding:2px 0 2px 0;"><span style="color:#333;text-decoration:line-through;white-space:pre-wrap;"> </span> 原始邮件 <span style="color:#333;text-decoration:line-through;white-space:pre-wrap;"> </span></div><div style="font: 12px/1.5 'Lucida Grande';background:#efefef;color:#666666;padding:8px;"><div><b style="color:#999;">发件人:</b> Tatsuo Ishii<ishii@postgresql.org></div><div><b style="color:#999;">收件人:</b> liujinfei<liujinfei@xiangrikui.com></div><div><b style="color:#999;">抄送:</b> pgpool-general<pgpool-general@pgpool.net></div><div><b style="color:#999;">发送时间:</b> 2015年8月11日(周二) 11:28</div><div><b style="color:#999;">主题:</b> Re: [pgpool-general: 3942] Re: pgpool establish new connectiontooslow</div></div><br><div class="mail_quote_94D7A607C02B42FAA52640E74EA0A21E" style="font: 14px/1.5 'Lucida Grande';color:#333;"><pre style="white-space:pre-wrap;">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: <a href="http://www.sraoss.co.jp/index_en.php" title="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</a>
Japanese:<a href="http://www.sraoss.co.jp" title="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
> 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@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：<a href="mailto:liujinfei@xiangrikui.com" title="mailto:liujinfei@xiangrikui.com">liujinfei@xiangrikui.com</a>
> Skype : scofier.liu
> 原始邮件
> 发件人:Tatsuo <a href="mailto:Ishiiishii@postgresql.org" title="mailto:Ishiiishii@postgresql.org">Ishiiishii@postgresql.org</a>
> 收件人:<a href="mailto:liujinfeiliujinfei@xiangrikui.com" title="mailto:liujinfeiliujinfei@xiangrikui.com">liujinfeiliujinfei@xiangrikui.com</a>
> 抄送:<a href="mailto:pgpool-generalpgpool-general@pgpool.net" title="mailto:pgpool-generalpgpool-general@pgpool.net">pgpool-generalpgpool-general@pgpool.net</a>
> 发送时间: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: <a href="http://www.sraoss.co.jp/index_en.php" title="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</a> Japanese:<a href="http://www.sraoss.co.jp" title="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
</pre></div></div></div>