概要
ある産業IoT事業者のClickHouseテーブルは、作成当初は高速に動いていたものの、センサーデータが蓄積するにつれて着実に性能が落ちていました。ソートキーがデータの絞り込み方と合っていなかったため、ダッシュボードのクエリは各テーブルの大半を読み込んでいたのです。AceMQは、実際のクエリの構成に合わせてスキーマを再設計しました。
課題
ClickHouseの性能は、クエリがどれだけのデータを読み飛ばせるかでほぼ決まり、それはソートキーの順序、パーティショニング、インデックスの粒度にかかっています。既存のテーブルはカーディナリティの高い識別子を先頭に置いたソートキーで作成されていたため、すべてのダッシュボードが使う時間範囲のフィルターに対して、スパースなプライマリインデックスによる読み飛ばしがほとんど効いていませんでした。
環境
工場監視ダッシュボード向けに、複数年分のセンサーテレメトリーを保存するオンプレミスのClickHouseクラスター。
アプローチ
AceMQは実際のクエリの構成をプロファイリングし、クエリごとに、読み込まれたグラニュール数と一致する行を含むグラニュール数を計測しました。この差をもとに、ソートキーとパーティショニングを再設計しています。そのうえで、コーデックと圧縮方式をデフォルトに頼らず、計測したデータの特性に基づいてカラムごとに選定しました。
ソリューション
- ダッシュボードのクエリ構成をプロファイリングし、読み込まれたグラニュールと一致する行を含むグラニュールの数を計測
- クエリが実際に絞り込みに使うカラムを先頭にするようソートキーを並べ替え、インデックスによる読み飛ばしを回復
- パート数を抑えつつ、時間範囲でのパーティションプルーニングも効くパーティション粒度を選定
- 時系列カラムや低カーディナリティのカラムに、計測したデータ特性に合ったカラム単位のコーデックを適用
- 定期的なダッシュボードの集計向けに、マテリアライズドビューと集計テーブルを導入
- 古いデータを安価なストレージへ移動し、文書化したスケジュールで期限切れにするTTLポリシーを定義
結果
ダッシュボードのクエリは、各テーブルの大半をスキャンする状態から、ごく一部だけを読む状態になり、一般的な応答時間はおよそ1桁改善しました。コーデックとTTLの変更により、ストレージ使用量も大きく減っています。