August 22, 2014 by Fernando Laudares
先の投稿「MySQLのibdata1ディスクスペースの問題と大きなテーブルについて詳しくみる」で、 共通テーブルスペースと呼ばれるところに属する大きなテーブルを持つという観点から、ibdata1の肥大化問題についてみた。 先の投稿をする動機となった特定のケースでは、サーバー上のディスクスペースが枯渇し、ibdata1ファイルを縮小する方法を探している顧客がいた。 ご存じの通り、そのファイル(または、共有テーブルスペースを構成するibdataファイル一式)は、 innodb_file_per_tableが無効になっているとき全てのInnoDBのテーブルのデータを格納するだけでなく、 UNDOログやデータディクショナリといった他のInnoDBの構造も格納する。
例えば、InnoDBテーブルのトランザクションを処理するとき、「ロールバック」することを後で決めたときの為にMySQLはまず初めにUNDOログに全ての変更を記録する。 長期にわたり、コミットされないトランザクションはibdataファイルの肥大化の1つの要因となる。もちろん、innodb_file_per_tableを無効にし、 全てのInnoDBテーブルがその中に格納されているときである。
どのようのテーブルを共通テーブルスペースの外部に移動し、ストレージエンジンを変えればよいだろうか？ また同様に重要な問題として、どのようにディスク容量の利用率に影響するだろうか？ 先の投稿で示したいくつかの選択肢を調査したところ、次のような発見があったので共有しよう。
実験
バッファプールが1GBであり、インストールした直後のTokuDBをサポートするPercona Server 5.5.37-rel35.1に、 シンプルなInnoDBを共通テーブルスペースに作成した。「datadir」と「tmpdir」に対して50Gのパーティションを設定した。
Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg0-lvFernando1 50G 110M 47G 1% /media/lvFernando1 # datadir /dev/mapper/vg0-lvFernando2 50G 52M 47G 1% /media/lvFernando2 # tmpdir
下記がテーブル構造である。
CREATE TABLE `joinit` ( `i` int(11) NOT NULL AUTO_INCREMENT, `s` varchar(64) DEFAULT NULL, `t` time NOT NULL, `g` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ここに1億3400万レコードを次のルーチンで投入した。
INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 ))); INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; # repeat until you reach your target number of rows
下記のようなテーブルになった。
mysql> show table status from test like 'joinit'G *************************** 1. row *************************** Name: joinit Engine: InnoDB Version: 10 Row_format: Compact Rows: 134217909 Avg_row_length: 72 Data_length: 9783214080 Max_data_length: 0 Index_length: 0 Data_free: 1013972992 Auto_increment: 134872552 Create_time: 2014-07-30 20:42:42 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
ibdata1ファイルは結果として11Gとなり、これをデータディレクトリのパーティション利用率100%とする。 これからテーブルを異なるストレージエンジンに変換し、共通テーブルスペース外に移動したり、圧縮したり、 ダンプしてレストアたときにディスク容量の使用率がどのように変化したかの経験について述べる。 それぞれのコマンドがどのくらい時間がかかったかを計測していなかったので、ほぼ変換後のファイルサイズに焦点を当てた。 また、追加のibdataファイルを加えることで共通テーブルを拡張する方法についても追加で検証した。
MyISAMへの変換
技術的特徴はさておき、MyISAMのテーブルはInnoDBのテーブルよりディスク容量を占有しないことで知られている。 どの程度少なくなるかは、テーブル構造に依存する。ここに最も簡単な変換方法を記す。
mysql> ALTER TABLE test.joinit ENGINE=MYISAM;
下記のファイル(.frmファイルは初めから存在)が生成される。
$ ls -lh /media/lvFernando1/data/test/ total 8.3G -rw-rw---- 1 fernando.laudares admin 8.5K Jul 31 16:21 joinit.frm -rw-rw---- 1 fernando.laudares admin 7.0G Jul 31 16:27 joinit.MYD -rw-rw---- 1 fernando.laudares admin 1.3G Jul 31 16:27 joinit.MYI
MyISAMのファイルは、追加で8.3Gの容量を消費した。
/dev/mapper/vg0-lvFernando1 50G 19G 29G 40% /media/lvFernando1
もっと小さくなることを期待していたが、もちろん結果はテーブルを構成するデータの型に大きく依存する。 問題(または導かれる結果)は最初利用されていたディスクスペースの2倍近くになることだ:
この章で示される他の方法でも発生することだが、共通テーブルスペースの外に対象のテーブルを移動するとき、 ibdata1ファイル内部の利用されなくなった領域をOSに 戻す 通常の/最も安全な方法は、 全データベースを ダンプとレストア する方法である
MyISAMでは別のアプローチもある。ダンプとレストアをせずに、MySQLを再起動しさえすれば良い方法だ。 しかしながら、全てのInnoDBテーブルをMyISQMに変換し、MySQLを停止し、全てのib*ファイル(.ibdファイルが全てのInnoDBテーブルをMyISAMに変換した後残っていてはいけない)を削除し、 MySQLを再起動する。MySQLを再起動したとき、ibdata1は標準の初期サイズ(下記に詳しく記載)で再作成される。 それからMyISAMテーブルをInnoDBに戻し、innodb_file_per_tableが有効になっていれば、テーブルは個別のテーブルスペースファイルに作成される。
テーブルを個別のテーブルスペースにエクスポート
innodb_file_per_tableを有効にすれば、テーブルをibdata1の内部から、個別のテーブルスペース(自身の.ibdファイル)へ ALTER TABLEかOPTIMIZE TABLEを実行することで移動できる。どちらのコマンドも「一時的な」テーブル(MyISAMでなくInnoDB)を、 自身のテーブルスペースファイルの内部のデータディクショナリ(私がそうおもっていたがtmpdirの中ではない)に作成し、 行を目的のテーブルからコピーする。
処理が進行中の間作られる一時テーブル(#sql-4f10_1)をここで示す。
$ ls -lh /media/lvFernando1/data/test total 2.2G -rw-rw---- 1 fernando.laudares admin 8.5K Jul 30 20:42 joinit.frm -rw-rw---- 1 fernando.laudares admin 8.5K Jul 30 23:05 #sql-4f10_1.frm -rw-rw---- 1 fernando.laudares admin 2.2G Jul 30 23:12 #sql-4f10_1.ibd
処理が完了したときの.ibdファイルは下記のようになる。
$ ls -lh /media/lvFernando1/data/test total 9.3G -rw-rw---- 1 fernando.laudares admin 8.5K Jul 30 23:05 joinit.frm -rw-rw---- 1 fernando.laudares admin 9.3G Jul 30 23:35 joinit.ibd
新しいjoint.ibdファイルはおよそ9.3Gである。再度示しておくが、結果として余分にディスク領域を消費している。
/dev/mapper/vg0-lvFernando1 50G 20G 28G 42% /media/lvFernando1
ダンプとレストア: ディスク容量の利用量について
先に述べた通り、ibdata1の利用されなくなったディスク領域をファイルシステムに返す(結果として縮小する)方法は、 データベース全体をテキストファイルにダンプしてレストアして元に戻す方法である。 全てのデータベースを簡単にフルダンプすることからはじめた。
$ mysqldump -S /tmp/mysql_sandbox5537.sock --user=msandbox --password=msandbox --all-databases > dump.sql
下記のファイルが生成される。
-rw-r--r-- 1 fernando.laudares admin 8.1G Jul 31 00:02 dump.sql
次にMySQLを停止し、システムテーブルを(再)作成する為にmysql_install_dbスクリプトを利用して、データディレクトリを全てクリアして (必須ではないが、比較のため実施した。一度全てのInnoDBテーブルをシステムテーブルスペースの外に出したら、関連するInnoDBテーブルの ib*, .ibd, .frmファイルを削除するだけでよい)、MySQLを再度起動し最後にはバックアップを復元した。
$ mysql -S /tmp/mysql_sandbox5537.sock --user=msandbox --password=msandbox < dump.sql
これは下記の結果となる。
/dev/mapper/vg0-lvFernando1 50G 9.4G 38G 21% /media/lvFernando1
そして
-rw-rw---- 1 fernando.laudares admin 18M Jul 31 05:05 /media/lvFernando1/data/ibdata1
ibdata1は処理の中で縮小し(標準*の初期サイズに戻る)、1つのInnoDBテーブルで利用されていたが利用されなくなった数ギガバイトを復元した。
- Percona Serverでの
innobd_data_file_pathの標準値に関してはこのテストでは「ibdata1:10M:autoextend」とした。 しかしながら、マニュアルには「標準では1つの自動拡張するデータファイルを作成し、10MBよりわずかに大きいサイズである。」と記載がある。 もしinnodb_autoextend_incrementが標準で8Mであれば、実際に下記に示す通り18Mで初期化される。
テーブル圧縮
元のシナリオに戻り、テストテーブルが共有テーブルスペースにある状態を考えてみる。innodb_file_per_tableを有効にし、innodb_file_formatをBarracudaとし下記を実行した。
mysql> ALTER TABLE test.joinit ENGINE=INNODB ROW_FORMAT=Compressed;
.ibdファイルは結果として半分程度のサイズとなった。
-rw-rw---- 1 fernando.laudares admin 4.7G Jul 31 13:59 joinit.ibd
処理には時間がかかったが、MyISAMテーブルに変換するよりもはるかにディスク利用量が少なくなった。
/dev/mapper/vg0-lvFernando1 50G 20G 28G 42% /media/lvFernando1
TokuDBへの変換
Percona Server 5.5.37-rel35.1にビルトインのTokuDB 7.1.6(最新版ではない)を利用した。
mysql> ALTER TABLE test.joinit ENGINE=TOKUDB;
通常InnoDBを自身のテーブルスペースに変換する際に、テストデータベースディレクトリの中に一時テーブルがテーブル定義とともに中間処理用に作成される。
$ ls -lh /media/lvFernando1/data/test/ total 24K -rw-r----- 1 fernando.laudares admin 8.5K Jul 31 14:22 joinit.frm -rw-rw---- 1 fernando.laudares admin 8.5K Jul 31 14:28 #sql-7f51_1.frm
TokuDBでは、一時ファイルをメインデータベースディレクトリに作成し、データをその中にコピーする。処理中のある時点では下記のようになる。
-rwxrwx--x 1 fernando.laudares admin 32K Jul 31 14:28 _test_sql_7f51_1_main_a_2_19.tokudb -rwxrwx--x 1 fernando.laudares admin 16K Jul 31 14:28 _test_sql_7f51_1_status_a_1_19.tokudb -rw------- 1 fernando.laudares admin 528M Jul 31 14:29 tokuldNk5W4v
処理が完了すると tokuldNk5W4v は消えてなくなり、_test_sql_7f51_1_main_a_2_19.tokudb ファイルとなる。
-rwxrwx--x 1 fernando.laudares admin 1.1G Jul 31 14:32 _test_sql_7f51_1_main_d_1_19_B_0.tokudb
以前に紹介した他の方法はディスク容量をみたときにほとんど気づかない程度であったが、新しい.tokudbファイルはそれらに比べはるかに小さい。
/dev/mapper/vg0-lvFernando1 50G 12G 36G 25% /media/lvFernando1
面白いことに、データファイルは使っていた一時テーブルの名前/参照を保持しており、それはテーブルファイルの表記(#sql-7f51_1.frm)が消えたとしてもである。 joint.frmだけがテストデータベースディレクトリに残る。
ここで全データベースをダンプリストアした場合に何が起こるかをみてみることにした。
$ mysqldump -S /tmp/mysql_sandbox5537.sock --user=msandbox --password=msandbox --all-databases > dump2.sql $ ls -lh dump2.sql -rw-r--r-- 1 fernando.laudares admin 8.1G Jul 31 15:07 /home/fernando.laudares/dump2.sql
レストアされた後、ibdata1ファイルが初期の標準サイズで復活し、データディレクトリのディスク使用量は1.1Gに切り詰められた。 またまた面白いことに、ダンプリストア操作によりTokuDBテーブルのファイル名が"修正"された。
-rwxrwx--x 1 fernando.laudares admin 980M Jul 31 15:36 _test_joinit_main_21_1_19_B_0.tokudb
既存のInnoDBテーブルをTokuDBに変換する処理は、ROW_FORMAT=Compressedでの圧縮より高速で、かつ容量も小さくなった。 これはTokuDBを使うのが最善の解だ、と主張している訳ではないが、処理に時間がかからず、InnoDBが消費していた余分なディスク容量を活用することができ、 空きスペースがそんなにない場合でも役に立つことは述べておこう。また、テストが非常に大きな(かつ単純な構造の)テーブルで実施されたことも注意していただきたい。 TokuDBに変換できるかどうかと、インデックスがどのように変化するかについても確認する必要があるだろう。
共有テーブルスペースの拡張
先に述べたように、「ibdata1:10M:autoextend」を innodb_data_file_path の標準値としてこのテストで使った。
別のパーティションに未使用で利用可能な領域があり、共有テーブルスペースに大きなテーブルを持つことが気にならず、「ディスク容量枯渇」問題のみ解決すればよいのであれば、
拡張することが出来る。これは、2つ目のibdataをテーブルスペースの定義に追加することにより実現できる。もちろん、既に稼働中のibdata1と別のパーティションに作成した場合のみ機能する。
これをする為には、MySQLを停止して、ibdata1ファイルのサイズを確認する必要がある(ここで示すバイト数のサイズは「ls -lh」で丸められる)。
$ ls -l /media/lvFernando1/data/ibdata1 -rw-r----- 1 fernando.laudares admin 10957619200 Aug 1 16:20 /media/lvFernando1/data/ibdata1
/media/lvFernando2 パーティションに利用可能なスペースがあったので、ibdata2をそこに作成することにした。 これをする為に、my.cnfに下記の2行を追加した。
innodb_data_home_dir = innodb_data_file_path=/media/lvFernando1/data/ibdata1:10957619200;/media/lvFernando2/tmp/ibdata2:10M:autoextend
それからMySQLを再起動しibdata2ファイルが上記の初期化パラメータ通り作成されることを確認した。
$ ls -lh /media/lvFernando2/tmp/ total 10M -rw-rw---- 1 fernando.laudares admin 10M Aug 1 16:30 ibdata2
この方法では3つの重要なポイントがある。
- innodb_data_file_pathに列挙したibdataファイルのうち「autoextend」が設定できるのただ1つだけである - リストの最後の1つ
- innodb_data_file_pathのibdata1を現在のサイズで再定義する必要がある。サイズが異なると動かない。
- ibdata1を現在のサイズで再定義し、ibdata2を「autoextend」で加えた瞬間から、ibdata1のサイズは増えなくなる。共通テーブルスペースはibdata2に増える。
3点目が実際に意味するのは、それに応じて計画する必要があるということだ。まだパーティションにibdata1を格納する利用可能なスペースがあり、 それを最初に使いたい場合は、そのポイントにくるまで変更を遅らせる必要がある。ibdata2を共通テーブルスペースに加えたときから、新たなデータはその中に記録されるようになる。
結論
実験により、以前の投稿で示したInnoDBテーブルを共通テーブルスペースから移動するいくつかの方法が、実際に機能し、さらに最も重要なのは、 処理の最中でどの程度追加でスペースが必要か、を説明する助けとなった。面白かったのは、変換処理の中間プロセスで一時ディレクトリ使うものがなかったことで、 常にメインデータディレクトリに作成された。ここで使ったテストテーブルは10Gを少し超える程度で、1TBサイズのテーブルには到底及ばないので、 データが大きな環境では違う結果となるかもしれない。
補足の情報として、MySQL 5.6はInnoDBテーブルの作成時に、個別のテーブルスペースをデータディレクトリの外部に持たせることが出来 (しかしテーブル定義時に指定する必要があり、ALTER TABLEでは変更できない)、これはトランスポータブルテーブルスペース(個別のテーブルスペースを別のサーバにコピー出来るようにする)と同様である。
