【進撃の巨大データ】Log集計用DBとシステム構成の美しい設計を考える

[:W560]

Log集計用DB設計

考える問題

Document無しのAgile開発をガチで推奨したい@yutakikuchi_です。【進撃の巨大データ】の第2回目として巨大アクセスLog集計用DBの設計について勉強した内容についてメモしたいと思います。DB周りはそこまで詳しく無いので詳しい皆様からの突っ込み大歓迎でございます。また図々しいですが知恵をください(笑)。
今日の主目的は下の2要件を叶えるためのDB設計を考える事です。特に問題になるのがRealTimeの話でTableにLogDataを書き込む処理と集計のSQLをどのように組み立てるか、それ以外にもSystemPerformanceとArchitectureにも関わってきます。

1. リアルタイムで大量データを集計したい
2. 定期処理で大量データを集計したい

使うもの

1. Fluentd : Fluentd: Open Source Log Management
2. Mysql Innodb : MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.3 InnoDB 設定
3. Mysql Infinidb InfiniDB – the high performance, column oriented analytic database

Fluentdを使ってNginxLogをMysqlにリアルタイムで格納する - Yuta.Kikuchiの日記
FluentdはRealTimeでLogをLog収集用のサーバに転送とDBへの書き込み、Mysql InnodbはLogデータを格納するDBです。以前これらを用いてNginxのLogをリアルタイムで格納することを試したのでよければ上のリンクを参照してください。なぜMysqlか？という質問が出そうですが、安定と実績を買います。Mongodbのようなスキーマレスの方が後から柔軟に集計が可能という話もでてきそうですが、必要なデータ項目だけを抽出してディスク容量を抑えることと集計の高速化を目指します。そんなことしないで最初からTresureData使えよって話もありそうですが、自前で作りたいというプライドのためです(笑) Mysqlの種類も行指向のInnoDBと集計処理に向いているとされる列指向のinfinidbの両方を使ってみます。

リアルタイム大量データの格納をどうするか？

リアルタイム大量データ格納の方法を決める上で重要ポイントとなのがMysqlのrowをどのように使うかです。取り得る方法とそれぞれのメリット/デメリットを考えて下にまとめました。もしMongodbのようなKVSを使ったLog集計でもKeyとValueをどのように使うかという同じ問題になると思います。

No	Method	merit	demerit
1	InnoDBを使う。格納時点でデータを集計。 1行中の集計用カラムを都度Update。	特定のデータ集計を1行で管理可能。 行数が膨らまない。 集計SQLのPerformanceを出す事ができる。	書き込み口が複数あると行ロックが心配なので、 サーバ構成を工夫しなければ行けない。 格納時にデータを集約してしまうので、 シンプルで特定の集計しかできない
2	InnoDBを使う。格納時点でデータを1行ずつ書き込む。 集計時にSQLで行数をSUMる。	行ロックの心配が無くなる。	行数が膨大になる。 集計用SQLも重くなるし、GROUP BYにも限界がある。 行数が膨らまないように 定期的に古いデータは削除するなどの処理が必要。
3	InfiniDBを使う。格納時点でデータを1行ずつ書き込む。 集計時にSQLで行数をSUMる。	列指向なので集計が速い。	InfiniDBは制限が多くて使いづらい。 行数が膨大になる。 INSERTに時間がかかる。 行数が膨らまないように 定期的に古いデータは削除するなどの処理が必要。

Server構成はどうなるの？

Logを集約する専用Serverを設ける事を重点に上のようなServer構成を考えました。この構成だとDBに書き込むComponentが1つになるのでマルチプロセスで無いプログラム処理でSQLをちゃんと組み立てれば行ロックの心配が解消されます。
図中左の各種AppServerはFluetndを使ってLogAggregatorのMasterにLogを常にForwardします。また冗長化としてLogAggregatorのMasterがDownして転送できない時のためにSlaveにもLogをForwardするためのFailOver機能を入れておきます。ただしLogAggregatorのSlaveは基本的にMasterからFowardされるようにします。今回の本題とはあまり関係ありませんが、LogAggregatorからHDFSにLogを書き込む事もしておいた方が良いと思います。HadoopでLogを集計して結果をMysqlに流すなどの処理を予め想定しておくこと、そこまでしなくても単にFileSystemとして使ってもいいですし。
LogAggregatorにLogが集約されているのでそこからMysqlにDataを書き込みに行きます。書き込みはFluentdのin_tailを使います。SELECTからのINSERTをすると処理が重たくなるのでINSERT/UPSERTを垂れ流すイメージです。各種Serverの役割を表で整理します。

Component	Role
AppServer	Fluentdを使ってLogをLogAggregator MasterにForwardする。 FailOver用としてLogAggregator Slaveも設定する。
LogAggregator Master	AppServerからLogを受け取りMysqlにRealTimeでDataを書き込む。
LogAggregator Slave	LogAggregatorのSlave用。MasterからLogを受け取り定期処理でMysqlにDataを書き込む
Hadoop HDFS	LogAggregatorからLogを受け取る。MapReduceをしたり単なるFileSystemとして利用
Mysql Master	LogDataを書き込む
Mysql Slave	MasterからLogを転送しLogDataを書き込む

Fluentdの設定はどうなるの？

Fluentd High Availability Configuration | Fluentd

When the active aggregator (192.168.0.1) dies, the logs will instead be sent to the backup aggregator (192.168.0.2). If both servers die, the logs are buffered on-disk at the corresponding forwarder nodes.

WebAppサーバでLogAggregatorにRealTimeでLogをForwardするための設定として/etc/td-agent/td-agent.confを以下のように設定します。192.168.0.1がLogAggregatorのMaster、192.168.0.2がSlaveになります。もしSlaveにもDataがForwardできなかった場合にはpos_fileで指定しているファイルに最終読み込みに関するデータを登録します。

<source>
  type tail
  path /var/log/nginx/access.log
  format apache
  tag nginx.access
  pos_file /var/log/td-agent/nginx.pos
</source>

<match nginx.access>
  type forward
  <server>
    host 192.168.0.1
    port 24224
  </server>

  # use secondary host
  <server>
    host 192.168.0.2
    port 24224
    standby
  </server>

  # use file buffer to buffer events on disks.
  buffer_type file
  buffer_path /var/log/td-agent/buffer/forward

  # use longer flush_interval to reduce CPU usage.
  # note that this is a trade-off against latency.
  flush_interval 30s
</match>

LogAggregator側の設定は以下のようになります。AppServerからForwardされたLogを/var/log/nginx/access.logに落として、in_tailでMysqlに転送するようにします。

  <source>
    type forward
    port 24224
  </source>

  <match nginx.access>
    type file
    path /var/log/nginx/access.log
  </match>

  <source>
    type tail
    format apache
    path /var/log/nginx/access.log
    tag nginx.realtimeaccess
    pos_file /var/log/td-agent/nginx.pos
  </source>

  <match nginx.realtimeaccess>
    type mysql
    host localhost
    database nginx
    key_names code,path,agent
    sql INSERT INTO access_log_realtime1 (code, path, agent, count, curdate, created_at) VALUES( ?, ?, ?, 1, CURDATE() + 0 , NOW() ) ON DUPLICATE KEY UPDATE count = count + 1;
    username root
    flush_interval 10s
  </match>

Tableの設計と実行結果はどうなるか？

例として特定のURLをDailyで何回閲覧されたかをリアルタイムで集計するという問題のTableを設計したいと思います。
No.1) 格納時点でデータを集計。1行中の集計用カラムを都度UPDATE。
CREATE,INSERT,SELECTをそれぞれ次のように定義します。まずはInnoDBで作ります。

/* Table作成 */
CREATE DATABASE IF NOT EXISTS `nginx`;
DROP TABLE IF EXISTS `nginx.access_log_realtime1`;
CREATE TABLE `nginx.access_log_realtime1` (
  `code` int(3) unsigned NOT NULL,
  `path` varchar(255) NOT NULL,
  `agent` varchar(255) NOT NULL,
  `curdate` char(8) NOT NULL,
  `count` int(11) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`code`,`path`,`agent`,`curdate`),
  KEY `count_index` (`code`,`path`,`agent`,`curdate`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* 格納 */
INSERT INTO access_log_realtime1 (code, path, agent, count, curdate, created_at) VALUES( ?, ?, ?, 1, CURDATE() + 0 , NOW() ) ON DUPLICATE KEY UPDATE count = count + 1;

/* 集計 */
SELECT code,path,curdate,agent,code FROM nginx.access_log_realtime1 WHERE path = '/foo.html' AND curdate = CURDATE();
EXPLAIN SELECT code,path,curdate,agent,count FROM nginx.access_log_realtime1 WHERE path = '/foo.html' AND curdate = CURDATE();
+----+-------------+----------------------+-------+---------------+-------------+---------+------+------+--------------------------+
| id | select_type | table                | type  | possible_keys | key         | key_len | ref  | rows | Extra                    |
+----+-------------+----------------------+-------+---------------+-------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | access_log_realtime1 | index | NULL          | count_index | 1566    | NULL |    1 | Using where; Using index |
+----+-------------+----------------------+-------+---------------+-------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

では実際に50万件アクセスがあった場合の集計結果ですが、当然格納時にアクセス数が集計されているのですぐに結果が返ってきます。

#!/bin/sh

for i in `seq 1 500000`; do
   curl http://localhost/foo.html
done

mysql> SELECT code,path,curdate,agent,count FROM nginx.access_log_realtime1 WHERE path = '/foo.html' AND curdate = CURDATE();
+------+-----------+----------+--------------------------------------------------------------------------------------------------------+--------+
| code | path      | curdate  | agent                                                                                                  | count  |
+------+-----------+----------+--------------------------------------------------------------------------------------------------------+--------+
|  200 | /foo.html | 20130706 | curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.14.0.0 zlib/1.2.3 libidn/1.18 libssh2/1.4.2 | 500000 |
+------+-----------+----------+--------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.00 sec)

No.2) 格納時点でデータを1行ずつ書き込む。集計時にSQLで行数をSUMる。
CREATE,INSERT,SELECTをそれぞれ次のように定義します。こちらもまずはInnoDBで作ります。

/* Table作成 */
CREATE DATABASE IF NOT EXISTS `nginx`;
DROP TABLE IF EXISTS `nginx.access_log_realtime2`;
CREATE TABLE `access_log_realtime2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(3) unsigned NOT NULL,
  `path` varchar(255) NOT NULL,
  `agent` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `count_index` (`code`,`path`,`agent`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8

/* INSERT */
INSERT INTO access_log_realtime2 (code, path, agent, created_at) VALUES( ?, ?, ?, NOW() );

/* 集計 */
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS created_at , agent, COUNT(*) AS count FROM access_log_realtime2 WHERE path = '/foo.html' GROUP BY DATE_FORMAT(created_at, '%Y%m%d'), agent;
EXPLAIN SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS created_at , agent, COUNT(*) AS count FROM access_log_realtime2 WHERE path = '/foo.html' GROUP BY DATE_FORMAT(created_at, '%Y%m%d'), agent;
+----+-------------+----------------------+-------+---------------+-------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table                | type  | possible_keys | key         | key_len | ref  | rows   | Extra                                                     |
+----+-------------+----------------------+-------+---------------+-------------+---------+------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | access_log_realtime2 | index | NULL          | count_index | 1546    | NULL | 500074 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------------------+-------+---------------+-------------+---------+------+--------+-----------------------------------------------------------+
1 row in set (0.17 sec)

50万件アクセスがあった場合の集計結果ですが、INDEXを貼ってはいますが6秒以上処理に掛かっています。

mysql> SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS created_at , agent, COUNT(*) AS count FROM access_log_realtime2 WHERE path = '/foo.html' GROUP BY DATE_FORMAT(created_at, '%Y%m%d'), agent;
+------------+--------------------------------------------------------------------------------------------------------+--------+
| created_at | agent                                                                                                  | count  |
+------------+--------------------------------------------------------------------------------------------------------+--------+
| 2013-07-06 | curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.14.0.0 zlib/1.2.3 libidn/1.18 libssh2/1.4.2 | 500000 |
+------------+--------------------------------------------------------------------------------------------------------+--------+
1 row in set (6.34 sec)

No.3) No.2のやり方でInfiniDBを使う
CREATE,INSERT,SELECTをそれぞれ次のように定義します。こちらはInfiniDBで作ります。

/* Table作成 */
CREATE DATABASE IF NOT EXISTS `nginx`;
DROP TABLE IF EXISTS `nginx.access_log_realtime3`;
CREATE TABLE `access_log_realtime3` (
  `code` int(3) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `agent` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL
) ENGINE=InfiniDB DEFAULT CHARSET=utf8;

/* 格納 */
INSERT INTO access_log_realtime3 (code, path, agent, created_at) VALUES( ?, ?, ?, NOW() );

/* 集計 */
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS created_at, COUNT(*) AS count FROM access_log_realtime3 WHERE path = '/foo.html' GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d');
EXPLAIN SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS created_at, COUNT(*) AS count FROM access_log_realtime3 WHERE path = '/foo.html' GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d');
+----+-------------+----------------------+------+---------------+------+---------+------+------+--------------------------------------------------------------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows | Extra                                                              |
+----+-------------+----------------------+------+---------------+------+---------+------+------+--------------------------------------------------------------------+
|  1 | SIMPLE      | access_log_realtime3 | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where with pushed condition; Using temporary; Using filesort |
+----+-------------+----------------------+------+---------------+------+---------+------+------+--------------------------------------------------------------------+
1 row in set (0.01 sec)

50万件アクセスがあった場合の集計結果ですが、INDEXを貼ってはいませんが0.75秒で処理が返ってきます。InfiniDBで注意したいのは長い文字列が格納されるとGROUP BYで条件指定できないようです。例えば今回のagentがそうです。

mysql> SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS created_at, COUNT(*) AS count FROM access_log_realtime2 WHERE path = '/foo.html' GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d');
+------------+--------+
| created_at | count  |
+------------+--------+
| 2013-07-06 | 500000 |
+------------+--------+
3 rows in set (0.75 sec)

結局何を採用すべきか？

今回のRealTime集計DBの実験結果をまとめると次のようになります。※INSERTの時間ですが、Fluentd経由でやると時間がかかりすぎるため、直接SQLを実行して計測しました。結果としてはNo.1のInnoDBを使う。格納時点でデータを集計。1行中の集計用カラムを都度Updateの結果がINSERT、SELECTともに良い結果が出ていて効率が良さそうです。僕が今まで見て来た開発現場ではNo.2のInnoDBを使う。格納時点ではデータを1行ずつ書き込み、集計時にSQLで行数をSUMる手法の方が多かったように思います。おそらくNo.2は一番安定した手法でどこの企業でも取り入れているなのではないでしょうか。No.3のInfiniDBを使う。格納時点でデータを1行ずつ書き込む。集計時にSQLで行数をSUMるはInfiniDB自体の実績があまり無い事と、INSERTに時間がかかりすぎるのですが大量データのGROUP BYをどうしても使わざるを得ない場合はInnfiniDB(列指向DB)で実行すると良いと思います。

No	方法	50万件 INSERT時間	50万件 SELECT時間
1	InnoDBを使う。格納時点でデータを集計。 1行中の集計用カラムを都度Update。	0.6 hour	0.00 sec
2	InnoDBを使う。格納時点でデータを1行ずつ書き込む。 集計時にSQLで行数をSUMる。	2.15 hour	6.34 sec
3	InfiniDBを使う。格納時点でデータを1行ずつ書き込む。 集計時にSQLで行数をSUMる。	39 hour	0.75 sec

また今回定期処理の件については検証をしませんでしたが、バッチプログラムの中で集計をして集計結果をDBに格納すれば良いだけなのでNo.1の手法を採用すれば良いと思います。

おまけ：Infinidbの設定方法と制限

少しだけInfinidbの設定手順について記載します。凄く簡単です。無料のCommunity版について書きます。 必要な項目を記載してDownloadします。僕の環境はCentosなのでrpmパッケージを落としてきます。socketファイルのパスがデフォルトと変わっているので起動時に注意してください。起動時のoptionでsocketファイルのパスを指定するよりはDefaultのPathにsymlinkを貼るのがいいかもしれないです。またinfinidbのパッケージだと何故かinnodbが入っていないのでINSTALL PLUGINします。
InfinidbはPRIMARY KEY/AUTO_INCREMENTの指定、NOT NULLやDEFAULTの制限、INDEXの作成ができません。その代わりにINDEXの作成やパーティショニングはInfinidb側で自動的に行ってくれるようです。

$ wget "Download Path"
$ tar xzf calpont-infinidb-2.2.11-1.x86_64.rpm.tar.gz
$ sudo rpm -ivh calpont*.rpm
$ sudo /etc/init.d/mysqld stop
$ sudo /usr/local/Calpont/bin/install-infinidb.sh
$ sudo /etc/init.d/infinidb start
$ sudo ln -s /usr/local/Calpont/mysql/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock
//socketを指定して起動
$ mysql -u root -p --socket=/usr/local/Calpont/mysql/lib/mysql/mysql.sock
//通常起動
$ mysql -u root -p 
mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                   | Transactions | XA   | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| CSV        | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| InfiniDB   | YES     | Calpont InfiniDB storage engine                           | YES          | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
5 rows in set (0.03 sec)

mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb.so';
mysql> INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so';
mysql> INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so';
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| InfiniDB   | YES     | Calpont InfiniDB storage engine                            | YES          | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.01 sec)

mysql> show plugins;
+--------------+--------+--------------------+---------------------+-------------+
| Name         | Status | Type               | Library             | License     |
+--------------+--------+--------------------+---------------------+-------------+
| binlog       | ACTIVE | STORAGE ENGINE     | NULL                | GPL         |
| CSV          | ACTIVE | STORAGE ENGINE     | NULL                | GPL         |
| MEMORY       | ACTIVE | STORAGE ENGINE     | NULL                | GPL         |
| MyISAM       | ACTIVE | STORAGE ENGINE     | NULL                | GPL         |
| MRG_MYISAM   | ACTIVE | STORAGE ENGINE     | NULL                | GPL         |
| InfiniDB     | ACTIVE | STORAGE ENGINE     | libcalmysql.so      | PROPRIETARY |
| InnoDB       | ACTIVE | STORAGE ENGINE     | ha_innodb.so        | GPL         |
| INNODB_TRX   | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL         |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL         |
+--------------+--------+--------------------+---------------------+-------------+