週に一度か二度、Bacteriaが管理するサイトは大規模なプロモーションをすることがある。
140万通ものメール広告でもってやるわけだけど、そのレスポンスが強烈に重い。
サーバが悲鳴を上げているのがわかる。
ちょっと重すぎるよこれどうなってんのってことで、MySQLのslow_queryで分析をはじめることにした。
すると一番最初に引っかかったのが下記の事例だった。
MySQLにクエリを投げる場合、Explainして最も見たくないのがUsing file sortの文字だ。
file sortは負荷がかかると、Using Temporaryまで出てき始める。
こうなるともうお手上げで、クエリは結果を表示するためにディスクアクセスを繰り返し、速度は低下の一途をたどることになる。
file sortが発生するときは、たいてい決まっているがorder byを使うときだろう。
そりゃそうだ、order by するからsortするわけだ、超そのまま!
だからorder byに使用するキーにインデックスを設定してやればいいじゃん。
とまあこんな感じでガシガシインデックスを貼ってみたが、全然改善しない。
何だよおい頼むよ俺が何したって言うんだよ、と、いろいろ調べると、
MySQL による ORDER BY の最適化 http://dev.mysql.com/doc/refman/4.1/ja/order-by-optimisation.html
MySQL で ORDER BY の解決にインデックスを使用できない場合は以下のとおりです
複数のキーに対して ORDER BY を実行する場合。
SELECT * FROM t1 ORDER BY key1,key2
連続しないキー部分に対して ORDER BY を実行する場合。
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
ASC と DESC が混在している場合。
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
レコードの取り出しに使用されるキーが ORDER BY の実行に使用されるキーと異なる場合。
SELECT * FROM t1 WHERE key2=constant ORDER BY key1
ORDER BY で多くのテーブルとカラムを結合していて、それら全てがレコードの取り出しに使用される最初の非 const テーブルではない場合(これは EXPLAIN で出力される最初のテーブルで、かつ、const メソッドを使用していないテーブル)。
ORDER BY と GROUP BY 式が異なる場合。
使用されたテーブルインデックスが、並び順にレコードを格納していないインデックスタイプの場合(HEAP テーブルの HASH インデックスなど)。
ひどい。結構使用できないパターンがある。
今回引っかかったのは
・複数のキーに対して ORDER BY を実行する場合。
・連続しないキー部分に対して ORDER BY を実行する場合。
この二つだった。
こういった場合、MySQLでの解決策は以下の2点しかない。
・テーブルの非正規化
・スナップショットテーブルの作成
テーブルの非正規化については別にもうやりたい放題してくれればよいのだ。
ログテーブルを作るでも何でも、好きにしてくれって感じ。
スナップショットは、リアルタイム性が1時間をきらないところで許容できるなら、Order byした順にスナップショットテーブルでPKをはり、あとはcronでtruncateとインサートを繰り返すのみである。
通常selectしたときはPK順にレコードは取り出されるので、ふつーにselectしてくれればよい。
これでも解決できなければそれはもう要求にMySQLが見合ってないか、ただのアプリケーション設計ミスだろう。
健闘を祈る。
posted by Bacteria : 2005年10月29日 23:14 : サーバ管理
このエントリーのトラックバックURL: