POSTGRESQL 和 MYSQL 到底應該不應該控制活躍連接

開頭還是介紹一下羣，如果感興趣 polardb ,mongodb ,mysql ,postgresql ,redis 等有問題，有需求都可以加羣羣內有各大數據庫行業大咖，CTO，可以解決你的問題。加羣請聯繫 liuaustin3 ，在新加的朋友會分到 2 羣（共 650 人左右 1 + 2）。

最近羣裏某個同學的提問，引起的本篇文章，關於數據庫連接的部分，沒有廢話，我們先針對 MYSQL 來說說數據庫連接的部分。

首先 MYSQL 的客戶連接方式是通過線程的方式來進行客戶和數據庫之間的連接，在連接被使用的過程中，會出現活躍連接和 idel 連接，我們稱之爲空閒連接。

這裏我們假設客戶的連接是通過  libmysqlclient 連接到我們的 MYSQL 中的通過我們最常用的 TCP/IP 的協議方式通過 3306 端口，而在接受到連接手，MYSQL 通過我們的隊列形勢，我們可以稱之爲接收隊列來將我們客戶發來的請求進行排隊。

這裏的一個關鍵是線程的緩存，緩存中會存在已經創建好的線程，如果發現當前的線程並不足以滿足客戶的需求，則會開啓新的線程給新的來訪者進行數據庫的訪問。那麼控制來訪者與線程緩存之間存在一個複用的關係，第一個問題在 mysql 中 thread cache 可以緩存多少線程滿足用戶的訪問。

公式爲：8 + （max_connections/100） = thread_cache_size

舉例你的系統最大知識 2000 個連接（不是併發連接是最大連接），那麼你的 thread_cache_size = 8 + (2000/100) = 208

實際上也就是在變相的告訴使用者，我最大可以緩存的連接數複用的是 208。實際上如果你的一半的主機配置（8C 32 是無法達到這個最大活躍連接數的，一半以我們測試的經驗這個配置的機器 60 的併發連接數據已經是一個較高的值了，在超過這個值你的 MYSQL 很可能出現無法響應的問題）

在我們的使用中瞬時連接在 30 以內屬於性能良好的範疇（因人而異，不是一個標準值是一個經驗值, 具體你的系統是多少，這個你自己的去摸索和評估）

而在 MYSQL 的每個線程連接中有一個 THD 的部分，這個部分是你在創建這個線程時共同創建出來的，這個部分就是收集你這個連接的狀態信息的部分，代碼在 sql_class.h 中，這個部分的內存會隨着連接的時間而增長，有的時候每個連接的佔用的內存可能會達到 10MB。這裏存儲着你的線程在處理任務中的事務執行中的上下文，事務的狀態會話中的一些變量，臨時表，等等

在連接使用完畢後，會進行釋放這還是由客戶端發起的 com_quit  信號，通過他來將你的客戶與 MYSQL 的線程進行分離，分離後 THD 使用的內存將被釋放，並且這個線程將重新放回到 thread cache 中。

實際上一個線程在處理任務中的三個關鍵因素

1  互斥鎖

2  數據庫鎖

3  IO 

一個線程在處理一個事務的過程中，會考慮在處理事務的情況下是否有其他的線程也在處理與我同樣的資源，互斥鎖的主要目的就是，獨佔性，通過獨佔來滿足一個線程處理數據時的排他性。數據鎖 (如行鎖) 通常會保護一個線程正在更新的數據不被另一個線程讀或寫。元數據鎖通常會保護數據庫模式不受併發的、不兼容的更新的影響，而 IO 是在數據處理中，一個任務等待處理的數據通過 IO 操作讀入到內存的一個等待的事件。最終一個線程的整體操作會受到這三個方面的限制和控制。

而系統性能消耗最大的部分就是一個線程處理不完他的任務，而另一個需求已經來了，那麼系統會不斷開心的線程滿足客戶的需求，直到你的系統的資源出現瓶頸，可能是 IO 也可能是 CPU 或內存。

那麼怎麼控制這個問題，有同學試圖從數據庫入手來解決這個問題，比如降低 max_connections  ，或者通過降低  thread_cache_size 的方式，這些都是不可取的想法，首先數據庫是一個包容性的集中處理數據的機構，任何想通過各種設置，降低客戶訪問便利性的想法都是對於數據庫本身運行的機制的一種誤解。

實際中正確的控制的方式應該是從軟件的形成方來進行控制，如他們的軟件的連接數的設置，連接池的設計與配置，等等，DBA 工作的方向是和開發聯合進行溝通和正確的數據庫連接的使用，而不是自行進行活躍連接數據的限制這樣的想法和做法。

而羣裏面提到的 innodb_commit_concurrency, 並不是一個可以控制併發連接數的設置，他的主要的初衷是控制併發線程在同一個時刻可以進行 commit 的數量，這實際上與活躍連接控制無關。具體他是做什麼的，可以參考下面的文字。

https://www.percona.com/blog/innodb-thread-concurrency/

反過來我們在說說 POSTGRESQL ，PG 工作的客戶連接是進程的模式，與 MYSQL 是不一樣的，客戶通過 libpq 的方式與 PG 建立連接，客戶首先會與主進程進行訪問的申請後，建立 backend process 的進程與數據庫進行數據處理，這裏每個進程在 PG14 並不是自己進行信息的統計，而是通過另一個進程來進行相關的每個進程的工作信息的收集，這個進程是 statistics collector 。

與 mysql  不同的是 POSTGRESQL 有一個統一管理客戶進程內存的參數, work_mem  他來提供客戶端訪問數據庫的使用的內存。相比較線程的模式，進程的模式以及 POSTGRESQL 對於客戶連接的處理上，POSTGRESQL 的使用是更願意複用的，也就是將一個連接給多個應用在不同的時間部分進行利用，所以 PG 有一個數據庫連接池 pgbouncer 的產生，來去做這個複用的部分。

如果你不想使用 pgbouncer 的情況下，建議不要長時間一個進程被應用程序霸佔時間太長，你的應用線程池需要進行合理的設置，在多長時間釋放掉連接，但是 POSTGRESQL 有一個特性是並行，並行查詢中是可以利用多個 CPU 爲一個 SQL 進行服務的，在這樣的情況下，需要評估你的 CPU 的數量，與你參數 max_worker_processes 和 max_parallel_workers 的配置。

控制 PG 的連接的幾個部分參數有一下，建議不熟悉 PG 的同學先把以下的參數與以及功能弄清楚

max_connections

work_mem

max_worker_processes = 8             

max_parallel_workers_per_gather = 4    

max_parallel_maintenance_workers = 2    

max_parallel_workers = 8         

下圖爲 POSTGRESQL 客戶連接進程與 PG 內部進程  

另外一個問題，爲什麼 MYSQL 是線程，POSTGRESQL 是進程，

進程比線程更沉重，每個進程都有自己的虛擬內存，它維護稱爲 PCB(進程控制塊) 的元數據，其中包括用於將虛擬地址映射到物理地址的頁表以及有關進程的任何其他元數據。PCB 必須存儲在內存中，並進入 CPU 緩存寄存器，將虛擬內存地址轉換爲物理地址。另一方面，線程與它們的父進程共享虛擬內存空間，並且它們的 TCB(線程控制塊) 通過指向父進程 PCB 的指針要小得多。所以線程的緩存命中率要比進程高得多。但是在早期的系統設計中有一個概念，線程相對於進程是不穩定的，基於這個理念 PG 在設計中採用了進程，而不是線程來設計。

如果對此有疑問，可以自行查找 MYSQL 5.0 的穩定性的一些歷史文章和問題。

總結：控制數據庫的活躍線程，是一個僞命題，無論 MYSQL 或 PG 都不應該支持這樣的想法，應用設計模塊該去解決的問題，應該去對應的部分去解決，而不是將所有的問題都塞給數據庫。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/mrjYtukxkyE2CBDMtMNE8Q