📣
TiDB Cloud Serverless が
Starter
に変わりました！このページは自動翻訳されたものです。
原文はこちらからご覧ください。
TiDB Cloud
TiDB Self-Managed
Learning Center
お問い合わせ

接続プールと接続パラメータ

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

Javaアプリケーション開発に関するさらなるヒントにご興味がおありの場合は、 TiDB を使用したJavaアプリケーション開発のベスト プラクティス参照してください。

接続プール

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

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

接続数を設定する

アプリケーションのニーズに応じて接続プールのサイズを適切に調整するのが一般的です。HikariCPを例に挙げましょう。

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

アプリケーションは、使用を終えた後、接続を返却する必要があります。接続プールの問題を早期に特定するために、アプリケーションでは対応する接続プール監視（ metricRegistryなど）を使用することをお勧めします。

プローブ構成

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

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

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

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 milliseconds ago分のn 0または非常に小さい値である場合、通常は実行されたSQL操作によってTiDBが異常終了したことが原因です。原因を特定するには、TiDBのstderrログを確認することをお勧めします。

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の使用方法について説明します。

準備APIを使用する

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

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

また、MySQL Connector/J のデフォルト実装では、クライアント側のステートメントのみが前処理され、クライアント側で?が置換された後、テキストファイルでサーバーに送信されます。そのため、TiDBサーバーでステートメントの前処理を実行する前に、Prepare API の使用に加えて、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に従ってクライアントにデータを返します。そのため、通常は1番目の方法よりも多くのメモリを消費します。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 を使用した場合でも、「準備」操作はクライアント側でのみ実行されます。サーバーの解析オーバーヘッドを回避するため、同じ 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含む複数のパラメータが同時に設定されます。

  • 準備StmtCacheSqlLimit

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

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

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

    • TiDB 監視ダッシュボードに移動し、クエリ サマリー>インスタンス別の CPSを通じて要求コマンド タイプを表示します。
    • そして、 cachePrepStmts=true設定されていることがわかりますが、 COM_STMT_PREPAREまだCOM_STMT_EXECUTEとほぼ同じで、 COM_STMT_CLOSE存在します。

  • 準備StmtCacheSize

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

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

    • TiDB 監視ダッシュボードに移動し、クエリ サマリー>インスタンス別の CPSを通じて要求コマンド タイプを表示します。
    • リクエスト内のCOM_STMT_EXECUTEの数がCOM_STMT_PREPARE数よりはるかに多い場合、この設定はすでに有効になっていることを意味します。

バッチ書き込み処理中は、 rewriteBatchedStatements=true設定することをお勧めします。 addBatch()またはexecuteBatch()使用した後でも、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-コネクタ-j 8.0とmysql-コネクタ-j 5.1参照してください。

設定後、監視をチェックして、 SELECTステートメントの数が減っていることを確認できます。

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

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

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

ヘルプが必要ですか?

不和またはスラック 、あるいはサポートチケットを送信するについてコミュニティに質問してください。

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