MySQL5 MEMORY ストレージエンジンの活用


いまごろ、MySQL memory engine の活用方法なんぞ書いてみようかと・・。 ちょっと前に話題になった・・かどうかしらないけど、インメモリデータベースって知ってますか?あらゆるシステムにおいて最大のボトルネックの箇所って、まあ、ほぼ例外なくデータアクセスの部分だと思います。そこのパフォーマンスがよくなれば!と、データベース管理者さんは、日々チューニングしているわけです。データベースの最大のボトルネックの箇所はハードディスクアクセスの部分が高速であればレスポンスも良いですし、高負荷にならないでしょう。話しを戻しますが、インメモリデータベースとは、そのディスクアクセスの部分を、全部メモリでやっちゃえば、レスポンスっていいんじゃないの?という発想のもと作られたものだと思います(こんな解釈であっているのかな?)

商用製品ですと、

[oracle times ten]
http://www.oracle.com/jp/products/database/timesten/index.html

[IBM soliddb]
http://www-06.ibm.com/software/jp/data/solid/

がそれらに該当します。

では、OSSでそのような製品はないの?

・・・といいますと、postgreSQLベースのとか、MySQL ベースの・・とかならありますが、事実上存在しないのが現状です。あと、調べるのが面倒なので割愛します。

では、OSSデータベースで、インメモリみたいなことはできないの?

・・・というところまで掘り下げるとあったりします。しかも結構手軽に!!それが今回説明するMySQL の MEMORY エンジンですね。rpm 等でMySQLをインストールした方ならデフォルトでインストールされているのがわかるかと思います

MEMORY エンジンは、MySQL5.1 から実装(*)されており、存在自体は知ってはいたのですが、これどこで活用するの?という部分に突然興味を持ち始め、実際に試してみたくなりました。以下がその手順になります。
* MySQL 5.1 以前は、HEAPエンジンという名前で実装されておりました。

設定手順

今回利用した環境は以下の通りです。

上記テーブルのデータは、126740件入っております。テーブルの内容につきましては、今回の主題とは外れますので割愛させていただきます。

上記のテーブルを元に、

  1. cyome_innodb のデータを、 cyome_memory にコピーする
  2. cyome_innodb のデータと、cyome_memoryのデータのSELECT 時の平均クエリータイムを計測する

といった単純な計測を行いたいと思います。

  1. cyome_memory テーブルを作成する
    ・・の前に、memory エンジンで作成する、テーブルの上限サイズの設定は、max_heap_table_sizeに上限が決められております。対象のデータをメモリに展開するためには、max_heap_table_sizeの設定の上限値を変更する必要があります。
    以下の通りデフォルトの値を変更しました。(実運用ですと、my.cnfに設定したほうが良いかと思います) 
  2. memory エンジンのテーブルを作成する
    テーブル定義を新規で作成するのが面倒だったので、CREATE SELECT で作成しました。テーブル名は、cyome_memory (汗 

    以下のようなテーブルが出来上がりました。

    ENGINEがメモリになっただけですね。

    CREATE ~ SELECTなのでデータがちゃんと入っているかについても確認しました。

    ※データもちゃんと入っているようです。もうすでにレスポンスが速くなっている・・これは期待できそうです。

  3. 上記の作業で、cyome_innodb とcyome_memoryという2つのテーブルが作成されたのをご理解いただけたかと思います。テーブルの定義も同様、データの件数も同様、違うのは、ストレージエンジンとインデクスが作成されたかされていないかの違いだけです。では早速ですが、どれくらいパフォーマンスがよくなったかを確認してみたいと思います。
  4. 検証としては、前回こちらで掲載しました2点間の距離を出そうで試しましたストアドファンクションでSQLを実行してみたいと思います。
    ・まずinnodb ストレージエンジンを利用した場合の、二点間距離SQLを実行してみます。 

    ▲上記結果は以下のようになりました。(2分10秒79)

    ・次にmemoryエンジンを利用した同様のSQLを実行してみます。

    ▲上記結果は、以下のようになりました(46.44)

    データは同じで、参照しているテーブルだけ違います。なのでクエリ結果も同様のものが帰ってきているのがわかるかと思います。

  5. 結果
    もうちょっと高速になるかな?と思っておりましたが、ん・・ん~期待したほどではありませんでした。それでもインメモリ検索は速い!!3倍近く性能が向上しているのがわかるかと思います。ちなみに今回のSQLの書き方ですと、全件テーブルスキャンが入ります。なので意図的にパフォーマンスが悪くなる形で書いております。(そもそも、メモリ1Gでコアは、0.1 core くらいしかないかも)
  6. 結論
    いうまでもなく、これは使える!・・のですが、データがメモリ内に格納されるため、当然ではありますが、MySQLを再起動したりするとガラ(定義体))だけ残してデータ部分は消滅します。自分なりの考えでの利用ケースはデータは基本不変で且つ、大量なものに適用できるのではないかと思います。それに近いのが今回利用した「国土交通省の地区データ」です。量はあるけど、頻繁に更新はれないもの。このような条件がそろっているのであれば、本来のマスタを、MyISAMやらINNODBやらであらかじめ保有しておき、次回起動時にメモリエンジンテーブルにデータをSELECT INSERTで全件追記することですね。私の場合は、/etc/init.d/mysql 内に自動的にデータをコピーするsqlをしかけて、なんとなく無意識にメモリエンジンテーブルにデータがが作成できるしくみにしております。こんな感じです。(ベタベタスクリプトですんません) 

さいごに

今回 SELECTのSQLを中心に説明させていただきましたが、実際MEMORY ストレージエンジンのテーブルは、INSERT,UPDATE,DELETE等のクエリーも流すことが可能です。そしてかなり速い。利用用途によっては本当に使えるエンジンだと思いますので、マッチする部分があれば使っていただきたいと思っております。(データをロストさせることのないよう、運用設計はしっかりしておいてくださいね)あと、ここでは記載しませんでしたが、メモリエンジンには以下のような制限もあります

  1. HashとBtreeの2つのインデックスが使用可能です
  2. BLOBとTEXT型は使用できません、基本的にですが、データベースの設計時に、BLOBとか、TEXTは、システムで利用しないほうが良いと僕は思っている

切り替え前に今一度ご確認をお願いします。

以下のサイトが大変参考になりました!!

http://napzak.com/tips/index.php?MEMORY%E5%9E%8B%E3%82%B9%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B8%E3%82%A8%E3%83%B3%E3%82%B8%E3%83%B3

http://oss.timedia.co.jp/show/MySQL%E6%97%A5%E6%9C%AC%E8%AA%9E%E3%81%AE%E6%97%85/%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%8CHEAP%E3%81%AE%E5%A0%B4%E5%90%88%E3%81%AF%E3%81%A9%E3%81%86%E3%81%AA%E3%82%8B%E3%81%8B