ablog

不器用で落着きのない技術者のメモ

MySQL の InnoDB テーブルの断片化状況を確認する

MySQL InnoDB

MySQL の InnoDB テーブルの空き領域は INFORMATION_SCHEMA.TABLES の DATA_FREE カラムで確認でき、ALTER TABLE <テーブル名> ENGINE INNODB で断片化（フラグメンテーション）を解消することができる。なお、テーブルの断片化でI/O量が増えて性能劣化するのはフルテーブルスキャンで、インデックススキャンは影響を受けない。

前提

確認方法

select table_schema, table_name, data_free, table_rows
	from information_schema.tables
	where table_name='テーブル名';

例

  • テーブルを作成してデータをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=10000 \
 --mysql-host=aurora01.******.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=******** \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
  • 1件を残して削除する
mysql> delete from sbtest1 where id < 10000;
Query OK, 9999 rows affected (0.16 sec)
  • テーブルの空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows
    -> from information_schema.tables
    -> where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   6291456 |          1 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

mysql> alter table sbtest1 engine innodb;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select table_schema, table_name, data_free, table_rows
    -> from information_schema.tables
    -> where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |         0 |          1 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

参考

DATA_FREE カラムには、InnoDB テーブルの空き領域がバイト単位で表示されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.22 INFORMATION_SCHEMA TABLES テーブル

少し話がそれてしまったが、OPTIMIZEが必要になるのはどういう時だろうか？簡単にいうと、「行をDELETEのして無駄な領域がたくさん生じたとき」だけである。これによって、利用されていない領域が回収されることになる。そして、フラグメンテーションが解消するわけである。

実は、InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。OPTIMIZE TABLE t1は次のコマンドを実行するのと同じなのである。

mysql> ALTER TABLE t1 ENGINE INNODB;

このコマンドを実行すると、テーブルのデータが詰め直される。データの挿入は主キーの順番で行われるため、OPTIMIZE TABLEすると

という効果が期待できることになる。残念ながら、セカンダリインデックスをセカンダリインデックスの順番で詰め直す方法はない。そのため、セカンダリインデックスの空間効率はあまりよくない場合があるので注意が必要である。

OPTIMIZE TABLEが内部的にALTER TABLEと同じということは、別に悪いことばかりではない。モノホンのOPTIMIZE TABLEだと実行中は参照も更新もブロックしてしまうことになるが、ALTER TABLEであればメンテナンス中も参照は可能なのである。

漢(オトコ)のコンピュータ道: 大人のためのInnoDBテーブルとの正しい付き合い方。

13.7.2.4 OPTIMIZE TABLE 構文

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

ストレージ領域を削減し、テーブルアクセス時の I/O 効率を向上させるために、テーブルデータとそれに関連付けられたインデックスデータの物理ストレージを再編成します。各テーブルに加えられる正確な変更は、そのテーブルによって使用されているストレージエンジンによって異なります。

OPTIMIZE TABLE は、テーブルのタイプに応じて次の場合に使用します。

  • innodb_file_per_table オプションが有効な状態で作成されたために独自の .ibd ファイル を含む InnoDB テーブルに対して大量の挿入、更新、または削除操作を行なったあと。テーブルとインデックスが再編成されるため、ディスク領域をオペレーティングシステムによる使用のために再利用できます。
  • InnoDB テーブル内の FULLTEXT インデックスの一部であるカラムに対して大量の挿入、更新、または削除操作を行なったあと。最初に、構成オプション innodb_optimize_fulltext_only=1 を設定します。インデックスの保守期間を妥当な時間に維持するために、検索インデックスで更新するワード数を指定する innodb_ft_num_word_optimize オプションを設定し、検索インデックスが完全に更新されるまで OPTIMIZE TABLE ステートメントのシーケンスを実行します。
  • MyISAM または ARCHIVE テーブルの大きな部分を削除するか、あるいは可変長行を含む MyISAM または ARCHIVE テーブル (VARCHAR、VARBINARY、BLOB、または TEXT カラムを含むテーブル) に多くの変更を行なったあと。削除された行はリンクリスト内に保持され、以降の INSERT 操作は古い行の位置を再利用します。OPTIMIZE TABLE を使用すると、未使用領域を再利用したり、データファイルをデフラグしたりできます。テーブルを大幅に変更したあとは、このステートメントにより、そのテーブルを使用するステートメントのパフォーマンスを (場合によっては大幅に) 向上させることができます。

（中略）

InnoDB の詳細
InnoDB テーブルの場合、OPTIMIZE TABLE は ALTER TABLE ... FORCE にマップされます。これは、インデックス統計を更新し、クラスタ化されたインデックス内の未使用領域を解放するためにテーブルを再構築します。これは、次に示すように、InnoDB テーブルに対して実行したときに OPTIMIZE TABLE の出力に表示されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文

MySQL 5.0以降は、INFORMATION_SCHEMAという便利なシロモノがある。SHOW TABLE STATUSコマンドを利用する代わりに、INFOMATION_SCHEMA.TABLESテーブルを参照することで同様の情報を表示することが可能であり、innodb_file_per_tableオプション利用時は次のように利用することで、全てのテーブルに関する情報を一括表示することが可能だ。

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,DATA_FREE
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ENGINE='innodb';
mysql> SELECT TABLE_SCHEMA,SUM(DATA_FREE)
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ENGINE='innodb'
    -> GROUP BY TABLE_SCHEMA WITH ROLLUP;

SHOWコマンドの出力は加工することは出来ないが、INFORMATION_SCHEMAは好きなようにWHERE句で絞り込みを行ったり集計したりすることができるので便利なのだ。

ところで、Data_freeの数値はInnoDBにおいてどの程度信頼できるのだろうか。

InnoDBのテーブルスペース内では、データは16KBのページ単位で管理されており、さらに64個の連続するページから成るエクステントにグループ化されている。InnoDBテーブルからデータを削除すると、対応するBツリーインデックスが縮小される。これによって、他のテーブルに対して空き領域が再利用できるかどうかは、削除のパターンがテーブルスペースの個々のページやエクステントを解放するかどうかによる。エクステントが解放された場合には、そのエクステントは他のテーブルによって再利用されるが、個々のページやページの一部だけが開放されただけの場合には他のテーブルによって再利用されることはない。つまり、Data_freeは使用されていないエクステントのサイズであり、ページ単位で見ると空き領域がある場合があるということだ。しかしながら、領域を再利用できるかどうかはエクステント全体が空いているかどうかで決まるため、Data_freeはほぼ正確な空き領域を示していると言える。

最後にちょっとだけ内部構造的な話。SHOW TABLE STATUSやINFORMATION_SCHEMAの情報は、最終的にはinnobase/fsp/fsp0fsp.c内のfsp_get_available_space_in_free_extents()関数によって値が取得される。関数の引数はテーブルスペースのIDである。下記は関数の末尾からの抜粋である。

return((ullint)(n_free - reserve)
* FSP_EXTENT_SIZE
* (UNIV_PAGE_SIZE / 1024));

空き領域（n_free）から予約された領域（reserve）を引いたものに、一つのエクステントに含まれるページ数（FSP_EXTENT_SIZE=64）を掛けて、さらにページサイズ（UNIV_PAGE_SIZE/1024=16kb）を掛けたものを返しているというワケである。興味のある人はソースコードを覗いてみよう。

漢(オトコ)のコンピュータ道: InnoDBテーブルスペースの空き領域