Posted inMySQL

InnoDB Tablespaceを極める：肥大化するibdata1対策とI/O最適化の秘策

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

File-Per-Tableから始めよう：後悔する前に今すぐ確認

MySQL 5.6以降のほとんどのバージョンでは、innodb_file_per_tableがデフォルトで有効になっています。しかし、油断は禁物です。古いサーバーを引き継いだ場合や、以前にカスタマイズされたシステムを扱う場合、効率的なデータベース管理のために再確認することは極めて重要です。

ターミナルを開き、次のコマンドで現在のステータスを確認してください：

SHOW VARIABLES LIKE 'innodb_file_per_table';

もし結果がOFFであれば、大きなリスクに直面しています。すべてのデータがibdata1という単一のファイルに「詰め込まれて」いる状態です。新しいテーブルに対してファイル分割機能を有効にするには、以下のSQLコマンドを実行します：

SET GLOBAL innodb_file_per_table = ON;

なお、上記のコマンドは一時的なものです。サーバーの再起動後も設定を維持するには、<a href="https://itfromzero.com/ja/database/mysql-ja-database/mysql-8%e3%81%aemy-cnf%e6%9c%80%e9%81%a9%e5%8c%96%ef%bc%9a%e3%83%87%e3%83%95%e3%82%a9%e3%83%ab%e3%83%88%e8%a8%ad%e5%ae%9a%e3%81%a7%e3%82%b5%e3%83%bc%e3%83%90%e3%83%bc%e3%82%92%e3%80%8c%e7%aa%92.html">my.cnf</a>（Linux）またはmy.ini（Windows）ファイルに以下の行を追加する必要があります：

[mysqld]
innodb_file_per_table = 1

その後、MySQLサービスを再起動して変更を恒久的に適用してください。

ibdata1という悪夢：データを削除しても容量が減らない

かつて午前2時のオンコール対応で、忘れられない経験をしました。データベースサーバーのディスク容量が100%になり、システムが完全に停止したのです。実際のデータ量は約100GBだったにもかかわらず、<a href="https://itfromzero.com/ja/database/mysql-ja-database/mysql%e3%83%87%e3%83%bc%e3%82%bf%e5%be%a9%e6%97%a7%ef%bc%9ainnodb-corruption%ef%bc%88%e7%a0%b4%e6%90%8d%ef%bc%89%e3%82%a8%e3%83%a9%e3%83%bc%e3%81%ae%e5%ae%9f%e8%b7%b5%e7%9a%84%e3%81%aa%e5%af%be.html">ibdata1</a>ファイルは400GBまで肥大化していました。数百万件の古いログレコードをDELETEしても、ディスク上のファイルサイズは1バイトも減りませんでした。

これこそがSystem Tablespaceの致命的な弱点です。単一のファイルを共有している場合、MySQLは削除された領域を新しいデータのために再利用しようと保持し続け、OSに返却しません。

ibdata1を縮小したいですか？方法はただ一つです。データベース全体をSQLファイルにダンプし、物理ファイルであるibdata1を削除してから、最初からインポートし直すことです。数百GB規模のデータベースにとって、これはダウンタイムに関する正真正銘の悪夢です。

Tablespaceの種類：データに適した「格納場所」を選ぶ

InnoDBは主に3種類のTablespaceを提供しています。これらを理解することで、よりスマートなシステム設計が可能になります：

1. System Tablespace (ibdata1)

デフォルトでは、ここはデータディクショナリとUndoログの格納場所です。file-per-tableをオフにすると、テーブルデータとインデックスもここに混在することになります。アドバイスとしては、ここはメタデータの保存という本来の役割に専念させ、業務データを詰め込まないようにしましょう。

2. File-Per-Table Tablespaces

このオプションを使用すると、各テーブルがデータベースディレクトリ内に独自の.ibdファイルを保持します。このアプローチには、主に3つの大きな利点があります：

  • 容量の回収: TRUNCATEやDROP TABLEを実行すると、即座にディスク容量が解放されます。
  • 柔軟なメンテナンス: OPTIMIZE TABLEを使用してファイルを圧縮し、不要な空き領域を整理できます。
  • 高速なバックアップ: 特定のテーブルを別のサーバーにコピーしたり移動したりするのが容易になります。

3. General Tablespace

バージョン5.7から導入された、バランスの取れたソリューションです。複数のテーブルをいくつかの固定されたTablespaceファイルにグループ化できます。この方法は、数千の個別の.ibdファイルを開くよりもOSのファイルハンドルリソースを節約でき、かつ必要な柔軟性も維持できます。

I/O最適化のためのSSDとHDDের分離術

実際、すべてのデータに高速アクセスが必要なわけではありません。古いログテーブルは非常に重いですが、アクセスされることは稀です。サーバーにNVMe SSDとHDDの両方がある場合、ログをHDDに移動して、貴重なSSDのスペースをトランザクションテーブルのために節約すべきです。

まず、HDD上のディレクトリ（/mnt/data_hdd/にマウント済み）を指す新しいTablespaceを作成します：

CREATE TABLESPACE ts_archive 
ADD DATAFILE '/mnt/data_hdd/mysql/ts_archive.ibd' 
ENGINE=InnoDB;

次に、ログテーブルを「新しい家」に移動します：

ALTER TABLE logs_2023 TABLESPACE ts_archive;

これにより、負荷の高いログ書き込み操作がHDDに分散されます。SSDの負荷が軽減され、クライアントからのクエリ処理に専念できるようになるため、システム全体のレスポンス速度が明らかに向上します。

教訓：OPTIMIZE TABLEでWebサイトをダウンさせない

よくある間違いは、ピーク時間帯に巨大なテーブル（例：200GB）に対してOPTIMIZE TABLEを実行してしまうことです。このコマンドは、データを新しいファイルにコピーするためにテーブル全体をロックします。その結果、Webサイトは何時間もフリーズすることになります。

代わりに、以下のコマンドを使用してデータの断片化（フラグメンテーション）を事前に監視しましょう：

SELECT table_name, 
       round(data_free/1024/1024, 2) AS free_mb, 
       round(data_length/1024/1024, 2) AS data_mb 
FROM information_schema.tables 
WHERE engine = 'InnoDB' 
ORDER BY data_free DESC;

もしfree_mbカラムの値がテーブル全容量の20〜30%を超えている場合、ユーザーが最も少ない時間帯（例：午前3時）にメンテナンスを計画し、容量を回収すべきタイミングです。

データベース管理者への結び

Tablespaceの管理は、単に設定をオン・オフするだけではなく、データを論理的に整理する技術です。システムを常に「健全」に保つために、以下の4つの黄金律を覚えておいてください：

  1. サーバー構築時には、常にinnodb_file_per_tableを優先的に設定する。
  2. General Tablespaceを活用し、SSDとHDDの間でストレージの階層化を行う。
  3. information_schemaを通じて、週に一度は定期的に断片化を確認する。
  4. ibdata1が肥大化してしまった場合は、ディスク残量が警告（赤色）になるのを待たず、早めにダンプ/リストアの計画を立てる。

MySQLがディスク上でどのようにデータを保存しているかを深く理解することで、トラブルに対してより冷静かつプロフェッショナルに対応できるようになります。あなたのデータベースシステムがスムーズに稼働し続けることを願っています！

Share:
Tags: