MySQL道普請便り

第56回InnoDBテーブルサイズのいろいろな確認方法

日々更新されるテーブルは、想定していたよりも予想外に更新が増え、サイズが肥大化してストレージの問題が発生することがあると思います。今回はそれを未然に防ぐため、InnoDBのテーブルサイズを確認する方法をいくつか紹介します。今回の例ではMySQLのバージョン5.7.19を使用します。

TABLESテーブル

はじめに、InnoDBのテーブルとインデックスサイズを確認する方法として、information_schema内のtablesテーブルを見るのが一般的だと思います。ただし、このテーブルは統計情報から取得されるため正確な値ではありません。概算値となります。

mysql> SELECT TABLE_NAME,ENGINE,DATA_LENGTH,INDEX_LENGTH,DATA_FREE 
       FROM information_schema.tables WHERE TABLE_NAME = 't0';
+------------+--------+-------------+--------------+-----------+
| TABLE_NAME | ENGINE | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+------------+--------+-------------+--------------+-----------+
| t0         | InnoDB |    26804224 |     15253504 |   4194304 |
+------------+--------+-------------+--------------+-----------+
  • 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でデータベース名を指定でき、LIKEWHERE name = ?を使用することで特定のテーブルを指定することもできます。

<表示一部割愛>
mysql> show table status from db1 like 't0'\G
*************************** 1. row ***************************
           Name: t0
         Engine: InnoDB
    Data_length: 26804224
   Index_length: 15253504
      Data_free: 4194304

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のメリット・デメリットをご参照ください。

mysql> SELECT NAME,FILE_SIZE,ALLOCATED_SIZE 
       FROM information_schema.innodb_sys_tablespaces WHERE NAME LIKE '%t0%';
+-------+-----------+----------------+
| NAME  | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------+----------------+
| db/t0 |  50331648 |       50335744 |
+-------+-----------+----------------+
1 row in set (0.00 sec)
  • NAMEカラム…テーブル名。データベース名/テーブル名のように表記されます。
  • FILE_SIZEカラム…圧縮前のファイルサイズ(バイト単位)
  • ALLOCATED_SIZEカラム…実際にディスクに割り当てられたibdファイルサイズ(バイト単位)

ここの圧縮というのはMySQL5.7とそれ以降で追加された機能「透過的ページ圧縮」です。この機能を使用したテーブルのFILE_SIZEALLOCATED_SIZEカラムの値の差を比較すると、どのくらい圧縮されたのかわかります。

「透過的ページ圧縮」を使用したテーブルt1の例

mysql> SELECT NAME,FILE_SIZE,ALLOCATED_SIZE 
       FROM information_schema.innodb_sys_tablespaces WHERE NAME LIKE '%t1%';
+-------+-----------+----------------+
| NAME  | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------+----------------+
| db/t1 |  50331648 |       21295104 |
+-------+-----------+----------------+

また、従来の圧縮方式row_format=compressedで作成された場合は、FILE_SIZEALLOCATED_SIZEカラムはすでに圧縮された値になります。

INNODB_SYS_TABLESTATSビュー

これはテーブルではなく、information_schema内のInnoDBのステータス情報を提供するビューです。ここからはテーブルとインデックスの確保されたInnoDBのページ数を確認することができます。

ページ数なので、実際のバイト数を導くには、前述のページサイズ(デフォルトのinnodb_page_size=16384バイト)×ページ数(1636)=26804224バイトだとわかります。このビューも統計情報から取得されるため正確な値ではありません。概算値となります。

mysql> SELECT NAME,CLUST_INDEX_SIZE,OTHER_INDEX_SIZE
       FROM information_schema.innodb_sys_tablestats WHERE NAME LIKE '%t0%';
       
+-------+------------------+------------------+
| NAME  | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE |
+-------+------------------+------------------+
| db/t0 |             1636 |              803 |
+-------+------------------+------------------+
  • NAMEカラム…テーブル名。INNODB_SYS_TABLESPACESテーブルと同様です。
  • CLUST_INDEX_SIZEカラム…主キーを含むテーブルサイズ(ページ単位)
  • OTHER_INDEX_SIZEカラム…セカンダリーインデックスのサイズ(ページ単位)

innodb_table_statsテーブル

mysqlデータベース内のテーブルです。ここからも、テーブルとインデックスの確保されたInnoDBのページ数を確認することができます。このビューも統計情報から取得されるため正確な値ではありません。概算値となります。

mysql> SELECT database_name,table_name,clustered_index_size,sum_of_other_index_sizes 
       FROM mysql.innodb_table_stats WHERE database_name = 'oo' AND table_name = 't0';
       
+---------------+------------+----------------------+--------------------------+
| database_name | table_name | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+----------------------+--------------------------+
| oo            | t0         |                 1636 |                      803 |
+---------------+------------+----------------------+--------------------------+
  • 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構文を使用することで統計情報を再取得できます。

おすすめ記事

記事・ニュース一覧