ClickHouse で億単位レコードの集計をざっくりベンチマーキングしてみた

TL;DR

OLAP 向け DBMS の ClickHouse の、実際のパフォーマンスについての記事をあまり見かけなかったので、実際に1億超のレコードを持つテーブルで集計問い合わせしてみた、という話です。

背景

業務で携わっているサービス(AWS にて運営)にて提供しているアクセス集計機能が、年々のデータ増加によりじゅうぶんなレスポンスタイムを得られなくなって来ている問題に遭遇しています。アクセスデータは RDS for MySQL に集約してあり、集計が重くなってくる度に中間集計等で最適化を図って凌いできましたが、そろそろ限界みたいです。

そもそも、こういう用途に RDBMS を用いる事じたいが選定ミスというのは当初から判ってはいたので、他の OLAP 向きなソリューションを模索してはいました。AWS Redshift も試してみたのですが料金的にサービスのサイズと釣り合わないかもだし、統合的なデータウェアハウスをやりたい訳じゃなく、もうちょっとサクッとビッグデータ集計だけできちゃう OSS 製品とかない？と思ってたところ、ClickHouse の存在を知りました。少し調べたところ、

OLAP 用途を想定した、Redshift 等と同じく列指向の DBMS。
RDBMS っぽい SQL が、ほぼそのまま使える。
水平スケーリングに対応。
公式 Docker イメージが提供されている。

ということが判り、比較的サクッと評価作業できそうです。そんな訳で、現状で問題が発生しているテーブルをそのまま移植して、同じクエリでどれだけのパフォーマンスが得られるかをゴールにして触ってみることにしました。

準備

では、実際に ClickHouse サーバを立ててみます。実験場所として、t3a.large クラスの EC2 インスタンスを1台起動して作業ディレクトリを用意↓。ストレージは16GB以上は用意したほうが良さげ。尚、EC2 インスタンス内で docker を使えるようにしたり、DB に接続するための設定手順は割愛します。

mkdir -p clickhouse/ch_data
cd clickhouse

ここで開発元から提供されている Docker イメージを使ってコンテナを起動します。Compose ファイルはこんな感じ。

services:
  ch:
    image: clickhouse/clickhouse-server
    container_name: some-clickhouse-server
    volumes:
      - type: bind
        source: "./ch_data"
        target: "/var/lib/clickhouse/"
    ports:
      - "8123:8123"
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    environment:
      - TZ="Asia/Tokyo"

docker compose up -d でコンテナが起動したら、下記のようにクライアントから接続してみて動作確認。MySQL と同様に quit で抜けられます。

docker exec -it some-clickhouse-server clickhouse-client

では、ベンチマーク用テーブルの用意を。このテーブルは、実際のネットサービスにてトラッキング収集したデータを一定条件で集計した結果を格納する為のものですが、話を分かりやすくする為に「ネット投稿記事閲覧の時間単位集計」としておきます。MySQL 側ではこんな DDL です。

CREATE TABLE `post_view_total` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `author_id` int unsigned NOT NULL COMMENT '著者ID',
  `post_id` int unsigned NOT NULL COMMENT '投稿ID',
  `category` varchar(20) NOT NULL COMMENT '記事カテゴリー',
  `platform` varchar(20) NOT NULL COMMENT 'プラットフォーム種別',
  `ymd` date NOT NULL COMMENT '閲覧日付',
  `hour` int NOT NULL COMMENT '閲覧時間帯',
  `views` int unsigned NOT NULL COMMENT '閲覧回数',
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `post_id` (`post_id`),
  KEY `category` (`category`),
  KEY `platform` (`platform`),
  KEY `ymd` (`ymd`),
  KEY `hour` (`hour`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ネット投稿記事閲覧の時間単位集計';

これと同じ構成のテーブルを ClickHouse にて生成。

CREATE TABLE `post_view_total` (
  `id` bigint unsigned NOT NULL COMMENT 'ID',
  `author_id` int unsigned NOT NULL COMMENT '著者ID',
  `post_id` int unsigned NOT NULL COMMENT '投稿ID',
  `category` varchar(20) NOT NULL COMMENT '記事カテゴリー',
  `platform` varchar(20) NOT NULL COMMENT 'プラットフォーム種別',
  `ymd` date NOT NULL COMMENT '閲覧日付',
  `hour` int NOT NULL COMMENT '閲覧時間帯',
  `views` int unsigned NOT NULL COMMENT '閲覧回数'
)
ENGINE=MergeTree
PRIMARY KEY (`id`)
COMMENT 'ネット投稿記事閲覧の時間単位集計';

これら2つのテーブル間でレコードをコピーする訳ですが、ClickHouse には MySQL 連携機能が用意されているので、いちいちダンプ→レストアのようなことをせずともサーバ間で直接データコピーが可能です。ClickHouse クライアントにて、こんなふうにします。

INSERT INTO post_view_total 
SELECT * FROM mysql('<MySQL サーバの FQDN>:3306', '<MySQL データベース名>', 'post_view_total', '<ユーザ名>', '<パスワード>');

プログレスがリアルタイム表示されてコピーが進行し、終わるとこんな表示が。

0 rows in set. Elapsed: 968.037 sec. Processed 111.96 million rows, 7.67 GB (115.65 thousand rows/s., 7.92 MB/s.)

1.1億強レコード、7.67 GB のコピーに16分程かかったという訳です。

ベンチマークと結果

ようやく準備が出来たので、このテーブルに対して以下のような問い合わせをしてみます。

SELECT post_id, SUM(views) AS view_count FROM post_view_total 
WHERE author_id = 1234 AND category = '身辺雑記' AND platform IN ('アプリ', 'ブラウザ') 
GROUP BY post_id;

RDS の MySQL (インスタンスクラスは m5.large)ではこの問い合わせに初回は6分程、キャッシュが効いた2回目以降でも2分半程かかっていました。それが ClickHouse の場合は、こういう結果に↓。

148 rows in set. Elapsed: 4.484 sec. Processed 111.96 million rows, 4.72 GB (24.97 million rows/s., 1.05 GB/s.)

こちらは初回も2回目以降もレスポンスタイムに大きな差はありませんでした。平均してだいたい4.5秒程ですから、30〜80倍程のパフォーマンスアップです。尚、色々な EC2 インスタンスで試してみましたが、レスポンス速度は vCPU 数に比例するようです。t3a.large は 2vCPU なので、xlarge (4vCPU)にすれば更に倍の性能が期待できます。

評価

そんな訳で、シンプルながらも業務で発生している問題とほぼ同等なベンチマーキングが実施できました。期待以上の好結果だったので、問題解決のソリューションたり得る感触を得ました。とはいえまだまだ運用ノウハウが足りないので、さらに深堀りしてみようと思います。

わたねこコーリング

野良プログラマ発、日々のアウトプット

ClickHouse で億単位レコードの集計をざっくりベンチマーキングしてみた

TL;DR

背景

準備

ベンチマークと結果

評価