TT Lab
はじめる
学ぶ 学習パス コース

ClickHouse — 列指向分析 DB を中身から

ソートキー外の条件 — インデックスとプロジェクションでスキップする

TT Labで続きを見る

目標

ソートキーがtsのログテーブルで、キー以外の列で絞り込むクエリを、スキップインデックスとプロジェクションで減らし、それぞれがいつ効いて、いつ効かないか、何を代償として払うかを、EXPLAIN・rows_read・systemテーブルで確認します。

なぜ重要なのか

スキップインデックスは、名前と違ってB-treeではなく、グラニュールのグループの要約です。データの分布が合わなければ、1つのグラニュールもスキップできず、既存のパートには、MATERIALIZEするまで何の効果もありません。プロジェクションは、クエリを直さずに別のソート順を使わせてくれますが、ディスクを大きく使います。このラボの採点ツールは、書き込まれた数値をそのまま信用しません。保存されたSELECTを読み取り専用でもう一度実行しますが、条件キャッシュを無効にし、インデックスを無効にしたままでも実行して、減った行が本当にそのインデックスやプロジェクションのおかげかを確認します。

ステップ

  1. データベースskpとテーブルskp.logsを作成してください。列はts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path String(この順序)、MergeTree、ORDER BY tsです。/opt/lab/fixtures/skip/logs.sqlで100万行を1回だけ入れ、OPTIMIZE TABLE skp.logs FINALでパートを1つにしてください。
  2. ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1を実行し(MATERIALIZEはまだ実行せずに)、EXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861の出力を保存してください(保存先: /root/ch/skip/explain_before.txt)。
  3. MATERIALIZE INDEX idx_traceをmutations_sync = 2で実行し、上のSELECTを保存して(/root/ch/skip/q_trace.sql)、条件キャッシュを無効にして測ったrows_readと、そのミューテーションのmutation_idを書き込んでください(保存先: /root/ch/skip/trace.json)。
  4. seqにidx_seq、user_idにidx_userというminmaxインデックス(GRANULARITY 1)を追加して、MATERIALIZEしてください。seq BETWEEN 1001500000 AND 1001520000の行のsum(latency_ms)を求めるクエリ(/root/ch/skip/q_seq.sql)と、user_id BETWEEN 100 AND 120の行のsum(latency_ms)を求めるクエリ(/root/ch/skip/q_user_range.sql)を作成し、2つのクエリのrows_readをseq_rows_read・user_rows_readとして書き込んでください(保存先: /root/ch/skip/minmax.json)。
  5. プロジェクションp_user (SELECT * ORDER BY user_id)を追加して、MATERIALIZEしてください。user_id = 4242の行のcount(), sum(latency_ms)を求めるクエリ(/root/ch/skip/q_user.sql)を作成し、プロジェクションを使ったrows_readと、--optimize_use_projections 0で無効にしたrows_readを、with_projection・without_projectionとして書き込んでください(保存先: /root/ch/skip/proj.json)。
  6. ステップ5のクエリにSETTINGS log_comment = 'chs-skip-06'を付けて実行したあと、system.query_logの終了(QueryFinish)の記録から、query_id・read_rows・projectionsを書き込んでください(保存先: /root/ch/skip/qlog.json)。
  7. 集計プロジェクションp_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts))を追加して、MATERIALIZEしてください。サービスごとのservice, count(), round(avg(latency_ms), 3)をサービス順で出すクエリ(/root/ch/skip/q_svc.sql)を作成し、そのrows_readと、system.projection_partsのp_svc_hourのrowsを、rows_read・projection_rowsとして書き込んでください(保存先: /root/ch/skip/agg.json)。
  8. アクティブなパートのbytes_on_disk(part_bytes)、2つのプロジェクションのパートのbytes_on_disk(p_user_bytes・p_svc_hour_bytes)、2つのインデックスのdata_compressed_bytes(idx_trace_bytes・idx_seq_bytes)を書き込んでください(保存先: /root/ch/skip/cost.json)。

参考

時刻でソートしたログテーブルを作成する

データベースskpとテーブルskp.logsを作成してください。列はts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path Stringの順序、MergeTree、ORDER BY tsです。/opt/lab/fixtures/skip/logs.sqlで100万行を1回だけ入れ、OPTIMIZE TABLE skp.logs FINALでパートを1つにしてください。

パートを1つにまとめておくと、グラニュール数が1つに決まり、インデックスの前後を同じ尺度で比べられます。100万行なら、8192行のグラニュールが123個です。seqは時間とともに大きくなり、user_idは時間と無関係に均等に散らばっていて、trace_idは行ごとに違います。

インデックスを定義しただけのときのEXPLAIN

ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1を実行してください(MATERIALIZEはまだ実行しません)。そしてEXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861の出力を保存してください(保存先: /root/ch/skip/explain_before.txt)。

EXPLAINの出力のIndexesの下に、PrimaryKeyの欄とSkipの欄があります。SkipのGranulesが、残った数/全体の数です。既存のパートにはインデックスファイルがまだないので、インデックスは何も絞り込めません。system.data_skipping_indicesのサイズも確認してみてください。

MATERIALIZEしたあとに読んだ行

ALTER TABLE skp.logs MATERIALIZE INDEX idx_trace SETTINGS mutations_sync = 2を実行し、ステップ2のSELECT(EXPLAINなし)を保存してください(/root/ch/skip/q_trace.sql)。条件キャッシュを無効にして測ったstatistics.rows_readをrows_read、system.mutationsで見つけたそのミューテーションのidをmutation_idとして書き込んでください(保存先: /root/ch/skip/trace.json)。

MATERIALIZE INDEXは、すべてのパートにインデックスファイルを新しく書くミューテーションです。パート名の末尾にミューテーション番号が付くのを見てください。ブルームフィルターは、ないと断言できるグラニュールだけを捨てるので、探す行が1行でも、偽陽性のグラニュールを数個余分に読むことがあります。

同じminmaxでも効果が違う

seqにidx_seq、user_idにidx_userというminmaxインデックス(GRANULARITY 1)を追加して、両方MATERIALIZEしてください。seq BETWEEN 1001500000 AND 1001520000の行のsum(latency_ms)を求めるクエリ(/root/ch/skip/q_seq.sql)と、user_id BETWEEN 100 AND 120の行のsum(latency_ms)を求めるクエリ(/root/ch/skip/q_user_range.sql)を作成し、2つのクエリのrows_readをseq_rows_read・user_rows_readとして書き込んでください(保存先: /root/ch/skip/minmax.json)。

minmaxは、グラニュールごとに最小値と最大値だけを記録します。ソートキー(ts)とともに大きくなる列なら、グラニュールごとに範囲が狭く、条件の外のグラニュールの大半が外れますが、時間と無関係に均等に散らばる列なら、すべてのグラニュールの範囲がほぼ全体になり、1つも外れません。

別のソート順を持つ隠れたコピー

ALTER TABLE skp.logs ADD PROJECTION p_user (SELECT * ORDER BY user_id)のあとに、MATERIALIZE PROJECTION p_userをmutations_sync = 2で実行してください。user_id = 4242の行のcount(), sum(latency_ms)を求めるクエリ(/root/ch/skip/q_user.sql)を作成し、条件キャッシュを無効にして測ったrows_readをwith_projection、そこに--optimize_use_projections 0を加えて測った値をwithout_projectionとして書き込んでください(保存先: /root/ch/skip/proj.json)。

プロジェクションは、パートごとにサブディレクトリに入るコピーです。クエリは元のテーブル名のままにしておき、オプティマイザーが読む量が最も少ない側を選びます。user_idでソートされたコピーでは、user_idがソートキーなので、プライマリインデックスがグラニュール1つに絞り込みます。

どのプロジェクションを選んだかを記録で見る

SELECT count(), sum(latency_ms) FROM skp.logs WHERE user_id = 4242 SETTINGS log_comment = 'chs-skip-06'を実行し、system.query_logの終了(type = 'QueryFinish')の記録から、query_id・read_rows・projectionsを書き込んでください(保存先: /root/ch/skip/qlog.json)。

クエリの文にはプロジェクション名がないので、何が使われたかは、記録からしかわかりません。query_logのprojections列は、使われたプロジェクションを、データベース.テーブル.名前の形の配列で残します。ログは約1秒ごとにフラッシュされるので、先にSYSTEM FLUSH LOGSを実行してください。

事前に集計したプロジェクション

プロジェクションp_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts))を追加して、MATERIALIZEしてください。サービスごとのservice, count(), round(avg(latency_ms), 3)をサービス順で出すクエリ(/root/ch/skip/q_svc.sql)を作成し、条件キャッシュを無効にしたrows_readと、system.projection_partsのp_svc_hourのアクティブなパートのrowsを、rows_read・projection_rowsとして書き込んでください(保存先: /root/ch/skip/agg.json)。

GROUP BYを含むプロジェクションは、隠れたAggregatingMergeTreeになり、時間・サービスごとに、countとavgの中間状態を1行ずつ持ちます。サービスごとの集計は、その行をもう一度まとめるだけで済むので、元の100万行の代わりに、コピーの数千行だけを読みます。

速くしてくれたもののディスクの代償

skp.logsのアクティブなパートのbytes_on_diskをpart_bytes、system.projection_partsのp_user・p_svc_hourのアクティブなパートのbytes_on_diskをp_user_bytes・p_svc_hour_bytes、system.data_skipping_indicesのidx_trace・idx_seqのdata_compressed_bytesをidx_trace_bytes・idx_seq_bytesとして書き込んでください(保存先: /root/ch/skip/cost.json)。

パートのbytes_on_diskは、その中に入っているプロジェクションのサブディレクトリとインデックスファイルまで含みます。すべてをソートし直したコピー、数千行の集計のコピー、ブルームフィルター、minmaxを並べて見ると、何が高いかが一目でわかります。