Check

今運用している某システム、MyISAMのある難癖によって非常に辛い目に遭っております。それはもちろんテーブルロック。これはもうMyISAMを選択した時点でどうしても避けては通れない道。そこんところ仕組みをちゃんと理解してないと、MyISAMのほうが速いって言うから選んだのに、なんだよ全然遅いじゃん！っていうか使い物にならないよウワァァァァンみたいなことになりがち。今のシステムでどうやってMyISAMかInnoDBかを分けたのかというと、単純に更新頻度だった。頻繁に更新されるテーブルはInnoDB、そうじゃなければMyISAM。参照が殆どならInnoDBを選択するメリットは何もないと思っていた。が、実は全然そうじゃなかった。

MyISAMが辛いのは、INSERT文実行時にテーブルロックされることもそうだが、時間のかかるSELECT文を実行したときにREADロックされることなんじゃなかろうか。特に業務系のシステムを構築する場合、何かと複雑なSQLを発行せざるを得ない場合がある。それはもちろんテーブル設計を行った者の技量不足や、プログラマの技量不足が原因であることが多いのだが、不可避な理由（例えばクソくだらない出来損ないのコーディング規約など）によってそうせざるを得ない場合も、悲しいかな下っ端風情の一プログラマには良くあることだ。そこを正していくのが本筋だけども、そこはオトナの事情というかまぁ色々あるので以下略（もちろん技量不足が理由の大半なんだろうけども）。

この場合レプリケーションしていたとしても、悲惨な結末を辿る。例えばマスタ1台に対してスレーブを3台用意していたとしよう。普通に考えるとスレーブへの接続はラウンドロビンで順番に割り振ってやるわけだが、あるSQLによって1台のスレーブが長時間テーブルロックするとシステム全体に影響が及んでしまう。アクセス数が多ければ多いほど（というか発行するSELECT文が多ければ多いほど）、ある時間のかかるSQLによってどっかがテーブルロック→ロックしてないスレーブではすぐに結果を返す→単純なラウンドロビンなのでロックしてるスレーブにすぐに順番が回ってくる→ひたすら実行待ちSQLがキューに入る→コネクション使い切る。かと言ってロックを監視して、それによってどのスレーブに接続するかを選択するってのはオーバーヘッドが大きすぎる気が。ロックによる待ち時間よりは全然ましかも知れないけど…

と言うわけで、InnoDBにするかMyISAMにするかは、単純に更新頻度で考えてはいけないことになる。例え参照が大半を占めようとも、ある程度複雑で時間のかかるSQLを投げることが避けられないのならInnoDBにするべきだ。で、InnoDBとMyISAMと共存してるけど、特にInnoDBだからどうこうということはないように思うです。バックアップに関してはレプリケーションしてれば特に問題ないと思うし、気になるとすれば速度とディスク容量でしょうか。結構InnoDBは容量食うので注意が必要。速度に関してはSQLによるとしか言いようがないので、実際に試してみる以外ないかと。naoyaさんが言っているような「運用が面倒臭いのは萎える」のは例えばどのようなことを言ってるのかわかりかねますが…俺の中のイメージでは定期的にoptimizeしてあげたりしなくちゃいけない分、MyISAMのほうが運用は面倒なのではと思ったりします。

ちなみにMyISAM の「速い」というのがどう速いのか、という疑問についてはインデックスが絡んでいるのでは?MyISAMは文字列のキーに対してプリフィックス圧縮を行うのに対してInnoDBでは単なるB-Tree。また、InnoDBではINSERTやUPDATEのたびにインデックスの統計情報が最適化されるが、MyISAMではoptimize tableしない限り最適化しない。あとMyISAMはMVCCじゃないので、COUNTがインデックス見ただけで分かるので速い。それ以上はソース読んでないのでわかりませぬ…ちゃんと読んでみようと思いながら怠けてます。

結局のところ俺の中では、単純なSELECT文しか発行しない、かつDELETEやUPDATEが極端に少ないテーブルならMyISAM、それ以外はInnoDB、という結論で落ち着いています。あくまで4.1系の話ですが…5系はInnoDBが極端に遅いという話も聞く（検証はしてない）のでよくわからず。でも5.1からNDB Clusterがオンメンモリじゃなくても動くようになるらしいので、それについては動向をウォッチして行きたい所存。

Check

MySQLのMyISAM型テーブルは、レコードの追加・削除を繰り返しているとどんどん効率が悪くなっていく。そこで定期的にoptimize tableを発行して統計情報の更新や、インデックスページのソートを行ってやることでテーブルの状態を最適化することが出来る。のだが…そこで思いっきり嵌った。

全部のテーブルをoptimizeして、今までslow-logに出ていたあるSQLを実行すると7秒かかっていた処理が1秒かからないまでに実行時間が短縮されたため、効果が出たと喜んでいたのだが。しばらく経って普通にシステムを使ってみると、時折異様に処理が遅いことがある。いままでDBの構成はMaster1+Slave1の２台構成だったのだが、メンテナンスでSlaveを2台増やした。どうも増設したSlaveにアクセスすると重いことがわかった。既存のSlaveと増設Slaveでの違いは、optimizeしたかどうか。optimizeしたSlaveのほうが、ある特定のSQLで200倍も遅くなっていることが判明した…200倍って！

問題になったSQLは、outer joinやunionを使いまくった、一目見ただけでは何をしているのかサッパリわからないほど複雑なSQLだった。保守性を考えれば、そういう複雑なSQLは使わずに、プログラム側でループを使うなりハッシュを使うなりしてわかりやすくするべきだ。しかし今回はあくまで処理速度重視というかアプリサーバとDBサーバ間の通信を極力減らす必要があったため、複雑なSQL1発で済ませなければならない事情があった。

最適化していないDBでは、explainしてみるときちんとインデックスが使われているのに対し、最適化したほうのDBではkeyがnullになっており、あるテーブルがフルスキャンされていた。そのフルスキャンされているテーブルはレコードが100万近く入っており、そりゃ時間かかるわなって話。2000秒以上テーブルロックして（MyISAM型は行ロックではなくテーブルロックがかかる…その間readもwriteも不可）、そのせいでアプリサーバもウェブサーバもコネクション使い切って死ぬ。

結局その問題はforce indexを使うことで無理矢理解決。あるいはstraight joinを使うことでも同様の効果が得られた。たまたま同じデータで最適化したものとしてないものがあったので、explainでどのindexを使えば速くなるのかすぐわかったので助かった…

実際のところforce joinよりもstraight joinのほうが、optimizerが順序を考えなくて済む分若干速いのかな？その辺はちゃんと調べる余裕がなかったので謎。まぁ複雑なクエリを投げるときはoptimizerに頼っちゃダメってことですな…