Help us understand the problem. What is going on with this article?

ClickHouseとMySQLのベンチマークをとってみた。

More than 1 year has passed since last update.

ClickHouseとは?

オープンソースのカラム型データベース。
ClickHouse is an open source column-oriented database management system capable of real time generation of analytical data reports using SQL queries.
https://clickhouse.yandex/

SQLが使えて、データ更新や削除ができないけど、爆速で検索結果を返してくれるらしい。。

前置き

WEBサーバのアクセスログを解析するのにSQL使いたいんだけど、データ量多すぎてRDBじゃ使い物ならなくて、RedShiftはコスト高くてどうしようかなって時にみつけたデータウェアハウス。

日本語の情報まったくないけど、試してみたらウヒョーってなったので簡単にMySQLとのベンチマークだけ備忘録。

ちなみにClickHouseは基本的にはデータの更新、削除はできない。
入れて参照するだけ。
この割り切った感が素敵。。
※Summing MergeTreeというEngineを利用することで、例えばレポート用のテーブルなどでImpressionやCV数など数値の足し上げしたいような場合に数値を更新できるものもあります。

ClickHouseのインストールはこちらをどうぞ。
https://clickhouse.yandex/#quick-start

マニュアルはこちらから。
http://clickhouse.readthedocs.io/en/latest/reference_en.html

日本語の参考サイトがほとんどない状態で、これはとても参考になった。
http://tech.geniee.co.jp/entry/2017/07/20/160100

ベンチマークのための準備

今回準備したサーバ

  • AWS EC2: r4.large
  • Ubuntu 16.04.2

比較したClickHouseとMySQLのバージョン

ClickHouse MySQL
1.1.54292 5.7.20

検証用に準備したデータ

WEBサーバのアクセスログ
約2,600,000件

検証用テーブル

MySQL

CREATE TABLE access_logs (
  log_date date DEFAULT NULL,
  url varchar(255) DEFAULT NULL,
  referrer varchar(255) DEFAULT NULL,
  ip varchar(255) DEFAULT NULL,
  ua text DEFAULT NULL,
  created_at datetime DEFAULT NULL,
  KEY idx_log_date (log_date),
  KEY idx_url (url),
  KEY idx_referrer (referrer),
  KEY idx_ip (ip),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ClickHouse

日別で保存するテーブルを分け、ClickHouseのEngineのひとつであるMergeTreeを利用する構成とした。
※ClickHouseはデータの更新、削除ができないため、万が一データに不備があるとデータをすべて削除し、一から取り込み直す必要がある。
そのため日毎にテーブルを分けるようにし、万が一の際にはその日の分だけテーブルを作り直すことで対処できるようにしている。

検索用テーブル
CREATE TABLE access_logs (
  log_date Date,
  url String,
  referrer String,
  ip String,
  ua String,
  created_at DateTime
) ENGINE = Merge(\'hogehoge\', \'^access_log_\')


実データテーブル
CREATE TABLE access_log_{YYYYMMDD} (
  log_date Date,
  url String,
  referrer String,
  ip String,
  ua String,
  created_at DateTime
) ENGINE = MergeTree(log_date, (log_date, ip, url), 8192)

実際のベンチマーク

※このベンチマークは3回クエリを投げた平均値を記載しています。

検証1 まずはシンプルに。

select count(*) from access_logs;
ClickHouse MySQL
0.005 sec 0.81 sec

すでに差がある。。

検証2 日付を絞ってみた。

select count(*) from access_logs where log_date = '2017-10-30';
ClickHouse MySQL
0.003 sec 0.13 sec

MySQLもIndexが効いているのでそこそこがんばっている。

検証3 時間別の集計を試す。

  • ClickHouse
SELECT
    count(*),
    toHour(created_at) AS hour
FROM access_logs
WHERE log_date = '2017-10-30'
GROUP BY toHour(created_at);
  • MySQL
SELECT
    count(*),
    date_format(created_at, '%H') AS hour
FROM access_logs
WHERE log_date = '2017-10-30'
GROUP BY hour
ClickHouse MySQL
0.010 sec 1.09 sec

GroupByになると差がでてきた。

検証4 リファラー×URLの上位50件を出してみる。

SELECT
    count(*) AS cnt,
    referrer,
    url
FROM access_logs
GROUP BY
    referrer, url
ORDER BY cnt DESC
LIMIT 50
ClickHouse MySQL
0.558 sec 16.27 sec

MySQLでもIndex貼っているにも関わらず、かなりの差が出てしまった。。

WEB上で公開されている他のミドルウェアとのベンチマーク結果

RedShiftとのベンチマーク

https://www.altinity.com/blog/2017/6/20/clickhouse-vs-redshift

Apache Spark/MySQLとのベンチマーク

https://www.percona.com/blog/2017/02/13/clickhouse-new-opensource-columnar-database/

公式サイトで公開されているベンチマーク

https://clickhouse.yandex/benchmark.html

ベンチマークしてみての考察

データ更新、削除できないというデメリットはあるものの、アクセスログなど更新、削除する必要のないものを集計したりすることに関しては、かなり有効に使えるデータウェアハウスであると言えるのではないかと。

レコード削除はできないが、日毎にデータテーブルを分けているので、過去日のテーブル自体を削除してしまえばデータサイズの節約も可能。

手軽にビッグデータ解析を行うには充分すぎるパフォーマンスが出せたので、しばらくはClickHouseでアクセスログの解析運用をしていきたいと思った今日この頃でした。

おまけ

ビッグデータをサーバ上に置くとHDDの容量の問題が常にでてきます。
なのでスケールアウトできる運用を心がける必要があるが、ClickHouseも容易にスケールアウトできる設計になっています。
この辺はまたいずれ検証したいと思いますが、その前に今回検証したデータの実容量をMySQLと比べてみました。

MySQL

# pwd
/var/lib/mysql/hogehoge
# du -h .
2.6G    .

ClickHouse

# pwd
/var/lib/clickhouse/data/hogehoge
# du -h .
201M
ClickHouse MySQL
201M 2.6G

データサイズでもMySQLに比べ1/10以下と、かなり節約できることも確認できました。
スケールアウトしなくても、しばらくは1台で運用していけそう^^
ということで財布にもやさしいClickHouseでした。

basicinc
マーケティングとテクノロジーで社会のあらゆる問題を解決する集団
https://tech.basicinc.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away