Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
5年以上前投稿 修正あり

innodb_file_per_tableが有効な時にディスク容量を開放するには

MySQL Performance Blogの翻訳。innodb_file_per_tableが有効になっている時、データを削除してもデータファイルのサイズは小さくならない。これを小さくする方法とは。

原文
How to reclaim space in InnoDB when innodb_file_per_table is ON (English)
原文ライセンス
CC BY-NC-SA
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
原著者への翻訳報告
未報告


September 25, 2013 By Nilnandan Joshi

innodb_file_per_tableがOFFの時には、全てのデータはibdataファイルに保存される。テーブルをドロップしたりデータを消したりしたとき、使用されていないディスク領域を解放するには、ダンプしてからリストアするしかない。

innodb_file_per_tableがONの時には、各テーブルのデータとインデックスはそれぞれの表領域ファイルに保存される。しかし、共有の表領域であるibdata1も容量が増えてしまう。これがなぜ起きて、どうやって解決するかはこちらの記事(日本語訳)を見て欲しい。

上に挙げたMiguel Angel Nietoのブログ記事の内容は、Percona Supportでは非常によくある質問なので、これに続いてこの記事ではinnodb_file_per_tableを有効にした時にどうやってディスク容量を確保するかについて書く。また、Percona Toolkitを使って、パフォーマンスや可用性に影響を及ぼさずにこの問題に対処する作業を行うかもお見せしよう。

行を削除する時、その行はディスク上で削除されたとマークされるだけで、実際は後で行をinsert/updateした時に使われるようInnoDBのファイル内では確保されたままになり、容量は減らない。これは非常に古いMySQLのバグだ。

しかし、innodb_file_per_tableを使っているなら、そのテーブルにOPTIMIZE TABLEを実行することで容量を開放することができる。OPTIMIZE TABLEは同じスキーマの空のテーブルを作る。それから1行ずつデータを古いテーブルから新しいテーブルにコピーする。このプロセスの中で、新しい.ibd表領域が作られ、ディスク容量が解放される。

mysql> select count(*) From test;
+----------+
| count(*) |
+----------+
| 3145728 |
+----------+
root@nil:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 168M Sep 5 11:52 test.ibd
mysql> delete from test limit 2000000;
mysql> select count(*) From test;
+----------+
| count(*) |
+----------+
| 1145728 |
+----------+
root@nil:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 168M Sep 5 11:52 test.ibd

2百万レコード削除した後もtest.ibdのサイズが168MBのままなのがわかるだろう。

mysql> optimize table test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+-------------------------------------------------------------------+
| mydb.test | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| mydb.test | optimize | status | OK |
+-----------+----------+----------+-------------------------------------------------------------------+
root@nil:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 68M Sep 5 12:47 test.ibd

OPTIMIZEの後では、領域が解放されていることがわかる。見ての通り、test.ibdのサイズが168MBから68MBに減っている。

ここで言っておきたいのが、このプロセスの間テーブルはロックされてしまうということだ(書き込みのロックのみ)。巨大なテーブルの場合、パフォーマンスに影響が出てしまう。ここで、テーブルをロックしたくない場合、Perconaの誇るユーティリティpt-online-schema-changeを使おう。これで、テーブルをロックせずにALTERできるようになる。ALTER TABLE文にENGINE=INNODBをつけることで、テーブルの再作成とディスク領域の解放が実行される。

(pt-*ユーティリティはいつも最新のものを使おう)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2991456 |
+----------+
mysql> delete from test limit 2000000;
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 991456 |
+----------+
root@nil:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 157M Sep 6 11:52 test.ibd
nilnandan@nil:~$ pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=test --execute
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `mydb`.`test`...
Creating new table...
Created new table mydb._test_new OK.
Altering new table...
Altered `mydb`.`_test_new` OK.
2013-09-06T15:37:46 Creating triggers...
2013-09-06T15:37:47 Created triggers OK.
2013-09-06T15:37:47 Copying approximately 991800 rows...
Copying `mydb`.`test`: 96% 00:01 remain
2013-09-06T15:38:17 Copied rows OK.
2013-09-06T15:38:17 Swapping tables...
2013-09-06T15:38:18 Swapped original and new tables OK.
2013-09-06T15:38:18 Dropping old table...
2013-09-06T15:38:18 Dropped old table `mydb`.`_test_old` OK.
2013-09-06T15:38:18 Dropping triggers...
2013-09-06T15:38:18 Dropped triggers OK.
Successfully altered `mydb`.`test`.
root@nil:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 56M Sep 6 15:38 test.ibd

先の例と同じように、test.ibdのファイルサイズが157MBから56MBに減ったのがわかるだろう。

次の記事
MySQL 5.7のマルチソースレプリケーション
前の記事
(帰ってきた)InnoDBパフォーマンス最適化の基礎

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント