MySQL5 MEMORY ストレージエンジンの活用
create
database
ENGINE
HEAP
MEMORY
MySQL
mysql5
select insert
truncate
ストレージエンジン
メモリ
メモリエンジン
メモリーエンジン
いまごろ、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をインストールした方ならデフォルトでインストールされているのがわかるかと思います
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.02 sec)
MEMORY エンジンは、MySQL5.1 から実装（*）されており、存在自体は知ってはいたのですが、これどこで活用するの？という部分に突然興味を持ち始め、実際に試してみたくなりました。以下がその手順になります。 * MySQL 5.1 以前は、HEAPエンジンという名前で実装されておりました。
設定手順
今回利用した環境は以下の通りです。
CREATE TABLE `cyome_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `jis_prefecture_code` int(11) NOT NULL COMMENT 'JIS都道府県コード', `prefecture` varchar(64) NOT NULL COMMENT '当該範囲の都道府県名', `city_code` int(11) NOT NULL COMMENT 'JIS市区町村コード', `city` varchar(128) NOT NULL COMMENT '当該範囲の市区町村名（郡部は郡名、政令指定都市の区名も含む）', `area_code` bigint(20) NOT NULL COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）', `area` varchar(128) NOT NULL COMMENT '当該範囲の大字・町丁目名（町丁目の数字は漢数字）', `lat` decimal(9,6) NOT NULL COMMENT '十進経緯度（少数第6位まで、半角）', `lng` decimal(9,6) NOT NULL COMMENT '十進経緯度（少数第6位まで、半角）', `org_material` int(11) NOT NULL COMMENT '大字・町丁目位置参照情報作成における原典資料を表すコード1：自治体資料 2：街区レベル位置参照 3：1/25000地形図 0：その他資料', `area_kubun_code` int(11) NOT NULL COMMENT '大字/字/丁目の区別を表すコード1：大字 2：字 3：丁目 0：不明', `updated` datetime NOT NULL COMMENT '更新日', `created` datetime NOT NULL COMMENT '登録日', PRIMARY KEY (`id`), KEY `ix02_cyome_innodb` (`jis_prefecture_code`), KEY `ix03_cyome_innodb` (`city_code`), KEY `ix04_cyome_innodb` (`area_code`) ) ENGINE=InnoDB AUTO_INCREMENT=171474 DEFAULT CHARSET=utf8
上記テーブルのデータは、126740件入っております。テーブルの内容につきましては、今回の主題とは外れますので割愛させていただきます。
mysql> select count(id) from cyome_innodb; +-----------+ | count(id) | +-----------+ | 126740 | +-----------+ 1 row in set (0.07 sec)
上記のテーブルを元に、
といった単純な計測を行いたいと思います。
- cyome_memory テーブルを作成する
・・の前に、memory エンジンで作成する、テーブルの上限サイズの設定は、max_heap_table_sizeに上限が決められております。対象のデータをメモリに展開するためには、max_heap_table_sizeの設定の上限値を変更する必要があります。
以下の通りデフォルトの値を変更しました。（実運用ですと、my.cnfに設定したほうが良いかと思います）
mysql> show variables like 'max_heap_table_size'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | +---------------------+----------+ 1 row in set (0.07 sec) mysql> set max_heap_table_size = 204857600 -> ; Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'max_heap_table_size'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 204857600| +---------------------+----------+ 1 row in set (0.00 sec)
- memory エンジンのテーブルを作成する
テーブル定義を新規で作成するのが面倒だったので、CREATE SELECT で作成しました。テーブル名は、cyome_memory （汗
mysql> create table cyome_memory engine=memory (select * from cyome_innodb); Query OK, 126740 rows affected (26.76 sec) Records: 126740 Duplicates: 0 Warnings: 0
以下のようなテーブルが出来上がりました。CREATE TABLE `cyome_memory` ( `id` int(11) NOT NULL DEFAULT '0', `jis_prefecture_code` int(11) NOT NULL COMMENT 'JIS都道府県コード', `prefecture` varchar(64) NOT NULL COMMENT '当該範囲の都道府県名', `city_code` int(11) NOT NULL COMMENT 'JIS市区町村コード', `city` varchar(128) NOT NULL COMMENT '当該範囲の市区町村名（郡部は郡名、政令指定都市の区名も含む）', `area_code` bigint(20) NOT NULL COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）', `area` varchar(128) NOT NULL COMMENT '当該範囲の大字・町丁目名（町丁目の数字は漢数字）', `lat` decimal(9,6) NOT NULL COMMENT '十進経緯度（少数第6位まで、半角）', `lng` decimal(9,6) NOT NULL COMMENT '十進経緯度（少数第6位まで、半角）', `org_material` int(11) NOT NULL COMMENT '大字・町丁目位置参照情報作成における原典資料を表すコード1：自治体資料 2：街区レベル位置参照 3：1/25000地形図 0：その他資料', `area_kubun_code` int(11) NOT NULL COMMENT '大字/字/丁目の区別を表すコード1：大字 2：字 3：丁目 0：不明', `updated` datetime NOT NULL COMMENT '更新日', `created` datetime NOT NULL COMMENT '登録日' ) ENGINE=MEMORY DEFAULT CHARSET=utf8
ENGINEがメモリになっただけですね。 CREATE ～ SELECTなのでデータがちゃんと入っているかについても確認しました。mysql> select count(id) from cyome_memory; +-----------+ | count(id) | +-----------+ | 126740 | +-----------+ 1 row in set (0.03 sec)
※データもちゃんと入っているようです。もうすでにレスポンスが速くなっている・・これは期待できそうです。 - 上記の作業で、cyome_innodb とcyome_memoryという２つのテーブルが作成されたのをご理解いただけたかと思います。テーブルの定義も同様、データの件数も同様、違うのは、ストレージエンジンとインデクスが作成されたかされていないかの違いだけです。では早速ですが、どれくらいパフォーマンスがよくなったかを確認してみたいと思います。
- 検証としては、前回こちらで掲載しました２点間の距離を出そうで試しましたストアドファンクションでSQLを実行してみたいと思います。
・まずinnodb ストレージエンジンを利用した場合の、二点間距離SQLを実行してみます。
SELECT * FROM ( SELECT `jis_prefecture_code`, `prefecture`, `city_code`, `city`, `area_code`, `area`, distance_sphere(35.687577,139.724501,lat,lng) AS KYORI FROM cyome_innodb where distance_sphere(35.687577,139.724501,lat,lng) < 1000 ) AS GISX ORDER BY GISX.KYORI;
▲上記結果は以下のようになりました。（2分10秒79）+---------------------+------------+-----------+--------------+--------------+-----------------+----------------+ | jis_prefecture_code | prefecture | city_code | city | area_code | area | KYORI | +---------------------+------------+-----------+--------------+--------------+-----------------+----------------+ | 13 | 東京都 | 13104 | 新宿区 | 131040020000 | 三栄町 | 256.2926606407 | | 13 | 東京都 | 13104 | 新宿区 | 131040022003 | 四谷三丁目 | 260.9893963283 | | 13 | 東京都 | 13104 | 新宿区 | 131040049000 | 須賀町 | 325.9508199217 | | 13 | 東京都 | 13104 | 新宿区 | 131040015000 | 荒木町 | 354.1090186855 | | 13 | 東京都 | 13104 | 新宿区 | 131040040001 | 若葉一丁目 | 426.5088305747 | | 13 | 東京都 | 13104 | 新宿区 | 131040022001 | 四谷一丁目 | 433.0466033733 | | 13 | 東京都 | 13104 | 新宿区 | 131040001000 | 愛住町 | 500.9756206003 | | 13 | 東京都 | 13104 | 新宿区 | 131040091000 | 本塩町 | 523.1516367820 | | 13 | 東京都 | 13104 | 新宿区 | 131040086000 | 片町 | 530.6539160300 | | 13 | 東京都 | 13104 | 新宿区 | 131040040003 | 若葉三丁目 | 540.2897205107 | | 13 | 東京都 | 13104 | 新宿区 | 131040044000 | 信濃町 | 742.6236707768 | | 13 | 東京都 | 13101 | 千代田区 | 131010059000 | 六番町 | 769.6840872854 | | 13 | 東京都 | 13101 | 千代田区 | 131010014006 | 麹町六丁目 | 793.0635485081 | | 13 | 東京都 | 13104 | 新宿区 | 131040042000 | 住吉町 | 908.4377473452 | | 13 | 東京都 | 13101 | 千代田区 | 131010051000 | 二番町 | 966.4189503143 | | 13 | 東京都 | 13104 | 新宿区 | 131040084000 | 富久町 | 994.5402523833 | +---------------------+------------+-----------+--------------+--------------+-----------------+----------------+ 16 rows in set, 65535 warnings (2 min 10.79 sec)
・次にmemoryエンジンを利用した同様のSQLを実行してみます。SELECT * FROM ( SELECT `jis_prefecture_code`, `prefecture`, `city_code`, `city`, `area_code`, `area`, distance_sphere(35.687577,139.724501,lat,lng) AS KYORI FROM cyome_memory where distance_sphere(35.687577,139.724501,lat,lng) < 1000 ) AS GISX ORDER BY GISX.KYORI;
▲上記結果は、以下のようになりました（46.44）+---------------------+------------+-----------+--------------+--------------+-----------------+----------------+ | jis_prefecture_code | prefecture | city_code | city | area_code | area | KYORI | +---------------------+------------+-----------+--------------+--------------+-----------------+----------------+ | 13 | 東京都 | 13104 | 新宿区 | 131040020000 | 三栄町 | 256.2926606407 | | 13 | 東京都 | 13104 | 新宿区 | 131040022003 | 四谷三丁目 | 260.9893963283 | | 13 | 東京都 | 13104 | 新宿区 | 131040049000 | 須賀町 | 325.9508199217 | | 13 | 東京都 | 13104 | 新宿区 | 131040015000 | 荒木町 | 354.1090186855 | | 13 | 東京都 | 13104 | 新宿区 | 131040040001 | 若葉一丁目 | 426.5088305747 | | 13 | 東京都 | 13104 | 新宿区 | 131040022001 | 四谷一丁目 | 433.0466033733 | | 13 | 東京都 | 13104 | 新宿区 | 131040001000 | 愛住町 | 500.9756206003 | | 13 | 東京都 | 13104 | 新宿区 | 131040091000 | 本塩町 | 523.1516367820 | | 13 | 東京都 | 13104 | 新宿区 | 131040086000 | 片町 | 530.6539160300 | | 13 | 東京都 | 13104 | 新宿区 | 131040040003 | 若葉三丁目 | 540.2897205107 | | 13 | 東京都 | 13104 | 新宿区 | 131040044000 | 信濃町 | 742.6236707768 | | 13 | 東京都 | 13101 | 千代田区 | 131010059000 | 六番町 | 769.6840872854 | | 13 | 東京都 | 13101 | 千代田区 | 131010014006 | 麹町六丁目 | 793.0635485081 | | 13 | 東京都 | 13104 | 新宿区 | 131040042000 | 住吉町 | 908.4377473452 | | 13 | 東京都 | 13101 | 千代田区 | 131010051000 | 二番町 | 966.4189503143 | | 13 | 東京都 | 13104 | 新宿区 | 131040084000 | 富久町 | 994.5402523833 | +---------------------+------------+-----------+--------------+--------------+-----------------+----------------+ 16 rows in set, 65535 warnings (46.44 sec)
データは同じで、参照しているテーブルだけ違います。なのでクエリ結果も同様のものが帰ってきているのがわかるかと思います。 - 結果
もうちょっと高速になるかな？と思っておりましたが、ん・・ん～期待したほどではありませんでした。それでもインメモリ検索は速い！！３倍近く性能が向上しているのがわかるかと思います。ちなみに今回のSQLの書き方ですと、全件テーブルスキャンが入ります。なので意図的にパフォーマンスが悪くなる形で書いております。（そもそも、メモリ1Gでコアは、0.1 core くらいしかないかも）
- 結論
いうまでもなく、これは使える！・・のですが、データがメモリ内に格納されるため、当然ではありますが、MySQLを再起動したりするとガラ（定義体））だけ残してデータ部分は消滅します。自分なりの考えでの利用ケースはデータは基本不変で且つ、大量なものに適用できるのではないかと思います。それに近いのが今回利用した「国土交通省の地区データ」です。量はあるけど、頻繁に更新はれないもの。このような条件がそろっているのであれば、本来のマスタを、MyISAMやらINNODBやらであらかじめ保有しておき、次回起動時にメモリエンジンテーブルにデータをSELECT INSERTで全件追記することですね。私の場合は、/etc/init.d/mysql 内に自動的にデータをコピーするsqlをしかけて、なんとなく無意識にメモリエンジンテーブルにデータがが作成できるしくみにしております。こんな感じです。（ベタベタスクリプトですんません）
echo "truncate cyome_memory" mysql gis -u mysqluser -pmysqlpasswd -e "truncate table cyome_memory;" echo "copy innodb to memory" mysql gis -u mysqluser -pmysqlpasswd -e "insert into cyome_memory select * from cyome_innodb;" exit 0
さいごに
今回 SELECTのSQLを中心に説明させていただきましたが、実際MEMORY ストレージエンジンのテーブルは、INSERT,UPDATE,DELETE等のクエリーも流すことが可能です。そしてかなり速い。利用用途によっては本当に使えるエンジンだと思いますので、マッチする部分があれば使っていただきたいと思っております。（データをロストさせることのないよう、運用設計はしっかりしておいてくださいね）あと、ここでは記載しませんでしたが、メモリエンジンには以下のような制限もあります
- HashとBtreeの2つのインデックスが使用可能です
- BLOBとTEXT型は使用できません、基本的にですが、データベースの設計時に、BLOBとか、TEXTは、システムで利用しないほうが良いと僕は思っている
切り替え前に今一度ご確認をお願いします。
以下のサイトが大変参考になりました！！
