このサイトがかなり遅くなっていたので治した

遅い問題

このサイトはHtmlCacheを用いて、一度表示したページをhtmlの静的ファイルにしているので速いといえば速いが、一度も表示させていないページを表示させようとすると遅くなりがちだった。

遅い原因

ボトルネックになっているのはデータベースであることが多いので、とりあえず遅いSQLをみてみることにした。ついうっかりスロークエリログを設置していなかったので、ローカルの開発環境でCakePHPのクエリログをみてみることにした。

そうすると、とあるSQLが恐ろしく遅い。開発環境だからというのもあるけど2000msとかかかる。

なんでこんなに遅いのかなと思ったので、EXPLAINを使って調べてみよう。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Tmcmaker
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 692
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: MetaContent
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1075
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: MetaKeyword
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1075
        Extra:
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: AliasURL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1075
        Extra:
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: Tmctag
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
        Extra:

rowsの列をみてみると、多分ほぼ全部の行を読み込んでいることがわかるし、typeもALLになっている。これだけ大量のデータを取り出したり、ましてやソートしたりすれば、それだけ時間がかかるのが納得できる。

改善策

つまり、これはインデックスが適切に貼られていない。WHEREで使用している列にインデックスを貼ると解決できるのでは。

インデックスを貼った後で、改めて画面をみてみると1msで処理できてる。EXPLAINをみてみる。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Tmcmaker
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 692
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: MetaContent
         type: ref
possible_keys: model,foreign_key
          key: foreign_key
      key_len: 5
          ref: tekitoh.Tmcmaker.id
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: MetaKeyword
         type: ref
possible_keys: model,foreign_key
          key: foreign_key
      key_len: 5
          ref: tekitoh.Tmcmaker.id
         rows: 1
        Extra:
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: AliasURL
         type: ref
possible_keys: model,foreign_key
          key: foreign_key
      key_len: 5
          ref: tekitoh.Tmcmaker.id
         rows: 1
        Extra:
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: Tmctag
         type: ref
possible_keys: tag
          key: tag
      key_len: 768
          ref: tekitoh.Tmcmaker.name
         rows: 7
        Extra:

一番上のTmcmakerというtableだけは692だけど、ほかはインデックスによって処理される行が格段に少なくなった。typeもみてみるとrefとなっている。これはUNIQUEかPRIMARYでないインデックスを使って等価の値を検索するときのインデックスとのこと。

これを本番にも当てはめると、かなり速くなった。めでたし？

スロークエリログ

これを忘れてた。というか、そんなにデータが集まることはないだろうと高をくくってたので、スロークエリログを貼ろう。MySQLは5.1 Debian/GNU Linux squeezeのもの。

my.cnfから、log_slow_queriesとlong_query_timeのコメントを削除。

log_slow_queries

スロークエリログを有効化。ファイル名を指定する。しない場合はhost_name-slow.logという名前になるらしい。

long_query_time

単位は秒。ここで指定した数値よりも遅いとlog_slow_queriesで指定されたファイルに記録される。

まとめ

CakePHPを使っている場合、アソシエーションに使用している列はインデックスをつくった方がいいかもしれない

参考

• http://dev.mysql.com/doc/refman/5.1/ja/mysql-indexes.html
• http://dev.mysql.com/doc/refman/5.1/ja/slow-query-log.html
• http://nippondanji.blogspot.jp/2009/01/mysql-51.html

2014/05/27 12:22