mysqldump と repair with keycache

でかいテーブルをdumpしてimportしなおすときに、alter enable keysで "repair with keycache" に悩まされてたんですが、MySQL Forums見てたらそのものズバリなのを見つけたのでメモ。

ご存知の方には当たりまえな感じですが、自分はそもそも repair by sorting と repair with keycache の2通りのメッセージが出し分けられていること自体に気づいてませんでした。

mysqldump

mysqldumpをつかってデータベースを(そのままimportに使える)SQL文に吐き出します。

% mysqldump -uuser -ppass -hhost hoge > hoge.sql

このときhoge.sqlの中身はこんな感じになります。(正確にはもっといろんな情報(set names utf8;とか)がゴチャゴチャ書いてあります。)

--
-- Table structure for table `fuga`
--

DROP TABLE IF EXISTS `fuga`;
CREATE TABLE `fuga` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `val` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=すごいでかい数 DEFAULT CHARSET=ujis;

--
-- Dumping data for table `fuga`
--

LOCK TABLES `fuga` WRITE;
/*!40000 ALTER TABLE `fuga` DISABLE KEYS */;
INSERT INTO `fuga` VALUES (1,10),(2,20),(3,30),(膨大な量のデータ);
/*!40000 ALTER TABLE `fuga` ENABLE KEYS */;
UNLOCK TABLES;

ちなみに /*!40000 ... */ ってのはMySQLのコメントの拡張だかなんかで、 http://dev.mysql.com/doc/refman/5.1/ja/comments.html とかに書いてある通り、たとえば「MySQLのバージョンが4以上ならば ENABLE KEYS を実行する」とかそういう意味になります。

で、1行insertするたびにキーを作り直していても時間かかって仕方ないので、mysqldumpの出力は

  1. lock tables
  2. disable keys
  3. insert, insert, insert, ...
  4. enable keys
  5. unlock tables

と流れるようにSQL文を作ってくれるのでした。

enable keys

さて、mysqldumpの出力に限らず load data (local) infile するときとかにも、手で alter enable keys を打つ場面はあったりします。多くの場合、これはけっこう待たされることになります。なんでかというと、わざわざdisable keysしないといけないくらいデータが大量だから、ですね。

このときshow processlistするとState欄に次のどっちかが表示されていると思います。diable keysでinsert時のキーの更新を止めておいて、repairでまとめてキーを作り直す、ということです。

  • Repair by sorting
  • Repair with keycache

両者の一番大きな違いは、必要な時間です。先に結論を言ってしまうと、自分の場合、keycacheのほうで30時間以上待っても終わらなかったのが、sortingだと1時間で終わりました。

やってることの違いは web のリファレンスマニュアル読んだ限りは次のようなものだと思います。(あくまで「思います」です。なので誰か詳しい仕組み教えてください)

  • sorting
    • インデックスが効いてないときのfilesort状態でindexを作る。(インデックスはいまから作ろうとしてるので当然使えないわけで)
    • indexのキーとポインタを一時的にどっかに保存しておくだけの一時記憶(一次じゃなくて)が必要
  • keycache
    • MyISAM的keycacheを使ってindexをつくるらしい。あんま意味がわかんないです。
    • テーブルを順次読んでいって、keycache作りつつ順次indexを作っていく、ということなのかな??
    • だとすると、enable keysな状態でinsertしてるのと変わらないってこと?
    • c.f. http://dev.mysql.com/doc/refman/5.1/ja/myisam-key-cache.html

両者はdumpの出力をそのまま突っ込む限りは自分で選べるわけではなく、MySQLさんがうまいこと勝手に選んでくれます。そのときの基準が、一時記憶の容量があるかどうか、です。

myisam_max_sort_file_size

その一時記憶のサイズを決めているのがこのパラメータ。Forumのほうで言ってるのは、具体的にいくつにしなきゃいけないかで、すくなくとも出来上がるであろうMYIファイルのサイズ以上ないとダメっぽい、ということのようでした。

まあできたらあるだけ与えてあげれば良さそうなので、いちばん容量に余裕ありそうな/homeとかにtmpディレクトリ作って

% sudo /etc/init.d/mysql restart --myisam_max_sort_file_size=100G --tmpdir=/home/tmp

みたいにしてあげました。

watch show processlist

ちゃんとこの設定がうまく行ってるかwatchしてあげたいですが、ちまちまCtrl-P RETでprocesslistを繰り替えしているのは右手に悪いので、

% watch -n60 '( \
> echo -n "`date +"%Y-%m-%d %H:%M:%S"`     "; \
> echo "show processlist" | mysql -u(ry | grep "^12345      " |cut -f5-\
> ) | tee -a /tmp/watch'

みたいな感じでwatchしてあげました。12345のところは、importしてるスレッドのIDです。

myisam_max_sort_file_sizeを設定しないとき、最初はrepair by sortingで頑張っているんだけど、途中であきらめてrepair with keycacheに移行しているようでした。

mysqldumpの出力を編集

ちなみにmysqldumpの出力はいまの文脈だとそうとう大きくて、数GBくらいのファイルになったりします。「insert前に新規でtrigger仕込みたいんだけど」というときに、viで編集するのも結構ムリだったりしますよね。

こういうときに大活躍なのがsedでして、ENGINE=InnoDBをENGINE=MyISAMに書き換えたい、くらいだったら

#!/bin/sed -f
/^) \(TYPE\|ENGINE\)=InnoDB;/s/InnoDB/MyISAM/
/^  KEY `iranai_index` (`hoge`,`fuga`),/d

なんてやってあげればOKです(ややこしいのは別の機会で)。"/^" で行頭を引っ掛けて上げると、大量のデータにはまることもありません。いったん grep -v ^INSERT hoge.sql > hoge.nodata.sql とかして確認しながらやると、失敗したときのショックが少なくて済むかも。

あと、dumpしたファイルをうかつに分割すると

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

とかでユーザ変数に突っ込んだ値が復旧できなかったりするので、dumpしたファイルはすこしいじるくらいに止めておくといいかもしれないです。

…なんてかんじで、mysqldumpの使い方がすこし分かった気がしました。