先日大きめ(といっても500万行くらい)のテーブルにインデックス付きのカラムを追加しようとして痛い目にあったので調査。
大きめのテーブルにカラムやインデックスを追加するとどうなるか
今回は単純に、「ALTER TABLE 〜 」で追加しようとしました。追加するカラムは3つで、
- varchar(255)
- インデックスなし
- varchar(255)
- ↓のdate 型カラムとマルチカラムインデックスの形式のユニークインデックスあり
- date
- インデックスあり
SQL を実行し、状況を「SHOW PROCESSLIST」で監視していたら、1つ目のカラム追加で次のような状態に…
- 最初にState が「copy to tmp table」状態になり、次の状態に遷移するまで1時間かかる
- 次にState が「Repair with keycache」状態になり、完了までに1時間かかる
- 次のカラム追加に対する「copy to tmp table」が開始される
もう超絶的に遅いです。まぁ、1時間とかは序の口のようで、7時間かかっても終わらないこともあるらしい
たかだか700万行、 mysqldump して 150MB 弱のデータ量しかないテーブルに一つユニークインデックスを貼るだけで7時間半かかってまだ終わらないってどういうことよ。この大きさになるともう ALTER TABLE では無理だってことか・・・。
ただ、今回の場合監視していて不思議だったのが、top でmysql の状態を見ると全然仕事をしていなかったことです。CPU 使用率も2%とかで、おい!って感じ。
解決策
結論から言うと、「key_buffer_size」の不足でした…。「repair with keycache」を実行する際にも、MyISAM のインデックスのキャッシュ機構が使われるわけで、このサイズが足りなかったわけです。
インデックスブロックには、key cache (あるいはkey buffer)という特別な構造が保持されています。その構造には最も頻繁に使用されるインデックスがおかれた多数のブロックバッファが含まれます。
…
キーキャッシュのサイズを制限するには、key_buffer_sizeシステム変数を使用します。
実際に追加している最中に確認したら驚きの少なさ!
mysql> show variables LIKE '%key_buffer%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | key_buffer_size | 8384512 | +-----------------+-----------+
なんとデフォルトのサイズ。MyISAM 使っていて、「key_buffer_size」の値を確認していないとはどこの小学生だよって感じです…( ﾟДﾟ)
というわけで、直ぐに変更。マシンのメモリが1G だったので1/4 の256M に設定。クエリ実行中だったのでSET 構文使う。
SET GLOBAL key_buffer_size=268435456;
これを設定したら、mysqld が仕事し始めました。CPU も7~9割使っているし、メモリも割りあてた分をフルに使っている模様。さっきまでは使用できるメモリが小さすぎてディスクを使っていたのでIO 待ちが長くCPU を有効に使えていなかったのだと思います。
結果、最初のカラム追加に2時間かかったのに対し、15分ほどで追加できるようになりました。これで、無事カラム追加を完了させることができました。
最後に、SET 構文で設定した値はmysqld を再起動すると無効になってしまうので設定ファイルで値を変更して完了。
もう少し調べてみる
せっかくなので、テーブルへのカラム追加に関してもう少し調べてみます。
copy to tmp table
まず、カラム追加時に最初に行なわれる一時テーブルへのコピーについて。これは、「ALTER TABLE」を実行すると確実に行なわれる動作のようです。また、実行中対象テーブルは読み取り可、書き込み不可の状態になります。
ALTER TABLE の処理では、元のテーブルの一時的なコピーが作成されます。 変更はこのコピーに対して実行されます。その後元のテーブルが削除され、新しいテーブルの名前が変更されます。この変更処理は、すべての更新が、エラーになることなく、確実に新しいテーブルに自動でリダイレクトされるように実行されます。ALTER TABLE の実行中、元のテーブルは他のクライアントによって読み取り可能です。このテーブルの更新とテーブルへの書き込みは、新しいテーブルの準備が整うまで停止されます。
repair with keycache
この動作は、「LOAD DATA INFILE」とかで大量のデータをinsert する処理を高速に行いたい場合に使われる手法と同じです。insert 毎にインデックスの更新を行なうと効率が悪いので、「ALTER TABLE ... DISABLE KEYS」でインデックスの更新を行わないようにし、データを挿入。その後、「ALTER TABLE ... ENABLE KEYS」でインデックスを再構築します。
ALTER TABLE を実行する場合はこの動作が自動で行なわれ、インデックス構築の処理を高速化するとあります。
MyISAM テーブルに対して ALTER TABLE を使用すると、非ユニークなインデックスのすべてが別のバッチに作成される（REPAIR の場合と同様）。 インデックスが数多くある場合は、これによって ALTER TABLE の処理がはるかに迅速化される。
ただ、この「repair with keycache」には問題があるようです。
repair with keycache ではなく、repair by sorting を使うようにする
インデックスの再構築処理には「repair with keycache」を使う方法と、「repair by sorting」を使う方法があるようです。
ALTER TABLE ... ENABLE KEYSステートメントをMyISAMテーブルに対して実行する場合やインデックスの修復を行う際、MyISAMは以下の2通りのうちいずれかの方法によりインデックスの修復を試みる。
- Repair by sorting
- Repair with keycache
名前からすると後者の方が速そうだが、実は前者の方が遙かに速い。
Repair with keycache 修復コードが、キーキャッシュにより、キーを1つずつ作成している。これは、Repair by sorting よりも大幅に時間がかかる。
インデックスの再構築を行なっている時に、「repair with keycache」が使われるのはよくないようです。高速な「repair by filesort」を使うようにするには、パラメータの設定が必要。適切なパラメータが設定されていないと、MySQL の判断によって「repair with keycache」が使われるようです。
名前からすると後者(Repair with keycache )の方が速そうだが、実は前者(Repair by sorting)の方が遙かに速い。ただしこの手法を用いるにはソート用のバッファが確保されている必要があるのだが、これが実にやっかいである。バッファのサイズがとてつもなく大きいのだ。バッファはmyisam_max_sort_file_sizeにて指定するが、必要な分だけ確保するようになっているので100Gぐらいにしておいて差し支えないだろう。ファイルシステムがいっぱいになったり、ここに指定した値を超えてバッファが必要な場合には、Repair with keycacheに自動的に切り替わる。
…
myisam_max_sort_file_sizeだけでなく、myisam_sort_buffer_sizeを増やしておくとメモリ上でバッファリングが行われるので、Repair by sortingがさらに高速になる。REPAIR TABLEやLOAD DATAで非常に大きなテーブルを扱う場合、myisam_sort_buffer_sizeを増やしておくと良いだろう。
100G くらい割りあてるといいと書いてあるけど、パーティションとかの関係で/tmp にそんなに容量を割りあてていない場合結局「repair with keycache」が使われるので、「tmpdir」の値も変更してあげる必要があります。例えば、「/home」の容量に余裕があれば以下のような感じ。
$ mkdir /home/luke/tmp
$ sudo /etc/init.d/mysql restart --myisam_max_sort_file_size=100G --tmpdir=/home/luke/tmp
これで、カラムやインデックスの追加を高速に行なうことができるようになります。
なお、「myisam_max_sort_file_size」や「myisam_sort_buffer_size」は今回のようなインデックスの作成時にしか使用されないバッファなので、通常は大きく割りあてておく必要はなさそうです。
同事例があった…
あとからリファレンスを見たら、コメントの部分に同じようなことが普通に書いてありました。しかも、最初の「key_buffer_size」の問題の部分も同じとか…orz
In conclusion key_buffer_size, myisam_sort_buffer_size and sort_buffer_size can all be set at runtime. If you want to optimize a REPAIR TABLE to complete ASAP you should set these variables to correct values and monitor iostat or other platform specific CPU and disk performance metrics to determine feedback.
さらに処理を速くするには
キーやカラム追加をこれ以上速くしたい場合は、ALTER TABLE 以外の方法を使うというのもあるようです。copy to tmp table - 刺身☆ブーメランのはてなダイアリーの例だと、
- RENAME TABLE したあと元の名前でインデックスありのテーブルを作り直し、 INSERT INTO new_table SELECT * FROM old_table
- mysqldump して CREATE TABLE にインデックスたして全部ロードしなおす
という2種類の方法が挙げられています。ただ、ベンチの結果とかは出されていないので、自分で検証する必要がありそうです。
