SE の雑記

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

JDBC で HikariCP を使用した SQL Server (SQL Database) への接続について調べてみる

leave a comment

JDBC で接続をする際のコネクションプールで使用するライブラリとして HikariCP があります。

HikariCP を使用して SQL Server に接続をする際の挙動について、いくつか調査する必要があったのでその時に確認した内容を残しておきたいと思います。

Azure SQL Database Support Blog の内容

HikariCP はメジャーなコネクションプールのライブラリとなるため、Azure SQL Database Support Blog でも記事が公開されています。

SQL Server ベースの環境に対して接続ををする際にも活用されている事例があり、サポートでも対応を行っているケースがあるようです。

 

HikariCP によるコネクションプールの基本的な動作

コネクションプールの基本的な動作

.NET の SQL プロバイダーのコネクションプールの基本動作

.NET の SQL Server プロバイダーで接続した場合の動作については、SQL Server の接続プール (ADO.NET) の情報から確認できますが、コネクションプールの動作としては次のようになるかと思います。

  1. Connection を Open した際に、ログオンが発生する
  2. Connection を Close した際に、ログオフが発生するが接続は維持された状態となる。
  3. プール内の接続をオープンした場合、sp_reset_connection を実行して、コネクションの設定をリセットする
  4. Min Pool Size が指定されている場合は、初回の接続時に指定した設定値の接続を作成し、必要に応じて、Max Pool Size までプールの接続を増加させる。
    • Min Pool Size を指定していない場合は最小の接続から開始する

コネクションプールにより接続が再利用されているかについては、「is_cached = True」 でログインが発生しているかで確認できます。

image

 

HikariCP のコネクションプールの基本動作

HikariCP を使用した場合、コネクションプールの挙動は上記とは異なるようです。

  1. Connection を Open した際に、ログオンが発生する
  2. Connection を Close しても、ログオフは発生せず、ログオンした状態で接続を維持する
  3. プールを生成した際に、MaximumPoolSize (デフォルト 10) の接続が生成される
  4. MaxLifetime (デフォルト 30 分) に達した接続についてログオフする

image

.NET と異なり、「sp_reset_connection」を実行して、接続を初期化しながら再利用するというのではなく、ログインした状態を維持することで 接続の再利用を行うという方式となっているようです。

「接続プールを利用した接続」の考え方が .NET と異なっており、どのようにして接続が再利用されるのかの違いについては意識しておく必要があるのではないでしょうか。

 

HikariCP の接続に関しての設定

HikariCP の接続に関しての設定は Configuration (knobs, baby!) から確認できます。

設定については Google Cloud の次のドキュメントも参考になりそうです。

 

検証をしていた範囲では、次のような設定はワークロードに応じて調整する必要があると感じました。

プール数

  • maximumPoolSize
    • デフォルト: 10 / 最小: 1
  • minimumIdle
    • デフォルト: maximumPoolSize と同じ

  • IdleTimeout

    • デフォルト: 600,000 (ミリ秒) / 最小: 10,000 ミリ秒 (秒)
    • mimimumIdle に接続数を減少させる際のアイドル時間
    • minimumIdle が明示的に差設定されている場合にのみ考慮され

プールの接続維持

  • keepaliveTime
    • デフォルト: 0 (無効) / 最小: 30,000 ミリ秒 (30 秒)
    • maxLifeTime より小さい値を設定する
    • connectionTestQuery に指定したクエリまたは JDBC4 IsValid メソッドを呼び出すことで、接続を維持する

  • connectionTestQuery

    • keepaliveTime の間隔で実行されるクエリを指定
    • SQL Server の JDBC ドライバーは JDBC4 をサポートしているため IsValid の実装の処理が実行されるため、明示的に設定をしなくても「SELECT 1」が実行される
  • maxLifeTime
    • デフォルト: 1,800,000 ミリ秒 (30 分) / 最小: 30,000 (ミリ秒)
    • プールに作成した接続を維持する期間となり、時間に達した場合ログオフが実行される

  • connectionInitSql

    • デフォルト: なし
    • 新しい接続をプールに作成する際に実行するクエリを指定

  • connectionTimeout

    • デフォルト: 30,000 ミリ秒 (30 秒) / 最小: 250 ミリ秒
    • プールから接続を取得する際の最大待機時間を指定
    • プールに利用可能な接続が無い場合に、利用可能な接続ができるまで待機する時間
  • validationTimeout
    • デフォルト: 0 (無効) / 最小: 2,000 ミリ秒 (2 秒)
    • connectionTimeout より小さい値を設定する
    • 接続が有効かをテストする最大時間
      • TCP/IP レベルで、DB ポートに対しての Keep Alive の確認
    • LeakDetectionThreshold
      • デフォルト: 0 (無効) / 最小: 2,000 ミリ秒 (2 秒)
      • リークしている可能性のある接続についてのメッセージの出力時間

 

接続プールの接続をクリア (初期化) をするタイミングの検討

基本的な対応

接続の状態によっては、意図的に接続プールをクリアする必要が出てきます。

例としては、SQL Database のフェールオーバーグループを使用して、READ_WRITE と READ_ONLY を切り替えた場合です。

フェールオーバー中の接続の状態 / コードの内容にもよるのですが、「Application Intent=READ_WRITE」を使用した接続でも、フェールオーバー後に次の接続のような状態 (READ_WRITE の接続オプションを使用していても READ_ONLY に接続されている) になることがあります。

[2024-05-25T13:18:52.781073651] : [004] : [000] : [READ_WRITE] : [70] 
[2024-05-25T13:18:53.047959613] : [004] : [001] : [READ_WRITE] : [70] 
[2024-05-25T13:18:53.313150309] : [004] : [002] : [READ_WRITE] : [70] 
[2024-05-25T13:18:53.573689525] : [004] : [003] : [READ_WRITE] : [70] 
[2024-05-25T13:18:53.839673551] : [004] : [004] : [READ_WRITE] : [70] 
[2024-05-25T13:19:07.778258839] : [004] : [007] : [READ_ONLY] : [71]  ←
[2024-05-25T13:19:08.048725472] : [004] : [008] : [READ_ONLY] : [71] 
[2024-05-25T13:19:08.323028310] : [004] : [009] : [READ_ONLY] : [71] 
[2024-05-25T13:19:08.628237821] : [004] : [010] : [READ_ONLY] : [71] 
[2024-05-25T13:19:08.964955947] : [004] : [011] : [READ_ONLY] : [71]

 

.NET であれば、ClearPool / ClearAllPools を使用して、プールをクリアすることで、最新の READ_WRITE のサーバーに接続を行うようにすることができます。

HikariCP の場合は、基本的な対応としては Lesson Learned #408: The Strange Case of the Failover Conundrum の内容となると思います。

 

想定した動作が行われなかった場合の考慮点

Lesson Learned の方法で READ_WRITE に切り替わらない場合には、次のような対応を検討する必要があるかもしれません。(瞬間的なタイミングの問題で、READ_WRITE → READ_ONLY への切り替えが完了したタイミングで接続がされ、その接続状態がプールされているような挙動が発生するタイミングがありそうな雰囲気ではあるのですが)

  • アプリケーションの再起動を行って接続を再作成
  • 処理のリトライ等を組み合わせながらプール内の接続をクリアしていく 
ds.evictConnection(con);
or 
ds.close();
ds = new HikariDataSource(config);
  • 該当の JDBC からの接続を DB 側ですべて KILL する 
select 'kill ' + CAST(session_id AS char(100)), * from sys.dm_exec_sessions
where program_name like '%JDBC%' or program_name LIKE'%pwsh%'
ORDER BY program_name


DECLARE @kill varchar(255)

DECLARE cur_Kill CURSOR FOR 
select 'kill ' + CAST(session_id AS varchar(100)) from sys.dm_exec_sessions
where program_name like '%JDBC%'

OPEN cur_KILL

FETCH NEXT FROM cur_Kill INTO @kill
WHILE @@FETCH_STATUS = 0
BEGIN
	EXECUTE (@kill)
	FETCH NEXT FROM cur_Kill INTO @kill
END

CLOSE cur_Kill
DEALLOCATE cur_Kill

select 'kill ' + CAST(session_id AS char(100)), * from sys.dm_exec_sessions
where program_name like '%JDBC%'
  • TestQuery / InitQuery を使用して、READ_WRITE の指定で READ_ONLY の接続となった場合、エラーを発生させるクエリを指定する 
IF DATABASEPROPERTYEX(DB_NAME(), 'Updateability') = 'READ_ONLY'
BEGIN
	RAISERROR ('Not READ WRITE', 11, 1)
END
Share

Written by Masayuki.Ozawa

5月 25th, 2024 at 11:23 pm

Posted in JDBC,SQL Server

Tagged with ,

«
»

Leave a Reply