大規模データを扱う事の難しさを少しだけ体感してみる
最近、大規模データからのデータマイニングエンジニアを志す私として、大規模なデータは扱ったことないからしりませーん、というのは通用しないと感じています。
実際の運用レベルを再現する事は不可能だとしても、自分のできる範囲で知識を補っていく必要があると考えています。
というわけで、
[Web開発者のための]大規模サービス技術入門 ―データ構造、メモリ、OS、DB、サーバ/インフラ (WEB+DB PRESS plusシリーズ) | |
![[Web開発者のための]大規模サービス技術入門 ―データ構造、メモリ、OS、DB、サーバ/インフラ (WEB+DB PRESS plusシリーズ)](http://ecx.images-amazon.com/images/I/51GW5jxmdvL._SL160_.jpg) | 伊藤 直也 田中 慎司 技術評論社 2010-07-07 売り上げランキング : 2308 Amazonで詳しく見る by G-Tools |
- インデックス重要
- データがメモリに載るなら載せる
の２点について検証してみました。
インデックスに関しては言わずもがなな所はありますが、データをメモリに載せる効果は、実際に手を動かしてみてすごく実感を持つ事ができました。
テーブルの準備
IDとIDを含む文字列から構成されるTITLEカラムからなる簡単なテーブルを作成しました。
ID:1、TITLE:ID1
みたいな感じになります。
mysql> desc bigdata; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(7) | NO | PRI | NULL | auto_increment | | title | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
大規模データ（というには小さいですが）を想定して500万件のデータをインサートしました。
mysql> select count(*) from bigdata; +----------+ | count(*) | +----------+ | 5000000 | +----------+ 1 row in set (0.00 sec)
インデックスのあるなしの検証
インデックスについてはここが非常に良くまとまっていて分かりやすかったです。
- インデックスの効いている検索
- インデックスの効いていない検索
- インデックスを作成して比較
と行っていきます。
インデックスの効いている検索
mysql> explain select * from bigdata where id = 100011; +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | bigdata | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec)
注目すべきはrowsの欄です。
1レコードしか見ていない事を表しているそうです。
すなわち、一発で目的のレコードが見つかっているということですね。
インデックスの効いていない検索
mysql> explain select * from bigdata where title = 'ID100011'; +----+-------------+---------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | bigdata | ALL | NULL | NULL | NULL | NULL | 5000000 | Using where | +----+-------------+---------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
同じくrowsの欄に注目します。
先ほどと比較して、500万行全て舐めて検索していることが分かります。
今回は非常に簡単なクエリだったから良いですが、このクエリが実運用で混ざっていたら・・・怖いですね。
今回は、非常にシンプルなクエリなので、インデックスが効いていると一瞬で応答が返ってきて、インデックスが効いていないと一拍おいて応答が返ってくる感じでした。
インデックスを作成して比較
まずはTITLEカラムにインデックスを作ります。
mysql> alter table bigdata add index title_index(title);
レコードが多いのでindexを作るのも一苦労ですね。しばらく待ちます。
先ほどと同様のクエリを投げます。
mysql> explain select * from bigdata where title = 'ID100011'; +----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+ | 1 | SIMPLE | bigdata | ref | title_index | title_index | 14 | const | 1 | Using where | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
インデックスが効いているので、rowsの欄が1に変わりました。
１レコードを見て瞬時に応答が返ってきているのが分かりますね。
体感速度も全然違います。
メモリに載せるの重要、についての検証
続いて、今までまともに試したことのなかった、OSのページキャッシュを利用して、まずデータをメモリに載せる事による性能改善について検証してみます。
- データがキャッシュされることの確認
- まずデータをキャッシュさせてみての比較
の順で検証していきます。
今回の実験環境だと、インデックスが効いていると一瞬で応答が返ってきますので、まず先ほど作成したTITLEカラムについてのindexを削除します。
mysql> drop index title_index on bigdata;
データがキャッシュされることの確認
OS起動直後のメモリ状況です。
$free
total used free shared buffers cached
Mem: 510532 172544 337988 0 28164 117588
-/+ buffers/cache: 26792 483740
Swap: 2048276 0 2048276この状態から
- 即座にMySQL起動
- select * from bigdata where title = 'ID100011';を実行
したのが以下の結果になります。
$ free
total used free shared buffers cached
Mem: 510532 303544 206988 0 28236 226912
-/+ buffers/cache: 48396 462136
Swap: 2048276 0 2048276実際にfreeの欄が減り、usedの欄が増えているのが確認できます。
クエリについても、１度目は応答がやや遅く、２度目以降に同様のクエリを投げると１度目より高速に応答が返ってきます。
キャッシュの効果が確認できました。
一度データファイルをメモリに載せてやることによる高速化
さて、いよいよ本題です。
参考書籍にあるように、まずデータファイルをキャッシュさせた後にクエリを投げます。
の順に検証を進めていきます。
OSの起動
起動直後は以下のようになっています。
$ free
total used free shared buffers cached
Mem: 510532 173136 337396 0 27760 117852
-/+ buffers/cache: 27524 483008
Swap: 2048276 0 2048276先ほどの検証のOS起動時とほぼ同じメモリ状況ですね。
catでMySQLのデータファイルを読む
/var/lib/mysql/(DB名)以下にできる、bigdata.MYD, bigdata.MYI, bigdata.frmの３ファイルをキャットで読みます。
ちなみに、快速MySQLでデータベースアプリ！（11）：MySQLの高度な管理とチューニングテクニック (1/2) - ＠ITによると、３つのファイルはそれぞれ、テーブル構造、データファイル、インデックスデータファイルのようです。
$ free
total used free shared buffers cached
Mem: 510532 323880 186652 0 27916 265716
-/+ buffers/cache: 30248 480284
Swap: 2048276 0 2048276freeの欄の数値が減り、usedが増えています。
キャッシュされていることが確認できます。
MySQLを起動して、同じクエリを投げる
この状態で、MySQLを起動して、先ほどの検証と同じクエリselect * from bigdata where title = 'ID100011';を投げます。
１度目から応答がそこそこ早く返ってきます。
先ほどとの違いとしては、１度目、２度目・・・と応答に大きな差がないことが挙げられます。
すなわち、メモリにキャッシュされた状態で１度目のクエリが発行されている、ということですね。
参考書籍で言及されている通りの結果になりした。
一方、実運用で、本当に大規模なデータを扱う場合は、この部分をしっかり考慮に入れておかないと、１度目のクエリで色々と問題が発生してしまうことが予想されます。
知らないと非常に怖いですね。
考察
参考書籍通りの結果になりました。
つまり、メモリにデータをのせる事が重要、インデックスが重要ということになりました。
非常に簡単な状況での検証ではありましたが、知識だけではなく肌感覚として重要でクリティカルであることも体感できたのは大きいと思います。
これでデータのオーダーが一桁上がり、より複雑なクエリとなると、しっかりとした知識がないと非常に怖いということが想像されます。
そういう意味で、やはり参考書籍は非常に有用なものだと再認識しました。
余力があればテーブルのパーティショニング等も試してみたいと思います。
