MySQL FAQ シリーズ: ibdata1 ファイルのサイズが突然増加しないようにする方法

0. はじめに

ibdata1 ファイルとは何ですか?

ibdata1 は、innodb システム テーブルスペースを構築するために使用されるファイルです。このファイルには、innodb テーブルのメタデータ、元に戻すレコード、変更バッファ、および二重書き込みバッファが含まれています。ファイルごとのテーブル オプションがオンになっている場合、ファイルに必ずしもすべてのテーブルのデータが含まれているわけではありません。 innodb_file_per_table オプションをオンにすると、新しく作成されたテーブルのデータとインデックスはシステム テーブルスペースではなく、各テーブルの .ibd ファイルに保存されます。

当然、このファイルはどんどん大きくなります。innodb_autoextend_increment オプションは、毎回自動的に大きくなるファイルのステップ サイズを指定します。デフォルト値は 8M です。

ibdata1 ファイルがどんどん大きくなる原因は何ですか?

ibdata1 はデータ、インデックス、キャッシュなどを格納しており、MYSQL の最も重要なデータです。したがって、データベースが大きくなるにつれてテーブルも大きくなりますが、これは避けられません。時間が経ってサイズが大きくなると、丸太やスペースの処理が面倒になり、どこから手を付けてよいか分からなくなります。次に、このような状況に対処し、データを別のデータベースに保存します。

InnoDB の共有テーブルスペース ファイル ibdata1 のサイズが大幅に増加した場合はどうすればよいですか?

1. 問題の背景

MySQL/InnoDB を使っている友人も困っているかもしれません。なぜか ibdata1 ファイルがどんどん大きくなっていき、30 歳を過ぎた男性のお腹のように、どうやって小さくしたらいいのかわからないのです。幸い私のお腹はまだ大きくなっていません、ほほ～

正式に始める前に、ibdata1 ファイルが何に使用されるのかを知る必要があります。

ibdata1 ファイルは、InnoDB ストレージ エンジンの共有テーブルスペース ファイルです。このファイルには主に次のデータが格納されます。

• データ辞書
• ダブル書き込みバッファ
• バッファの挿入/バッファの変更
• ロールバックセグメント
• 元に戻すスペース
• 外部キー制約システムテーブル

さらに、オプションinnodb_file_per_table = 0の場合、InnoDB テーブル データとインデックスを ibdata1 ファイルに保存する必要があります。 ibdata1 ファイルのデフォルト サイズはバージョン 5.6.7 以降では 12MB で、それ以前のデフォルト サイズは 10MB でした。関連するオプションは innodb_data_file_path です。たとえば、通常は次のように設定します。

innodb_data_file_path = ibdata1:1G:自動拡張

もちろん、 innodb_file_per_table = 1が有効かどうかに関係なく、ibdata1 ファイルは存在する必要があります。これは、InnoDB エンジンが依存し、必要とするデータ、特に上記で太字でマークされたロールバック セグメントと UNDO 領域を格納する必要があるためです。これらは、ibdata1 ファイルのサイズが増加する最大の理由であり、以下で詳しく説明します。

2. 原因分析

InnoDB が MVCC をサポートしていることはわかっています。ORACLE と同様に、InnoDB は UNDO ログと REDO ログを使用して MVCC 機能を実装します。トランザクションでデータ行が変更されると、InnoDB はデータの古いバージョンを UNDO ログに保存します。別のトランザクションがこの時点でデータを変更する場合、データの最新の表示バージョンを UNDO ログに保存します。以下同様に続きます。データを変更するトランザクションが N 個ある場合、N 個の履歴バージョンを保存する必要があります (ORACLE とは少し異なり、InnoDB の UNDO ログは完全に物理ブロックではなく、主に論理ログです。これについては、InnoDB ソース コードまたはその他の関連資料を参照してください)。これらの UNDO ログは、トランザクションが完了するまで待機し、トランザクション分離レベルによって決定される他のトランザクションからの可視性に基づいて、これらの UNDO ログを削除できるかどうかを再度判断する必要があります。この作業をパージと呼びます (パージ作業は、期限切れの未使用 UNDO ログを削除するだけではありません。他にもさまざまな作業があります。これについては後述します)。

ここで疑問が生じます。大量のデータの履歴バージョンを読み取る必要があるトランザクションがあり、何らかの理由で今朝そのトランザクションをコミットまたはロールバックできず、トランザクションの開始後に大量のトランザクションでデータを変更する必要がある場合、これらの新しいトランザクションによって生成された UNDO ログを削除できず、結果的に蓄積されてしまいます。これが、ibdata1 ファイルのサイズが増加する主な理由の 1 つです。この状況の最も典型的なシナリオは大量のデータのバックアップであるため、バックアップ作業をマスター サーバーではなく専用のスレーブ サーバーに配置することをお勧めします。

また、ファイルI/Oパフォーマンスの低下などの理由により、InnoDBのパージ作業で、時間内に削除できるUNDOログをパージできずに蓄積されてしまうケースもあります。これもibdata1ファイルのサイズが増加する大きな原因です。このシナリオは、サーバーのハードウェア構成が比較的弱く、ビジネスの発展に追いつくために時間内にアップグレードされていない場合に発生します。

比較的まれな問題としては、32 ビット システムで実行されている初期の MySQL バージョンにバグがあるというものがあります。パージする undo ログの合計量が一定値を超えると、パージ スレッドは抵抗を放棄し、それ以上のパージを行わなくなります。この問題は、初期の 32 ビット MySQL 5.0 バージョンを使用していたときに頻繁に発生しました。このファイルが 100G を超える状況に遭遇したこともあります。その後、私たちはこれらすべてのインスタンスを 64 ビット システムに移行するために多大な労力を費やし、ついにこの問題を解決しました。

最後は、オプション innodb_data_file_path の値が最初に調整されていなかったか、非常に小さく設定されていたため、必然的に ibdata1 ファイルが増加したことです。 Percona が公式に提供している my.cnf 参照ファイルでは、この値が増加したことは一度もありません。これは不思議です。私がよく不満を言う xx のように、意図的に秘密のドアを残しておき、顧客がその後最適化しやすいようにするためでしょうか。 （心が暗すぎてダメだ〜〜）

要約すると、ibdata1 ファイルのサイズが突然増加した理由は次のとおりです。

• 同時トランザクションが多数あり、大量の UNDO ログが生成されます。
• 長期間コミットされていない古いトランザクションがあり、その結果、古い UNDO ログが大量に存在します。
• ファイル I/O パフォーマンスが低下し、パージの進行が遅くなります。
• 初期設定が小さすぎて不十分です。
• 32 ビット システムにはバグがあります。

ちょっとした補足ですが、別の人気データベースであるPostgreSQLは、各履歴バージョンのデータを元のデータテーブルスペースと一緒に保存するため、この場合の問題は発生しません。したがって、PostgreSQLトランザクションのロールバックは非常に高速になり、定期的にバキューム作業を行う必要があります（詳細については、PostgreSQLのMVCC実装メカニズムを参照してください。完全に正確ではない可能性があります）。

3. 解決策の提案

上記の問題の原因の説明を見た後、一部の学生は、これは簡単に解決できると思うかもしれません。ibdata1 ファイルのサイズを縮小し、テーブル スペースを再利用するだけです。残念なことに、現時点では、InnoDB には ibdata1 ファイルのテーブルスペースを再利用/縮小する方法がありません。ibdata1 ファイルが拡大されると、元のサイズを復元する唯一の方法は、データをバックアップして復元し、インスタンスを再初期化するか、独立した各テーブルスペース ファイルを新しいインスタンスに順番にバックアップして復元することです。これより良い方法はありません。

もちろん、この問題は予防不可能ではありません。上記の理由により、推奨される対策は次のとおりです。

• 5.6 以上 (64 ビット) にアップグレードし、独立した UNDO 表領域を使用します。独立した UNDO 表領域はバージョン 5.6 以降でサポートされています。ibdata1 ファイルのサイズが大きくなることを心配する必要はなくなりました。
• 初期化中に、ibdata1 ファイルを少なくとも 1 GB に設定します。
• パージ スレッドの数を増やします。たとえば、 innodb_purge_threads = 8設定します。
• ファイル I/O 機能を改善し、必要に応じて SSD をインストールします。
• トランザクションをタイムリーに送信し、バックログを回避します。
• デフォルトでは、長期間トランザクションのコミットを忘れないように、 autocommit = 1が有効になっています。
• 開発フレームワークをチェックして、 autocommit=0が設定されているかどうかを確認します。トランザクションの終了後は、明示的にコミットまたはロールバックすることを忘れないでください。

要約する

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