SQL Connection Pooling 行為觀察
4 |
前些時候幫忙排除一個 DB Connection Pool 相關問題,學到點東西,所以有了這篇。
這篇文章將介紹如使用效能監視器觀測 SQL Connection Pool 使用狀況,並以實驗驗證其行為模式。
如果對 Connection Pool 還有點陌生,以下是一些參考資料:
- SQL Server Connection Pooling (ADO.NET) - Microsoft Docs
- .NET Connection Pool 與連線相關問題整理 by Huanlin 學習筆記
- SQL 資料庫 Connection Pool 連線池觀念釐清 by 從入門到放棄
- Performance Counters in ADO.NET - Microsoft Docs
Connection Pool 的原理跟用途在此不贅述,簡單來說是因為跟資料庫伺服器建立連線是很耗資源的事,所以用完 IDbConnection 後系統不馬上切斷連線,會保留在 Pool 裡一陣子,等下有其他連線需求時重複利用,省去重新建立連線的時間跟資源消耗。基本上,各家資料庫客戶端程式庫預設都會啟用 Pooling,它已是當代資料庫連線作業中重要的一環。
開始前,先整理 SQL Connection Pool 的一些行為特性及預設值:
- 連線字串要完全相同才能共享 Pool,故要避免動態產生不同連線字串以免搞出一堆 Pool 失去共用資源的意義(術語叫 Pool Fragmentation)
- Pool 最大連線數(Max Pool Size)預設為 100 條
- Pool 連線數達上限時需排隊等待,若逾時將拋出錯誤,預設等待上(Connection Timeout)限為 15 秒
- 最低連線數(Min Pool Size)是每個 Pool 維持的基本連線數(低消),預設為 0
- Pool 中的連線若閒置 4-8 分鐘或發生斷線(Severed Connection),將被從 Pool 移除。
我寫了一個小 WebForm 程式,以多執行緒方式每隔一秒啟動一筆 SQL 查詢作業,每筆作業查詢後會故意等待 3.8 秒才關閉連線(等於霸佔一條連線近 4秒),因此前四筆作業將同時開啟 4 條 SQL 連線,直到第五筆作業才有機會重複利用第一筆作業釋放的連線。若沒有意外,這個 Connection Pool 最多將用到 4 條連線。
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Threading" %>
<%@ Import Namespace="System.Threading.Tasks" %>
<script runat="server">
static string dbPath = @"X:\LOCALDB\RUNNERDB.MDF";
static string cs = $@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog={dbPath};Integrated Security=True;Connect Timeout=30;";
void Output(string msg) => Response.Write(string.Format("<li>{0:mm:ss.fff} {1}", DateTime.Now, msg));
void Page_Load(object sender, EventArgs e)
{
var tasks =
Enumerable.Range(1, 8)
.Select(i =>
Task.Factory.StartNew(() =>
{
Thread.Sleep(i * 1000);
Output("Start Query - " + i);
ExecQuery(i);
})
).ToArray();
Task.WaitAll(tasks);
Output("Done!");
}
void ExecQuery(int jobIdx)
{
using (var cn = new SqlConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "SELECT GETDATE() AS D";
var dr = cmd.ExecuteReader();
dr.Read();
Thread.Sleep(3800);
cn.Close();
Output("Connection for " + jobIdx + " is closed");
Thread.Sleep(2000);
}
}
</script>
Windows 效能監視器中有一組 .NET Data Provider for SqlServer 計數器,是觀察 Connection Pool 使用狀態最方便的工具,本實驗將用到三個計數器:
- Number of Active Connection - 使用中的連線數量
- Number of Free Connections - 可使用的連線數量
- Number of Pooled Connections - Pool 現有的連線數量
若所有連線都來自 Pool,Pooled Connections 應等於 Active Connections + Free Connections。有一點要注意的是,Number of Active Connection 及 Number of Free Connections 預設沒啟用,要在 web.config 或 exe.config 加入以下設定啟用之:
<system.diagnostics>
<switches>
<add name="ConnectionPoolPerformanceCounterDetail" value="4" />
</switches>
</system.diagnostics>
加入計數器時需在「所選取物件的例項(Instance)」選取要觀察的對象:
挑選對象時,IIS 或 IISExpress 的 Process Id (PID) 是最重要線索:
實測結果如下圖,其中紅線為 Active Connections、綠線為 Free Connections、藍線為 Pooled Connections,為方便觀察我把比例設成 10 倍,數字 40 代表 4 條連線。執行網頁後,我們順利觀察到 Pooled Connections 跟 Active Connections 一起上升到 4,維持約 2-3 秒,之後 Action Connections 每秒減少一條直到 0,Free Connections 每秒增加一條升到 4,跟預期完全相同。
文件上說連線會在閒置 4-8 分鐘後關閉,我觀察到的時間點大約在 5 分半鐘左右 Free Connetions 與 Pooled Connections 一起降到 0,也算得到印證:
第二個實驗,連線字串不同會產生多個 Pool?
修改程式,跑四次查詢,每次刻意修改 Min Pool Size = 1, 2, 3 及 4 讓每次使用的連線字串不同,預計會產生 4 個 Connetion Pool,加上設定了最少連線數,Pool Connections 預期為 1 + 2 + 3 + 4 共 10 條:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Threading" %>
<%@ Import Namespace="System.Threading.Tasks" %>
<script runat="server">
static string dbPath = @"X:\LOCALDB\RUNNERDB.MDF";
static string cs = $@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog={dbPath};Integrated Security=True;Min Pool Size=$poolSize;Connect Timeout=30;";
void Output(string msg) => Response.Write(string.Format("<li>{0:mm:ss.fff} {1}", DateTime.Now, msg));
void Page_Load(object sender, EventArgs e)
{
var tasks =
Enumerable.Range(1, 4)
.Select(i =>
Task.Factory.StartNew(() =>
{
Thread.Sleep(i * 1000);
Output("Start Query - " + i);
ExecQuery(cs.Replace("$poolSize", i.ToString()), i);
})
).ToArray();
Task.WaitAll(tasks);
Output("Done!");
}
void ExecQuery(string cnStr, int jobIdx)
{
using (var cn = new SqlConnection(cnStr))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "SELECT GETDATE() AS D";
var dr = cmd.ExecuteReader();
dr.Read();
cn.Close();
}
}
</script>
實際執行,與預期完全相同:
最後一個實驗,將 Max Pool Size 設成 4,Connetion Timeout 設為 5 秒,平行進行五筆佔用連線 10 秒的查詢作業,預計第五筆會發生 Connection Timeout:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Threading" %>
<%@ Import Namespace="System.Threading.Tasks" %>
<script runat="server">
static string dbPath = @"X:\LOCALDB\RUNNERDB.MDF";
static string cs = $@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog={dbPath};Integrated Security=True;Max Pool Size=4;Connect Timeout=5;";
void Output(string msg) => Response.Write(string.Format("<li>{0:mm:ss.fff} {1}", DateTime.Now, msg));
void Page_Load(object sender, EventArgs e)
{
var tasks =
Enumerable.Range(1, 5)
.Select(i =>
Task.Factory.StartNew(() =>
{
Thread.Sleep(i * 100);
Output("Start Query - " + i);
ExecQuery(i);
})
).ToArray();
Task.WaitAll(tasks);
Output("Done!");
}
void ExecQuery(int jobIdx)
{
using (var cn = new SqlConnection(cs))
{
try
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "SELECT GETDATE() AS D";
var dr = cmd.ExecuteReader();
dr.Read();
Thread.Sleep(10000); //佔用連線 10 秒
cn.Close();
Output("Connection for " + jobIdx + " is closed");
}
catch (Exception ex)
{
Output("Error-" + ex.Message);
}
}
}
</script>
成功製造 Pool Connection 全部忙線無法連線錯誤!
雖然 Connection Pool 是很早以前就知道的觀念,實地驗證過文件所說的理論或行為,同樣的知識,在腦中烙印的深度就是不同,蠻有趣的。
This article containing 3 experiments to observe the behavior of SQL connection pooling with performance counter.
Comments
# by bravomao
如果遇到的錯誤訊息是: 已超過連接逾時的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應 那這個問題似乎跟pool就沒有關係,但卻又好像跟leak有關@@ 真煩惱
# by 路人R
99%是SQL跑太久,超過timeout設定所以斷線...
# by SSL
請問若我將max pool設定為最大值32767。是不是就能避免等待逾時的錯誤? 是否會有其它影響或浪費效能嗎?
# by Jeffrey
to SSL, 這麼做一般會讓情況惡化。若問題根源是 DB 效能不佳查詢過慢,原本只有 100 條連線同時查詢,增加更多連線只會讓查詢更慢(每條連線會耗用記憶體,同時的查詢動作愈多速度愈慢),甚至可能突破 DB 連線數上限。就像廚房出餐太慢,等侯區塞滿客人擠不進店裡,把店面擴建到一百坪解決不了問題。