重要
このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

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

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

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

接続プール

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

ドルイドには、 HikariCPなどの多くの接続プールのc3p0dbcp tomcat-jdbc 。 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 milliseconds agon0または非常に小さい値である場合、通常、実行されたSQL操作によってTiDBが異常終了することが原因です。原因を特定するには、TiDBstderrログを確認することをお勧めします。

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_count0に設定する必要があります。キャッシュのヒット率が低下すると、カウントは実際の数HDDに近くなります。
  • 式がSSDで機能するかどうかはテストされておらず、不明です。

SSDを使用する場合は、代わりに経験に基づいて次の式を使用することをお勧めします。

connections = (number of cores * 4)

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

チューニングの方向

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

最適なサイズを取得するための基本的なルールは次のとおりです。

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

接続パラメータ

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

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

JDBC API

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

PrepareAPIを使用する

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

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

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

バッチAPIを使用する

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

ノート:

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

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

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

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

通常、JDBCには2種類の処理方法があります。

  • クライアントがキャッシュしないようにするにはFetchSizeInteger.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は自動的に閉じられますが、前のストリーミングクエリで読み取られる結果セットは失われます。

  • カーソルフェッチを使用するには、最初にFetchSizeを設定しますを正の整数として使用し、JDBCURLでuseCursorFetch=trueを構成します。

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

MySQLJDBCパラメータ

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

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

  • useServerPrepStmts

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

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

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

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

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

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

    QPS By Instance

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

  • prepStmtCacheSqlLimit

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

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

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

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

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

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

    • TiDBモニタリングダッシュボードに移動し、[クエリの概要]>[インスタンス別のQPS ]からリクエストコマンドタイプを表示します。
    • リクエストの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」キーワードの後の値を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=trueuseServerPrepStmts=trueを構成する場合は、このバグを回避するためにallowMultiQueries=trueパラメーターも構成することをお勧めします。

パラメータを統合する

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

useConfigs=maxPerformance構成には、構成のグループが含まれます。

cacheServerConfiguration=true
useLocalSessionState=true
elideSetAutoCommits=true
alwaysSendSetIsolation=false
enableQueryTimeouts=false

構成後、モニターをチェックして、 SELECTステートメントの数が減少していることを確認できます。

TiDBは、タイムアウトを制御するための2つのMySQL互換パラメーターwait_timeoutmax_execution_timeを提供します。これらの2つのパラメーターはそれぞれ、Javaアプリケーションとの接続アイドルタイムアウトと接続でのSQL実行のタイムアウトを制御します。つまり、これらのパラメータは、TiDBとJavaアプリケーション間の接続の最長アイドル時間と最長ビジー時間を制御します。 TiDB v5.4以降、デフォルト値のwait_timeout28800秒、つまり8時間です。 v5.4より前のバージョンのTiDBの場合、デフォルト値は0です。これは、タイムアウトが無制限であることを意味します。デフォルト値のmax_execution_time0です。これは、SQLステートメントの最大実行時間が無制限であることを意味します。

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