<Meta name="keywords" content="PostgreSQL, pgBouncer"/>

# pgBouncer 筆記
* [官方文檔](https://www.pgbouncer.org/)
---
### 為什麼需要 pgBouncer?

PostgreSQL 每建立一個 Session，就是一個 Process 專門服務該 Session。當連線量大、有大量閒置連線，或有頻繁連線、斷線的情況時，會使 PostgreSQL 產生很大的維護成本。

pgBouncer 是開源的連線池管理工具，目的是改善閒置連線、短期連線的資源使用，降低 PostgreSQL Server 因連線管理導致的性能損失，並重複使用 Session 以減少 PostgreSQL Server Backend Process 的數量，節省系統資源以提供更好的資料庫性能。

pgBouncer 使用非同步 I/O 的更輕量型模型，只在需要時 (例如開啟的 Transaction 或查詢作用中時) 使用實際的 PostgreSQL 連線，可以在低負荷下輕鬆支援數千個連線。

---
### pgBouncer 機制簡介
所有連線都是透過 pgBouncer (Port 預設 6432) 對 PostgreSQL 發出請求，保護資料庫不受異常連線影響、維持正常作業。

收到連線請求後，會查看 Pool 是否有空位可提供連線，沒有的話，在資料庫連線數未達上限 (max_connections) 的狀況下會建立一個連線到 Pool 並提供連線，否則就會安排等待有連線被釋回 Pool 中，再行連線。


其連線控制有三種層級:

1. Session pooling: (預設模式) 對於連線請求，分給一個 Session 到它結束。
2. Transaction pooling: 一次交易結束，才把連線交還 Pooling。
3. Statement pooling: 一個 Query 結束，就把連線交還 Pooling。(最激進的模式，此模式不允許一次提交多個 Query Statement。)

---

### pgBouncer 安裝

Docker Compose 配置可以參考我的 [GitHub](https://github.com/michelle0809/PostgresDockerCompose)。

pgBouncer 的 Config 設定檔是 pgbouncer.ini。
對資料庫的連線設定需在此文檔的 [databases] 區域設定連線資訊。
pgBouncer 對每個資料庫採用分開的 Pool，故==每個資料庫要單獨一行設定==。
我的 PosgreSQL 建有 postgres、test_database 兩個資料庫，故在 pgbouncer.ini 會看到有兩行資料庫連線設定。

要能對 pgBouncer 的 Port 連線，必須要把使用者帳號、密碼登記在 auth_file 變數指定的檔案 userlist.txt 中，否則會回報 'psql: error: FATAL:  password authentication failed'。

---

### 常用指令
1. `SHOW POOLS;` 查看連接池中所有連線，包含連線資料庫、連線使用者等資訊。
2. `SHOW CLIENTS;` 列出客戶端連線資訊，有幾個連線就會列出幾筆，並會記錄其連線建立的時間。
3. `SHOW STATES;` 各個資料庫的統計資訊，例如透過 pgBouncer 執行了幾筆交易、幾筆 Query 等訊息。
4. `RELOAD;` 重新載入配置。當修改 pgbouncer.ini 或 userlist.txt 等配置文件後，需執行此命令，配置才會生效。
5. `DISABLE {DB名稱};`、`ENABLE {DB名稱};` 關閉、開啟對某資料庫的連線。

---

### pgBouncer 效能測試

資料庫用 PostgreSQL 17 預設參數，以 [pgbench](https://docs.postgresql.tw/reference/client-applications/pgbench) 簡單測試運行狀況。

1. 初始化資料庫，建立測試資料

    pgbench -i --host localhost --port 5432 -U postgres test_database


2. 執行測試
* `-C`: 在每一個交易執行前都重新建立連線，而不是都在同一個用戶連線中完成全部交易。這在測試連線成本時特別有用。
* `-c`: 模擬用戶的數量，指的是同一時間連入資料庫的連線數，模擬 10、50、100、150。(ps. PostgreSQL 預設參數的 max_connection = 100)
* `-t`: 每一個模擬用戶端要執行的交易數量。
* `-v`: Vacuum All。
* `-port`: 分別對 5432、6432 執行，比較沒有/有透過 pgBouncer 連線的情況。

    pgbench --host localhost --port {port} -U postgres -t 50 -c {用戶數量} -v -C test_database

3. 驗證 KPI
* number of transactions actually processed
* 交易的實際完成數/應完成數，通常實際完成數=應完成數，但若發生連線數爆量 (超過 max_connections) 無法負荷的情況，實際完成數<應完成數。
* Clinens 數超過 max_connections 會顯示以下錯誤
* latency average
* 平均延遲(ms)，越小越好。
* tps (including connections establishing)
* 平均的交易完成率(TPS, Transaction Per Second)，包含連線建立和終止時間，越高越好。此項可以視為整個測試的總體性能。
* tps (excluding connections establishing)
* 平均的交易完成率(TPS, Transaction Per Second)，排除連線建立和終止時間，越高越好。

4. 模擬不同的連線數，分別對 5432、6432 Port 執行測試，並記錄其結果。
在 PostgreSQL 參數 max_connections = 100；pgBouncer 參數 pool_mode = session、==max_client_conn = 1000== 的情況下，差異如下。
* 可負荷連線量大幅提升。在用戶數量 100、每個用戶 commit 50 筆交易的情況下，直接連線的方式會超過資料庫可接受連線數而報錯；而==透過 pgBouncer 連線則會讓這些連線排隊等候 session 釋出==，而不會直接報錯。
注意==須將 max_client_conn 配置比 PostgreSQL max_connections 高的數值==才會有排隊的效果。
* 交易的平均延遲 (ms) 約降低 75-85%。
* 建立連線時間 (ms) 約降低 80-90％。
* 每秒交易數量 (TPS) 提升至少 300%。