pgpool-II 性能损失（performance lossy）

原文：http://blog.163.com/digoal@126/blog/static/1638770402014316448141/

 

经常有人问我pgpool-II做连接池合不合适. 这个要结合业务需求来看, 除了PGPOOL-II本身的性能损失, 它的功能还是很强大的.

因为我以前在某项目上用过这个产品, 对性能有一定的损耗, 不知道现在的版本怎么样了?

所以接下来测试一下最新的pgpool-II3.3.3的性能. 对比不使用连接池的性能.

测试方法很简单, 连接到数据库, 执行 select 1;

测试环境 : 

pgpool, pgbench所在服务器硬件, 志强8核1.6G. 

postgresql 9.3.3所在服务器硬件, 志强8核2.0G.

 

vi ~/.pgpass

127.0.0.1:9999:digoal:postgres:postgres

172.16.3.39:1999:digoal:postgres:postgres

 

测试结果如下 : 

测试连接数	使用pgpool-II(tps)	直连数据库(tps)	性能损失(%)
8	8850	44526	80%
16	16896	98001	82.7%
32	26780	139980	80.9%
64	28151	138575	79.7%

说明pgpool-II本身处理SQL的TPS极限大概在2.8万左右(CPU耗尽). (我后来加大num_init_children=256, 使用pgbench开启256个连接测试, 性能下降到23232.)

如果你的架构中只有1个pgpool-II, 那么这个架构的瓶颈很大可能就在pgpool这里. 当然, 如果你的业务不会达到这么大的tps, 又另当别论了, 因为pgpool-II的其他特性还是很不错的, 比如load balance, failover, shared nothing, query cache等.

 

详细测试数据如下 : 

直连数据库

 

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 172.16.3.39-p 1999-U postgres -f ./test.sql -c 8-j 8-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:8

number of threads:8

duration:10 s

number of transactions actually processed:445294

tps=44526.661610(including connections establishing)

tps =44574.757044(excluding connections establishing)

statement latencies in milliseconds:

        0.177982        select1;

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 172.16.3.39-p 1999-U postgres -f ./test.sql -c 16-j 16-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:16

number of threads:16

duration:10 s

number of transactions actually processed:980250

tps=98001.949941(including connections establishing)

tps =98102.005783(excluding connections establishing)

statement latencies in milliseconds:

        0.161363        select1;

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 172.16.3.39-p 1999-U postgres -f ./test.sql -c 32-j 32-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:32

number of threads:32

duration:10 s

number of transactions actually processed:1399989

tps=139980.268626(including connections establishing)

tps =140246.063527(excluding connections establishing)

statement latencies in milliseconds:

        0.226628        select1;

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 172.16.3.39-p 1999-U postgres -f ./test.sql -c 64-j 64-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:64

number of threads:64

duration:10 s

number of transactions actually processed:1386700

tps=138575.283794(including connections establishing)

tps =139064.044053(excluding connections establishing)

statement latencies in milliseconds:

        0.458081        select1;

 

通过pgpool-II连接数据库

 

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 127.0.0.1-p 9999-U postgres -f ./test.sql -c 8-j 8-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:8

number of threads:8

duration:10 s

number of transactions actually processed:88520

tps=8850.806911(including connections establishing)

tps =8859.269474(excluding connections establishing)

statement latencies in milliseconds:

        0.696641        select1;

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 127.0.0.1-p 9999-U postgres -f ./test.sql -c 16-j 16-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:16

number of threads:16

duration:10 s

number of transactions actually processed:168990

tps=16896.036435(including connections establishing)

tps =16914.751122(excluding connections establishing)

statement latencies in milliseconds:

        0.728099        select1;

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 127.0.0.1-p 9999-U postgres -f ./test.sql -c 32-j 32-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:32

number of threads:32

duration:10 s

number of transactions actually processed:267883

tps=26780.814762(including connections establishing)

tps =26807.235961(excluding connections establishing)

statement latencies in milliseconds:

        0.920283        select1;

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -h 127.0.0.1-p 9999-U postgres -f ./test.sql -c 64-j 64-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:64

number of threads:64

duration:10 s

number of transactions actually processed:281642

tps=28151.773807(including connections establishing)

tps =28231.503065(excluding connections establishing)

statement latencies in milliseconds:

        1.676748        select1;

 

pgpool-II配置 : 

 

listen_addresses ='localhost'

port =9999

socket_dir ='/tmp'

pcp_port =9898

pcp_socket_dir ='/tmp'

backend_hostname0 ='172.16.3.39'

backend_port0 =1999

backend_weight0 =1

enable_pool_hba = off

pool_passwd ='pool_passwd'

authentication_timeout =60

ssl= off

num_init_children =256 

max_pool =4

child_life_time =300

child_max_connections =0

connection_life_time =0

client_idle_limit =0

log_destination ='stderr'

print_timestamp = on

log_connections = off

log_hostname = off

log_statement = off

log_per_node_statement = off

log_standby_delay ='none'

syslog_facility ='LOCAL0'

syslog_ident ='pgpool'

debug_level =0

pid_file_name ='/var/run/pgpool/pgpool.pid'

logdir ='/tmp'

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 = off

ignore_leading_white_space = on

white_function_list =''

black_function_list ='nextval,setval'

master_slave_mode = off

master_slave_sub_mode ='stream'

sr_check_period =0 

sr_check_user ='postgres'

sr_check_password ='postgres'

delay_threshold =0

follow_master_command =''

parallel_mode = off

pgpool2_hostname =''

system_db_hostname  ='localhost'

system_db_port =5432

system_db_dbname ='pgpool'

system_db_schema ='pgpool_catalog'

system_db_user ='pgpool'

system_db_password =''

health_check_period =0

health_check_timeout =20

health_check_user ='nobody'

health_check_password =''

health_check_max_retries =0

health_check_retry_delay =1

failover_command =''

failback_command =''

fail_over_on_backend_error = on

search_primary_node_timeout =10

recovery_user ='nobody'

recovery_password =''

recovery_1st_stage_command =''

recovery_2nd_stage_command =''

recovery_timeout =90

client_idle_limit_in_recovery =0

use_watchdog = off

trusted_servers =''

ping_path ='/bin'

wd_hostname =''

wd_port =9000

wd_authkey =''

delegate_IP =''

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 = 'host0_ip1'

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 = ''

relcache_expire = 0

relcache_size = 256

check_temp_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 = ''

 

其他

将pgbench和pgpool再分开到2台主机, 得到的结果比现在更烂.有兴趣的朋友可以使用3台主机测试一下.

我这里附上pgbench, pgpool, postgresql分别在3台主机的一个测试结果.

连pgpool-ii测试的结果 : 

 

pg93@db-172-16-3-33-> pgbench -M prepared -n -r -h 172.16.3.150-p 9999-U postgres -f ./test.sql -c 64-j 64-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:64

number of threads:64

duration:10 s

number of transactions actually processed:227504

tps=22738.371402(including connections establishing)

tps =22819.463248(excluding connections establishing)

statement latencies in milliseconds:

        2.161423        select1;

pg93@db-172-16-3-33-> pgbench -M prepared -n -r -h 172.16.3.150-p 9999-U postgres -f ./test.sql -c 128-j 128-T 10 digoal

transaction type:Custom query

scaling factor:1

query mode: prepared

number of clients:128

number of threads:128

duration:10 s

number of transactions actually processed:227544

tps=22733.673710(including connections establishing)

tps =22844.669605(excluding connections establishing)

statement latencies in milliseconds:

        4.857240        select1;