ミリ秒単位の最適化

ミリ秒単位の最適化

目次

1. ReplacingMergeTreeの扱いの難しさ
2. 奇妙なパーティションキー
3. ORDER BY句のわずかな変更
4. コストのかかるJOINの削除
5. より少ないカラムの読み込み
6. テンプレート化
7. 積極的なマージ
8. より少ない行の読み込み
9. なぜこれを行ったのか

明確な目標を持って新しいプロジェクトに参加したと想像してください。その目標はClickHouseのパフォーマンスを向上させ、最大の顧客が合理的な時間でクエリを実行できるようにすることです。これは以前にも何度も経験したことです。最もパフォーマンスの低いクエリを尋ねると、通常は最も影響が大きいものが見つかります。そして、いくつかの簡単な改善策が見つかるはずです。そのクエリを実行してみると、完了までに1分以上かかります。

1 row in set. Elapsed: 85.715 sec. Processed 1.96 billion rows, 198.69 GB
Peak memory usage: 23.05 GiB.

このクエリは、それぞれが独自のメトリックまたはスパークラインを計算している12個のクエリのうちの1つです。そして、それらはクライアントがアプリケーションにアクセスしたときに最初に表示される画面にあります。ログイン直後に少なくとも数分間スピナーを見ているクライアントを想像してみてください。

これは、過去4ヶ月間で行った変更、学習、最適化のすべてを記録したもので、そのクエリをサブ秒のレイテンシまで引き下げました。変更はシンプルで段階的でした。どれも巧妙なトリックではなく、基本的な改善が互いに積み重なったものです。

ReplacingMergeTreeの扱いの難しさ

セットアップはシンプルで、イベントはKafkaから直接ClickHouseテーブルに書き込まれます。イベントは変更可能であり、任意の時点で更新または削除できます1。そのため、過去の履歴を変更するイベントは、新しいイベントと同等かそれ以上の速度で受信されます。これは、イミュータブルストレージに基づいたデータベースには全く向いていません。私たちはReplacingMergeTreeエンジンを使用し、FINALを多用することを余儀なくされており、そこに時間のほとんどが費やされます。

さらに、クライアントのイベントの履歴全体をたどって計算する必要があるメトリックがいくつかあります。それらの履歴は、クライアントが毎日アクティビティを行いイベントを生成するため、常に増加し続けています。このため、この記事の数値は変動する可能性があります。例えば、ある最適化でクエリが200GBから150GBになったとしても、次の最適化までのデータ受信量によって、後で210GBを読み込んでいるように見えるかもしれません2。

奇妙なパーティションキー

テーブルは当初、月ごとにパーティション化されていました。

PARTITION BY toYYYYMM(event_created_at)

これは明白な選択ですが、このケースでは多くの点で間違っていました。

イベントはバッチで到着し、それらのバッチはあらゆる時点に影響を与えます。単一のバッチには複数の月のイベントが含まれ、挿入ごとに多くのファイルが作成され、通常の雪だるま効果を引き起こします。これは、リアルタイムクエリが読み取るファイル数を増やし、マージに圧力をかけます。

また、FINALは行をマージし、その並列処理は、キーがパーティション間でどのように分散され、どのように重複するかによって大きく異なります。最悪のシナリオは、単一のスレッドが最終マージを行い、多くの時間を費やすことです。時間によるパーティショニングは、クライアントのイベントがテーブルのすべてのパーティションに分散されることを意味するため、ClickHouseはパイプラインの構築と範囲の分割にかなりの時間を費やして交差を減らす必要があります。

このパーティショニングが原因でFINALが単一スレッドで実行されるパフォーマンスへの影響の例です。

［画像: FINAL performance using a single thread］

FINAL performance using a single thread

［画像: FINAL performance using the right parallelism］

FINAL performance using the right parallelism

これらの両方の問題を解決し、パフォーマンスを向上させるために、以下のパーティショニング戦略を適用しました。

PARTITION BY (client_customer_id % 36)

client_customer_idはクライアントのIDではなく、クライアントのクライアントIDであり、クエリではこのカラムを使用しないことに注意してください。この戦略は直感的ではありませんが3、私たちのユースケースではうまく機能します。

• 挿入ごとに「わずか」36個のパートを作成することで、挿入パフォーマンスを制御下に置きます。
• パーティションは均等に分散されます。巨大なパーティションにクローラークライアントが含まれ、同じパーティションに着地したすべてのクライアントのクエリが遅くなることはありません。
• ClickHouseが範囲の交差を計算する必要なしに、FINALの並列処理を保証します。これは、split_parts_ranges_into_intersecting_and_non_intersecting_finalとsplit_intersecting_parts_ranges_into_layers_finalを無効にできることを意味し、データパイプライン構築ステップで数秒を節約できます。
• client_customer_idでフィルタリングすることは決してないため、パーティションプルーニングを完全に諦めます。コストはかかりますが、クライアントの履歴全体を読み取る必要があるメトリックには影響しません。月ごとのパーティショニングも何もプルーニングしていませんでした。そして前述のように、並列処理で多くのメリットを得られます。

ORDER BY句のわずかな変更

各event_typeが各メトリックにどのように貢献しているかを確認したところ、一部のイベントタイプは貢献していないにもかかわらず、それらを読み込んで処理していることが判明しました。それらはテーブルの行の約16%を占めていました。大きな違いではありませんが、クエリパフォーマンスを向上させる明らかな変更でした。

event_typeをより前の位置に昇格させることはALTERではありません。テーブル作成時にソートキーは固定されるため、これは新しいテーブルと完全な書き直しを意味しました。event_typeはすでにキーに含まれており、その位置が変わっただけなので、ReplacingMergeTreeは以前と同じ行を正確に折りたたみました。変更後、クエリパフォーマンスに顕著な改善が見られました。

Before4

1 row in set. Elapsed: 13.43 sec. Processed 256.67 million rows, 18.82 GiB
Peak memory usage: 18.78 GiB.

After

1 row in set. Elapsed: 11.44 sec. Processed 237.86 million rows, 15.67 GiB
Peak memory usage: 14.04 GiB.

クエリレイテンシは約15%改善され、読み取る行数は約7%減少しました。これが、完全な約16%が表示されない理由を説明しています。ソートキーはグラニュールをフィルタリングし、グラニュールに興味のあるevent_typeの行が1つでも含まれている場合、そのグラニュールから残りの行を読み取る必要があります。

コストのかかるJOINの削除

クエリにはいくつかのコストのかかるJOINがありましたが、ほとんどは実際のテーブルをスキップできるトリックの背後に隠されていました。右側はパラメータでゲートされたサブクエリであるため、ブランチが必要ない場合、フィルタは偽になり何も返しません。

ANY LEFT JOIN (
    SELECT ...
    FROM table_2
    WHERE {has_currency:Boolean} = 1 AND ...
) USING ...

それを実行するには数ミリ秒かかり、解析と計画にはさらに数ミリ秒かかります。これはテンプレート化セクションで説明する内容です。

しかし、すべての呼び出しで実際のテーブルに対して実行されていたJOINが1つありました。そのJOINは、メトリックの計算に使用される中間値をいくつか取得するだけでした。

ANY LEFT JOIN (
    SELECT
        client_id,
        internal_id,
        latest_a_field,
        latest_another_field
    FROM table_derived_from_original_table FINAL
    PREWHERE client_id IN {client_ids:Array(UInt64)}
) USING client_id, internal_id

元のテーブルから派生したテーブルに対する2回目のFINALで、興味のある2つのフィールドの最新値を取得します。ClickHouseは右側のハッシュテーブルを構築してメモリに配置し、JOINは比較的速いですが、コストは読み込みだけでなくメモリフットプリントにも及びます。

修正は思ったよりも簡単でした。これらの2つの値で計算された中間カラムは、アプリケーションレベルで事前計算し、イベントの一部として送信することができました。クエリは現在よりシンプルになり、JOINは完全に削除できます。

1 row in set. Elapsed: 2.46 sec. Processed 49.86 million rows, 5.60 GiB
Peak memory usage: 1.09 GiB

メモリ使用量は13倍近く減少し、クエリ時間は5倍近く改善されました。

より少ないカラムの読み込み

アプリケーションレベルでのカラムの事前計算が、メモリとCPUを大幅に節約できることを確認しました。なぜそこで止まる必要があるのでしょうか。

特にクライアントの履歴全体を読み取る必要があるメトリックは、9つのカラムを使用していました。行ベースのデータベースでは大きな問題ではありませんが、ClickHouseはカラムナであり、多数のカラムを読み取ることは、回避できる追加データにアクセスすることを意味します。

アプリケーションレベルで各行のメトリック貢献度を事前計算した後5、その影響は明らかでした。

1 row in set. Elapsed: 1.38 s. Processed 49.69 million rows, 3.46 GiB
Peak memory usage: 582.40 MiB

9つのカラムは、行あたり約38バイトでした。それらを8バイトのカラムに置き換えるだけで、1.5GBの読み込みが節約されます。さらに、読み込みを回避できた追加ファイル、インデックス、マーク、そして節約できたシーク時間を考慮すると、さらに約2倍の改善が得られます。

テンプレート化

この時点で、13.43秒から1.38秒まで短縮することに成功しました。これは、数週間で適用できた最適化を積み重ねただけで、ほぼ10倍の改善です。このプロセス中に、より積極的なアプローチで対処する必要があることがいくつか明らかになりました。

特定された最大のボトルネックの1つは、クエリの実行方法に関連していました。ネストされたClickHouseパラメータ付きビューを3レベル深く使用しており、巨大なクエリが生成され、リクエストごとに再解析および再計画されていました。これらのビューはパラメータを渡し、JOINのようなオプションのブランチをレンダリングしていました。どんなに試しても、ブランチは使用されていなくてもCPUを消費していました。

影響の測定は簡単でした。クエリの最小バージョンを作成し、データのないクライアントに対して実行しました。実行時間はほぼゼロなので、残りは解析、AST、パイプライン構築です6。

平均実行時間が740ミリ秒のクエリで、420ミリ秒の固定オーバーヘッドが発生していました。時間の半分以上がクエリの準備に費やされていました。

そこで、ビューをフラットなクエリテンプレートに置き換えました。dbtは各メトリックを単一の自己完結型ステートメント（パラメータ付きビューを構築するのと同じマクロから構築されるため、単一の真実のソースがあります）にコンパイルし、第二段階のマーカーをブラケット区切り文字で残します。これはdbt自身の波括弧のJinjaをそのまま通過します。

SELECT
    [% if has_currency %]
        {currency:String} as display_currency,
        coalesce(nullIf(exchange_rate, 0), 1)
    [% else %]
        'USD' as display_currency,
        1
    [% endif %] AS exchange_rate,
    money_field_in_usd * exchange_rate as money_field_in_display_currency
FROM original_table FINAL
PREWHERE client_id IN {client_ids:Array(UInt64)}
[% if has_currency %]
ANY LEFT JOIN ( 
    SELECT date, exchange_rate
    FROM exchange_rates PREWHERE currency = {currency:String}
) USING date
[% endif %]

アプリケーションはリクエストごとにJinjaテンプレート（ブラケット区切り文字付き）をレンダリングします。レンダリングされたクエリは、20KB以上から数KBになります。例に見られるように、通貨がない場合、為替レートのJOINはクエリにまったく存在しません。

A/Bテストの結果は、system.query_logでlog_commentタグ付けされています。

Grafanaでさらに印象的な別のクエリの結果です。

［画像: Performance chart in Grafana］

平均2.21秒から527ミリ秒、p95は2.43秒から586ミリ秒に改善しました。

もちろん、これには代償が伴います。テンプレートはアプリケーションサーバーでコンパイルおよびレンダリングする必要があります。最初の試みはコンパイル済みテンプレートをメモリにキャッシュすることでしたが、数千台のサーバーとデプロイによる再起動の頻度を考えると、これだけでは不十分でした。重いテンプレートは300ミリ秒以上かかっていました。追加のキャッシュレイヤーを導入し、コンパイル済みバイトコードをmemcachedに移動しました。テンプレートがメモリに見つからない場合は、memcachedから取得しようとします。それでも見つからない場合は、オンザフライでコンパイルします。

［画像: Template prepare time］

共有キャッシュの前と後のテンプレート準備時間（コンパイル+レンダリング）。

［画像: Template hit ratio］

ヒット率は約100%に上昇しました。2番目のレイヤーは期待通りに機能しています。

コンパイル時間はほぼゼロと見なすことができます。

これらのクエリは週に約240万回実行されるため、クエリごとの小さな節約が積み重なり、クラスター全体で体感できるほどの効果を生み出します。

積極的なマージ

テンプレート化により、すべてのクエリにあった定数オーバーヘッドを削減できました。しかし、その影響は限定的であり、まだ大量のデータを読み込んでいるクエリにとっては改善は比較的小さいものでした。

次に、より少ないデータを読み込むことに焦点を当てます。私が発見した最も簡単な方法は、system.processors_profile_logの情報を分析することです。結果をプロットするツールさえあります。最適化しようとしているクエリがどのように見えるかを見てみましょう。

［画像: ClickHouse query pipeline for the metric query］

履歴全体を必要とするメトリックのClickHouseパイプライン。

このクエリが読み取るデータを減らそうとしているため、パイプラインの最初の4〜5ステージに焦点を当てることができます。これらはデータを読み込み、FINALの重複排除を適用するステージです。それ以降のすべては、他のフィルタリング、集計などであり、現時点では関心がありません。

注意すべき重要な点がいくつかあります。

• 130GBのデータを読み込みますが、計算に関連するのは35GBのみです。
• テーブルにはすでに2倍の重複があります（18億行→9億2000万行）。
• FINALを適用するためだけに読み取られるソートキーのカラムは、60GBのうち30GBを占めます。2倍の重複を考慮すると、これらのカラムはディスクから読み取られた130GBのうち60GBを占めます。

A ReplacingMergeTreeは、そのマージと同じくらいクリーンです。そして、どういうわけかマージが停滞していました。

Totals:
   ┌─partition─┬─parts─┬──parts_+30_gb──┬──perc─┬─bytes────┐
1. │           │   534 │             32 │ 50.73 │ 2.12 TiB │
   └───────────┴───────┴────────────────┴───────┴──────────┘

36個のパーティションのうち32個に30GBを超える単一のパートが含まれており、パーティション行の約50%を占めていました。そして、マージ制限（max_bytes_to_merge_at_max_space_in_pool）が50GBに設定されていたようです。これらのパートは、これ以上マージされることはないため、隣に着地した重複はすべて永続的に保持されます。

ここで2つの設定が変更されました。

• max_bytes_to_merge_at_max_space_in_poolを50GBから100GBに引き上げました。これにより、マージされていなかった30GBを超えるパートが再びチャンスを得られるようになりました。
• min_age_to_force_merge_secondsを86400（24時間）に設定しました。これにより、24時間より古いパートが定期的にマージされるようになりました。

最初の設定は、マージセレクターがマージプールの空き容量に基づいてマージ入力サイズをフィルタリングするため、効果がありませんでした。また、max_bytes_to_merge_*は入力に適用され、結果には適用されません。これはReplacingMergeTreeであるため、重複排除により出力はパートの合計よりも小さくなります（つまり、55.9 GiBの入力から40.69 GiBのパートが生成されます）。

2番目の設定が効果を発揮しました。24時間より古いパートが定期的にマージされるようになりました。本番データに戻ると、テーブルは最適化されました。

［画像: Cluster CPU usage per page load dropping after the merge change］

ページロードあたりのクラスター全体のCPU使用量。マージ変更後に減少しました。FINALが安くなり、CPU消費量が減少しました。

代償は、ほぼ毎日すべてのレプリカでテーブルの書き直しが発生することです。しかし、これが、この記事の冒頭で取り上げたクライアントを含む、すべてのクライアントにとって許容できるクエリ時間と見なせるレベルに到達できた変更です。

より少ない行の読み込み

これまでのすべての作業には大きな欠点があります。最もコストのかかるクエリが読み取るデータは、クライアントとともに増加します。最大のクライアントでクエリパフォーマンスを約20倍改善しましたが、クライアントが同じ比率で成長すると、元に戻ってしまいます。

クライアントのサイズに関係なく一定に保ちながら、より少ない行を読み取る方法を見つける必要があります。明白な解決策は、マテリアライズドビューを使用して事前集計することです。しかし、私たちが扱っているのは、いつでも変更または削除できるものすべてであるということを忘れないでください。一般的に、マテリアライズドビューはReplacingMergeTreeや変更可能なイベントとはうまく連携しません7。

代替案を探したところ、ほとんどのメトリックは一定期間に発生するイベント（例：開始日と終了日を持つサブスクリプション）に対して計算されていることがわかりました。

   ┌─expired─┬────count()─┐
1. │       1 │ 6208430114 │ -- 6.21 billion
2. │       0 │  179498782 │ -- 179.50 million
   └─────────┴────────────┘

読み取る行の約97%は、すでに終了した期間に属しています。2021年に終了した期間は、今日のメトリックには貢献しません。理論的には、60億行を読み込んで1億8000万行を追加していることになります。

この情報を価値あるものとして利用するのを妨げていた3つの要因がありました。

• period_ends_atがORDER BYに含まれていなかった。
• カラムは誰も使用していなかったため、更新されていなかった。
• それを修正するには60億行を編集する必要があった。

カラムをORDER BYの先頭に昇格させるのが比較的容易であることはすでに確認しましたが、これは別の話です。トームストーンを送信して行を削除する必要がある場合、ORDER BYの値を知る必要があるため、アプリケーションのいくつかの場所に影響します。

また、この最適化を続行する前に、カラムをORDER BYに追加することが実際にパフォーマンスを向上させるかどうかを確認する必要があります。現在の句は次のようになっています。

ORDER BY client_id, event_type, toDate(event_created_at), ...

新しい句は次のようになります。

ORDER BY client_id, event_type, toDate(event_created_at), toDate(period_ends_at), ...

新しいカラムは4番目の位置に追加する必要があります。なぜなら、最適化しようとしているクエリはevent_created_atでフィルタリングすることから利益を得られませんが、他の多くのクエリはそれから利益を得られるからです。したがって、問題は次のようになります。toDate(period_ends_at)カラムを4番目の位置に配置することで、グラニュールをフィルタリングできるようになるでしょうか？

その背後にある理論は簡単です。グラニュールはディスクから読み取ることができるデータの最小単位であり、インデックスとマークは、読み取る前にグラニュールをフィルタリングするために使用されます。デフォルトでは、これらのグラニュールは8192行の長さです。カラムでフィルタリングするには、前のカラムに十分なデータがあり、一定である必要があります。つまり、毎日、同じクライアントとイベントタイプから少なくとも8192件のイベントを受信する必要があります。

トップ10クライアント、つまりこの最適化を実行しているクライアントは、そのしきい値を大幅に超えていることが判明しました。

処理された行数とバイト数が100倍削減されていることがわかります。グラニュールをフィルタリングすることは期待通りの成果でしたが、予期せぬ成果もありました。グラニュールがフィルタリングされず、ディスクから不要な行が読み取られる場合でも、FINAL句を適用する前にそれらをフィルタリングするようになり、小規模なクライアントにも影響があります。

最終的に、クエリ時間を元の85秒から約400ミリ秒にまで短縮することに成功しました。これは200倍以上の改善です。

なぜこれを行ったのか

ここまで読んでくださった方には、感謝しかありません。また、なぜこの記事を書いているのか疑問に思っているかもしれません。

数年間、私はClickHouseの内部構造について学んだことすべてを書き留め、その過程で発見したすべてを調査し最適化するために従ったプロセスを記録してきました。長文の調査はすべて、以前の会社の内部ツールに投稿されていました。私はその仕事に誇りを持っており、非現実的な楽観主義者として、それはそこにいた人々にとって有用だったと思います。うまくいけば、今でもそうです。

その後、私は退職し、それに伴ってすべてへのアクセスを失いました。彼らはすでに別のプラットフォームに移行し、それを置き去りにしたかもしれません。

これは私が学んだことのほんの一部であり、ここで共有することは、その仕事を生き続け、他の人がアクセスできるようにするための方法です。役に立つことを願っています。

フットノート

1. プロジェクトは移管される可能性があり、ユーザーは重要な属性を変更したり、サブスクリプションをキャンセルしたりすることができます。↩
2. 以前の最適化は依然として有効ですが、これにより、私たちがかなりのプレッシャー下にあったことが明確になりました。↩
3. この戦略が実際に機能していると確信するには、まだ時間がかかります。将来的に変更するかもしれませんが、今のところはうまく機能しています。↩
4. これらのテストは、中間結果を得るために本番データの一部を削減したバージョンで実行されたことに注意してください。そのため、数値は最初に報告されたものよりも著しく小さくなっています。ここからマージセクションまでのすべてが、同じ削減されたデータセットを使用しています。↩
5. これは、DEFAULTまたはMATERIALIZEDカラムを使用してClickHouseレベルで行うこともできましたが、カラム定義に隠されているよりもアプリケーションレベルでロジックを持つことを好みました。↩
6. これらのステップを簡単に測定するために、ClickHouseリポジトリにPRをプッシュしました：PR #108282 ↩
7. イベントをイミュータブルにし、メトリックデルタで作業できるようにする作業ラインがすでに開かれています。これにより、マテリアライズドビューを使用できますが、実装には時間がかかります。↩