Use pgpool and pgbouncer limit global concurrent SQL Execution

这个需求来自于我们这边的数据仓库，可能是连接的用户太多了，大家都在上面执行SQL，导致大家的查询都很慢。

所以通过连接池来限制并发的SQL请求是比较靠谱的方法。

允许用户连接上来，并且执行SQL，超出配置的请求放入队列,等有空闲的数据库端连接的时候再执行。而不是直接返回错误,这一点很重要。

pgbouncer和PostgreSQL之间连接的复用，pgpool和PostgreSQL之间连接的复用不在这里讨论。可以参考手册。

pgbouncer 可以限制每一对用户+数据库 到PostgreSQL Cluster的总连接数。

pgpool 可以限制pgpool到PostgreSQL cluster的总连接数。

结合起来可以干什么呢？

可以设置PostgreSQL Cluster全局的连接限制。如图

 

假设有客户端的需求最大有2万个连接，我们的需求是限制这2万个连接里面并发提交给数据库的请求是100个。超出100的SQL请求全部排队。

一个简单的配置如下:

pgbouncer.config

[databases]

db1 = host=pgpool_host_ip dbname=db1 port=9999 pool_size=1000

db2 = host=pgpool_host_ip dbname=db2 port=9999 pool_size=1000

[pgbouncer]

pool_mode = transaction  # 配置为事务级是比较适合我们前面提到的场景的。

listen_port = 1999

unix_socket_dir = /opt/pgbouncer/etc

listen_addr = *

auth_type = md5

auth_file = /opt/pgbouncer/etc/users.txt

logfile = /dev/null

pidfile = /opt/pgbouncer/etc/pgbouncer.pid

max_client_conn = 20000

reserve_pool_timeout = 0

server_reset_query = DISCARD ALL;

admin_users = pgbouncer_admin

stats_users = pgbouncer_guest

ignore_startup_parameters = extra_float_digits

server_idle_timeout = 1

server_round_robin = 1

pgbouncer.user

"username" "password"

启动pgbouncer

pgbouncer -d -u nonroot_user /opt/pgbouncer/etc/config.ini 

pgpool:

pcp.conf

# USERID:MD5PASSWD

pgpool.conf

listen_addresses = '*'

port = 9999

pcp_port = 9898

socket_dir = '/tmp'

pcp_socket_dir = '/tmp'

backend_socket_dir = '/tmp'

pcp_timeout = 10

num_init_children = 100    # num_init_children*max_pool = 限制的连接数

max_pool = 1

child_life_time = 300

connection_life_time = 0

child_max_connections = 0

client_idle_limit = 0

authentication_timeout = 60

logdir = '/tmp'

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

replication_mode = false

load_balance_mode = false

replication_stop_on_mismatch = false

failover_if_affected_tuples_mismatch = false

replicate_select = false

reset_query_list = 'ABORT; DISCARD ALL'

white_function_list = ''

black_function_list = 'nextval,setval'

print_timestamp = true

master_slave_mode = false

master_slave_sub_mode = 'slony'

delay_threshold = 0

log_standby_delay = 'none'

connection_cache = true

health_check_timeout = 20

health_check_period = 0

health_check_user = 'nobody'

failover_command = ''

failback_command = ''

fail_over_on_backend_error = true

insert_lock = true

ignore_leading_white_space = true

log_statement = false

log_per_node_statement = false

log_connections = false

log_hostname = false

parallel_mode = false

enable_query_cache = false

pgpool2_hostname = ''

backend_hostname0 = 'real_database_ip'

backend_port0 = real_database_port

backend_weight0 = 1

enable_pool_hba = false

recovery_user = 'nobody'

recovery_password = ''

recovery_1st_stage_command = ''

recovery_2nd_stage_command = ''

recovery_timeout = 90

client_idle_limit_in_recovery = 0

lobj_lock_table = ''

ssl = false

debug_level = 0

启动pgpool

pgpool -c -f /opt/pgpool/etc/pgpool.conf -F /opt/pgpool/etc/pcp.conf

客户端连接配置:

ip : pgbouncer_host_ip

port : pgbouncer_listener_port

dbname : pgbouncer_configed_dbname

user : pgbouncer_configed_name equal to real db's username

password : pgbouncer_configed_password equal to real db's password

【参考】

pgbouncer/doc

pgbouncer/share/man

pgpool/doc

pgpool/share/man