日々更新されるテーブルは、想定していたよりも予想外に更新が増え、サイズが肥大化してストレージの問題が発生することがあると思います。今回はそれを未然に防ぐため、InnoDBのテーブルサイズを確認する方法をいくつか紹介します。今回の例ではMySQLのバージョン5.7.19を使用します。
TABLESテーブル
はじめに、InnoDBのテーブルとインデックスサイズを確認する方法として、information_schema内のtablesテーブルを見るのが一般的だと思います。ただし、このテーブルは統計情報から取得されるため正確な値ではありません。概算値となります。
DATA_LENGTH
カラム … 主キー領域を含むテーブルサイズ
INDEX_LENGTH
カラム … セカンダリインデックスのサイズ
DATA_FREE
カラム … 空き領域
上記をバイト単位で確認することができます。
これらの値はInnoDBのページ単位で増加するため、今回はデフォルトのinnodb_page_size=16384(16KB)なので テーブルサイズは 26804224÷16384=1636ページ確保されています。
SHOW TABLE STATUS 構文
もうひとつ一般的な方法として、SHOW TABLE STATUS
構文を使用することで確認できます。ここで表示されるものは、前述のinformation_schema.tables
テーブルと同じものです。
以下のように、FROMまたはINでデータベース名を指定でき、LIKE
やWHERE name = ?
を使用することで特定のテーブルを指定することもできます。
INNODB_SYS_TABLESPACESテーブル
これは、information_schema内のInnoDBテーブルスペースに関するメタデータを提供するテーブルです。
MySQL5.7、およびそれ以降は、このテーブルのALLOCATED_SIZE
カラムから、ディスク上に物理的に確保されているibdファイルのサイズが確認することができます。ibdファイルなので、テーブルとセカンダリインデックスを合わせたサイズとなり、実測値です。
また、innodb_file_per_table=ON
でなければ表示されません。innodb_file_per_tableオプションについては第55回 innodb_file_per_tableのメリット・デメリットをご参照ください。
NAME
カラム…テーブル名。データベース名/テーブル名のように表記されます。
FILE_SIZE
カラム…圧縮前のファイルサイズ(バイト単位)
ALLOCATED_SIZE
カラム…実際にディスクに割り当てられたibdファイルサイズ(バイト単位)
ここの圧縮というのはMySQL5.7とそれ以降で追加された機能「透過的ページ圧縮」です。この機能を使用したテーブルのFILE_SIZE
とALLOCATED_SIZE
カラムの値の差を比較すると、どのくらい圧縮されたのかわかります。
「透過的ページ圧縮」を使用したテーブルt1
の例
また、従来の圧縮方式row_format=compressed
で作成された場合は、FILE_SIZE
とALLOCATED_SIZE
カラムはすでに圧縮された値になります。
INNODB_SYS_TABLESTATSビュー
これはテーブルではなく、information_schema内のInnoDBのステータス情報を提供するビューです。ここからはテーブルとインデックスの確保されたInnoDBのページ数を確認することができます。
ページ数なので、実際のバイト数を導くには、前述のページサイズ(デフォルトのinnodb_page_size=16384バイト)×ページ数(1636)=26804224バイトだとわかります。このビューも統計情報から取得されるため正確な値ではありません。概算値となります。
NAME
カラム…テーブル名。INNODB_SYS_TABLESPACESテーブルと同様です。
CLUST_INDEX_SIZE
カラム…主キーを含むテーブルサイズ(ページ単位)
OTHER_INDEX_SIZE
カラム…セカンダリーインデックスのサイズ(ページ単位)
innodb_table_statsテーブル
mysqlデータベース内のテーブルです。ここからも、テーブルとインデックスの確保されたInnoDBのページ数を確認することができます。このビューも統計情報から取得されるため正確な値ではありません。概算値となります。
database_name
カラム…データベース名
table_name
カラム…テーブル名
clustered_index_size
カラム…主キーを含むテーブルサイズ(ページ単位)
sum_of_other_index_sizes
カラム…セカンダリーインデックスのサイズ(ページ単位)
まとめ
今回はInnoDBのテーブルサイズを確認する5つの方法を紹介しました。それぞれの方法の違いは以下の表のようにまとめられます。
方法 | 単位 | 概算値/実測値 |
i_s.TABLES | バイト | 概算値 |
SHOW TABLE STATUS構文 | バイト | 概算値 |
i_s.INNODB_SYS_TABLESPACES | バイト | 実測値 |
i_s.INNODB_SYS_TABLESTATS | ページ | 概算値 |
mysql.innodb_table_stats | ページ | 概算値 |
※)i_s =information_schema
これらの方法でテーブルサイズを定期的に監視して、肥大化したテーブルの確認やサーバのディスクフルなど未然に防ぐことができます。
INNODB_SYS_TABLESPACESテーブルのibdファイルのサイズ以外は統計情報を基にしています。そのため、実際と大きくサイズがずれている場合は、ANALYZE TABLE
構文を使用することで統計情報を再取得できます。