4

SQL Connection Pooling 行為觀察

 3 years ago
source link: https://blog.darkthread.net/blog/sql-conn-pooling-experiment/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL Connection Pooling 行為觀察-黑暗執行緒

前些時候幫忙排除一個 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 是很早以前就知道的觀念，實地驗證過文件所說的理論或行為，同樣的知識，在腦中烙印的深度就是不同，蠻有趣的。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK