MySQL 5.1 InnoDB が遅い

MySQL

Rubyist Magazine 25号が出ました。Ruby 1.9.1 についての有用な記事が盛りだくさんです。

「Ruby 1.9 で Web アプリを想定したベンチマークをとってみた」は「言語の速度がそのままアプリケーションの速度になるわけではない」ということについて、実際にベンチマークを測定して説明しています。

その中の「データベースを使ったベンチマーク」で使われてたベンチマークプログラムを自分でも試してみました。

測定環境: ThinkPad X61, Ubuntu 8.10, Ruby 1.8.7, MySQL 5.0.67

$ ruby -s blog-bench.rb -N=500
      user     system      total        real
  5.830000   0.640000   6.470000 ( 23.872301)

なぜか記事の結果よりも、Ruby の時間が多く、MySQL の時間が少ないです。

クエリに USE INDEX を追加するオプションをつけてみました。

$ ruby -s blog-bench.rb -N=500 -useindex
      user     system      total        real
  5.390000   0.650000   6.040000 ( 12.675880)

MySQL の使用時間は半分くらいになりました。

試しに MySQL を 5.1.31 にしてみました。…いつまでたっても終わらない…。

N=500 を N=100 にしたら 5.0.67 と同じくらいの時間で終わりました。

$ ruby -s blog-bench.rb -N=100
      user     system      total        real
  1.270000   0.130000   1.400000 ( 24.704081)
$ ruby -s blog-bench.rb -N=100 -useindex
      user     system      total        real
  1.140000   0.110000   1.250000 ( 24.831864)

500 から 100 にしたので、Ruby の時間はちゃんと 1/5 になったのですが、MySQL の時間は変わりません。つまり 5.0.67 と比べて 5倍くらい時間がかかってることになります。また、-useindex をつけても変化ありませんでした。

どうやら使用されているインデックスが異なるためのようです。

5.0.67:

mysql> explain select * from blog_comments where entry_id in (4994,4984,4974,4964,4954,4944,4934,4924,4914,4904) order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_comments
         type: range
possible_keys: blog_comments_entry_id
          key: blog_comments_entry_id
      key_len: 4
          ref: NULL
         rows: 20
        Extra: Using where; Using filesort

5.1.31:

mysql> explain select * from blog_comments where entry_id in (4994,4984,4974,4964,4954,4944,4934,4924,4914,4904) order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_comments
         type: index
possible_keys: blog_comments_entry_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10123
        Extra: Using where

これだと1万行もあるテーブルから10個のキーのレコードを抜き出すのにフルスキャンしてそうな…。

-useindex 時に、強制的に blog_comments_entry_id を使用するように変更してみたら、5.0.67 とほぼ同じになりました。

$ ruby -s blog-bench.rb -N=500 -useindex
      user     system      total        real
  5.510000   0.620000   6.130000 ( 12.852172)

ちなみに blog_comments を InnoDB から MyISAM に変更してみたら -useindex つけなくても速いです。

$ ruby -s blog-bench.rb -N=500
      user     system      total        real
  6.490000   0.710000   7.200000 ( 12.015570)

MySQL のオプティマイザの仕様変更なのかバグなのか InnoDB 特有の罠なのかはわかりませんが、データベースのチューニングは一筋縄ではいきませんな…。