SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server でコネクションプールによる接続となっていることを確認してみる

leave a comment

定期的に SQL Server にクエリを実行するアプリを作っていたところ、コネクションプール利用を誤っていて、TIME_WAIT の接続を大量に残してしまうということをやらかしてしまいました orz

コネクションプールの管理はアプリケーション側になると思いますが、SQL Server 観点でコネクションプールによる接続かどうかを確認する方法もありますので今回はその方法を紹介したいと思います。

SQL Server の次のイベントには、「is_cached」というフィールドを持っており、このフィールドからコネクションプールが利用されているかを確認できます。

image

コネクションプールによって接続が再利用された場合、「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 回繰り返すことになります。

それでは、この状態でプログラムを実行するとどうなるでしょうか。

拡張イベントで取得した結果がこちらになります。

image

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 が残っていることになります)

image

それでは、設定を次のようにしてみるとどうでしょう。

[ExecSetting]
RoutineNum = 10
LoopCount = 2
MaxIdleConn = 10
MaxOpenConn = 0

 

今回の接続だと、10 の接続が非コネクションプールから接続され、その後の 10 の悦族は、コネクションプールから接続されているようになります。

SetMaxIdleConns の設定によってコネクションプールの使用状況が変わっていることが確認できましたね。

image

PowerShell のスクリプトの実行結果も変わってきて、IPv4 の接続については、半分の 10 の接続となります。

image

コネクションプールによって接続がされることで、TIME_WAIT の接続も減らすことができるようになりました。

今回は Go を利用した場合の状態を確認したくて、このような仕組みを利用しましたが、拡張イベントは SQL Server の汎用的な機能ですので、どのプログラミング言語からの接続でも状況は確認することができます。

新しい言語を使用し、頻繁にクエリ実行を行うようなものは、この方法でコネクションプールの状況を調べてみるとよいのではないでしょうか。

Written by masayuki.ozawa

1月 26th, 2019 at 11:17 pm

Posted in Go,SQL Server

Tagged with ,

Leave a Reply

*