定期的に SQL Server にクエリを実行するアプリを作っていたところ、コネクションプール利用を誤っていて、TIME_WAIT の接続を大量に残してしまうということをやらかしてしまいました orz
コネクションプールの管理はアプリケーション側になると思いますが、SQL Server 観点でコネクションプールによる接続かどうかを確認する方法もありますので今回はその方法を紹介したいと思います。
SQL Server の次のイベントには、「is_cached」というフィールドを持っており、このフィールドからコネクションプールが利用されているかを確認できます。
コネクションプールによって接続が再利用された場合、「is_cached」が「true」となり、プールを使用しない接続では「false」となります。
この情報を取得する拡張イベントを利用することで、ログインがプール内から接続されたものか確認することができます。
拡張イベントの定義は次のようになります。
CREATE EVENT SESSION [ConnectionPoolTest] ON SERVER ADD EVENT sqlserver.login(ACTION(sqlserver.client_app_name,sqlserver.session_id)), ADD EVENT sqlserver.logout(ACTION(sqlserver.client_app_name,sqlserver.session_id)) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO
作成した直後の状態は、拡張イベントが停止していますので、次のクエリで停止 / 開始を切り替えてください。
今回はリングバッファーの情報を使っていますので、停止 → 開始をすることで、リセットとなります。
ALTER EVENT SESSION [ConnectionPoolTest] ON SERVER STATE = STOP GO ALTER EVENT SESSION [ConnectionPoolTest] ON SERVER STATE = START GO
今回は次のような Go のコードで、テストを実施しています。
package main
import (
"database/sql"
"fmt"
"os"
"path/filepath"
"sync"
"time"
"github.com/BurntSushi/toml"
_ "github.com/denisenkom/go-mssqldb"
)
func main() {
var (
db *sql.DB
wg sync.WaitGroup
)
type structSQLConfig struct {
ServerName string
UserID string
Password string
Database string
}
type structExecSetting struct {
RoutineNum int
LoopCount int
MaxIdleConn int
MaxOpenConn int
}
type sqlConfig struct {
Server structSQLConfig
Execsetting structExecSetting
}
var conConfig sqlConfig
configPath, _ := filepath.Abs(filepath.Dir(os.Args[0]))
if _, err := toml.DecodeFile(filepath.Join(configPath, "connectionPool.config"), &conConfig); err != nil {
fmt.Println(err.Error())
return
}
conStr := fmt.Sprintf("server=%s;user id=%s;password=%s;database=%s",
conConfig.Server.ServerName,
conConfig.Server.UserID,
conConfig.Server.Password,
conConfig.Server.Database,
)
routineNum := conConfig.Execsetting.RoutineNum
loopCont := conConfig.Execsetting.LoopCount
maxIdleConn := conConfig.Execsetting.MaxIdleConn
maxOpenConn := conConfig.Execsetting.MaxOpenConn
db, _ = sql.Open("sqlserver", conStr)
db.SetMaxIdleConns(maxIdleConn)
db.SetMaxOpenConns(maxOpenConn)
defer db.Close()
startTime := time.Now()
for l := 1; l <= loopCont; l++ {
for i := 1; i <= routineNum; i++ {
wg.Add(1)
go func(db *sql.DB) {
if err := db.Ping(); err != nil {
fmt.Println(err.Error())
}
wg.Done()
}(db)
}
wg.Wait()
}
fmt.Printf("Elapsed Time (micro sec) : %d\n", time.Now().Sub(startTime).Nanoseconds()/1000)
}
プログラムと同一のディレクトリに「connectionPool.config」のファイルを置く必要があり、この内容は次のようになります。
[Server] ServerName = "Server Name" UserID = "Login Name" Password = "Password" Database = "master" [ExecSetting] RoutineNum = 10 LoopCount = 2 MaxIdleConn = 2 MaxOpenConn = 0
[Server] は、接続する SQL Server の情報となり、[ExecSetting] はプログラム内の設定となります。
- RoutineNum : goroutine 数
- LoopCount : 繰り返し数
- MaxIdleConn : SetMaxIdleConns の設定 (既定の設定は 2)
- MaxOpenConn : SetMaxOpenConns の設定 (既定の設定は 0)
上記の設定は、10 の goroutine から実行されるクエリ実行を 2 回繰り返すことになります。
それでは、この状態でプログラムを実行するとどうなるでしょうか。
拡張イベントで取得した結果がこちらになります。
10 × 2 の実行時には、18 の接続がコネクションプール外から取得され、2 の接続だけがコネクションプール内から使用されていることになります。
初期の設定は、「MaxIdleConn」は「2」となっていますので、アイドル状態になっている接続は 2 個のみとなります。
そのため、プール内に確保されている接続は 2 となり、この接続のみがコネクションプールとして利用されることになります。
PowerShell で次のコマンドを実行してみます。
while($true){
@(Get-NetTCPConnection -RemotePort 1433 -ErrorAction SilentlyContinue).Count
Start-Sleep -Seconds 3
}
実行が終わっても 36 の接続が残っていることが確認できます。
(IPv4 / v6 を区別せずに取得したので IPv4 だと半分の 18 が残っていることになります)
それでは、設定を次のようにしてみるとどうでしょう。
[ExecSetting] RoutineNum = 10 LoopCount = 2 MaxIdleConn = 10 MaxOpenConn = 0
今回の接続だと、10 の接続が非コネクションプールから接続され、その後の 10 の悦族は、コネクションプールから接続されているようになります。
SetMaxIdleConns の設定によってコネクションプールの使用状況が変わっていることが確認できましたね。
PowerShell のスクリプトの実行結果も変わってきて、IPv4 の接続については、半分の 10 の接続となります。
コネクションプールによって接続がされることで、TIME_WAIT の接続も減らすことができるようになりました。
今回は Go を利用した場合の状態を確認したくて、このような仕組みを利用しましたが、拡張イベントは SQL Server の汎用的な機能ですので、どのプログラミング言語からの接続でも状況は確認することができます。
新しい言語を使用し、頻繁にクエリ実行を行うようなものは、この方法でコネクションプールの状況を調べてみるとよいのではないでしょうか。