在Postgre中設定max_connections時，為什么需要使用連接池 （譯）

原文地址：https://www.enterprisedb.com/postgres-tutorials/why-you-should-use-connection-pooling-when-setting-maxconnections-postgres

PostgreSQL是“世界上最先進的開源資料庫”，我相信這一點，在我從事it作業的10多年中，它一直很穩定，向SaaS提供每秒超過1000個查詢的資料，很少出現故障，經受住了各種形式的事故（最終證明是軟體工程錯誤）和性能下降（最終證明是用戶錯誤）的指控，它有如此多的特性和擴展，可以滿足每一個需求，可能有50-60%的用戶不經常使用，或者根本沒有聽說過，不幸的是，在我最近的技術支持經驗中，我了解到Postgres通常是非常值得信任的，但是很多技術都是如此；它不會判斷您應該如何調整postgresql.conf中的引數，就像跑車上的油門踏板會影響行駛速度的一樣，僅僅因為您可以踩到油門踏板上，并不意味著您應該在高峰時間執行此操作，因此并不是說將某些引數值其設定得很高，就適合并發較高的OLTP應用程式，   最容易引起誤解的引數之一是max_connections，可以理解的是，在擁有大量CPU和大量RAM的現代系統上，向全球用戶群提供現代SaaS負載，一次可以看到成千上萬個用戶會話，每個會話都試圖查詢資料庫以更新用戶狀態、上載自拍或其他用戶可能做的事情，當然，DBA會希望在postgresql.conf設定與應用程式發送到資料庫的流量相匹配的值，但這是有代價的，這種代價的一個例子是生成連接/斷開連接的延遲；對于創建的每個連接，作業系統都需要為打開網路套接字的行程分配記憶體，PostgreSQL需要自己進行內部計算來建立連接，將其擴展到數千個用戶會話，而僅僅為用戶準備好資料庫連接就可能浪費大量時間，將max_connections設定為高的其他成本包括磁盤爭用、作業系統調度，甚至CPU級快取線爭用，  

max_connections究竟該設定為多大

沒有太多科學資料可以幫助DBA將max_connections設定為其適當的值，因此，大多數用戶發現PostgreSQL的默認值max_connections = 100太低，不斷有人將其設定為4k，12k甚至30k以上（這些人都遇到了一些主要的資源爭用問題），與那里的任何PostgreSQL專家交談，他們將給您一個范圍“幾百個”，或者有些人會斷然地說“不超過500個”，并且“絕對不超過1000個”，但是這些數字從何而來？他們怎么知道的，我們該如何計算呢？提出這些問題，可能只會發現自己更沮喪，因為有一種公式化的方法來確定該數字，設定此值的困難在于資料庫需要服務的應用程式，一些應用程式發送大量查詢并關倍訓話，而其他應用程式可能會突然發送查詢，并且之間有很多空閑時間，此外，某些查詢可能會花費大量CPU時間來執行聯接和排序，而其他查詢則會花費大量時間順序掃描磁盤，我見過的最合理的答案是計算CPU的數量，占利用率的百分比（基于一個基準測驗需要做的），然后乘以比例因子，但這甚至涉及一些“hand-waving”（譯者注：專門搜索了文獻，這里可以理解成拍腦袋做出的決定），  

測驗tribal知識(譯者注：原文作者用tribal這個詞是類似于"江湖傳聞"的一詞的語音)

沒有一種非常簡明清晰的方法來計算max_connections，我決定至少要測驗那里的tribal知識的有效性，確實應該是“幾百個”，“不超過500”和“絕對不超過1000”嗎？為此，我設定了一個AWS g3.8xlarge EC2實體（32 CPU，244GB RAM，1TB 3K IOPS SSD）來慷慨地模仿我在那里看到的一些資料庫服務器，并使用--scale = 1000初始化了一個pgbench實體， 我還設定了10個較小的EC2實體，用作應用程式服務器，并且在每個實體上運行pgbench測驗一小時，每小時將--client = NUM遞增（因此它們將總共創建100,200,300 ... 5000個連接每小時的測驗）， autovacuum已關閉，以防止任何不必要的干擾導致結果偏差（盡管我在每次測驗之間都進行了清理），否則將postgresql.conf調整為一些通常可接受的值，我將max_connections設定為12k，以確保我的測驗使用的不超過最終測驗中要求的5000，測驗運行時我走開了，結果又回來了，就像這樣：

下面是上圖的放大圖:

因此，對于改進后的設定使其類似于某些企業級計算機的服務器，最佳性能出現在300-500個并發連接的時候，在700之后，性能急劇下降（就每秒事務數和延遲而言）， 1000個以上的連接均表現不佳，并且延遲不斷增加，最后，等待時間開始是非線性的-這可能是因為我沒有將EC2實體配置為允許超過默認的~25M open filehandles，到在連接數超出3700之后，看到幾個無法fork新的連接程序： could not fork new process for connection: Resource temporarily unavailable.
有趣的是，這三中說法都匹配了：“幾百個”，“不超過500個”和“絕對不超過1000個”，這似乎太不可思議了，所以我再次進行了測驗，直到增加到最大1800個并發連接，結果：

因此，對于該服務器而言，最有效的點似乎確實在300-400個連接之間，并且max_connections不應設定得比該值高很多，以免我們冒失去性能的風險，

譯者注：這個結果是匹配原文作者的服務器配置：32 CPU，244GB RAM，1TB 3K IOPS SSD，應該是沒有啟動連接池的情況下得到的一個結果，而不是一個參考值或者標準值

如果需要更多的連接數，該怎么辦？

顯然，max_connections = 400不會允許高并發的應用程式處理用戶提供給它的所有作業，某種程度上，需要擴大資料庫以滿足這些要求，但是這樣做似乎需要一些魔法，一種選擇是設定復制系統，以使讀取分布在多個服務器上，但是如果并發寫超過400個并發會話（這很有可能），則需要考慮其他選擇，通過允許多個客戶端會話共享資料庫連接池并根據需要執行讀寫事務，并在空閑時將資料庫連接的控制權移交給其他會話，連接池將滿足此需求，在PostgreSQL社區中，池化應用程式的主要參與者是pgbouncer和pgpool-兩者都經過了充分的測驗，以使DBA能夠將其PostgreSQL資料庫擴展到成千上萬的并發用戶連接， 為了演示在使用連接池時提高的可伸縮性，我設定了一個類似于Alvaro Hernandez’s concurrent-connection test的m4.large EC2實體（譯者注：上述連接在原文中也打不開，另外，m4.large配置為2 個 vCPU，8GiB 記憶體）， 因為：1）我想使用一個不僅僅是我自己的基準數字；2）我想要節省一些成本，然后嘗試得到與他相似的圖形（中的結論）：
譯者注：
上述截圖的大概的結論是：基于m4.large當前這個配置，在小于200個并發連接的時候，TPS會隨著并發連接數的增加而增加，以下是原文的作者是嘗試驗證Alvaro的測驗結論，也就是上面這個截圖的結果，從下文看，不管用不用連接池，兩種情況下都無法得到這個結論，這說明Alvaro這個人的測驗結果是值得商榷的，在這種配置下，10個并發連接得到的TPS才是最高的，
但是，創建此圖時在pgbench中沒有-C /-connect標志（譯者注：為每個事務建立新的連接，而不是在一個連接中完成多個事務操作），這可能是Alvaro不是為了說明使用連接池的優點，因此，我重新運行了相同的測驗，但是這次使用了-C： 如我們所見，由于每個事務都必須連接和斷開連接，所以吞吐量下降了，這說明了建立連接的需要花費成本，然后我將pgbouncer配置為max_client_conn = 10000，max_db_connections = 300，pool_mode = transaction，然后再次使用pgbouncer埠運行相同的pgbench測驗（-h <hostname> -p6432 -U postgres --client=<num_clients> --progress=30 --time=3600 --jobs=2 -C bouncer）：

顯然，雖然pgbouncer維護了與資料庫的開放連接并與傳入的客戶端共享它們，但連接開銷卻降低了，從而提高了吞吐量，請注意，即使使用連接池池管理器，我們也永遠無法獲得Alvaro的圖，因為在啟用連接時總會有一些開銷（即，客戶端需要告訴OS分配一些空間并打開一個套接字以實際連接到pgbouncer） ，

結論

如我們所見，max_connections應該通過一些現場基準測驗和一些自定義腳本來確定（請注意，所有這些測驗都使用了內置的pgbench事務，該事務包含3個UPDATE，1個SELECT和1個INSERT-a 可以通過提供自定義.sql檔案并使用-f /-file標志來創建真實測驗），從根本上說就是，做一下家庭作業：（基于自己的服務器）進行基準測驗，找出可以可以提供良好性能的最大并發度，四舍五入到最接近的百位數（為您留出一些余地），并相應地設定max_connections，設定后，希望通過復制或連接池的任何組合來滿足并發性的所有其余要求，連接池是任何高吞吐量資料庫系統的重要組成部分，因為它可以消除連接開銷，并為較小的資料庫連接集保留更多的記憶體和CPU時間，從而防止不必要的資源爭用和性能下降，         譯者的總結
1，由資料庫最大連接數聯想到的多執行緒： 資料的最大連接數，也就是可支持的最大并發連接數，聯想到多執行緒，多執行緒總給人一種牛逼轟轟的，不用多執行緒就上不去臺面的感覺，其實除了CPU密集型的運算之外，而日常的大部分操作，都是和IO以及網路密切相關的，處理某個單一任務，多執行緒或者說純粹增加執行緒資料并不一定提升效率，這兩者相比CPU，都會先于CPU成為瓶頸，也就是說不等你并發起來，IO和網路已經成為瓶頸了， 以下摘自于：資料庫連接池到底應該設多大？這篇文章可能會顛覆你的認知 這篇文章中的部分章節

1，即使是單核CPU的計算機也能“同時”運行數百個執行緒，但我們都[應該]知道這只不過是作業系統用時間分片玩的一個小把戲，
一顆CPU核心同一時刻只能執行一個執行緒，然后作業系統切換背景關系，核心開始執行另一個執行緒的代碼，以此類推，
給定一顆CPU核心，其順序執行A和B永遠比通過時間分片“同時”執行A和B要快，這是一條計算機科學的基本法則，一旦執行緒的數量超過了CPU核心的數量，再增加執行緒數系統就只會更慢，而不是更快，

2，在這一時間段（即"I/O等待"）內，執行緒是在“阻塞”著等待磁盤，此時作業系統可以將那個空閑的CPU核心用于服務其他執行緒，
所以，由于執行緒總是在I/O上阻塞，我們可以讓執行緒/連接數比CPU核心多一些，這樣能夠在同樣的時間內完成更多的作業，
那么應該多多少呢？這要取決于磁盤，
較新型的SSD不需要尋址，也沒有旋轉的碟片，
可別想當然地認為“SSD速度更快，所以我們應該增加執行緒數”，恰恰相反，無需尋址和沒有旋回耗時意味著更少的阻塞，所以更少的執行緒[更接近于CPU核心數]會發揮出更高的性能，只有當阻塞創造了更多的執行機會時，更多的執行緒數才能發揮出更好的性能，

最大連接數計算公式
下面的公式是由PostgreSQL提供的，不過我們認為可以廣泛地應用于大多數資料庫產品，你應該模擬預期的訪問量，并從這一公式開始測驗你的應用，尋找最合適的連接數值，
連接數 = ((核心數 * 2) + 有效磁盤數)

公理：你需要一個小連接池，和一個充滿了等待連接的執行緒的佇列
如果你有10000個并發用戶，設定一個10000的連接池基本等于失了智，1000仍然很恐怖，即是100也太多了，你需要一個10來個連接的小連接池，然后讓剩下的業務執行緒都在佇列里等待，連接池中的連接數量應該等于你的資料庫能夠有效同時進行的查詢任務數（通常不會高于2*CPU核心數）

這段話反復琢磨了很多次 對于非CPU密集型計算來說，單純滴增加執行緒數可能會降低整體運算效果，然后想到了一個問題：為什么單執行緒的Redis單實體可以有10+QPS的超級回應速度，這里強調的單執行緒，除了哪些面試套路性的答案之外，其實很多問題上面也能夠說明其原理， 1，redis基于記憶體的操作，IO的阻塞延遲相比SSD和HDD，分別低了3/6個數量級，可以認為基于記憶體的IO延遲是極底的 2，基于1，在沒有IO阻塞或者IO延遲極地的情況下，相比多執行緒，單執行緒其實要比多執行緒更快，   2，如何處理對資料庫的并發請求數大于資料庫的最大連接數： 如果連接數是一個合理的值，不是100或者200，真正的并發數超出最大連接數，此時必須使用連接池，否則會報超出最大連接數的錯誤，連接池不僅可以提高連接效率，在超出最大連接后等待機制，也可以起到了限流的作用， 而如果真的不去限制最大連接數，隨著并發的增加去機械地增加連接數，只會沖垮資料庫，即便沖不跨資料庫，其結果就是所有的請求，在資料庫里相互牽制相互拖累，  

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/230265.html

標籤：其他

上一篇：MySQL資料庫基礎學習筆記

下一篇：力扣資料庫題目182查找重復的電子郵箱