MySQLを捨てClickHouseでレポートAPIのレスポンスを1000倍高速化した話

2017 07.20

: ジーニー

技術トピック

Share

はじめに

みなさん始めまして、R&D本部基盤技術開発部の石田祥英です。
北大の情報科学研究科を卒業前に飛び出しジーニーに半年早く17卒入社し、現在は主にFlink, Kafka, Play Framework(scala)や、バッチ用途でgolang,pythonなどを使いプロダクト横断のシステムの開発・検証をやっています。

TL;DR

ClickHouseで分析系クエリが1000倍の速度に爆速化
DBの容量もMySQLと比べ1/13に軽量化
ClickHouse導入はMySQLには無い制限もあるけど、なんとかなる
あんまり日本での導入事例が見当たら無いけど、クエリの高速化・DBの軽量化したい人はぜひ使うといいことがあるかもしれません

社内勉強会用の資料

Report apiを支える技術 from Geniee, Inc. / 株式会社ジーニー

ClickHouse導入の背景

一般的に広告プラットフォームでは「どの広告を、どの広告枠(サイト)に、いつ、どのように(RTB or Adnetwork)配信したのか」という様な情報をDBに格納し、広告主向けの管理画面がDBからレポートする内容を取得し、代理店や広告主の運用者に配信結果をレポート表示しています。

通常、「〜ごとのレポートを見たい」となった場合、それに相当するカラムをキーとして保存しなくてはいけません。

しかし、実際には「1日ではなく1時間ごとにレポートを見たい」、「OS、地域、デバイスごとなどのImpression(広告表示)や広告のClickを集計したい」となると、キーがどんどん増え、それに伴ってDBの行数が指数関数的に増えていってしまいます。

1日に15TBほどの広告配信のログが出るジーニーでは、実際にキーの数を4個から10個に増やそうとした時に、もうMySQLではインデックスを正しく張って頑張っても、運用者が我慢できる時間内にクエリが返ってこなくなってしまう、という問題にぶち当たっていました。

また事業成長の要因も考慮し、トラフィックが10倍に増えても低レイテンシでレポートを返すDBが求められており、何か手はないのかと言うことで色々な方法を模索していました。

DBに必要な要件

広告の運用者の方々がレポート画面をみて運用のPDCAを回せるように、とにかく低レイテンシであること
集計プログラムにバグが見つかった時に再集計してDBの内容を正せること
ログ転送遅延時やハードウェア故障時などへの耐障害性があること

検証した他サービス・手法

MySQLに並列でクエリを投げる

レポートAPIがscalaのPlayで書かれていることもあり、akkaを使い並列でクエリを投げる案があったのですが、正しくソートされたレポートを並列化されたクエリでうまくかける方法が見つからず実現が難しいと判断しました。

BigQuery

Googleの巨大なリソースを使い、SaaSであるので開発・メンテが不要なことやSQLライクなインターフェースと柔軟なUDFが魅力で検討対象に。

実データでの検証は行なっていませんが、Google社の営業担当にテーブルの行数とそれに対するクエリのベンチマークの結果を教えてもらい、それをクエリのパフォーマンスとして考えることができました。

BigQueryはクエリごとの課金とデータ量による課金があり、クエリ課金部分は月額3万ドル(2016/12時点)で叩き放題のプランがりましたが、データ量が膨大なことにより予算をオーバーしたことと、そもそもAPI向けの秒単位の低レイテンシ用途に最適ではないと判断し諦めることになりました。

InfoBright

MySQL上にビルドされたカラムナDB。列志向による軽量化と高速化が期待できる上、MySQLからの移行もスムーズに行えそうなことが魅力に映り実データでの検証に至りました。

しかしユニークキーがはれないことや、INSERT 〜 ON DUPLICATE KEY UPDATEができない、頻繁なアップデートに不向きで、特別な運用をしないといけないことに加え、手元のデータではClickHouseよりもクエリ性能が出なかったこともあり今回は使用しないことになりました。

ClickHouse

公式HPにMySQL・VerticaやInfoBrightなどのカラムナDB・Hiveとのベンチマークを乗せており、ほとんどの場合で、高速なことが魅力でした。特にレポートで頻出するSum関数やGroup By句でのパフォーマンスが高く、実データでの検証に至りました。特にフリーかつオープンソースであること、何よりドキュメントがしっかりしていたことも大きな魅力でした。

https://clickhouse.yandex/benchmark.html

ClickHouseの性質・性能

スキャンする性能がものすごく高い

手元の検証用データではスキャン性能 3億行/s 9GB/sほど出る。(後述のSumming Merge Treeエンジン使用時)

圧縮率が高くストレージの節約になる

カラムナDBなので圧縮率が高い。
検証用データ使用時にはMySQLに比べてストレージ使用量が1/13になりました。

ただし後述のMergeテーブルを使い、１つのテーブルを450テーブルに分散した状態で測定したので、１テーブルで行った場合にはさらに圧縮されるポテンシャルがあると考えられます。

多種多様なテーブルエンジンがある

ClickHouseには20種類のテーブルエンジンがあり、CREATE TABLE時に１つorいくつか組み合わせて選んでテーブルを作ります。

This is the most advanced table engine in ClickHouse. Don’t confuse it with the Merge engine.

とドキュメントにあるように、Merge TreeエンジンがClickHouseに置ける看板エンジンであり、広告主向けレポートで使用したテーブルもMerge Treeエンジンの亜種であるSumming Merge Treeエンジンを使用しています。

広告主向けレポートで使用している２つのテーブルエンジンを紹介しようと思います。

Summing MergeTreeエンジン

Summing Merge Treeエンジンは足し上げが起こるレポート等の用途に向いているエンジンです。キーになるカラムとメジャーとなるカラムを指定しておき、同じキーのレコードをinsertするとメジャーは足し上げられて一行として扱われるようになるという性質を持ちます。ImpressionやClick数、広告主にかかるコストなど、足し上げが発生するレポートにおいては非常に便利なテーブルエンジンで広告向けレポートにはピッタリでした。ClickHouseの行のUPDATEができないという問題も、足し上げるだけのためのUPDATEであればこのエンジンを使うだけで問題なくなります。

Mergeエンジン

Mergeエンジンは複数のテーブルを１つのテーブルとして扱うエンジンです。

テーブル作成時にテーブルの正規表現を指定しておくと、それにマッチした複数のテーブルを１つのテーブルとして扱い、Mergeエンジンにクエリを投げれば配下のDBにクエリを投げた結果を返してくれるという性質を持つため、巨大なテーブルを分割して保存しておくことが可能になります。

UPDATE・DELETEがない

ClickHouseでは行に対するUPDATEとDELETEができません。

これがClickHouseの性能に飛びつくも導入時に困るであろう大きな問題で、今回広告主向けレポートではどう乗り越えたかを紹介したいと思います。

ClickHouseの運用

困ったこと

SQLはほぼMySQLと同じであり、ClickHouse移行で最大にして唯一困ることは

「UPDATE・DELETEが無い問題」

でした。

具体的には

UPDATEができないので、同じ行への更新ができない。
DELETEができないので、集計プログラムのバグ発覚や、集計遅延時等にDB内のデータ修正ができない。行う場合は、DROP TABLEしてまた正しいデータを入れ直さないといけないため時間がかかる。

といった問題にぶつかりました。

今回の広告主向けレポートの要件として

1時間ごとの集計
再集計が短時間で可能

というものがあり、要件を満たしながら制限を回避する方法にたどり着きました。

乗り越え方

簡単に箇条書きにすると

Mergeエンジンを使い1日ごとにテーブルを作成することで、データが壊れた時のデータの入れ直しの時間を減らす。
Summing Merge Treeエンジンを使い、1時間ごとに最新データを入れていく。同じキーごとにメジャーが足し上げられるので、同じ行のimpressionやclick数などのメジャーの集計が遅れても正しく足し上げられる。
RENAME句のみがトランザクションを使える（複数テーブルの一括RENAME時にグローバルロックが走る）ので、それを使って壊れたデータと正しいデータに入れ替えを行う。

ここら辺は文字で書くとややこしいので冒頭のスライドにデータの入れ直しのテクニックを図解しているので参照してみてください。

ClickHouseの苦手だと感じたこと

非常に派手なベンチマークのClickHouseも以下の用途には向かない(そもそもそれ用に設計されてない)です。

OLTP処理

ClickHouseはそもそもがOLAP処理に作られたDBであり、トランザクション処理も実装していません。唯一RENAME句内での複数RENAMEにグローバルロックがかかるぐらいです。ECなどのトランザクション取引が発生するサービスのDBとしては向いていません。

分散ストリーミング環境からののインサート

ドキュメントに

We recommend inserting data in packets of at least 1000 rows, or no more than a single request per second

とあるよう1回のINSERTには1秒あけることと少なくとも1000行のデータをINSERTすることが推奨されており、ストリーミング処理したデータをポイポイ入れていくのではなく、ストリーミングの場合はどこかに一度貯めてからバッチ的にINSERTする必要があるようです。

分散処理基盤からのインサート

また広告主向けレポートでも現在Apache KafkaとApache Flinkなどを用いて分散ストリーミング処理を今後行っていくつもりですが、Apache kafkaやApache Flinkで分散したデータを最終的には１つのtsvファイルにまとめる必要があり、分散したままのINSERTには向いていないようです

https://clickhouse.yandex/docs/en/single/#performance-on-data-insertion

まとめ

社内の広告運用者からは「ロードに3分かかっていたレポート画面が0.5秒で返ってくるようになりました！」と感謝され、DBAからは「DBを圧迫していたレポートテーブルが小さくなった」と喜びの声をいただきました。

現状の使い方ではレプリケーションしかしていませんが、今後格納行数がさらに大きくなった時にもShardingしながら分散するDistributeテーブルエンジンもあるので、まだクエリが遅くなることがすぐに起こる予定はないですが、そちらを使って対処していこうかと思います。 ClickHouseの開発元であるYandex社では374台のサーバーに20兆行を超えるデータが入っているようです。

最後に、大規模なデータを持っていて超早い分析系のクエリを求めるプロダクト(アドテク、EC、ゲーム・メディアのユーザ分析等)なら本当に検証の価値ありだと思います。クエリが相当早くなりますし、データも軽量になります。

また、本番投入できるかの見極めについては、「データ入れ直しが許容時間内に終わる見通し」が立つのであるならば本番投入できる可能性が高いと言えるのではないかと考えています。

https://clickhouse.yandex/docs/en/single/index.html#distributed

以上。