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

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 を応援していただきありがとうございます。

以下もご興味があるかもしれません:
  • Mysql の一時テーブルとパーティションテーブルの違いの詳細な説明
  • Mysql一時テーブルの原理と作成方法の分析
  • mysql 一時テーブルの使用状況の分析 [クエリ結果は一時テーブルに保存できます]
  • MySQL 5.7 の一時テーブルスペースを使用して落とし穴を回避する方法
  • MySQL FAQ シリーズ: 一時テーブルを使用する場合
  • MySQL 一時テーブルの簡単な使用法
  • MySQL における単一テーブルと複数テーブル、およびビューと一時テーブルに対する Update と Select の違い
  • MySQL の 2 種類の一時テーブルの使用方法の詳細な説明
  • MySQL の一時テーブルと派生テーブルについての簡単な説明
  • MySQL の一時テーブルの基本作成と使用のチュートリアル
  • MySQL の一時テーブルの基本的な使用方法
  • 一時テーブルを使用して MySQL クエリを高速化する方法
  • MySQL での一時テーブルの使用例

<<:  Zookeeper&Kafka クラスターを構築するための Docker の実装

>>:  Vueは双方向データバインディングを実装します

推薦する

分散ロックの原理と3つの実装方法の詳細な説明

現在、ほぼすべての大規模な Web サイトとアプリケーションは分散方式で展開されています。分散シナリ...

Docker コンテナを他のサーバーに移行する 5 つの方法

多くの場合、移行は避けられません。ハードウェアのアップグレード、データ センターの変更、古いオペレー...

node.jsのインストールとHbuilderXの設定の詳細な説明

npm インストールチュートリアル: 1. Node.jsインストールパッケージをダウンロードする公...

Linuxドライバのプラットフォームバスの詳細説明

目次1. プラットフォームバスの紹介1.1. Linuxドライバの分離と階層化1.1.1. Linu...

回転するフリップカードアニメーションの効果を実現するCSS

回転フリップ効果の CSS アニメーション、具体的な内容は次のとおりです。 1. まず2つのボックス...

Docker で Springboot プロジェクトを実行する実装

導入: springboot プロジェクトを実行する Docker の構成は実は非常にシンプルで、L...

Windows Server 2012 リモート デスクトップ ライセンス サーバーがライセンスを提供できず、リモート セッションが切断される

本日、会社の内部サーバーにログインしたところ、リモートアクセスができませんでした。エラー メッセージ...

Nest.js パラメータ検証とカスタム戻りデータ形式の詳細な説明

0x0 パラメータ検証Nest.jsでは、パラメータ検証業務のほとんどをパイプライン方式で実装してい...

WeChatミニプログラムがシームレスなスクロールを実現

この記事の例では、WeChatアプレットのシームレスなスクロールを実現するための具体的なコードを参考...

単一選択折りたたみメニュー機能を実現するCSS

前回の「最もシンプルなスイッチを実現するCSS」のように、HTML5とCSS3でほとんどの機能をすで...

CSS で雨滴アニメーション効果を実装するサンプルコード

ガラス窓今日実現するのは雨滴効果です。ただし、雨滴効果を実現する前に、まずは曇りガラス効果を作成しま...

Vue のループフォーム項目例の詳細な説明

場合によっては、ユーザーがボタンをクリックして同様のフォームを追加し、クリックごとに 1 回追加でき...

加算、減算、乗算、除算の機能を実現するには、HTML に 2 つの数値を入力します。

1. parseFloat() 関数Web ページ上に簡単な計算機を作成し、テキスト ボックスに ...

mysql indexof関数の使用手順

以下のように表示されます。 LOCATE(部分文字列、文字列)文字列 str 内の部分文字列 sub...

Linux でディスクをマウントし、起動時に自動的にマウントするように設定する方法

皆さんの時間は貴重だと承知しているので、プロセス コマンドを直接書き留めておきます。設定できます。原...