こんにちは、ATOM事業本部のプロダクト開発グループの松尾です。 広告媒体からのデータ取得処理や集計前の加工処理などを中心にバックエンドエンジニアとして開発を行ってきました。 最近は新レポートや進捗管理機能の開発を行っています。
ATOMのリニューアル版では、新レポートや進捗管理機能の媒体データの実績値の集計をclickhouseというデータベースを採用しております。 長らく苦戦し続けていましたが運用が安定してきたので、採用経緯から苦戦してきた過程について、簡単にご紹介させていただければと思います。
採用経緯について
mysqlから集計処理を切り出したいと思った際に、１つの候補として挙がるのではないしょうか？ 実際に調査をしてみると、select, insertが圧倒的に早い！！となるはずです。
そして、扱うデータ量が多くなればなるほどに、mysqlとの集計処理の性能差に驚くはずです。 同じリレーショナルデータベースなのに。。。こんなに変わるのかと。。。
またmysqlとの接続することも可能です。 clickhouseでの集計時にmysql側のデータも一緒に扱うことができるようになります。 さらに、update, delete も従来のSQLのように柔軟に扱うことが可能です。
その一方で、以下のような懸念事項がありました。
- 日本語のドキュメントが圧倒的に少ない
- 開発陣に運用経験のある人がいない
- マネージドのサービスがない
これらを全て踏まえて、当時の主要な開発メンバーでclickhouseを採用することにしたようです。 ※2022年の後半にマネージドサービスが始まりました。
構成について
clickhouse, zookeeper のそれぞれでawsのec2を立てて以下のような構成にしました。
- zookeeper: 3
- clickhouse
- shard 2
- replica: 1
- table engine: ReplicatedMergeTree, Distributed
こちらの構成は、スモールな形でclickhouseを採用する際のよくある冗長構成パターンになります。
table engineについて
mysqlを採用する際には、あまり意識せず、InnoDB をengineとして採用することが多いかと思います。 clickhouseで冗長構成にする場合は、用途に分けた複数のtable engineの採用が推奨されています。 用途に分けて採用することにあまり馴染みがないと思われるので、簡単にご説明させていただければと思います。
ReplicatedMergeTree Engine
MergeTree系のengineです。 高機能な engine になります。
プロダクション用に冗長構成にする場合は、最初に候補に上がるclickhouseの代表的なtable engine になります。迷った場合は、一旦こちらを採用すれば問題ないかと思われます。
MergeTree系のengineは、非常に大量のデータをテーブルに挿入されるために設計されています。 データが部分ごとに素早くテーブルに書き込まれ、その後にバックグラウンドで部分ごとにマージされるようになっています。
Distributed Engine
こちらは少し特殊な engine です。 異なるサーバ間にあるテーブルに対して、単一のテーブルのように扱うことが可能です。
複数のシャード間では特定のルールに基づき同じ構成のテーブルでデータを保持しています。それらのデータにアクセスするためのインターフェースとして機能します。
採用すると簡単にinsert や select を行うことができるようになります。 採用しない場合は、clickhouseのconfigやクエリ上で 、クエリの実行内容に合わせて適切なサーバ(shard や replicaによる冗長構成された)を指定する必要が出てきます。
また実体となるデータを保持しないので、データの削除や更新はできないです。
採用した場合の補足
ReplicatedMergeTree engineとDistributed engineを採用した場合は、１つのテーブルを作成する際に同じカラム構成で、それぞれの engine でテーブルを作成する必要があります。
そして、作成したテーブルには、以下のように分けてクエリを流します。
- データの読み書きを行う場合は、from句 で Distributed Engine のテーブルを指定
- データの更新や削除を行う場合は、from句 で ReplicatedMergeTree Engine のテーブルを指定
注意点
ReplicatedMergeTree Engine のテーブルを扱う際の注意点です。
こちらのテーブルに向けてクエリを発行する場合は、実体のある全てのサーバに同じクエリが流れることを意識する必要があります。
Distributed Engine のテーブルを併用していると、ついついmysqlのような感覚でクエリを流してしまい、全てのサーバー間で実行されず差異ができてしまうことが何度かありました。。。
全てのサーバー間で実行する方法は２通りあります。
- 全てのサーバーに同じクエリを手動で流す
- 実行するクエリに on cluster句 を付け、テーブル作成時に指定した cluster を持つ単一のサーバーにクエリを流す
基本的には２を採用することになるかと思います。 ２の場合、単一サーバーでクエリが発行されると、非同期で同一の cluster を持つ異なるサーバー間でも同じクエリが発行されます。
運用方法について
ATOMで扱うデータについて
ATOMで扱うデータは、各媒体の媒体アカウントごとの実績値になります。
媒体差異はありますが、最初に実績値が媒体側で集計されてから、約１週間から１ヶ月ほど再集計され続け、その度に取得する実績値が更新され続けます。
そのため、毎日大量のデータを取得し、それらをATOMで扱いやすいように加工し更新し続ける必要がありました。 開発と運用を並行して進めながら、大きく２回運用フローを変更いたしました。 詳細について、３つのフェーズに分けてご説明させていただきます。
運用フェーズ１
最初の運用フェーズでは、媒体アカウントごとにデリートインサートで更新するようにしていました。 検証時点では、問題なく行うことができると判断しました。
ですが、実際に運用してみますと、短時間に大量の alter delete が走ってしまいました。
その結果、未実行のalter deleteのクエリがキューに溜まり、デフォルトの閾値を超えると、alter delete のクエリがエラーで流せなくなるという事象が定期的に発生するようになっていました。
一度その状態になると、キューの掃除をしてから影響範囲内のデータをもう一度最初から入れ直す必要があり、運用コストがかなり膨れていました。
運用フェーズ２
alter deleteのクエリの発行数を抑えるため、デリートインサートを行う際に、更新対象のデータをまとめて削除する作戦を取りました。 またclickhouseのサーバのconfigでデフォルトの閾値を上げ、キューに溜まったクエリの削除頻度も上げました。
それらの結果、alter delete のクエリの発行数をかなり減らすことができ、最初の運用時に発生していたエラーもほぼなくなりました。
ですが、この対応が原因で1点大きな問題点がありました。
それは、最初にまとめて更新対象のデータを削除するため、最新のデータの挿入までに一時的に空白期間が発生してしまうという点です。 その期間に参照されないようにするために、集計処理に関連する一部の機能の定期的なダウンタイムが発生してしまいました。
運用フェーズ３
運用フェーズ２の問題を改善するために、以下のように方針に切り替えました。
- デリートインサートの運用を辞める
- partition 単位で扱う
- insert クエリの発行回数を抑える
そして、以下のようなデータの更新フローに変更しました。
- 更新対象の加工済みの静的なデータををs3に保存
- clickhouseからs3上の静的ファイルを直接読み込みながら、insertクエリ実行し、一時テーブルにデータを挿入
- 一時テーブルから更新対象のテーブルへpartition単位で差し替え
その結果、以下のように改善しました。
- clickhouseが静的ファイルを直接読み込むようになったので、更新処理のバッチ用のリソースをかなり削減することができました。
- 負荷の大きいalter deleteや大量のinsertクエリの発行を辞めたため、clickhouseの負荷が減りました。また、その負荷が減ったため、それまで直列実行していたバッチ処理の並列実行が可能になり、全体の実行時間が５分の１に短縮できました。
- 最新データとのpartition単位での差し替えがミリ秒以下で行えるため、ダウンタイムが発生しなくなりました。
- alter delete のクエリが不要になったので、そのクエリが起因のデータの欠損や重複がなくなりました。
まとめ
柔軟なデータの更新や削除は可能ですが、partition単位で行わないと十分なパフォーマンスが出ない場合があります。 そのため、定期的な運用フローに取り入れる場合は、十分に注意する必要があります。
clickhouseを採用してから数年経ちますが、まだまだ扱い切れていない部分は多分にあるかと思っております。 これからもさらにパフォーマンスを高めたり、より低コストな運用フローを実現したりできるように改善していきたいです。
そして、clickhouseが日本でも流行ってほしいです！！
日本語の記事を一切見かけないです。。。笑 最近では、マネジードのサービスが発表されたり、バージョンの更新頻度も多く便利な機能が増えたり、どんどん使いやすくなっています。 mysqlから集計処理を切り出して、集計の高速化を目指したい場合は、ぜひ候補の１つとしてclickhouseも考慮していただけたらと思います。
最後までお読みいただきありがとうございました。
