前些時候幫忙排除一個 DB Connection Pool 相關問題,學到點東西,所以有了這篇。

這篇文章將介紹如使用效能監視器觀測 SQL Connection Pool 使用狀況,並以實驗驗證其行為模式。

如果對 Connection Pool 還有點陌生,以下是一些參考資料:

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 連線數上限。就像廚房出餐太慢,等侯區塞滿客人擠不進店裡,把店面擴建到一百坪解決不了問題。

Post a comment