Greenplum segment节点直接读写配置与性能
背景
《Use pgbouncer connect to GreenPlum’s segment node》
架构：
前端使用haproxy代理到后端的pgbouncer端口。
连接池使用pgbouncer，每个segment一个pgbouncer。
数据库为segment。
适合场景，随机分发表。
未来需要proxy实现gp同样的分布算法，来实现按列分布。
测试机：
一台主机。
实测短连接简单SQL qps 2W
#./pgsql9.5/bin/pgbench -M simple -C -n -r -P 1 -f ./test.sql -c 22 -j 22 -U digoal digoal -T 10000 -h 127.0.0.1 -p 5222
Password:
progress: 1.0 s, 20672.2 tps, lat 0.700 ms stddev 0.750
progress: 2.0 s, 20845.1 tps, lat 0.697 ms stddev 0.680
progress: 3.0 s, 21010.2 tps, lat 0.701 ms stddev 0.840
progress: 4.0 s, 20220.2 tps, lat 0.718 ms stddev 0.991
progress: 5.0 s, 20692.1 tps, lat 0.703 ms stddev 0.766
progress: 6.0 s, 20836.3 tps, lat 0.699 ms stddev 0.737
progress: 7.0 s, 20606.1 tps, lat 0.698 ms stddev 0.602
progress: 8.0 s, 20360.2 tps, lat 0.707 ms stddev 0.855
progress: 9.0 s, 20762.4 tps, lat 0.696 ms stddev 0.697
长连接简单SQL qps 9W
#./pgsql9.5/bin/pgbench -M simple -n -r -P 1 -f ./test.sql -c 22 -j 22 -U digoal digoal -T 10000 -h 127.0.0.1 -p 5222
Password:
progress: 1.0 s, 92493.6 tps, lat 0.471 ms stddev 0.310
progress: 2.0 s, 91775.2 tps, lat 0.477 ms stddev 0.301
progress: 3.0 s, 93823.5 tps, lat 0.467 ms stddev 0.271
progress: 4.0 s, 91906.8 tps, lat 0.477 ms stddev 0.337
progress: 5.0 s, 93752.2 tps, lat 0.467 ms stddev 0.271
数据压测，heap表，随机分布，不使用压缩：
短连接 9.5K QPS
#./pgsql9.5/bin/pgbench -M simple -C -n -r -P 1 -f ./test.sql -c 22 -j 22 -U digoal postgres -T 10000 -h 127.0.0.1 -p 5222
progress: 1.0 s, 9267.7 tps, lat 3.542 ms stddev 1.780
progress: 2.0 s, 9663.0 tps, lat 3.389 ms stddev 1.547
progress: 3.0 s, 9825.9 tps, lat 3.314 ms stddev 1.513
progress: 4.0 s, 9564.0 tps, lat 3.416 ms stddev 1.636
progress: 5.0 s, 9412.2 tps, lat 3.465 ms stddev 1.735
progress: 6.0 s, 9765.3 tps, lat 3.309 ms stddev 1.283
progress: 7.0 s, 9643.8 tps, lat 3.417 ms stddev 1.602
progress: 8.0 s, 9629.8 tps, lat 3.360 ms stddev 1.532
长连接 12.5K QPS
progress: 1.0 s, 12249.9 tps, lat 3.566 ms stddev 1.314
progress: 2.0 s, 12407.2 tps, lat 3.544 ms stddev 1.255
progress: 3.0 s, 12411.3 tps, lat 3.540 ms stddev 1.246
progress: 4.0 s, 12626.9 tps, lat 3.483 ms stddev 1.071
progress: 5.0 s, 12627.8 tps, lat 3.481 ms stddev 1.081
progress: 6.0 s, 12418.3 tps, lat 3.537 ms stddev 1.270
批量插入性能指标，一次批量1000条。
长连接，每秒入库200万记录。
#./pgsql9.5/bin/pgbench -M simple -n -r -P 1 -f ./test.sql -c 44 -j 44 -U digoal digoal -T 10000 -h 127.0.0.1 -p 5222
Password:
progress: 1.0 s, 2156.9 tps, lat 20.072 ms stddev 7.201
progress: 2.0 s, 2172.0 tps, lat 20.181 ms stddev 7.270
progress: 3.0 s, 2084.0 tps, lat 21.021 ms stddev 8.628
progress: 4.0 s, 2138.0 tps, lat 20.657 ms stddev 7.694
progress: 5.0 s, 2010.0 tps, lat 21.846 ms stddev 8.481
progress: 6.0 s, 2124.0 tps, lat 20.680 ms stddev 8.843
progress: 7.0 s, 2152.0 tps, lat 20.503 ms stddev 7.473
progress: 8.0 s, 1937.0 tps, lat 22.737 ms stddev 13.009
progress: 9.0 s, 2031.0 tps, lat 21.637 ms stddev 11.519
短连接，每秒入库170万记录。
#./pgsql9.5/bin/pgbench -M simple -C -n -r -P 1 -f ./test.sql -c 44 -j 44 -U digoal digoal -T 10000 -h 127.0.0.1 -p 5222
Password:
progress: 1.0 s, 1775.9 tps, lat 22.999 ms stddev 30.127
progress: 2.0 s, 2070.0 tps, lat 19.770 ms stddev 9.126
progress: 3.0 s, 1612.9 tps, lat 25.782 ms stddev 38.302
progress: 4.0 s, 2008.0 tps, lat 20.893 ms stddev 15.890
progress: 5.0 s, 1708.1 tps, lat 25.053 ms stddev 39.093
progress: 6.0 s, 1633.0 tps, lat 25.809 ms stddev 36.698
progress: 7.0 s, 1708.1 tps, lat 24.215 ms stddev 33.983
progress: 8.0 s, 1807.0 tps, lat 23.125 ms stddev 30.237
progress: 9.0 s, 2102.0 tps, lat 19.880 ms stddev 9.669
progress: 10.0 s, 1535.0 tps, lat 27.357 ms stddev 44.640
progress: 11.0 s, 1852.1 tps, lat 21.613 ms stddev 21.728
progress: 12.0 s, 1627.0 tps, lat 27.159 ms stddev 40.428
progress: 13.0 s, 1634.0 tps, lat 25.543 ms stddev 36.762
progress: 14.0 s, 1772.0 tps, lat 23.529 ms stddev 32.450
progress: 15.0 s, 1715.9 tps, lat 24.459 ms stddev 31.557
progress: 16.0 s, 2047.1 tps, lat 19.731 ms stddev 8.128
progress: 17.0 s, 1545.0 tps, lat 26.770 ms stddev 40.119
progress: 18.0 s, 1906.0 tps, lat 21.812 ms stddev 21.845
progress: 19.0 s, 1721.0 tps, lat 24.839 ms stddev 38.066
progress: 20.0 s, 1799.0 tps, lat 23.586 ms stddev 34.751
为什么不使用pgbouncer-x2?
短连接无法实现roundrobin, 造成数据倾斜。
目前的架构，短连接，满足一天8亿数据入库的需求，1天几百万的数据需求可以满足。
配置文件
haproxy.conf
global
log 127.0.0.1 local3 notice
ulimit-n 40960
maxconn 10240
user digoal
group users
nbproc 8
daemon
quiet
defaults
log global
mode tcp
option tcplog
listen mm
bind 0.0.0.0:5222
balance roundrobin
mode tcp
server t1 127.0.0.1:6400
server t1 127.0.0.1:6401
server t1 127.0.0.1:6402
server t1 127.0.0.1:6403
server t1 127.0.0.1:6404
server t1 127.0.0.1:6405
server t1 127.0.0.1:6406
server t1 127.0.0.1:6407
server t1 127.0.0.1:6408
server t1 127.0.0.1:6409
server t1 127.0.0.1:6410
server t1 127.0.0.1:6411
server t1 127.0.0.1:6412
server t1 127.0.0.1:6413
server t1 127.0.0.1:6414
server t1 127.0.0.1:6415
server t1 127.0.0.1:6416
server t1 127.0.0.1:6417
server t1 127.0.0.1:6418
server t1 127.0.0.1:6419
server t1 127.0.0.1:6420
server t1 127.0.0.1:6421
pgbouncer.ini.40000
pgbouncer.ini.40001
……
;; database name = connect string
;;
;; connect string params:
;; dbname= host= port= user= password=
;; client_encoding= datestyle= timezone=
;; pool_size= connect_query=
[databases]
; foodb over unix socket
digoal = dbname=digoal host=/tmp port=40000 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40001 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40002 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40003 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40004 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40005 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40006 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40007 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40008 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40009 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40010 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40011 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40012 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40013 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40014 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40015 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40016 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40017 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40018 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40019 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40020 user=digoal weight=1
;digoal = dbname=digoal host=/tmp port=40021 user=digoal weight=1
; redirect bardb to bazdb on localhost
;bardb = host=localhost dbname=bazdb
; acceess to dest database will go with single user
;forcedb = host=/tmp port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
; use custom pool sizes
;nondefaultdb = pool_size=50 reserve_pool_size=10
; fallback connect string
;* = host=testserver
;; Configuration section
[pgbouncer]
;;;
;;; Administrative settings
;;;
;logfile = /home/digoal/pgbouncerx2/pgbouncer.log
logfile = /dev/null
pidfile = /home/digoal/pgbouncerx2/pgbouncer.pid.40000
;;;
;;; Where to wait for clients
;;;
; ip address or * which means all ip-s
listen_addr = 0.0.0.0
listen_port = 6400
; unix socket is also used for -R.
; On debian it should be /var/run/digoalql
unix_socket_dir = /tmp
unix_socket_mode = 0700
;unix_socket_group =
;;;
;;; Authentication settings
;;;
; any, trust, plain, crypt, md5
auth_type = md5
;auth_file = /8.0/main/global/pg_auth
auth_file = /home/digoal/pgbouncerx2/userlist.txt
;; Query to use to fetch password from database. Result
;; must have 2 columns - username and password hash.
;auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
;;;
;;; Users allowed into database 'pgbouncer'
;;;
; comma-separated list of users, who are allowed to change settings
;admin_users = user2, someadmin, otheradmin
; comma-separated list of users who are just allowed to use SHOW command
;stats_users = stats, root
;;;
;;; Pooler personality questions
;;;
; When server connection is released back to pool:
; session - after client disconnects
; transaction - after transaction finishes
; statement - after statement finishes
pool_mode = transaction
;
; Query for cleaning connection immediately after releasing from client.
; No need to put ROLLBACK here, pgbouncer does not reuse connections
; where transaction is left open.
;
; Query for 8.3+:
; DISCARD ALL;
;
; Older versions:
; RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
; Empty if transaction pooling is in use.
;
server_reset_query = DISCARD ALL
; Whether server_reset_query should run in all pooling modes.
; If it is off, server_reset_query is used only for session-pooling.
server_reset_query_always = 0
;
; Comma-separated list of parameters to ignore when given
; in startup packet. Newer JDBC versions require the
; extra_float_digits here.
;
;ignore_startup_parameters = extra_float_digits
;
; When taking idle server into use, this query is ran first.
; SELECT 1
;
;server_check_query = select 1
; If server was used more recently that this many seconds ago,
; skip the check query. Value 0 may or may not run in immediately.
;server_check_delay = 30
;; Use <appname - host> as application_name on server.
;application_name_add_host = 0
;;;
;;; Connection limits
;;;
; total number of clients that can connect
max_client_conn = 1000
; default pool size. 20 is good number when transaction pooling
; is in use, in session pooling it needs to be the number of
; max clients you want to handle at any moment
default_pool_size = 8
;; Minimum number of server connections to keep in pool.
min_pool_size = 2
; how many additional connection to allow in case of trouble
;reserve_pool_size = 5
; if a clients needs to wait more than this many seconds, use reserve pool
;reserve_pool_timeout = 3
; how many total connections to a single database to allow from all pools
max_db_connections = 8
;max_user_connections = 50
; If off, then server connections are reused in LIFO manner
server_round_robin = 0
;;;
;;; Logging
;;;
;; Syslog settings
;syslog = 0
;syslog_facility = daemon
;syslog_ident = pgbouncer
; log if client connects or server connection is made
;log_connections = 1
; log if and why connection was closed
;log_disconnections = 1
; log error messages pooler sends to clients
;log_pooler_errors = 1
;; Period for writing aggregated stats into log.
;stats_period = 60
;; Logging verbosity. Same as -v switch on command line.
;verbose=0
;;;
;;; Timeouts
;;;
;; Close server connection if its been connected longer.
server_lifetime = 1200
;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
server_idle_timeout = 60
;; Cancel connection attempt if server does not answer takes longer.
;server_connect_timeout = 15
;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
;server_login_retry = 15
;; Dangerous. Server connection is closed if query does not return
;; in this time. Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0
;; Dangerous. Client connection is closed if the query is not assigned
;; to a server in this time. Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 120)
;query_wait_timeout = 120
;; Dangerous. Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0
;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60
;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 3600
;; How long SUSPEND/-R waits for buffer flush before closing connection.
;suspend_timeout = 10
;; Close connections which are in "IDLE in transaction" state longer than
;; this many seconds.
;idle_transaction_timeout = 0
;;;
;;; Low-level tuning options
;;;
;; buffer for streaming packets
;pkt_buf = 2048
;; man 2 listen
;listen_backlog = 128
;; Max number pkt_buf to process in one event loop.
;sbuf_loopcnt = 5
;; Maximum Postgres protocol packet size.
;max_packet_size = 2147483647
;; networking options, for info: man 7 tcp
;; Linux: notify program about new connection only if there
;; is also data received. (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0
;; In-kernel buffer size (Linux default: 4096)
;tcp_socket_buffer = 0
;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 1
;; following options are Linux-specific.
;; they also require tcp_keepalive=1
;; count of keepaliva packets
;tcp_keepcnt = 0
;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0
;; The time between individual keepalive probes.
;tcp_keepintvl = 0
;; DNS lookup caching time
;dns_max_ttl = 15
;; DNS zone SOA lookup period
;dns_zone_check_period = 0
;; DNS negative result caching time
;dns_nxdomain_ttl = 15
;;;
;;; Random stuff
;;;
;; Hackish security feature. Helps against SQL-injection - when PQexec is disabled,
;; multi-statement cannot be made.
;disable_pqexec=0
;; Config file to use for next RELOAD/SIGHUP.
;; By default contains config file from command line.
;conffile
;; Win32 service name to register as. job_name is alias for service_name,
;; used by some Skytools scripts.
;service_name = pgbouncer
;job_name = pgbouncer
;; Read additional config from the /etc/pgbouncer/pgbouncer-other.ini file
;%include /etc/pgbouncer/pgbouncer-other.ini