.NET Connection Pool 與連線相關問題整理

這或許算是個 Connection Pool 的懶人包,給我這個懶人用的。

更新日期:2012-06-09


常見的錯誤訊息

先把我自己印象中比較常碰到的,與資料庫連線有關的錯誤訊息列出來,稍後會再說明排除問題的方法和基本觀念。

錯誤訊息 1
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

這個錯誤通常跟應用程式的兩個地方有關:
  1. 資料庫連線字串所使用的 connection pool 參數。
  2. 程式的寫法有問題,導致資料庫連線沒有盡快釋放,以至於連線池爆滿。
此錯誤訊息很容易就可以重現。例如,你可以寫個程式,裡面用一個迴圈不斷開啟新的資料庫連線,但都不要釋放它。很快地,例如開啟 100 個連線之後,就會出現這個錯誤訊息。

錯誤訊息 2
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server.

這個問題就比較棘手了,因為導致此問題的原因挺多。而且,這個看起來跟「網路相關」的錯誤,若非偶爾才發生,這還算好解決,通常檢查一下 SQL Server 的 TCP/IP 協定有沒有打開、防火牆有沒有擋住(預設的 1433 port)等設定就能解決。但如果這個錯誤是偶發的,那就不好處理了,你甚至可能要懷疑是不是網路有瞬斷或 SQL Server 突然暫停服務的情形。

後文所提到的一些參數設定或效能監視的方法,多少都跟上述錯誤訊息有關。雖然我希望把連線相關的問題都整理在這一篇,但肯定會有所遺漏。如果你碰到難解的效能問題或連線錯誤,記得 Window Event Log 是你的好朋友。

接著就分別針對 server 端和 client 端可以調的參數或問題排除的方法做個整理。

Server 端

檢查 SQL Server 服務是否有啟動,這個應該不用說吧。底下是其他需要檢查的設定。

最大連線數

SQL Server 有最大連線數量的參數可以設定,一般是不會去改它。若你懷疑這個設定有問題,可以開啟 SQL Server Management Studio,然後開啟你所要檢視的 SQL Server 伺服器的屬性視窗,在 Connections 這一頁裡面有 "Maximum number of concurrent connections (0 = unlimited)" 欄位可讓你設定。0 代表不限制。參考下圖。


允許遠端連線

跟剛才一樣的地方:SQL Server 伺服器的屬性 > Connections 頁,裡面有個 "Allow remote connections to this server"。把它打勾。

防火牆的設定也要一並檢查,要讓 TCP 1433 port 通過防火牆。

啟動 SQL Server Browser Service

SQL Server Browser Service 的主要功能是找出可用的 SQL Server,以及協助用戶端連線到正確的 server instance。雖然不啟動這個 service,用戶端也應該要能夠順利連到資料庫(只要用戶端有指定正確的連線埠;預設的 1433 埠則不指定亦可),但如果發生像 [錯誤訊息 2] 的狀況,還是先把它啟動吧,這樣才好縮小問題範圍。


若有啟動防火牆,也要記得設定規則,讓 SqlBrowser.exe 以及 UDP 1434 port 通過防火牆。

關於 [錯誤訊息 2],我在網路上爬文時,還看到有人說把網路通訊協定中的 IPv6 關閉就解決了。這此方法我還沒確認是否有用,先記下來供日後參考。如果你想試試(反正不會影響系統運作),請注意那篇貼文的回應是說,把網路介面卡的屬性視窗裡面的 IPv6 取消勾選是沒用的,你得參考這篇官方文件的作法:http://support.microsoft.com/kb/929852。另一份 KB 文章也有提到類似問題:http://support.microsoft.com/kb/2526552

效能計數器

出現前面所說的資料庫連線失敗的錯誤時,在 SQL Server 這端你可以用 Performance Monitor 來監視和記錄與資料庫連線數量有關的計數值,例如:
  • SQLServer:General Statistics \ User Connections
  • SQLServer:Locks \ Number of Deadlocks/sec

參考下圖:


在加入效能計數器時,如果一開始還不太確定要加哪些,我常常是「料敵從寬」,把可疑或可能相關的都加進去。

Client 端

用戶端這邊主要是跟 connection pooling 機制以及資料存取的程式碼寫法有關。先來看 connection pooling 的部分。

Connection Pool 

首先要了解的是,connection pool 裡面會存放一些已經建立好、但目前沒人使用的連線,目的是讓用戶端快速取用。每當應用程式用完一條資料庫連線(亦即關閉連線)時,這條連線並沒有真正與資料庫伺服器斷開,而是先放到一個池子裏面。若該連線在池子裡面閒置了一段時間之後都沒有再被應用程式取用,才會真的釋放掉。

Connection Pool 的數量

一個應用程式可能會建立不只一個 connection pool。如果一個應用程式使用了 10 個 pools,而每個 pool 裡面的尖峰連線數量是 100 條連線,那麼此應用程式對後端資料庫的連線總數大約就等於 1000。

也就是說,connection pool 的數量越多,對 server 端的連線壓力自然也越大。所以我們應該要先確保 connection pool 數量越少越好,然後才去調整 pooling 的參數,讓池子裡面的連線資源以最經濟的方式運作。那麼,要如何減少 connection pool 的數量呢?

不同的應用程式會使用不同的 connection pool,同一個應用程式當中如果使用了不同的連線字串,也會有各自的 pool。當 ADO.NET 在建立連線時,會去尋找現有的連線池,看看有沒有使用相同連線字串的池子,有則取用;若沒有,就建立一個新的連線池。

除了連線字串之外,使用者身分也會令 ADO.NET 建立不同的 connection pool。所以,結論就是你的連線字串最好都長得一模一樣,例如把連線字串放在應用程式組態檔裡面,而且連線字串裡面所使用的身分驗證方式,如非必要,應使用 SQL Sever 的特定 user ID,而不要用整合式驗證,以免每一個 Windows 使用者帳戶就有一個專屬的連線池,導致產生一大堆小池子(這種狀況叫做 pool fragmentation [2])。
[碎碎念]  我看過有的程式是自己產生不同的連線字串,是依使用者所屬的單位來產生不同的連線字串。也就是說,人事、研發、管理等各部門的員工都會使用各自的連線池。我不明白這麼做有甚麼特殊用意,但是資料庫伺服器肯定不會太高興。
Connection Pool 在哪裡?App server 還是 DB server?

剛才已經隱約透露了,connection pool 是建立在 ADO.NET 程式碼所在的執行環境中,例如 Windows 程式或運行於 IIS 之上的 ASP.NET 應用程式集區 [1]。(也就是說,若資料庫伺服器與應用程式位於不同機器,那麼 connection pool 相關的效能計數器就不用在資料庫伺服器這邊加入了。)

使用連線字串控制 connection pooling 參數 

連線字串有幾個參數可以控制 connection pooling 行為,底下是幾個比較常用的參數(for SQL Server):
  • Connection Lifetime - 當用戶端關閉某個連線,亦即將那個連線還給 pool 時,這個連線就閒置在池子裏面,等其他用戶端取用。如果這個連線在池子裡閒置的時間超過此參數的設定值,就會被真的釋放掉(SQL Server 那頭的總連線數會少一個)。預設值為 0。
  • Max Pool Size - 連線池裡面最多可以有幾個連線。預設值為 100。當連線池中的連線數量已經達到最大,此時應用程式若要再建立新的連線,就會先等待,等超過特定時間都還沒有可用的連線,就會發生連線逾時的錯誤。
  • Min Pool Size - 連線池裡面最少要保持幾個連線。預設值為 0。若設定為 5,即表示當一個 pool 建立時,就要預先配置好 5 個可用連線,以便前五個用戶端直接提取。
  • Pooling - 是否啟用 pooling,預設為 True。
如果你的應用程式發生 [錯誤訊息 1] 的狀況,建議你先別急著把 Max Pool Size 調大,而是要先確定程式寫法有沒有問題,也就是遵循這個原則:連線越晚建立越好,越早釋放越好。千萬別依賴 .NET 自動資源回收機制來幫你回收資料庫連線,否則在應用程式負載的尖峰期,連線池很快就會爆滿。


參考資料
  1. The .NET Connection Pool Lifeguard
  2. MSDN: SQL Server Connection Pooling (ADO.NET)
  3. ADO.NET Connection Pooling at a Glance

1 則留言:

  1. http://blogs.msdn.com/b/kaevans/archive/2009/04/09/linq-to-sql-and-connection-pooling.aspx

    One thing to look at is how you are forming the connection string. If you have any variation in your connection string (using different user names in the string, for example), it can lead to an exhaustion of resources. The common guidance is to use a single connection string stored in the configuration file that uses integrated authentication (does not contain a user ID or password in the query string, the identity is determined based on the identity of the IIS application pool user account). A way to get an idea of the connections and processes is to go into SQL Server Management Studio and run the following query:

    回覆刪除

技術提供:Blogger.
回頂端⬆️