クエリ最適化のシンプルなガイド
このセクションでは、異なるパフォーマンスと最適化技術を使用する方法を、一般的なシナリオを通じて示すことを目的としています。これには、analyzer、query profiling、またはNullable Columnsの回避が含まれ、ClickHouseのクエリパフォーマンスを改善します。
クエリパフォーマンスの理解
パフォーマンスの最適化を考えるべき最良のタイミングは、ClickHouseにデータを初めて取り込む前にデータスキーマを設定しているときです。
しかし、正直に言うと、データがどれくらい増えるかや、どのようなクエリが実行されるかを予測するのは難しいです。
既存のデプロイメントがあり、改善したいクエリがいくつかある場合、最初のステップはそれらのクエリがどのように実行されるか、なぜ一部が数ミリ秒で実行され、他のクエリが長くかかるのかを理解することです。
ClickHouseは、クエリがどのように実行され、実行に必要なリソースを理解するのを助ける豊富なツールセットを提供しています。
このセクションでは、それらのツールとそれらをどのように使用するかを見ていきます。
一般的な考慮事項
クエリパフォーマンスを理解するために、クエリが実行されるときにClickHouseで何が起こるかを見てみましょう。
以下の部分は意図的に簡略化されており、いくつかの手短な表現が含まれています。ここでの目的は、詳細に埋もれてしまうのではなく、基本的な概念に追いつくことです。詳細については、クエリアナライザーに関する説明を読んでください。
非常に高レベルの観点から見ると、ClickHouseがクエリを実行すると、以下のことが起こります：
- クエリの解析と分析
クエリが解析され、分析され、一般的なクエリ実行プランが作成されます。
- クエリの最適化
クエリ実行プランが最適化され、不必要なデータが削除され、クエリプランからクエリパイプラインが構築されます。
- クエリパイプラインの実行
データが並行して読み取られ、処理されます。この段階では、ClickHouseはフィルタリング、集約およびソートといったクエリ操作を実行します。
- 最終処理
結果がマージされ、ソートされて、クライアントに送信される前に最終結果としてフォーマットされます。
実際には、多くの最適化が行われています。このガイドではそれらについて少し詳しく説明しますが、今のところ、これらの主要な概念は、ClickHouseがクエリを実行するときに何が起こっているのかを理解するのに役立ちます。
この高レベルの理解を持って、ClickHouseが提供するツールを検討し、それをどのように使用してクエリパフォーマンスに影響を与えるメトリクスを追跡するかを見ていきましょう。
データセット
クエリパフォーマンスへのアプローチを示すために、実際の例を使用します。
まず、NYCのタクシーデータセットを取り込み、最適化せずにフルデータを用います。
以下は、テーブルを作成し、S3バケットからデータを挿入するコマンドです。スキーマを意図的にデータから推測していることに注意してください。これは最適化されていません。
データから自動的に推測されたテーブルスキーマを見てみましょう。
遅いクエリを特定する
クエリログ
デフォルトでは、ClickHouseは実行された各クエリに関する情報をクエリログに収集し、ログを保存します。このデータはsystem.query_logテーブルに格納されます。
実行された各クエリに対して、ClickHouseはクエリの実行時間、読み取られた行の数、CPU、メモリ使用量、ファイルシステムキャッシュヒットなどのリソース使用状況といった統計をログに記録します。
したがって、クエリログは遅いクエリを調査する際の良い出発点です。実行に長時間かかるクエリを簡単に特定し、各クエリのリソース使用情報を表示できます。
NYCタクシーデータセットにおける実行に長時間かかっているトップ5のクエリを見てみましょう。
query_duration_msフィールドは、その特定のクエリが実行されるのにかかった時間を示します。クエリログの結果を見てみると、最初のクエリは2967msかかっており、改善できる可能性があります。
また、どのクエリがシステムに負荷をかけているかを確認するために、最もメモリまたはCPUを消費しているクエリを調べることもできます。
見つけた長時間実行しているクエリを isol ゼーションし、何度か再実行して応答時間を理解します。
この時点で、enable_filesystem_cache設定を0にしてファイルシステムキャッシュをオフにすることが重要です。これにより、再現性を向上できます。
視覚的に読みやすくするためにテーブルで要約しましょう。
| 名称 | 経過時間 | 処理された行数 | ピークメモリ |
|---|---|---|---|
| クエリ1 | 1.699秒 | 329.04百万 | 440.24 MiB |
| クエリ2 | 1.419秒 | 329.04百万 | 546.75 MiB |
| クエリ3 | 1.414秒 | 329.04百万 | 451.53 MiB |
クエリが達成する目的をもう少し理解しましょう。
- クエリ1は、時速30マイルを超える乗車の距離分布を計算します。
- クエリ2は、週ごとの乗車の合計数および平均コストを求めます。
- クエリ3は、データセット内の各旅行の平均時間を計算します。
これらのクエリはいずれも非常に複雑な処理を行っているわけではありませんが、最初のクエリは、クエリが実行されるたびにトリップタイムをその場で計算しています。しかし、これらの各クエリは1秒以上の実行時間を要しており、ClickHouseの世界では非常に長い時間です。また、これらのクエリのメモリ使用量も注意が必要です。各クエリのメモリ使用量は約400 MBであり、これはかなりの量です。また、各クエリは同じ行数（329.04百万）を読み取っているようです。このテーブルに行がいくつあるかを確認してみましょう。
テーブルには329.04百万行含まれているため、各クエリはテーブル全体をスキャンしています。
EXPLAIN文
長時間実行しているクエリがいくつかわかったので、これらがどのように実行されるかを理解しましょう。そのために、ClickHouseはEXPLAIN文コマンドをサポートしています。これは、クエリの実行ステージごとの詳細なビューを提供する非常に便利なツールです。クエリを実行せずに全てのステージを確認できます。そのため、非ClickHouseの専門家には圧倒されるかもしれませんが、クエリがどのように実行されるかを洞察するための必須ツールです。
ドキュメントでは、EXPLAIN文が何であるか、クエリ実行を分析するためにどのように使用するかについての詳細なガイドが提供されています。このガイドに記載されている内容を繰り返すのではなく、クエリ実行パフォーマンスのボトルネックを見つけるのに役立ついくつかのコマンドに焦点を当ててみましょう。
EXPLAIN indexes = 1
クエリプランを検査するために、EXPLAIN indexes = 1から始めましょう。クエリプランは、クエリがどのように実行されるかを示すツリーです。クエリの句がどの順序で実行されるかを確認できます。EXPLAIN文によって返されるクエリプランは、下から上へ読むことができます。
まず、長時間実行されるクエリの最初のものを使ってみましょう。
出力はわかりやすいです。クエリは最初にnyc_taxi.trips_small_inferredテーブルからデータを読み取ります。次に、WHERE句が計算された値に基づいて行をフィルタリングするために適用されます。フィルタリングされたデータが集約の準備をして、分位数が計算されます。最後に、結果がソートされて出力されます。
ここでは、プライマリキーが使用されていないことに注意できます。テーブル作成時にプライマリキーを定義しなかったため、ClickHouseはクエリのためにテーブル全体のスキャンを実行しています。
EXPLAIN PIPELINE
EXPLAIN PIPELINEは、クエリの具体的な実行戦略を示します。そこで、ClickHouseが実際にどのように先ほど見た一般的なクエリプランを実行したかを見ることができます。
ここでは、クエリを実行するために使用されるスレッド数（59スレッド）に注目できます。これは高い並列性を示しており、これによりクエリが短時間で実行され、より小さなマシンでは時間がかかる可能性があります。クエリが並列して実行されるスレッドの数は、クエリが消費するメモリの大きさの説明になることがあります。
理想的には、すべての遅いクエリを同じ方法で調査して、不必要で複雑なクエリプランを特定し、各クエリによって読み取られる行数と消費されるリソースを理解することが重要です。
方法論
本番環境で問題のあるクエリを特定するのは難しい場合があります。なぜなら、ClickHouseデプロイメントで同時に実行されているクエリが膨大な数に上る可能性があるからです。
問題が発生しているユーザー、データベース、またはテーブルを知っている場合は、system.query_logsからのフィールドuser、tables、またはdatabasesを使用して検索を絞り込むことができます。
最適化したいクエリを特定したら、それらを最適化するための作業を開始できます。この段階で開発者が犯しがちな一般的な間違いは、同時に複数のことを変更し、アドホックな実験を行い、通常は混合結果になり、最も重要なことは、クエリを速くした要因を十分に理解しないことです。
クエリ最適化には構造が必要です。高度なベンチマーキングについてではなく、変更がクエリパフォーマンスにどのように影響するかを理解するためのシンプルなプロセスを持つことで、大きな成果を得ることができます。
まず、クエリログから遅いクエリを特定し、潜在的な改善を個別に調査します。クエリをテストする際は、必ずファイルシステムキャッシュを無効にします。
ClickHouseは、異なる段階でクエリパフォーマンスを向上させるためにcachingを活用しています。これはクエリパフォーマンスにとって良いですが、トラブルシューティング中には潜在的なI/Oボトルネックや不適切なテーブルスキーマを隠すことがあります。このため、テスト中はファイルシステムキャッシュをオフにすることをお勧めします。本番環境での設定では、キャッシュをオンにするようにしてください。
潜在的な最適化を特定したら、一つずつ実装することをお勧めします。これにより、パフォーマンスにどのように影響するかをよりよく追跡できます。以下は、一般的なアプローチを説明する図です。
最後に、アウトライヤーに注意してください。ユーザーがアドホックな高コストのクエリを試みたり、システムが他の理由でストレス下にあるためにクエリが遅く実行されることは非常に一般的です。field normalized_query_hashでグループ化して、定期的に実行される高コストのクエリを特定できます。これらは調査したいクエリである可能性が高いです。
基本的な最適化
フレームワークが整ったので、最適化を始めることができます。
最初に見るべき場所は、データがどのように保存されるかです。あらゆるデータベースにおいて、読み取るデータが少ないほど、クエリは迅速に実行されます。
データを取り込む方法によっては、ClickHouseのcapabilitiesを活用して、取り込んだデータに基づいてテーブルスキーマを推測しているかもしれません。これは非常に便利ですが、クエリパフォーマンスを最適化したい場合は、使用ケースに最適なようにデータスキーマを見直す必要があります。
Nullable
ベストプラクティスドキュメントに記載されているように、可能な限りNullableカラムは避けてください。これらはデータ取り込み機構を柔軟にするために便利ですが、実行されるたびに追加のカラムを処理する必要があるため、性能に悪影響を及ぼします。
NULL値を持つ行をカウントするSQLクエリを実行すると、実際にNullable値が必要なテーブル内のカラムを簡単に発見できます。
NULL値を持つカラムはmta_taxとpayment_typeの2つだけであり、残りのフィールドはNullableカラムを使用する必要はありません。
低いカーディナリティ
文字列に適用する簡単な最適化は、LowCardinalityデータ型を最大限に利用することです。低カーディナリティのドキュメントに記載されているように、ClickHouseはLowCardinalityカラムに辞書コーディングを適用し、クエリパフォーマンスを大幅に向上させます。
LowCardinalityの適用が良いカラムを判断するための簡単なルールは、ユニークな値が1万未満のカラムが理想的な候補です。
ユニークな値が少ないカラムを見つけるための以下のSQLクエリを使用できます。
低カーディナリティを持つこれらの4つのカラムratecode_id、pickup_location_id、dropoff_location_id、およびvendor_idは、LowCardinalityフィールドタイプに最適です。
データ型の最適化
ClickHouseは、多数のデータ型をサポートしています。パフォーマンスを最適化し、ディスク上のデータストレージスペースを削減するために、使用ケースに最適な最小のデータ型を選択してください。
数値については、データセット内の最小および最大値を確認して、現在の精度値がデータセットの実態に合っているかを確認できます。
日付の場合、データセットに応じた精度を選択し、実行予定のクエリに最も適した精度を選択する必要があります。
最適化の適用
最適化スキーマを使用する新しいテーブルを作成し、データを再取り込みましょう。
新しいテーブルを使用してクエリを再実行し、改善を確認します。
| 名称 | Run 1 - 経過時間 | 経過時間 | 処理された行数 | ピークメモリ |
|---|---|---|---|---|
| クエリ1 | 1.699秒 | 1.353秒 | 329.04百万 | 337.12 MiB |
| クエリ2 | 1.419秒 | 1.171秒 | 329.04百万 | 531.09 MiB |
| クエリ3 | 1.414秒 | 1.188秒 | 329.04百万 | 265.05 MiB |
クエリ時間とメモリ使用量に改善が見られます。データスキーマの最適化により、データの総ボリュームが減少し、メモリの消費が改善され、処理時間が短縮されました。
テーブルのサイズを確認して違いを見てみましょう。
新しいテーブルは、前のテーブルに比べてかなり小さいです。テーブルのディスクスペースが約34％削減されています（7.38 GiB対4.89 GiB）。
プライマリキーの重要性
ClickHouseのプライマリキーは、ほとんどの伝統的なデータベースシステムとは異なる働きをします。そのシステムでは、プライマリキーが一意性とデータ整合性を強制します。重複するプライマリキー値を挿入しようとすると拒否され、通常、高速な検索のためにBツリーまたはハッシュベースのインデックスが作成されます。
ClickHouseでは、プライマリキーの目的が異なります。ユニーク性を強制したり、データ整合性に貢献するのではなく、クエリパフォーマンスの最適化を目的としています。プライマリキーは、データがディスクに保存される順序を定義し、各グラニュールの最初の行へのポインタを保存するスパースインデックスとして実装されます。
ClickHouseにおけるグラニュールは、クエリ実行中に読み取られる最小のデータ単位です。これには、index_granularityによって決定される固定数の行が含まれ、デフォルト値は8192行です。グラニュールは連続して格納され、プライマリキーによってソートされます。
良いプライマリキーのセットを選択することはパフォーマンスにとって重要であり、特定のクエリセットを高速化するために、同じデータを異なるテーブルに格納し、異なるプライマリキーのセットを使用することは一般的です。
ClickHouseがサポートする他のオプション、たとえばProjectionやMaterialized Viewにより、同じデータに異なるプライマリキーのセットを使用できます。このブログシリーズの第2部で、これについてさらに詳しく説明します。
主キーの選択
正しい主キーのセットを選択することは複雑なトピックであり、最適な組み合わせを見つけるためにはトレードオフや実験が必要になることがあります。
今のところ、次の簡単な実践に従います：
- ほとんどのクエリでフィルタリングに使用されるフィールドを使用する
- 最初にカーディナリティの低いカラムを選択する
- 主キーに時間ベースの要素を考慮する。タイムスタンプデータセットで時間によるフィルタリングが非常に一般的だからです。
私たちのケースでは、次の主キーを使用して実験します: passenger_count, pickup_datetime, および dropoff_datetime。
passenger_count のカーディナリティは小さく（24のユニークな値）、遅いクエリで使用されています。また、よくフィルタリングされるため、タイムスタンプフィールド（pickup_datetime と dropoff_datetime）も追加します。
主キーを使用して新しいテーブルを作成し、データを再取得します。
その後、クエリを再実行します。3つの実験からの結果をまとめ、経過時間、処理された行数、およびメモリ消費量の改善を確認します。
| クエリ 1 | |||
|---|---|---|---|
| 実行 1 | 実行 2 | 実行 3 | |
| 経過時間 | 1.699 秒 | 1.353 秒 | 0.765 秒 |
| 処理された行数 | 3.2904 百万 | 3.2904 百万 | 3.2904 百万 |
| ピークメモリ | 440.24 MiB | 337.12 MiB | 444.19 MiB |
| クエリ 2 | |||
|---|---|---|---|
| 実行 1 | 実行 2 | 実行 3 | |
| 経過時間 | 1.419 秒 | 1.171 秒 | 0.248 秒 |
| 処理された行数 | 3.2904 百万 | 3.2904 百万 | 41.46 百万 |
| ピークメモリ | 546.75 MiB | 531.09 MiB | 173.50 MiB |
| クエリ 3 | |||
|---|---|---|---|
| 実行 1 | 実行 2 | 実行 3 | |
| 経過時間 | 1.414 秒 | 1.188 秒 | 0.431 秒 |
| 処理された行数 | 3.2904 百万 | 3.2904 百万 | 276.99 百万 |
| ピークメモリ | 451.53 MiB | 265.05 MiB | 197.38 MiB |
実行時間と使用されたメモリにおいて、全体的に顕著な改善が見られます。
クエリ 2 は主キーの恩恵を最も受けています。生成されたクエリプランが以前とはどのように異なるかを見てみましょう。
主キーのおかげで、テーブルのグラニュールのサブセットのみが選択されました。これだけで、ClickHouseが処理するデータが大幅に少なくなるため、クエリパフォーマンスが大幅に向上します。
次のステップ
このガイドが、ClickHouseで遅いクエリを調査する方法や、それを速くする方法について良い理解を得るのに役立つことを願っています。このトピックについてさらに探求したい場合は、クエリアナライザーやプロファイリングについてさらに読むことで、ClickHouseがどのようにあなたのクエリを実行しているかをより良く理解できます。
ClickHouseの特性に慣れていくにつれて、パーティショニングキーやデータスキッピングインデックスについて読むことをお勧めします。これにより、クエリを加速させるために使用できるより高度な技術について学ぶことができます。