日々之迷歩

世の中わからんことだらけ

ITが複雑で難しくなっていく様に翻弄される日々です。微力ながら共著させていただいた「シェル・ワンライナー160本ノック」をよろしくお願い申し上げます。

RDBとテキスト処理の性能比較

シェル芸 ユニケージ RDB Shell

お詫び

この記事で重大な問題点がありました。RDBのCPU計測時間で抜けがあります。mysqldが使っているCPU時間が入っておりません。初歩的なミスで申し訳ありませんでした。
RDBの実験結果について、realの時間は信頼できますが、userとsysの時間は全く信用できません。userとsysの時間を「未計測」に修正しました。

情報系のシステムの場合、データを扱うにはRDBを使うのが常套手段（だと思う）。だが最近はNoSQLなどの選択肢や、シェル芸やユニケージのようにテキストファイルで扱うという選択肢も注目されつつある。

UNIXという考え方では、下記のようなものがあるようだ。

  • データはテキストファイルに
  • 一つのコマンドは単機能に
  • コマンドはフィルタとして連携

データを専用のバイナリ形式で扱い、スーパーマンのように振舞うRDBと比較すると、結構違う世界のような気もする。

大量のレコード数になるデータがある場合に、RDBとテキストファイルとに格納した場合で、抽出や集計の性能がどんな感じになるのだろうか？気になっていたので実験をやってみた。記載した内容については概要であるのでご了承いただきたい。

テスト環境は下記の通り。

データについて

500個程度のデバイスについて、5分ごとに計測値を収集することを仮定。年間で約5000万レコード程度のデータ量になる。

データの概略は下記の通り。

  • デバイス名：シリアル番号など一意性のあるもの（約500通り）
  • 計測値：data1〜data3
  • 時刻：計測した時刻

RDB

テーブル構造

テーブル構造の概略。実際には他のフィールドも存在する。

Field Type Null Key Default Extra
id int(11) NO MUL NULL auto_increment
device varchar(45) YES MUL NULL
data1 double YES NULL
data2 double YES NULL
data3 double YES NULL
date datetime YES MUL NULL
データの具体例
-- 最初のレコード
mysql> SELECT id,device,data1,data2,data3,date FROM data_2012 LIMIT 2;
+----+------------+-------+-------+-------+---------------------+
| id | device     | data1 | data2 | data3 | date                |
+----+------------+-------+-------+-------+---------------------+
|  1 | DEVICE001  |  1.6  |  33.8 |  38.8 | 2012-01-01 00:00:00 |
|  2 | DEVICE002  |  1.7  |  39.2 |  44.1 | 2012-01-01 00:00:00 |
+----+------------+-------+-------+-------+---------------------+

-- 最後のレコード
mysql> SELECT id,device,data1,data2,data3,date FROM data_2012 LIMIT 2 OFFSET 47999998;
+-----------+------------+-------+-------+-------+---------------------+
| id        | device     | data1 | data2 | data3 | date                |
+-----------+------------+-------+-------+-------+---------------------+
|  47999999 | DEVICE499  |    3  |  36.9 |  38.9 | 2012-12-31 23:55:00 |
|  48000000 | DEVICE500  |  4.4  |    42 |  31.7 | 2012-12-31 23:55:00 |
+-----------+------------+-------+-------+-------+---------------------+
INDEX

下記の2通り

  • deviceのみ Cardinality: 180000程度
  • device,dateの複合インデックス Cardinality: 210000と47000000程度

テキストファイル

ファイルのデータ構造

テキストデータの中身概略はこちら。実際には他のデータも存在する。

項目 データ内容
日付 14桁 yyyymmddHHMMSS
UNIX時間 10桁整数値
デバイス名 文字列
計測値1 数値
計測値2 数値
計測値3 数値
テキストファイルの格納方法

下記の2通りで実験。

年間で一つのファイル

すべてのデータを一つのファイル。ファイルの行数は約5000万行弱、容量は4GB強程度。データの構造と中身は下記の通り。

$ find data/year -type f
data/year/2012
data/year/2013
data/year/2014
data/year/2015

$ head -n 3 data/year/2012
20120101000000 1325343600 DEVICE001 1.6 33.8 38.8
20120101000000 1325343600 DEVICE002 1.7 39.2 44.1
20120101000000 1325343600 DEVICE003 -0.7 40.0 40.5

$ tail -n 3 data/year/2012
20121231235500 1356965700 DEVICE498 -3.4 40.3 41.8
20121231235500 1356965700 DEVICE499 3.0 36.9 38.9
20121231235500 1356965700 DEVICE500 4.4 42.0 31.7
検索キーで分割

デバイス名のディレクトリ内に、年月のファイル名でデータを作成。ファイル一つあたりの行数は7000行弱、容量は700K弱。データの構造と中身は下記の通り。

$ find data/device_month -type f
data/device_month/DEVICE001/201201
data/device_month/DEVICE001/201202
....
data/device_month/DEVICE001/201211
data/device_month/DEVICE001/201212
data/device_month/DEVICE002/201201
data/device_month/DEVICE002/201202
........
data/device_month/DEVICE500/201210
data/device_month/DEVICE500/201211
data/device_month/DEVICE500/201212

$ head -n 3 data/device_month/DEVICE001/201201
20120101000000 1325343600 DEVICE001 1.6 33.8 38.8
20120101000000 1325343600 DEVICE001 1.7 39.2 44.1
20120101000000 1325343600 DEVICE001 -0.7 40.0 40.5

$ tail -n 3 data/device_month/DEVICE500/201212
20121231234500 1356965100 DEVICE500 1.8 43.8 39.1
20121231235000 1356965400 DEVICE500 -2.2 44.9 34.1
20121231235500 1356965700 DEVICE500 4.4 42.0 31.7

実験内容

下記の内容で実験を行い、RDBの場合とテキストデータの場合で比較した。
日付の期間は、抽出集計とも、それぞれ下記のように3ヶ月（10月〜12月）の期間で指定。
デバイス名は一つDEVICE002を指定。

抽出
  • 抽出のキー：デバイス名と日付の期間を指定
  • 抽出データ：時刻、計測データ（data1〜data3）
集計
  • 抽出のキー：デバイス名と日付の期間を指定
  • 集計データ：計測データ（data1〜data3）の月毎平均値

実験結果

OSXのpurgeコマンドでメモリキャッシュを追い出した場合と、メモリキャッシュが効いた場合で比較を行った。

抽出実験

RDBで実行したSQLの概要
SELECT date,data1,data2,data3 FROM data_2012
  WHERE device = 'DEVICE002'
  AND date >= '2012/10/01 00:00:00'
  AND date <= '2012/12/31 23:59:59';
テキストファイルで実行したコマンドの概要
# 1年分1ファイルの場合
$ cat data/year/2012 |
  LANG=C ggrep -F 'DEVICE002' |
  awk '$1>="20121001000000"&&$1<="20121231235959{print $1,$4,$5,$6}'

# 分割ファイルの場合
$ mdate -e 201210m 201212m | tarr | sed 's/^/^/' > ymlist # 年月リスト
$ find data/device_month/DEVICE002 -type f |
  LANG=C ggrep -f ymlist |
  xargs cat |
  awk '$1>="20121001000000"&&$1<="20121231235959{print $1,$4,$5,$6}'
結果の見方

処理にかかった時間を記載。時間が短いほど高速ということ。real、user、sysの意味は下記の通り。

real user sys
実際にかかった時間 プログラムが使ったCPU時間 OSが使ったCPU時間

realとuser+sysを比較すると、下記のようなことが言える。

  • real > user + sys：ディスクIO等がボトルネックになっている。
  • real < user + sys：マルチコアの並列処理が効いている。
抽出実験の結果
INDEX等 メモリキャッシュ
削除 		メモリキャッシュ
後
MySQL INDEX:
deviceのみ 		real 0m38.468s
user 未計測
sys 未計測 		real 0m3.442s
user 未計測
sys 未計測
MySQL INDEX:
deviceとdateの
複合INDEX 		real 0m9.376s
user 未計測
sys 未計測 		real 0m1.036s
user 未計測
sys 未計測
テキスト
ファイル 		1年1ファイル real 0m5.938s
user 0m3.147s
sys 0m3.525s 		real 0m2.792s
user 0m2.509s
sys 0m2.146s
テキスト
ファイル 		分割ファイル
デバイス名/年月 		real 0m0.439s
user 0m0.098s
sys 0m0.099s 		real 0m0.135s
user 0m0.097s
sys 0m0.053s

RDBではメモリのキャッシュにのっているかでパフォーマンスに大きな影響があることが分かる。ただ、キャッシュにのっているのが何なのかが分かっていない。インデックスとかをキャッシュしているのだろうか？
RDBの場合で注目するところは、user + sys の時間よりもrealの方が大きいということ。結局ディスクIOがボトルネックになっていると思われる。ディスクIOがボトルネックの可能性はあるが、userとsysの時間が計測出来ていないため、ここではなんとも言えない。

テキストファイルでは、メモリキャッシュが効いていなくても、RDBに比べると性能低下は低い。ファイル分割をしている場合はかなり高速に動作している。ディレクトリやファイルの名前に検索キーを使い、ファイルシステムの検索性能を利用して高速化出来ているということだろう。

集計実験

1つのデバイスを指定し、3ヶ月間で1月分ずつの平均値を計算。RDBとテキストデータ処理で比較する。RDBは集約関数を使って集計。テキストデータ処理はOpen usp Tukubaiのsm2（Haskell版）とawkを利用。

RDBで実行したSQLの概要
SELECT
    DATE_FORMAT(date, '%Y%m') AS date_month,
    AVG(data1) AS data1_mean,
    AVG(data2) AS data2_mean,
    AVG(data3) AS data3_mean
  FROM data_2012
  WHERE device = 'DEVICE002'
    AND date >= '2012/10/01 00:00:00'
    AND date <= '2012/12/31 23:59:59'
  GROUP BY DATE_FORMAT(date,'%Y%m');
テキストファイルで実行したコマンドの概要
# 1年分1ファイルの場合
$ cat data/year/2012 |
  LANG=C ggrep -F 'DEVICE002' |
  awk '$1>="20121001000000"&&$1<="20121231235959{print substr($1,1,6),$4,$5,$6}' |
  sm2 +count 1 1 2 4 |
  awk '{print $1,$3/$2,$4/$2,$5/$2}'

# 分割ファイルの場合
$ mdate -e 201210m 201212m | tarr | sed 's/^/^/' > ymlist # 年月リスト
$ find data/device_month/DEVICE002 -type f |
  LANG=C ggrep -f ymlist |
  xargs cat |
  awk '$1>="20121001000000"&&$1<="20121231235959{print substr($1,1,6),$4,$5,$6}' |
  sm2 +count 1 1 2 4 |
  awk '{print $1,$3/$2,$4/$2,$5/$2}'
集計実験の結果
INDEX等 メモリキャッシュ
削除 		メモリキャッシュ
後
MySQL INDEX:
deviceのみ 		real 0m37.960s
user 未計測
sys 未計測 		real 0m3.657s
user 未計測
sys 未計測
MySQL INDEX:
deviceとdateの
複合INDEX 		real 0m10.558s
user 未計測
sys 未計測 		real 0m1.022s
user 未計測
sys 未計測
テキスト
ファイル 		1年1ファイル real 0m6.721s
user 0m4.515s
sys 0m3.470s 		real 0m3.807s
user 0m3.856s
sys 0m2.171s
テキスト
ファイル 		分割ファイル
デバイス名/年月 		real 0m1.544s
user 0m1.316s
sys 0m0.134s 		real 0m1.360s
user 0m1.332s
sys 0m0.095s

RDBではやはりuser+sysの時間よりもrealがかなり長い。計算時間自体はかなり短いのでは無いか。結局ディスクIOがボトルネックになってそうだ。抽出実験と同様に、userとsysの時間が計測出来ていないため、ここではなんとも言えない。

テキストファイルの場合でも、realはRDBと比べてあまり変わらなかった。裏技を使うとかなり高速化したのを確認済み。商用版のTukubaiコマンドを使えば、相当高速化されるのが予想出来る。

結果の総括

RDBはMySQLを使ったが、userやsysの時間自体は非常に小さいので大半はディスクIOがボトルネックになっているのが予想された。mysqldのuserとsysの時間が計測出来ていなかった。なのでこの実験でRDBに関する考察は控えた方がよさそうである。今回のデータは1年分だが、4年分だと約2億レコードとなる。RDBで1つのテーブルにこのくらいのレコードをブッ込むと、また違った結果になるのだろうか？

分割されたテキストファイルは、概ね高速な結果となった。検索キーで使うデータをディレクトリやファイルの名前にすることで、ファイルシステムの高速な検索性能を活用できていることか。集計や統計処理を高速するなら、分割テストファイルを使って、awkと商用版のTukubaiコマンドをうまく使うことで相当な高速化が期待できそうな結果となった。

今回の実験ではSSDでメモリ8GBのパソコンだったが、もっとメモリを積んでいたり、HDDな場合はどうなるのだろうか？

補足

facebookで抽出件数が少ない場合についてもみて欲しいという意見があったので追記。

-- 日付の範囲が狭い
SELECT date,data1,data2,data3 FROM data_2012
WHERE device = 'DEVICE002'
AND date >= '2012/12/31 23:55:00'
AND date <= '2012/12/31 23:59:59';

-- id一本釣り
SELECT id,device,data1,data2,data3,date FROM data_2012 where id=48000000

MySQL(複合INDEX)の場合と分割テキストファイル、realの時間はどちらも同等な処理時間だった。

  • メモリキャッシュ削除: 約0.4s
  • メモリキャッシュ後: 約0.05s