為什么要使用連接池？

PostgreSQL允許我們通過參數(shù)max_connections來控制最大連接數(shù) ，但是如果為前端應(yīng)用的每次請求都分配一個(gè)新的DB連接，那么DB服務(wù)端可能在鏈接風(fēng)暴到來時(shí)需要一次性提供大量的連接插槽，這會(huì)嚴(yán)重消耗服務(wù)端的內(nèi)存，并且會(huì)導(dǎo)致性能急劇下降。

在高并發(fā)請求的應(yīng)用場景，為了保護(hù)DB，取而代之的我們可以利用連接池的連接復(fù)用特性，來避免DB被突然到來的連接洪峰淹沒。這樣，當(dāng)應(yīng)用程序一次性發(fā)送成千上萬個(gè)查詢的時(shí)候，如果超過了DB的處理能力，請求將在會(huì)連接池中排隊(duì)，而不會(huì)導(dǎo)致后端PostgreSQL崩潰。

與突然分配大量DB連接相比，這種在連接池中配置固定數(shù)量的連接處理來接收請求，查詢的性能要好得多。另外，客戶端的連接非常輕巧，除了文件描述符外基本沒有其他消耗。但我們服務(wù)端連接卻很重，需要適當(dāng)配置。用較少的服務(wù)器連接來支持大量的客戶端連接，是我們在PostgreSQL使用pgouncer的主要用例。

•  能夠緩存和PostgreSQL的連接，當(dāng)有連接請求進(jìn)來的時(shí)候，直接分配空閑進(jìn)程，而不需要PostgreSQL fork出新進(jìn)程來建立連接，以節(jié)省創(chuàng)建新進(jìn)程，創(chuàng)建連接的資源消耗。
•  能夠有效提高連接的利用率，避免過多的無效連接，導(dǎo)致數(shù)據(jù)庫消耗資源過大，CPU占用過高。
• 對客戶端連接進(jìn)行限制，預(yù)防過多或惡意的連接請求。

PgBouncer的特點(diǎn)

• C語言編寫，效率高，內(nèi)存消耗低(默認(rèn)為2k/連接)，因?yàn)锽ouncer不需要每次都接受完整的數(shù)據(jù)包

• 可以把不同的數(shù)據(jù)庫連接到一個(gè)機(jī)器上，而對客戶端保持透明

• 支持在線的重新配置而無須重啟

• 僅支持V3協(xié)議，因此后端版本須>=7.4

• 使用libevent進(jìn)行socket通信，通信效率高。

PgBouncer的編譯安裝需要依賴下面相關(guān)包:

GNU Make 3.81+
Libevent 2.0+(需要編譯安裝)
pkg-config
OpenSSL 1.0.1+ for TLS support
(optional) c-ares as alternative to Libevent’s evdns
(optional) PAM libraries

依賴包安裝：

download libevent-2.1.11-stable.tar.gz
tar -xvf libevent-2.1.11-stable.tar.gz
configure --prefix=/MySql/pg/libevent

make && make install"

環(huán)境變量配置：

vi .bash_profile
export LIBDIR=/MySql/pg/libevent/lib
export LD_RUN_PATH=$LIBDIR
export PKG_CONFIG_PATH=$LIBDIR/pkgconfig

解決安裝包之后，編譯安裝其實(shí)比較簡單:

$ ./configure --prefix=/usr/local
$ make
$ make install

配置文件主要分2部分

[databases]

要連接的數(shù)據(jù)庫名，默認(rèn)跟 客戶端相同的名字 。

[pgbouncer]

這個(gè)部分的配置項(xiàng)比較多，主要分為下面幾類：

• 通用配置項(xiàng)

• 日志配置項(xiàng)

• 控制界面訪問控制配置項(xiàng)

• 連接健康檢查和超時(shí)配置項(xiàng)

• 危險(xiǎn)的超時(shí)配置項(xiàng)

• 底層網(wǎng)絡(luò)配置項(xiàng)

1. 創(chuàng)建一個(gè)pgbouncer.ini文件

簡例：

[databases] 
postgres = --databases
host=127.0.0.1 
port=5432 
dbname=template1 
[pgbouncer] 
listen_port = 666 
listen_addr = 127.0.0.1
auth_type = md5 
auth_file = /home/postgres/pgbouncer/user.txt
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
admin_users = someuser
pool_mode = Transaction

2. 配置文件詳解

[databases]部分配置

數(shù)據(jù)庫連接字符串：database name = connect string連接字符串參數(shù)有：

dbname=
（這種指定的的數(shù)據(jù)庫名為真實(shí)的數(shù)據(jù)庫名 與前面的 database name 應(yīng)該與其一致，試過 bazdb = host=127.0.0.1 port=5432 dbname=sheen,結(jié)果在pgadmin 端進(jìn)行連接時(shí)，可以連接到pgbouncer，也能看到 連接的服務(wù)器端數(shù)據(jù)庫集群中所有的數(shù)據(jù)庫，但是卻不能連接，會(huì)報(bào)找不到指定數(shù)據(jù)庫，連 數(shù)據(jù)庫名是時(shí)sheen 的也不能連接，改為 sheen = ... dbname=sheen  才可連接到 sheen 這個(gè)數(shù)據(jù)庫上）。

host=
要連接的數(shù)據(jù)庫的主機(jī)IP

port=
要連接的數(shù)據(jù)庫的監(jiān)聽端口（默認(rèn)5432）

user=
如果指定了用戶名，所有的連接到該設(shè)置的數(shù)據(jù)庫連接都會(huì)以指定的用戶名進(jìn)行連接，那么也意味著所有的連接在連接該數(shù)據(jù)庫時(shí)都只能共用一個(gè)連接池，如果沒有設(shè)置，那么pgbouncer 在連接該數(shù)據(jù)庫時(shí)，將會(huì)使用客戶端的用戶名。這種情況下是一個(gè)用戶獨(dú)自使用一個(gè)連接池

password=指定用戶登錄數(shù)據(jù)庫的密碼

client_encoding=指定客戶端的編碼格式

datestyle=指定日期格式

timezone=指定時(shí)區(qū)

pool_size=
這個(gè)數(shù)據(jù)庫連接每個(gè)連接池最大的連接數(shù)

connect_query=
建立連接后執(zhí)行的sql 查詢，如果查詢失敗將會(huì)被記錄在日志中，沒有設(shè)置將會(huì)被忽略

sheen = host=127.0.0.1 port=5432 dbname=sheen

fsdb_paas = host=127.0.0.1 port=5432 dbname=postgres connect_query=SELECT 1

wenzhong = host=127.0.0.1 port=5432 dbname=wenzhong connect_query=SELECT 1

針對庫 postgres 創(chuàng)建了一個(gè)連接池，該連接池對調(diào)用方的呈現(xiàn)的數(shù)據(jù)庫名為  fsdb_paas，它映射到了 postgres庫上，即所有訪問 pgbouncer上的 fsdb_paas的請求都會(huì)轉(zhuǎn)到 postgres庫上完成。

pgbouncer 的相關(guān)設(shè)置：

logfile=/dir/of/logfile/pgbouncer.log 日志文件的存放位置
pidfile=/dir/of/pidfile/pgbouncer.pid pid 文件的存放目錄
listen_addr = 指定監(jiān)聽的IP 如不做對指定客戶端監(jiān)聽 使用 * 監(jiān)聽所有
listen_port = pgbouncer 的監(jiān)聽端口
使用unix 套接字的話，使用如下參數(shù)設(shè)置
unix_socket_dir=
unix_socket_mode=
unix_socket_group=

授權(quán)設(shè)置

auth_type = trunst 授權(quán)方式 （any ,trust , plain , crypt , md5)
auth_file = /etc/pgbouncer/userlist.txt 授權(quán)文件，到指定的文件中讀取用戶及密碼，也只有文件中存在的用戶才能登錄
                   userlist.txt 的格式："username" "password"
auth_type 和 auth_file 是 pgbouncer用于完成對客戶端身份認(rèn)證的配置項(xiàng)， auth_file中保存用戶名和密碼，根據(jù)驗(yàn)證方式auth_type的不同，auth_file中的內(nèi)容也不同。
md5: 基于md5的密碼驗(yàn)證，auth_file中需要有普通文本和md5值兩種形式的密碼；
crypt: 基于crypt的密碼驗(yàn)證(man 3 crypt), auth_file必須包含文本密碼；
plain: 明文驗(yàn)證方式；
trust: 不進(jìn)行驗(yàn)證，但auth_file依然需要保存用戶名；any: 也不進(jìn)行驗(yàn)證，而且auth_file中不需要保存用戶名了。但此種方式需要在pg_template1中明確說明用戶名進(jìn)行真實(shí)數(shù)據(jù)庫的登錄。如: pg_template1?=?host=127.0.0.1 user=exampleuser dbname=template1.否則會(huì)報(bào)錯(cuò)的。
注意：auth_file中的用戶名、密碼都必須使用雙引號。

允許登錄pgbouncer 的用戶

admin_users= 管理用戶名，隨便設(shè)置一個(gè)名字，用于登錄pgbouncer 數(shù)據(jù)庫用，這里設(shè)置的用戶具有更改設(shè)置的權(quán)限

stats_users = 只允許使用show 命令的用戶名

連接池設(shè)置

pool_mode =
 (session ,transaction , statement 上面已經(jīng)有說過3種連接方式)

server_reset_query =
DISCARD ALL   pg 8.3 后使用該參數(shù)，清空server_reset_query。
當(dāng)上一個(gè)查詢連接釋放后，確保可以給下一個(gè)客戶端使用前連接中沒有任何事物，也不能包含 ABORT 或者ROLLBACK

max_client_conn =
允許客戶端的最大鏈接數(shù)。如果連接串中沒有指定用戶名的 max_client_conn + (max_pool_size * total_databases * total_users)如果連接串中指定了用戶名的 max_client_conn + (max_pool_size * total_databases)

default_pool_size 鏈接池的大小（per user/database）

reserve_pool_size 當(dāng)發(fā)生問題時(shí)，額外的鏈接數(shù)

reserve_pool_timeout 額外鏈接超時(shí)

log_pooler_errors 記錄從連接池發(fā)送到客戶端的錯(cuò)誤信息

超時(shí)設(shè)置

server_lifetime 默認(rèn)值 1200 秒  連接到服務(wù)器端超過這個(gè)時(shí)間自動(dòng)關(guān)閉連接

server_idle_timeout = 60 空閑連接超過60秒關(guān)閉連接

server_connect_timeout = 15 連接超時(shí)，鏈接服務(wù)器超15秒沒響應(yīng)取消

server_login_retry = 15 登錄服務(wù)器失敗后重連的時(shí)間間隔 默認(rèn)15秒

client_login_timeout=60

autodb_idle_timeout = 3600

危險(xiǎn)的超時(shí)設(shè)置

query_timeout 默認(rèn)0 關(guān)閉，當(dāng)查詢超過設(shè)置的時(shí)間關(guān)閉

query_wait_timeout默認(rèn)0 關(guān)閉,當(dāng)查詢等待服務(wù)器端響應(yīng)超過設(shè)置時(shí)間關(guān)閉

client_idle_timeout默認(rèn)0 關(guān)閉,當(dāng)客戶端在設(shè)置實(shí)際內(nèi)沒有活動(dòng)，連接關(guān)閉

底層參數(shù)調(diào)節(jié)

pkt_buf = 2048 流包緩沖區(qū)大小

listen_backlog =128 確定隊(duì)列中保存多少新的未答復(fù)的連接嘗試。當(dāng)隊(duì)列已滿，進(jìn)一步新的連接將被丟棄

網(wǎng)絡(luò)層參數(shù)調(diào)節(jié) tcp

tcp_defer_accept (linux 默認(rèn)45 其他OS 為0）
tcp_socket_buffer (linux 默認(rèn)4096）
tcp_keepalive (0/1)

下面的參數(shù)只有在linux 中可以設(shè)置，同樣需要設(shè)置tcp_keepalive =1

tcp_keepcnt=
tcp_keepidle=
tcp_keepintvl=
dns_max_ttl
dns_zone_check_period=

3. 簡易版的pgbouncer 連接配置

[databases]
sheen = host=127.0.0.1 port=5432 dbname=sheen
[pgbouncer]
logfile=/dir/of/logfile/pgbouncer.log
pidfile=/dir/of/pidfile/pgbouncer.pid
listen_addr =*
listen_port =6432
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
adminuser=pgbouncer

配置pgbouncer.ini 的時(shí)候，在原來自帶的那份 pgbouncer.ini 上面進(jìn)行修改就好了，要啟用哪個(gè)參數(shù) 前面的;去掉，設(shè)上相應(yīng)的值即可

userlist.txt 生成方式

postgres=# copy (select usename,passwd from pg_shadow) to /etc/pgbouncer/userlist.txt;
或
postgres=# o /etc/pgbouncer/userlist.txt
postgres=# select usename,passwd from pg_shadow;

第二種方法：使用 pgbouncer 自帶的工具   mkauth.py 工具導(dǎo)出用戶名和密碼；這種方法需要首先安裝 psycopg2這個(gè)python模塊，因?yàn)閙kauth是利用該模塊連接postgres數(shù)據(jù)庫的。

mkauth腳本使用格式方法：
./mkauth.py 密碼文件名 連接參數(shù)

[root@vlnx107001 pgbouncer]# ./mkauth.py /etc/pgbouncer/userlist.txt  "host=127.0.0.1 user= zhaowz password=zhaowz dbname=postgres"

4. 重點(diǎn)參數(shù)介紹

pool_mode

有三種模式:session,transaction,statement

session ：是默認(rèn)的模式,每開啟一個(gè)進(jìn)程，DB端也會(huì)開啟一個(gè)新的進(jìn)程

transaction ：是基于事務(wù)模式的

statement ：是基于每個(gè)查詢的，開啟此模式不適合執(zhí)行事務(wù)，會(huì)報(bào)錯(cuò)。

原文見安裝后pgbouncer中的usage.txt,
本次文件地址：
/home/postgres/pgbouncer/share/doc/pgbouncer

auth_type和auth_file

這兩個(gè)是pgbouncer用以完成客戶端身份認(rèn)證參數(shù)。auth_file中保存用戶名和密碼，根據(jù)驗(yàn)證方式(auth_type)的不同，auth_file的內(nèi)容也有不同。

md5:
基于md5的密碼驗(yàn)證，auth_file中需要有普通文本和md5值兩種形式的密碼;

crypt:
基于crypt的密碼驗(yàn)證(man 3 crypt), auth_file必須包含文本密碼;

plain:
明文驗(yàn)證方式;

trust:
不進(jìn)行驗(yàn)證，但auth_file依然需要保存用戶名;

any:
也不進(jìn)行驗(yàn)證，而且auth_file中不需要保存用戶名了。但此種方式需要在pg_template1中明確說明用戶名進(jìn)行真實(shí)數(shù)據(jù)庫的登錄。
如: pg_template1 = host=127.0.0.1 user=exampleuser dbname=template1.否則會(huì)報(bào)錯(cuò)的。

需要說明的是：auth_file中的用戶名、密碼都必須使用雙引號，否則還是報(bào)錯(cuò)。

"postgres" "123456"

"postgres" "md5a3556571e93b0d20722ba62be61e8c2d"

postgres=# o userlist.txt
postgres=# SELECT rolname,rolpassword from pg_authid where rolname = postgres;
postgres=# o
postgres=# q

5. 參數(shù)修改

如果修改了ini文件中相關(guān)參數(shù)，需要通過命令告知bouncer重新讀取配置內(nèi)容：

pgbouncer=# reload;

未完待續(xù)

更多精彩干貨分享

點(diǎn)擊下方名片關(guān)注

IT那活兒