📣
TiDB Cloud Essential はパブリックプレビュー中です。このページは自動翻訳されたものです。原文はこちらからご覧ください。
AI
開発者向け
ベストプラクティス
API

接続プールと接続パラメータの設定

このドキュメントでは、ドライバまたはORMフレームワークを使用してTiDBに接続する場合に、接続プールと接続パラメータを設定する方法について説明します。

ヒント：

この文書では、以下のセクションはTiDBを使用したJavaアプリケーション開発のベストプラクティスから抜粋したものです。

接続プール

TiDB（MySQL）接続の構築は、（少なくともOLTPシナリオにおいては）比較的コストがかかります。これは、TCP接続の確立に加えて、接続認証も必要となるためです。そのため、クライアントは通常、TiDB（MySQL）接続を接続プールに保存して再利用します。

JavaにはTomcat JDBC HikariCP dbcp多くの接続プール実装があります。TiDBは使用できる接続プールdruid制限しないため、アプリケーションに合わせて好きなものc3p0選択できます。

接続数を設定する

接続プールのサイズは、アプリケーションのニーズに合わせて適切に調整するのが一般的です。HikariCPを例にとってみましょう。

  • maximumPoolSize : コネクションプール内の最大接続数。この値が大きすぎると、TiDB は不要な接続を維持するためにリソースを消費します。この値が小さすぎると、アプリケーションの接続が遅くなります。したがって、アプリケーションの特性に応じてこの値を構成する必要があります。詳細については、 プールのサイズについて参照してください。
  • minimumIdle ：接続プール内のアイドル接続の最小数。主に、アプリケーションがアイドル状態のときに突発的なリクエストに対応するために、一部の接続を確保するために使用されます。アプリケーションの特性に合わせて設定する必要があります。

アプリケーションは、接続の使用が終了したら、その接続を返却する必要があります。接続プールの問題を早期に発見するために、アプリケーションは適切な接続プール監視ツール（ metricRegistryなど）を使用することをお勧めします。

接続の有効期間を設定する

TiDBサーバーがシャットダウン、メンテナンスのために再起動、またはハードウェア障害やネットワーク障害などの予期せぬ問題が発生した場合、既存のクライアント接続がリセットされ、アプリケーションの動作が中断される可能性があります。このような問題を回避するため、長時間稼働しているデータベース接続は少なくとも1日に1回は切断して再作成することをお勧めします。

ほとんどの接続プールライブラリには、接続の最大有効期間を制御するためのパラメータが用意されています。

    • maxLifetime ：プール内の接続の最大有効期間。
    • maxAge ：プール内の接続の最大有効期間。
    • maxConnectionAge ：接続プールにおける接続の最大有効期間。
    • maxConnLifetimeMillis ：接続プールにおける接続の最大有効期間。

    プローブ構成

    接続プールは、以下のようにクライアントからTiDBへの永続的な接続を維持します。

    • バージョン5.4より前のTiDBでは、デフォルトでは（エラーが報告されない限り）クライアント接続を積極的に閉じることはありません。
    • バージョン5.4以降、TiDBはデフォルトで28800秒間（つまり8時間）の非アクティブ状態が続くとクライアント接続を自動的に閉じます。このタイムアウト設定は、TiDBとMySQL互換のwait_timeout変数を使用して制御できます。詳細については、 JDBCクエリタイムアウト参照してください。

    さらに、クライアントとTiDBの間には、 LVSやHAProxyようなネットワークプロキシが存在する場合があります。これらのプロキシは通常、特定のアイドル期間（プロキシのアイドル設定によって決定されます）が経過すると、接続を自動的にクリーンアップします。接続プールは、プロキシのアイドル設定を監視するだけでなく、キープアライブのために接続を維持またはプローブする必要もあります。

    Javaアプリケーションで以下のエラーが頻繁に発生する場合：

    The last packet sent successfully to the server was 3600000 milliseconds ago. The driver has not received any packets from the server. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

    nがn milliseconds agoまたは非常に0値の場合、通常は実行されたSQL操作によってTiDBが異常終了したことが原因です。原因を特定するには、TiDBの標準エラーログを確認することをお勧めします。

    nが非常に大きな値 (上記の例の3600000など) の場合、この接続は長時間アイドル状態になり、その後プロキシによって閉じられた可能性が高いです。通常の解決策は、プロキシのアイドル設定の値を増やし、接続プールが次のことを実行できるようにすることです。

    • 接続を使用する前に、毎回接続が利用可能かどうかを確認してください。
    • 別のスレッドを使用して、接続が利用可能かどうかを定期的に確認してください。
    • 接続を維持するために、定期的にテストクエリを送信してください。

    接続プールの実装によっては、上記の方法のうち1つ以上がサポートされている場合があります。対応する設定については、接続プールのドキュメントを参照してください。

    経験に基づいた公式

    HikariCPのプールのサイズについて記事によると、データベース接続プールの適切なサイズを設定する方法がわからない場合は、 経験に基づいた公式から始めることができます。次に、式から計算されたプールサイズのパフォーマンス結果に基づいて、最適なパフォーマンスを実現するためにサイズをさらに調整できます。

    経験に基づいた公式は以下のとおりです。

    connections = ((core_count * 2) + effective_spindle_count)

    式中の各パラメータの説明は以下のとおりです。

    • 接続数：取得された接続のサイズ。
    • core_count ：CPUコアの数。
    • effective_spindle_count ：ハードドライブの数（ SSDではありません）。回転するハードディスクはそれぞれスピンドルと呼ばれるためです。たとえば、16台のディスクで構成されたRAIDサーバーを使用している場合、 effective_spindle_countは16になります。HDDは通常、一度に1つのリクエストしか処理できないため、この式は実際にはサーバーが同時に処理できるI/Oリクエストの数を測定しています。

    特に、 式下の次の注記に注意してください。

    A formula which has held up pretty well across a lot of benchmarks for years is
that for optimal throughput the number of active connections should be somewhere
near ((core_count * 2) + effective_spindle_count). Core count should not include
HT threads, even if hyperthreading is enabled. Effective spindle count is zero if
the active data set is fully cached, and approaches the actual number of spindles
as the cache hit rate falls. ... There hasn't been any analysis so far regarding
how well the formula works with SSDs.

    このメモは以下を示しています。

    • core_countは、 ハイパースレッディング有効にするかどうかに関わらず、物理コアの数です。
    • データが完全にキャッシュされると、 effective_spindle_count を0に設定する必要があります。キャッシュのヒット率が低下すると、カウントは実際の数値であるHDDに近づきます。
    • この計算式がSSDにも有効かどうかは検証されておらず、不明である。

    SSDを使用する場合は、経験に基づき、以下の式を使用することをお勧めします。

    connections = (number of cores * 4)

    したがって、SSDの場合は初期接続プールの最大接続サイズをcores * 4に設定し、さらにサイズを調整してパフォーマンスを最適化できます。

    チューニング方向

    ご覧のとおり、 経験に基づいた公式から計算されたサイズはあくまで推奨される基本値です。特定のマシンで最適なサイズを見つけるには、基本値周辺のさまざまな値を試してパフォーマンスをテストする必要があります。

    最適なサイズを選ぶための基本的なルールをいくつかご紹介します。

    • ネットワークまたはstorageのレイテンシーが高い場合は、最大接続数を増やしてレイテンシーによる待ち時間を短縮してください。スレッドがレイテンシーによってブロックされた場合でも、他のスレッドが処理を引き継いで処理を続行できます。
    • サーバー上に複数のサービスがデプロイされており、各サービスがそれぞれ独立した接続プールを持っている場合は、すべての接続プールへの最大接続数の合計を考慮してください。

    接続パラメータ

    Javaアプリケーションは、さまざまなフレームワークでカプセル化されたできます。ほとんどのフレームワークでは、データベースサーバーとのやり取りを行うために、最下層でJDBC APIが呼び出されます。JDBCに関しては、以下の点に重点を置くことをお勧めします。

    • JDBC APIの使用方法の選択
    • API実装者のパラメータ設定

    JDBC API

    JDBC API の使用方法については、 JDBC公式チュートリアル参照してください。このセクションでは、いくつかの重要な API の使用方法について説明します。

    Prepare APIを使用する

    OLTP（オンライン・トランザクション処理）シナリオでは、プログラムからデータベースに送信されるSQL文は、パラメータ変更を除けば、複数のタイプが存在します。そのため、通常のテキストファイルからの実行ではなく準備された声明使用し、プリペアドステートメントを再利用して直接実行することをお勧めします。これにより、TiDBでSQL実行プランを繰り返し解析および生成するオーバーヘッドを回避できます。

    現在、ほとんどの上位フレームワークはSQL実行のためにPrepare APIを呼び出しています。開発でJDBC APIを直接使用する場合は、Prepare APIを選択するように注意してください。

    さらに、MySQL Connector/J のデフォルト実装では、クライアント側のステートメントのみが前処理され、クライアント側で?が置換された後、ステートメントはテキスト ファイルとしてサーバーに送信されます。したがって、Prepare API を使用するだけでなく、TiDBサーバーでステートメントの前処理を実行する前に、JDBC 接続パラメータでuseServerPrepStmts = true設定する必要があります。パラメータ設定の詳細については、 MySQL JDBC パラメータ参照してください。

    バッチAPIを使用する

    バッチ挿入の場合は、 addBatch / executeBatch APIを使用できます。3 addBatch()は、複数の SQL ステートメントを最初にクライアントにキャッシュし、 executeBatchメソッドを呼び出すときにそれらをまとめてデータベースサーバーに送信するために使用されます。

    注記：

    デフォルトのMySQL Connector/Jの実装では、 addBatch()でバッチに追加されたSQL文の送信時間はexecuteBatch()呼び出されるまで遅延されますが、実際のネットワーク転送中は文は1つずつ送信されます。そのため、この方法は通常、通信オーバーヘッドを削減しません。

    バッチネットワーク転送を行う場合は、JDBC接続パラメータでrewriteBatchedStatements = true設定する必要があります。詳細なパラメータ設定については、 バッチ関連パラメータ参照してください。

    StreamingResultを使用して実行結果を取得します。

    ほとんどのシナリオでは、実行効率を向上させるために、JDBCはデフォルトでクエリ結果を事前に取得し、クライアントのメモリに保存します。しかし、クエリが非常に大きな結果セットを返す場合、クライアントはデータベースサーバーに一度に返されるレコード数を減らし、クライアントのメモリが準備できるまで待機し、次のバッチを要求することがよくあります。

    JDBCでは通常、以下の2つの処理方法が使用されます。

    • 最初の方法: FetchSizeをInteger.MIN_VALUEに設定します。クライアントがキャッシュしないようにします。クライアントはStreamingResultを介してネットワーク接続から実行結果を読み取ります。

      クライアントがストリーミング読み取り方式を使用する場合、クエリを実行するためにステートメントを引き続き使用する前に、読み取りを完了するか、 resultset閉じる必要があります。そうしないと、エラーNo statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.が返されます。

      クライアントが読み取りを完了するか、 resultset閉じる前にクエリでこのようなエラーが発生するのを回避するには、URLにclobberStreamingResults=trueパラメータを追加できます。そうすると、 resultset自動的に閉じられますが、前のストリーミングクエリで読み取られる結果セットは失われます。

    • 2つ目の方法：まず正の整数としてFetchSize設定設定し、次にJDBC URLでuseCursorFetch = true設定することで、カーソルフェッチを使用します。

    TiDBは両方の方法をサポートしていますが、実装がよりシンプルで実行効率も優れているため、 FetchSizeからInteger.MIN_VALUEに設定する最初の方法を使用することをお勧めします。

    2番目の方法では、TiDBはまずすべてのデータをTiDBノードにロードし、次にFetchSizeに従ってクライアントにデータを返します。そのため、通常は最初の方法よりも多くのメモリを消費します。3 tidb_enable_tmp_storage_on_oom ON設定されている場合、TiDBは結果を一時的にハードディスクに書き込む可能性があります。

    システム変数tidb_enable_lazy_cursor_fetch ONに設定されている場合、TiDB はクライアントがデータを取得するときにのみデータの一部を読み取ろうとします。これによりメモリ使用量が削減されます。詳細および制限事項については、 tidb_enable_lazy_cursor_fetchシステム変数の完全な説明を参照してください。

    MySQL JDBC パラメータ

    JDBCは通常、JDBC URLパラメータの形式で実装関連の設定を提供します。このセクションでは、 MySQL Connector/Jのパラメータ設定 （MariaDBを使用する場合はMariaDBのパラメータ設定参照）について説明します。このドキュメントではすべての設定項目を網羅することはできないため、主にパフォーマンスに影響を与える可能性のあるいくつかのパラメータに焦点を当てます。

    このセクションでは、 Prepareに関連するパラメータを紹介します。

    • useServerPrepStmts

      useServerPrepStmts はデフォルトでfalseに設定されています。つまり、Prepare API を使用する場合でも、「prepare」操作はクライアント側でのみ実行されます。サーバーの解析オーバーヘッドを回避するため、同じ SQL ステートメントで Prepare API を複数回使用する場合は、この設定をtrueに設定することをお勧めします。

      この設定が既に有効になっていることを確認するには、次の操作を実行してください。

      • TiDB モニタリング ダッシュボードに移動し、 [クエリ概要] > [インスタンス別 CPS]からリクエスト コマンド タイプを確認します。
      • リクエストでCOM_QUERY COM_STMT_EXECUTEまたはCOM_STMT_PREPAREに置き換えられている場合、この設定は既に有効になっていることを意味します。

    • キャッシュ準備ステートメント

      useServerPrepStmts=trueではサーバーがプリペアドステートメントを実行できますが、デフォルトではクライアントは実行後にプリペアドステートメントを閉じ、再利用しません。つまり、「準備」操作はテキストファイルの実行ほど効率的ではありません。この問題を解決するには、 useServerPrepStmts=true設定した後、 cachePrepStmts=true設定することをお勧めします。これにより、クライアントはプリペアドステートメントをキャッシュできるようになります。

      この設定が既に有効になっていることを確認するには、次の操作を実行してください。

      • TiDB モニタリング ダッシュボードに移動し、 [クエリ概要] > [インスタンス別 CPS]からリクエスト コマンド タイプを確認します。
      • リクエスト内のCOM_STMT_EXECUTEの数がCOM_STMT_PREPAREの数よりはるかに多い場合、この設定は既に有効になっていることを意味します。

      さらに、 useConfigs=maxPerformance設定すると、 cachePrepStmts=true含む複数のパラメータが同時に設定されます。

    • prepStmtCacheSqlLimit

      cachePrepStmts設定が完了したら、 prepStmtCacheSqlLimit設定（デフォルト値は256 ）にも注意してください。この設定は、クライアントにキャッシュされるプリペアドステートメントの最大長を制御します。

      この最大長を超えるプリペアドステートメントはキャッシュされないため、再利用できません。この場合、アプリケーションの実際のSQLの長さに応じて、この設定値を増やすことを検討してください。

      次のような場合は、この設定が小さすぎるかどうかを確認する必要があります。

      • TiDB モニタリング ダッシュボードに移動し、 [クエリ概要] > [インスタンス別 CPS]からリクエスト コマンド タイプを確認します。
      • そして、 cachePrepStmts=true設定されているが、 COM_STMT_PREPAREは依然としてCOM_STMT_EXECUTEとほぼ等しく、 COM_STMT_CLOSE存在することがわかった。

    • prepStmtCacheSize

      prepStmtCacheSize は、キャッシュされるプリペアドステートメントの数を制御します（デフォルト値は25です）。アプリケーションで多くの種類の SQL ステートメントを「準備」する必要があり、プリペアドステートメントを再利用したい場合は、この値を増やすことができます。

      この設定が既に有効になっていることを確認するには、次の操作を実行してください。

      • TiDB モニタリング ダッシュボードに移動し、 [クエリ概要] > [インスタンス別 CPS]からリクエスト コマンド タイプを確認します。
      • リクエスト内のCOM_STMT_EXECUTEの数がCOM_STMT_PREPAREの数よりはるかに多い場合、この設定は既に有効になっていることを意味します。

    バッチ書き込みを処理する際は、 rewriteBatchedStatements=true設定することをお勧めします。3 またはexecuteBatch() addBatch()使用した後でも、JDBC はデフォルトでは SQL を 1 つずつ送信します。例:

    pstmt = prepare("INSERT INTO `t` (a) values(?)");
pstmt.setInt(1, 10);
pstmt.addBatch();
pstmt.setInt(1, 11);
pstmt.addBatch();
pstmt.setInt(1, 12);
pstmt.executeBatch();

    Batchメソッドが使用されているにもかかわらず、TiDBに送信されるSQLステートメントは依然として個別のINSERTステートメントです。

    INSERT INTO `t` (`a`) VALUES(10);
INSERT INTO `t` (`a`) VALUES(11);
INSERT INTO `t` (`a`) VALUES(12);

    しかし、 rewriteBatchedStatements=true設定すると、TiDB に送信される SQL ステートメントは単一のINSERTステートメントになります。

    INSERT INTO `t` (`a`) values(10),(11),(12);

    INSERTのステートメントの書き換えは、複数の「values」キーワードの後の値を連結して、1つのSQLステートメントにすることです。3 INSERTのステートメントに他の違いがある場合は、書き換えることはできません。たとえば、次のようになります。

    INSERT INTO `t` (`a`) VALUES (10) ON DUPLICATE KEY UPDATE `a` = 10;
INSERT INTO `t` (`a`) VALUES (11) ON DUPLICATE KEY UPDATE `a` = 11;
INSERT INTO `t` (`a`) VALUES (12) ON DUPLICATE KEY UPDATE `a` = 12;

    上記のINSERTつの文は1つの文に書き換えることはできません。しかし、3つの文を次のように変更すると次のようになります。

    INSERT INTO `t` (`a`) VALUES (10) ON DUPLICATE KEY UPDATE `a` = VALUES(`a`);
INSERT INTO `t` (`a`) VALUES (11) ON DUPLICATE KEY UPDATE `a` = VALUES(`a`);
INSERT INTO `t` (`a`) VALUES (12) ON DUPLICATE KEY UPDATE `a` = VALUES(`a`);

    すると、書き換え要件を満たします。上記のINSERTつの文は、次の1つの文に書き換えられます。

    INSERT INTO `t` (`a`) VALUES (10), (11), (12) ON DUPLICATE KEY UPDATE a = VALUES(`a`);

    バッチ更新中に3つ以上の更新がある場合、SQLステートメントは書き換えられ、複数のクエリとして送信されます。これにより、クライアントからサーバーへのリクエストのオーバーヘッドは効果的に削減されますが、副作用として、より大きなSQLステートメントが生成されます。例：

    UPDATE `t` SET `a` = 10 WHERE `id` = 1; UPDATE `t` SET `a` = 11 WHERE `id` = 2; UPDATE `t` SET `a` = 12 WHERE `id` = 3;

    さらに、 クライアントのバグ問題があるため、バッチ更新中にrewriteBatchedStatements=trueとuseServerPrepStmts=trueを設定する場合は、このバグを回避するためにallowMultiQueries=trueパラメータも設定することをお勧めします。

    パラメータを統合する

    監視を通じて、アプリケーションが TiDB クラスタに対して実行する操作はINSERTだけであるにもかかわらず、冗長なSELECTステートメントが多数あることに気づくかもしれません。通常、これは JDBC が設定を照会するためにいくつかの SQL ステートメントを送信することによって発生します (例: select @@session.transaction_read_only )。これらの SQL ステートメントは TiDB にとって不要なので、余分なオーバーヘッドを避けるためにuseConfigs=maxPerformanceを設定することをお勧めします。

    useConfigs=maxPerformanceには設定のグループが含まれています。MySQL Connector/J 8.0およびMySQL Connector/J 5.1の詳細な設定については、それぞれmysql-connector-j 8.0およびmysql-connector-j 5.1を参照してください。

    設定が完了したら、監視画面でSELECTステートメントの数が減少していることを確認できます。

    TiDB はタイムアウトを制御するために 2 つの MySQL 互換パラメータ ( wait_timeoutとmax_execution_time ) を提供します。これらの 2 つのパラメータはそれぞれ、 Javaアプリケーションとの接続アイドルタイムアウトと接続内の SQL 実行のタイムアウトを制御します。つまり、これらのパラメータは、TiDB とJavaアプリケーション間の接続の最長アイドル時間と最長ビジー時間を制御します。TiDB v5.4 以降、 wait_timeoutのデフォルト値は28800秒で、8 時間です。v5.4 より前の TiDB バージョンでは、デフォルト値は0で、タイムアウトは無制限です。11 のデフォルト値はmax_execution_time 0 、SQL ステートメントの最大実行時間は無制限であり、 SELECTステートメントすべて ( SELECT ... FOR UPDATEを含む) に適用されます。

    デフォルト値のwait_timeoutは比較的大きな値です。トランザクションが開始されてもコミットもロールバックもされないような状況では、ロックの保持時間が長引くのを防ぐために、よりきめ細かな制御と短いタイムアウトが必要になる場合があります。このような場合は、 tidb_idle_transaction_timeout （TiDB v7.6.0で導入）を使用して、ユーザーセッション内のトランザクションのアイドルタイムアウトを制御できます。

    しかし、実際の本番環境では、アイドル状態の接続や実行時間が長すぎるSQL文は、データベースやアプリケーションに悪影響を及ぼします。アイドル状態の接続や実行時間が長すぎるSQL文を回避するには、アプリケーションの接続文字列でこれらの2つのパラメータを設定できます。例えば、 sessionVariables=wait_timeout=3600 （1時間）とsessionVariables=max_execution_time=300000 （5分）を設定します。

    お困りですか？

    このページは役に立ちましたか？