[pgpool-general: 3953] Re: pgpool establish newconnectiontooslow

Tue Aug 11 13:17:16 JST 2015

Hi ,Tatsuo Ishii

Do your test with Pgpool and postgresql on the same server ?

Can you increase -c parameter to 1024 ?

地址：广州市珠江新城花城大道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日(周二) 12:04
主题:Re: [pgpool-general: 3942] Re: pgpool establish newconnectiontooslow

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150811/d344a6de/attachment-0001.html>