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

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

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

接続プール

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

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

接続数を構成する

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

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

アプリケーションは、使用終了後に接続を返す必要があります。アプリケーションで対応する接続プール監視 ( metricRegistryなど) を使用して、接続プールの問題を時間内に特定することをお勧めします。

プローブ構成

接続プールは、TiDB への永続的な接続を維持します。デフォルトでは、TiDB は積極的にクライアント接続を閉じませんが (エラーが報告されない限り)、通常、クライアントと 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または非常に小さい値である場合、通常は、実行された SQL 操作によって0が異常終了するためです。原因を特定するには、TiDB の stderr ログを確認することをお勧めします。

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

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

異なる接続プールの実装では、上記のメソッドの 1 つ以上がサポートされている場合があります。接続プールのドキュメントを確認して、対応する構成を見つけることができます。

経験に基づく公式

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

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

connections = ((core_count * 2) + effective_spindle_count)

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

• connections : 取得した接続のサイズ。
• 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に設定し、さらにサイズを調整してパフォーマンスを調整できます。

チューニング方向

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

最適なサイズを得るのに役立ついくつかの基本的なルールを次に示します。

• ネットワークまたはストレージのレイテンシーが長い場合は、接続の最大数を増やしてレイテンシーを短縮します。スレッドがレイテンシーによってブロックされると、他のスレッドが引き継ぎ、処理を続行できます。
• サーバーに複数のサービスがデプロイされていて、各サービスに個別の接続プールがある場合は、すべての接続プールへの最大接続数の合計を考慮してください。

接続パラメータ

Java アプリケーションは、さまざまなフレームワークでカプセル化できます。ほとんどのフレームワークでは、JDBC API が最下位レベルで呼び出され、データベースサーバーと対話します。 JDBC の場合、次の点に注目することをお勧めします。

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

JDBC API

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

準備 API を使用する

OLTP (オンライン トランザクション処理) シナリオの場合、プログラムによってデータベースに送信される SQL ステートメントは、パラメーターの変更を削除した後に使い果たされる可能性があるいくつかのタイプです。したがって、通常のテキストファイルからの実行の代わりに準備されたステートメントを使用し、Prepared Statements を再利用して直接実行することをお勧めします。これにより、TiDB で SQL 実行計画を繰り返し解析して生成するオーバーヘッドが回避されます。

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

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

バッチ API を使用する

バッチ挿入の場合は、 addBatch / executeBatch APIを使用できます。メソッド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は自動的に閉じられますが、前のストリーミング クエリで読み取られる結果セットは失われます。

• Cursor Fetch を使用するには、最初に正の整数としてFetchSizeを設定を指定し、JDBC URL でuseCursorFetch=trueを構成します。

TiDB は両方の方法をサポートしていますが、最初の方法を使用することをお勧めします。これは、実装がより単純で実行効率が高いためです。

MySQL JDBC パラメータ

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

準備関連パラメータ

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

• useServerPrepStmts

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

  この設定が既に有効になっていることを確認するには、次のようにします。

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

• cachePrepStmts

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

  この設定が既に有効になっていることを確認するには、次のようにします。

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

  

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

• prepStmtCacheSqlLimit

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

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

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

  • TiDB モニタリング ダッシュボードに移動し、 [ Query Summary ] > [ QPS By Instance ] からリクエスト コマンド タイプを表示します。
  • cachePrepStmts=trueが構成されていることを確認しますが、 COM_STMT_PREPAREはまだCOM_STMT_EXECUTEとほぼ等しく、 COM_STMT_CLOSEが存在します。

• prepStmtCacheSize

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

  この設定が既に有効になっていることを確認するには、次のようにします。

  • TiDB モニタリング ダッシュボードに移動し、 [ Query Summary ] > [ QPS By Instance ] からリクエスト コマンド タイプを表示します。
  • リクエスト内の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ステートメントの書き直しは、複数の「値」キーワードの後の値を SQL ステートメント全体に連結することであることに注意してください。 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 がselect @@session.transaction_read_onlyなどの設定をクエリするためにいくつかの SQL ステートメントを送信するために発生します。これらの SQL ステートメントは TiDB には役に立たないため、余分なオーバーヘッドを避けるためにuseConfigs=maxPerformanceを構成することをお勧めします。

useConfigs=maxPerformanceには、構成のグループが含まれます。 MySQL JDBC 8.0 と MySQL JDBC 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です。これは、タイムアウトが無制限であることを意味します。デフォルト値max_execution_timeは0です。これは、SQL ステートメントの最大実行時間が無制限であることを意味します。

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