JDBC および MySQL 一時テーブルスペースの詳細な分析

背景

一時テーブルスペースは、データベースのソート操作を管理し、一時テーブルや中間ソート結果などの一時オブジェクトを保存するために使用されます。開発では、関連する要件に頻繁に遭遇すると思います。次の記事では、JDBC と MySQL の一時テーブルスペースに関する詳細な情報を提供し、参考と学習のために共有します。詳細な紹介を見てみましょう。

JDBC 接続パラメータuseCursorFetch=trueが使用され、クエリ結果セットが mysqld 一時テーブルスペースに保存されるため、ibtmp1 ファイルのサイズが 90G 以上に急増し、サーバーのディスク領域が枯渇します。一時表領域のサイズを制限するには、次のように設定します。

innodb_temp_data_file_path = ibtmp1:12M:自動拡張:最大:2G

問題の説明

一時テーブルスペースを制限した後、アプリケーションが以前の方法で引き続きアクセスすると、ibtmp1 ファイルが 2G に達した後、プログラムはタイムアウトして切断されるまで待機します。 SHOW PROCESSLIST は、プログラムの接続スレッドがスリープ状態にあり、状態と情報情報が空であることを示します。これはアプリケーション開発にとってあまり使いやすくありません。プログラムがタイムアウトを待機した後、原因を分析するときにプロンプト情報が表示されません。

問題分析プロセス

問題を分析するために、以下のテストを実施しました。

テスト環境:

マイスク:5.7.16

java:1.8u162

JDBC ドライバー: 5.1.36

OS: レッドハット6.4

1. 一時テーブルが最大制限を超えるシナリオを手動でシミュレートする

次の環境をシミュレートします。

ibtmp1:12M:自動拡張:最大:30M

500万行のsbtestテーブルのkフィールドインデックスを削除します。

グループバイクエリを実行するときに、生成された一時テーブルのサイズが制限を超えると、エラーが直接報告されます。

sbtest1 から sum(k) を選択し、k でグループ化します。
エラー 1114 (HY000): テーブル '/tmp/#sql_60f1_0' がいっぱいです

2. mysqlのドライバーの設定を確認する

前の手順では、SQL を手動で実行するとエラーが返されるのに対し、JDBC ではエラーが返されず、接続が常にスリープ状態になることを確認しました。mysql ドライバーが特別な設定を行ったことが疑われます。ドライバーは mysql に接続し、general_log を通じてどのような設定が行われたかを確認します。特別な設定は見つかりませんでした。

3. JDBC接続をテストする

この問題の背景には、JDBC の特別な設定、useCursorFetch=true があります。これがエラーの非表示に関係しているかどうかはわかりません。次に、これをテストします。

以下の現象が見つかりました。

パラメータuseCursorFetch=trueを追加すると、同じクエリでエラーが報告されなくなります。

このパラメータは、返される結果セットが大きくなりすぎないようにし、セグメント化された読み取りを使用するために使用されます。つまり、プログラムは MySQL に SQL 文を送信した後、MySQL が結果を読み取ることができるフィードバックを待機します。MySQL は、SQL 文を実行するときに返された結果が ibtmp 上限に達した後にエラーを報告しますが、スレッドを閉じないためです。スレッドはスリープ状態を処理し、プログラムはフィードバックを取得できず、エラーを報告せずに待機し続けます。このスレッドを強制終了すると、プログラムはエラーを報告します。

パラメータuseCursorFetch=trueがない場合、同じクエリはエラーになります。

結論は

1. 通常の状況では、SQL 実行中に一時テーブルのサイズが ibtmp 上限に達するとエラーが報告されます。

2. JDBC でuseCursorFetch=true設定すると、SQL 実行中に一時テーブルのサイズが ibtmp 上限に達してもエラーは報告されません。

解決

さらに、 useCursorFetch=trueを使用すると、クエリ結果セットが大きくなりすぎて JVM がバーストするのを防ぐことができることもわかりました。

ただし、 useCursorFetch=trueを使用すると、通常のクエリによって一時テーブルが生成され、一時テーブルスペースが大きくなりすぎます。

一時表領域が大きすぎる問題を解決するには、ibtmp1 のサイズを制限することです。ただし、 useCursorFetch=trueの場合、JDBC はエラーを返しません。

したがって、同じ効果を得るには他の方法を使用する必要があり、SQL がエラーを報告した後、プログラムもそれに応じてエラーを報告する必要があります。 useCursorFetch=true のセグメント読み取り方式に加え、ストリーム読み取り方式も使用できます。ストリーム読み取り手順の詳細は添付ファイルに記載されています。

エラー比較

セグメント読み取りモード、SQLがエラーを報告した後、プログラムはエラーを報告しません

· ストリーム読み取りモードでは、SQLがエラーを報告した後、プログラムはエラーを報告します。

メモリ使用量の比較

ここでは、通常読み取り、セグメント読み取り、ストリーム読み取りの 3 つの方法を比較します。初期のメモリ使用量は約 28M です。

通常の読み取り後、メモリは100M以上を占有します

セグメントが読み取られた後、メモリは約60Mを占有します

ストリームが読み込まれた後、メモリは約60Mを占有します

追加の知識ポイント

MySQL共有一時テーブルスペースの知識ポイント

MySQL 5.7 では、一時表領域が改善され、一時表領域が ibdata (共有表領域ファイル) から分離されました。また、再起動してサイズをリセットすることで、ibdata が大きすぎて以前のように解放できなくなるという問題を回避できます。
パラメータは次のとおりです: innodb_temp_data_file_path

1. パフォーマンス

MySQL が起動すると、初期サイズが 12M の ibtmp1 ファイルが datadir の下に作成されます。デフォルト値では、このファイルは無限に拡張されます。

一般的に、クエリ（group by など）によって生成された一時テーブルが tmp_table_size と max_heap_table_size のサイズ制限を超えると、innodb ディスク一時テーブルが作成され（MySQL5.7 のデフォルトの一時テーブルエンジンは innodb）、共有一時テーブルスペースに保存されます。

操作によってサイズが 100 MB の一時テーブルが作成される場合、一時テーブルのニーズを満たすために一時テーブルスペースデータファイルは 100 MB に拡張されます。一時テーブルを削除すると、解放されたスペースを新しい一時テーブルに再利用できます。ただし、ibtmp1 ファイルは拡張されたままになります。

2. クエリビュー

共有一時表領域の使用状況を照会できます。

INFORMATION_SCHEMA.FILES から FILE_NAME、TABLESPACE_NAME、ENGINE、INITIAL_SIZE、TOTAL_EXTENTS*EXTENT_SIZE を TotalSizeBytes、DATA_FREE、MAXIMUM_SIZE として選択します。ここで、TABLESPACE_NAME は 'innodb_temporary'\G です。
************************** 1. 行 ****************************
    ファイル名: /data/mysql5722/data/ibtmp1
テーブルスペース名: innodb_temporary
      エンジン: InnoDB
    初期サイズ: 12582912
   合計サイズバイト: 31457280
    データ空き容量: 27262976
  最大サイズ: 31457280
セット内の 1 行 (0.00 秒)

3. リサイクル方法

回復するにはMySQLを再起動してください

4. サイズを制限する

一時データファイルが大きくなりすぎないようにするには、innodb_temp_data_file_path (有効にするには再起動が必要) オプションを構成して最大ファイルサイズを指定します。データファイルが最大サイズに達すると、クエリはエラーを返します。

innodb_temp_data_file_path=ibtmp1:12M:自動拡張:最大:2G

5. 一時テーブルスペースと tmpdir

共有一時表領域は、圧縮されていない InnoDB 一時表、関連オブジェクト、ロールバックセグメントなどのデータを格納するために使用されます。

tmpdir は、指定された一時ファイルと一時テーブルを保存するために使用されます。共有一時テーブルスペースとは異なり、tmpdir は圧縮された InnoDB 一時テーブルを保存します。

これは次のステートメントでテストできます。

一時テーブルcompress_tableを作成します(id int、name char(255)) ROW_FORMAT=COMPRESSED;
一時テーブル uncompress_table (id int、name char(255)) を作成します。

付録

シンプルな例.java

java.sql.Connection をインポートします。
java.sql.DriverManager をインポートします。
java.sql.PreparedStatement をインポートします。
java.sql.ResultSet をインポートします。
java.sql.SQLException をインポートします。
java.sql.Statement をインポートします。
java.util.Properties をインポートします。
java.util.concurrent.CountDownLatch をインポートします。
java.util.concurrent.atomic.AtomicLong をインポートします。
パブリッククラスSimpleExample {
 パブリック静的void main(String[] args)は例外をスローします{
  クラス.forName("com.mysql.jdbc.Driver");
  プロパティ props = new Properties();
  props.setProperty("ユーザー", "ルート");
  props.setProperty("パスワード", "ルート");
  SimpleExample エンジン = 新しい SimpleExample();
// engine.execute(props,"jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false");
  engine.execute(props,"jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false&useCursorFetch=true");
 }
 最終的なAtomicLong tmAl = 新しいAtomicLong();
 最終的な文字列 tableName="test";
 パブリック void 実行(プロパティ props、文字列 url) {
  カウントダウンラッチ cdl = 新しいカウントダウンラッチ(1);
  長い開始 = System.currentTimeMillis();
  (int i = 0; i < 1; i++) の場合 {
   テストスレッド insertThread = new TestThread(props,cdl, url);
   スレッド t = new Thread(insertThread);
   t.start();
   System.out.println("テスト開始");
  }
  試す {
   cdl.await();
   長い終了 = System.currentTimeMillis();
   System.out.println("テスト終了、合計コスト:" + (end-start) + "ms");
  } キャッチ (例外 e) {
  }
 }
 
 クラス TestThread は Runnable を実装します {
  プロパティ props;
  プライベート CountDownLatch countDownLatch;
  文字列 URL;
  パブリック TestThread(プロパティ props、CountDownLatch cdl、文字列 url) {
   プロパティ
   this.countDownLatch = cdl;
   this.url = url;
  }
  パブリックボイド実行() {
   接続 connection = null;
   PreparedStatement ps = null;
   ステートメント st = null;
   長い開始 = System.currentTimeMillis();
   試す {
    接続 = DriverManager.getConnection(url,props);
    接続.setAutoCommit(false);
    st = 接続.createStatement();
     
    //st.setFetchSize(500);
    st.setFetchSize(Integer.MIN_VALUE); //ここを変更するだけです ResultSet rstmp;
     
    st.executeQuery("sbtest1グループからkでsum(k)を選択");
    rstmp = st.getResultSet();
    while(rstmp.next()){
      
    }
   } キャッチ (例外 e) {
    System.out.println(System.currentTimeMillis() - 開始);
    System.out.println(新しいjava.util.Date().toString());
    e.printStackTrace();
   ついに
    (ps != null) の場合
     試す {
      ps.close();
     } キャッチ (SQLException e1) {
      e1.printStackTrace();
     }
    (接続 != null) の場合
     試す {
      接続を閉じます。
     } キャッチ (SQLException e1) {
      e1.printStackTrace();
     }
    this.countDownLatch.countDown();
   }
  }
 }
}

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

以下もご興味があるかもしれません: