« Hibernateを使用した ― O/Rマッピングは漢(オトコ)の浪漫 | トップページ | CPU切替器経由で接続するとマウスのサイドボタンを認識しなくなってしまった »

2011/05/14

JOINが遅すぎて泣ける ― MySQLは漢(オトコ)のコンピュータ道?

はじめての永続化、全2話中第2話、最終回です。第1話はこちら。サブタイトルに意味は無いです。

下のような関連を持つありがち、とはいかないまでも珍しくは無いクラス群をクエリしようと思いました。(クリックで拡大)

mysql_uml

Earthにいくつかのマスタデータがくっついているようなイメージです。ちなみにクラス名は適当につけているので意味はありません。

今回記載しているのはMySQL5.5です。MySQL5.1でも試しましたが、パフォーマンスは同程度でした(ただし後で述べますがEXPLAINの結果は若干変わっていました)。その他テストに使用したものは以下の通りです。

クエリはこんな感じです。これを実行すると、上で添付したSQLと、select-joinのクエリがいくつか走ります。

   1: Criteria c = session.createCriteria(Earth.class);
   2: List<Earth> res = c.list();

結果は6件得られます。

問題は実行にかかる時間なのですが、この程度のデータ数で1分程度かかってしまいました。上で添付しているメインのSQLをEXPLAINした結果の抜粋は下記の通りです(結果の完全なものは上に添付しています)。IDと他の関連先テーブルのIDから成る外部キーしか持っていないので当然ですが、”key”は全てPRIMARYになっています。

   1: +----+-------------+-----------------+--------+---------+--------------------------------+
   2: | id | select_type | table           | type   | key     | Extra                          |
   3: +----+-------------+-----------------+--------+---------+--------------------------------+
   4: |  1 | SIMPLE      | beta2_          | index  | PRIMARY | Using index                    |
   5: |  1 | SIMPLE      | beta4_          | index  | PRIMARY | Using index; Using join buffer |
   6: |  1 | SIMPLE      | beta2_1_        | eq_ref | PRIMARY |                                |
   7: |  1 | SIMPLE      | beta4_1_        | eq_ref | PRIMARY |                                |
   8: |  1 | SIMPLE      | this_           | ALL    | NULL    | Using where; Using join buffer |
   9: |  1 | SIMPLE      | shot15_         | eq_ref | PRIMARY |                                |
  10: |  1 | SIMPLE      | block5_         | eq_ref | PRIMARY | Using index                    |
  11: |  1 | SIMPLE      | block5_1_       | eq_ref | PRIMARY |                                |
  12: |  1 | SIMPLE      | departure6_     | eq_ref | PRIMARY | Using index                    |
  13: |  1 | SIMPLE      | direct7_        | eq_ref | PRIMARY |                                |
  14: |  1 | SIMPLE      | problem8_       | eq_ref | PRIMARY |                                |
  15: |  1 | SIMPLE      | problem9_       | eq_ref | PRIMARY |                                |
  16: |  1 | SIMPLE      | arm10_          | eq_ref | PRIMARY |                                |
  17: |  1 | SIMPLE      | computer11_     | eq_ref | PRIMARY |                                |
  18: |  1 | SIMPLE      | curry12_        | eq_ref | PRIMARY |                                |
  19: |  1 | SIMPLE      | exhibition13_   | eq_ref | PRIMARY | Using index                    |
  20: |  1 | SIMPLE      | exhibition13_1_ | eq_ref | PRIMARY |                                |
  21: |  1 | SIMPLE      | radio14_        | eq_ref | PRIMARY |                                |
  22: |  1 | SIMPLE      | piano3_         | eq_ref | PRIMARY |                                |
  23: |  1 | SIMPLE      | piano3_1_       | eq_ref | PRIMARY | Using index                    |
  24: |  1 | SIMPLE      | piano3_2_       | eq_ref | PRIMARY | Using index                    |
  25: |  1 | SIMPLE      | departure6_1_   | eq_ref | PRIMARY |                                |
  26: +----+-------------+-----------------+--------+---------+--------------------------------+
  27: 22 rows in set (56.69 sec)

にも関わらず、遅い。たったこれだけのデータ量に対してクエリするだけで私の環境で1分程度かかります。

PROFILINGの結果を見ると、statisticsという部分で時間のほとんどが費やされています。ソースコードをチラ見してみると下のようなコメントがあり、やはりJOINに関わるところなんだろうな、と推測できます。

   1: /* Calculate how to do the join */
   2: thd_proc_info(thd, "statistics");
   3: if (make_join_statistics(this, select_lex->leaf_tables, conds, &keyuse) ||
   4:     thd->is_fatal_error)
   5: {

コンフィグをデフォルトのまま使っているのが問題なのでは、と、書籍を参照したりwebを検索したりして目ぼしい設定を変更してみたのですが改善されず。というか、私の感じたところ、コンフィグ設定というのは(RDBMSにとって潤沢とは言えない)メモリ資源をどこに分配するのかを決定するものなのかなと。一方、今回の件はタスクマネージャーを見てる限りmysqld.exeが25%に張り付いており(4コアなので)、メモリではなくCPUバウンドな問題のように思われます。従ってあまりコンフィグ設定は結果に影響を与えるものではないと結論付け、諦めました。

上のSQL文から、Shotテーブルに属すカラムを削除し、それに伴って不要となったShotテーブルのJOINも削除することで、大分速度は改善されました。それでも6秒程度はかかっていますが。

同じデータに対して同じクエリをPostgreSQL9.0とOracle10g XEで試したところ、どちらも即答でした。

素人考えだと、同じ結果を得られるのであればSELECT文は少ない方が速い、と考えていたのですが(少なくともMySQLにおいては)そうではない状況もあるのだと気付かされたのも新鮮でした。

実際のエンティティクラスはもちろん他の属性も持っていますし関連もこれより多く、更にMySQLが苦手であるということで有名な相関サブクエリなんかも行っていますので私はPostgreSQLに逃げることにしました(あとMySQLではJOINの最大数が61というのも心許ない)が、もしMySQLを使用するのであれば、JOINを控えめにした方が良さそうです(Lazy Fetchにするとか、Select-Fetchにするとか、何らかの対価で回避できる状況はあると思います)。ただし、前回記載したような強制的にJoin-Fetchになるような場合もある(HHH-3538)のでFetch戦略を自分で制御できない部分があるかどうかは注意して見ておくべきでしょう(自分は一回目のクエリはIDにProjectionして2回目のクエリでそのIDをキーに引っ張ってくる、という自力Select-Fetchみたいなこともやってました)。

本筋から外れますが最後に。上で示したEXPLAIN結果の5行目Extra列ではUsing indexとUsing join bufferが同時に表れています。他の方が書かれたMySQLパフォーマンスチューニングに関するいくつかのblogエントリでは『join bufferが使用されるのはindexが用いられない場合のみであり、コンフィグのjoin_buffer_sizeを増加させるよりindexを利用できるクエリに書き換えることを検討すべきだ』とあるのを見かけました。おそらくMySQL5.1の時代かそれ以前に書かれたものだとは思います(今回試した結果でも、確かに5.1ではjoin bufferは使われていなかったように記憶しています)。MySQLを使用しないことに決めたので詳しくは調べていないのですが、この差異はMySQL5.5(正確には5.4?)で導入されたBatched Key Accessから来ているのではないかと思います。ですので、必ずしも昔書かれたチューニングが現在でも適用可能であるとは限らないので、自身でもちゃんと調べた方が良いのかな、と思いました。

 

[追記]

これを書いた後に確認のため、と再実行したときに驚愕の事実が発覚しました。mysqldumpしたファイルを読み込ませた直後にクエリを実行すると確かに上のような結果になるのですが、リストア後に一度MySQLをrestartしてから同じクエリを実行すると全然スピードが違いました。リストア直後は前述の通り1分弱かかるのですが、再起動後だとなんと4秒!(ただしこれでも他のRDBMSよりは遅いです)

実際のデータで検証してたときは何度もMySQLの再起動は行ったはずで、そのときはやっぱり遅かったのでクエリがスローダウンするJOIN数の閾値が変わってるだけなんでしょうかね…(前述の通り、restartしない場合でもJOINを一つ減らすだけで高速化しましたし)。でも検証の後半で行っていたDbUnitでデータ突っ込んでクエリして、という自動化した検証の結果は間違っている可能性もあるのか…うーん。

« Hibernateを使用した ― O/Rマッピングは漢(オトコ)の浪漫 | トップページ | CPU切替器経由で接続するとマウスのサイドボタンを認識しなくなってしまった »

コメント

コメントを書く

(ウェブ上には掲載しません)

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/18902/51665434

この記事へのトラックバック一覧です: JOINが遅すぎて泣ける ― MySQLは漢(オトコ)のコンピュータ道?:

» OracleのJOINも遅い [雪羽の発火後忘失]
JOINが遅すぎて泣ける ― MySQLは漢(オトコ)のコンピュータ道? でMySQLのJOINでparse時間が極端に長くなる例を上げましたが、同様の症状がOracle 11g R2でも発生しました。 MySQLのところで挙げた例では比較的JOINが長かったですが(定説としてはJOINは6回くらいまでに抑えた方が良いらしいですが、それから比べると大したことは無いですが)、今回は単純な2表のINN... [続きを読む]

« Hibernateを使用した ― O/Rマッピングは漢(オトコ)の浪漫 | トップページ | CPU切替器経由で接続するとマウスのサイドボタンを認識しなくなってしまった »

other sites

  • follow us in feedly
  • github
  • stackoverflow

ソフトウェアエンジニアとして影響を受けた書籍

  • Christain Bauer: HIBERNATE イン アクション

    Christain Bauer: HIBERNATE イン アクション
    理論と実践が双方とも素晴らしい製品であるHibernate。本書はそのプロダクトを書名に冠していますが、Hibernateを使うつもりがなく、ORマッピングの解説書として読むにしても十分な良書です。Second EditionとしてJava Persistence With Hibernateという書籍も出版されていますが、残念ながら現在のところ 和訳はされていません。-インアクションは2.xの、Java Persistence-は3.1の頃のものなので、最新版とはちょっと違うところもあることに注意。 (★★★★★)

  • アンドリュー・S・タネンバウム: 分散システム 原理とパラダイム 第2版

    アンドリュー・S・タネンバウム: 分散システム 原理とパラダイム 第2版
    クライアント/サーバシステムを構築する上で必要となる知識が総論されてます。Web技術者も、フレームワーク部分を開発するのであれば必読。 (★★★★★)

  • Joel Spolsky∥著: ジョエル・オン・ソフトウェア

    Joel Spolsky∥著: ジョエル・オン・ソフトウェア
    前述の書籍「ソフトウエア開発プロフェッショナル」をより砕いたもの、という感じでしょうか。 前書きではプログラマでなくSE向けの本のように書かれているが、プログラマが読んでも面白い本であると思われます。 SEになった新人(あるいはそういう会社に入る/入りたての人)にとっては、これからどういったことが仕事を遂行していく上で起こりえるのか、どのように考えて行なっていけばいいのか決定する助けになると思います。 元は″Joel on Software″というブログの記事で、web上でも一部日本語で読めます。 http://japanese.joelonsoftware.com/ (★★★)

  • ドナルド・C・ゴース,ジェラルド・M・ワインバーグ: ライト、ついてますか

    ドナルド・C・ゴース,ジェラルド・M・ワインバーグ: ライト、ついてますか
    問題解決(一昔前のの流行語で言うところの『ソリューション』)能力は、システムエンジニアのスキルとして備えるべきもののうちのひとつです。しかし、これは難しい。学校で出されるテストと違い、唯一の、(問題提出者が想定している)解を求めるだけが「問題解決」では無いからです。そもそも、何が問題なのか、それは本当に問題なのか、それは本当に解決すべき問題なのか、その問題解決方法は正しいのか、などを解決しなければ、「その解は正しいのか」に辿りつくことができません。この本の最も良いところのひとつは、本があまり厚くないこと。すぐに読めるし、何回も読み返す気になるでしょう。 (★★★★★)

  • スティーブ・マコネル: ソフトウエア開発プロフェッショナル

    スティーブ・マコネル: ソフトウエア開発プロフェッショナル
    コードコンプリートで有名なスティーブマコネルの著書。新人SEに読んで欲しい。個人として業界の中でどうあるべきか、組織としてどうあるべきか、SEのプロ意識とは?SEの心構え概論、といったところでしょうか。また、業界における資格の重要性についても説かれています。この業界では資格が特に軽んじられる傾向がありますが、この傾向はどんな弊害をもたらすのか、将来的にこの業界は資格に対してどのような姿勢で臨んでいくべきなのか。日経BP社では(他の出版社もだが)最近、似たような類いのあまり面白くない書籍が乱出版されていますが、この本は別格だと思うので安心して購入して欲しいと思います。 (★★★★★)

無料ブログはココログ