2010-04-16

MySQLを使ってみる

無題

お仕事でちょっとMySQLをやってみているのだが、データ量が
数千万件。サーバー機はメモリ1GBのCentOSという酷い有様。
設定もデフォルトのままだったためか、最初のうちはキーワードで
IN句とかGroup Byとか使った日には一晩経っても返って来ない。
そんな酷い状況だったのでちょっと頑張ってみる。

[対策その1]Indexを張ってみる

Oracle厨的にはIndexだろとか意味の分からない理屈でIndexを張る。
Indexも構築だけで16時間とかマジかよって時間。
CPUもメモリもゴリゴリ動いてたんでマジなんだろうな(；´Д｀)
1つIndexを作った後にmy.confを書き換えてkey_buffer_sizeを
デフォルトの8K→256Mに増やすとか色々したけど、あんまり
解決したとは言い難く。
ただ、単一クエリのレスポンスは2分→0.2秒と劇的に改善。
Indexさんパネェっす。

[対策その2]設定を変えまくる

単一クエリの速度は上昇したものの、Index項目でも複数の
条件をorで括るとレスポンスが一気に悪くなる。
1件は早いのになあ。うーん。
と言う事はサーバー内部で結果をまとめているのに時間が
掛かってるんじゃないだろうか。というところに着目して
sort_buffer_sizeやらread_rnd_buffer_sizeを派手に
割り当ててみる。途中、派手に割り当てすぎたらSQL投げた
瞬間にout of memory起こしたのは内緒だ。
特に書籍を呼んだわけでもなく、チューニングに詳しい訳でも
ないので適正値が今ひとつ分からなかったが256M、128Mとか
それっぽい値でやってみる。そしたらようやく副問い合わせで
キーになるレコードが5000件くらいになるクエリでレスポンスが
1時間半で得られるとかまでどうにか改善。
ちゃんとチューニングかければもうちょい行けそうな気がしない
こともないかなあ。でも、やっぱり単一クエリで0.2秒の
レスポンスがある手前、上記はちょっとないなあとか。
多分、取れるデータ量が大きすぎて内部で検索結果を
まとめるのにメモリ内で処理しきれずにスワップでも発生して
それが元で遅延してるんだろうなあと予想。
ここはサーバー資源の関係上、致し方なし。

[対策その3]視点を変えてみる

ソート領域が不足してるとかだったら単一データ単位でデータ
抜き出して別テーブルに必要データまとめればいいじゃない、
とか頭をよぎったのでプログラムをちゃちゃっと組んで、
副問い合わせで呼んでるキーレコードをフェッチしつつ
本体からInsert Selectで別テーブルに流し込みまくる暴挙に
走ってみる。
そしたらまあ、早い早い。30秒で8万レコード近くがガスガスと
入ってくれる。そのお陰でなんとか要件達成。

久々に限定された資源の環境で諸々挑戦してみたが如何にして
活かせる部分を見つけ出してそれを最大限に利用できるか
ってのが試されてるようでちょっと面白かった。
依頼主は数日間サーバー動かずデータ抜けずでかなりもう
ヤキモキしておられたのは申し訳ないと思っている。うん。