メインコンテンツまでスキップ
メインコンテンツまでスキップ

クエリ最適化のためのシンプルなガイド

このセクションでは、analyzer、クエリプロファイリング、またはNullableカラムを避けるなどの異なるパフォーマンスおよび最適化技術を使用する方法を、一般的なシナリオを通じて説明し、ClickHouseのクエリパフォーマンスを改善します。

クエリパフォーマンスの理解​

パフォーマンス最適化を考える最適なタイミングは、データをClickHouseに初めて取り込む前にデータスキーマをセットアップしているときです。

しかし、正直に言うと、データの成長量や実行されるクエリの種類を予測するのは難しいです。

既存のデプロイメントがあり、パフォーマンスを向上させたいクエリがいくつかある場合、最初のステップは、それらのクエリがどのように実行されているか、なぜ一部が数ミリ秒で実行され、他のものは時間がかかるのかを理解することです。

ClickHouseには、クエリがどのように実行され、実行するためにどのリソースが消費されるかを理解するのに役立つ豊富なツールセットがあります。

このセクションでは、それらのツールとその使用方法を見ていきます。

一般的な考慮事項​

クエリパフォーマンスを理解するために、クエリがClickHouseで実行されるときに何が起こるかを見てみましょう。

以下の部分は意図的に簡略化されており、いくつかの省略を行っています。ここでのアイデアは、詳細を詰め込みすぎず、基本的なコンセプトを速やかに把握できるようにすることです。詳細については、クエリアナライザーについて読んでください。

非常に高いレベルの視点から、ClickHouseがクエリを実行すると、以下のことが起こります：

  • クエリの解析と分析

クエリは解析され、分析され、一般的なクエリ実行計画が作成されます。

  • クエリの最適化

クエリ実行計画は最適化され、不必要なデータは剪定され、クエリ計画からクエリパイプラインが構築されます。

  • クエリパイプラインの実行

データは並行して読み取られ、処理されます。この段階では、ClickHouseがフィルタリング、集計、並べ替えなどのクエリ操作を実行します。

  • 最終処理

結果はマージされ、並べ替えられ、クライアントに送信される前に最終結果にフォーマットされます。

実際には、多くの最適化が行われており、このガイドではそれらについてもう少し詳しく説明しますが、今のところ、これらの主要な概念は、ClickHouseがクエリを実行する際に何が裏で起こっているかを理解するのに役立ちます。

この高レベルの理解をもとに、ClickHouseが提供するツールとそれを使用してクエリパフォーマンスに影響を与えるメトリックを追跡する方法を検討してみましょう。

データセット​

クエリパフォーマンスにアプローチする方法を示すために、実際の例を使用します。

NYCのタクシーのデータセットを使用します。このデータセットには、NYCのタクシーの乗車データが含まれています。最初に、最適化なしでNYCタクシーデータセットを取り込みます。

以下は、テーブルを作成し、S3バケットからデータを挿入するためのコマンドです。データからスキーマを自動的に推測することに注意してください。これは最適化されていません。

データから自動的に推測されたテーブルスキーマを見てみましょう。

遅いクエリを見つける​

クエリログ​

デフォルトでは、ClickHouseは実行されたクエリに関する情報をクエリログに収集し、記録します。このデータはテーブルsystem.query_logに保存されます。

実行された各クエリについて、ClickHouseはクエリ実行時間、読み取った行数、CPUやメモリ使用量、ファイルシステムキャッシュヒットなどのリソース使用量などの統計を記録します。

したがって、クエリログは遅いクエリを調査する際の良い出発点です。実行に時間がかかるクエリを簡単に見つけ、それぞれに対するリソース使用情報を表示できます。

NYCタクシーデータセットで、上位5つの長時間実行されるクエリを見つけてみましょう。

query_duration_msフィールドは、その特定のクエリの実行にかかった時間を示します。クエリログの結果を見ると、最初のクエリが2967msの実行時間を要していることが分かります。これは改善可能です。

また、メモリやCPUを最も消費しているクエリを調べることで、システムに負荷をかけているクエリも知りたいかもしれません。

見つかった長時間実行されるクエリを隔離し、応答時間を理解するために数回再実行してみましょう。

この時点で、再現性を向上させるために、enable_filesystem_cache設定を0に設定してファイルシステムキャッシュをオフにすることが重要です。

分かりやすくテーブルにまとめましょう。

名前経過時間処理された行数ピークメモリ
クエリ11.699 秒329.04百万行440.24 MiB
クエリ21.419 秒329.04百万行546.75 MiB
クエリ31.414 秒329.04百万行451.53 MiB

それぞれのクエリの達成する目的をもう少し理解しましょう。

  • クエリ1は、平均速度が30マイルを超える乗車の距離分布を計算します。
  • クエリ2は、週ごとの乗車数と平均コストを見つけます。
  • クエリ3は、データセット内の各乗車の平均時間を計算します。

これらのクエリのいずれも非常に複雑な処理を行っているわけではなく、特に最初のクエリは、クエリが実行されるたびにトリップタイムをその場で計算しています。しかし、これらのクエリはいずれも実行に1秒以上かかっており、ClickHouseの世界では非常に長い時間です。また、これらのクエリのメモリ使用量には、各クエリで約400MBが消費されています。また、各クエリは同じ数の行（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は、キャッシングを活用して、クエリパフォーマンスをさまざまな段階で向上させます。これはクエリのパフォーマンスには良いですが、トラブルシューティング中には、潜在的なI/Oボトルネックや不良なテーブルスキーマを隠蔽する可能性があります。そのため、テスト中はファイルシステムキャッシュをオフにすることをお勧めします。プロダクション環境では有効にしてください。

潜在的な最適化を特定したら、それを一つずつ実装して、パフォーマンスに与える影響をより良く追跡することをお勧めします。以下は、一般的なアプローチを説明するダイアグラムです。

最後に、外れ値に注意してください; ユーザーがアドホックな高コストのクエリを試したり、システムが別の理由でストレスを受けている場合、クエリが遅くなることは非常に一般的です。フィールドnormalized_query_hashでグループ化して、定期的に実行されている高コストのクエリを特定できます。それらはおそらく、調査したいものです。

基本的な最適化​

フレームワークをテストする準備ができたので、最適化を始めましょう。

最適化の最初のステップは、データがどのように保存されているかを確認することです。どのデータベースでも同じですが、読み取るデータが少ないほど、クエリが早く実行されます。

データをどのように取り込んだかによって、ClickHouseの機能を利用して、取り込まれたデータに基づいてテーブルスキーマを推測しているかもしれません。これは始めるには非常に便利ですが、クエリパフォーマンスを最適化したい場合は、データスキーマを再評価して、ユースケースに最適になるよう調整する必要があります。

Nullable​

ベストプラクティス文書で説明されているように、可能な限りNullableカラムは避けるべきです。これらはしばしば使いたくなりますが、データ取り込みメカニズムをより柔軟にする反面、追加のカラムが毎回処理されるため、パフォーマンスに悪影響を与えます。

NULL値を持つ行を数えるSQLクエリを実行すれば、実際にNullable値が必要なカラムを簡単に明らかにすることができます。

NULL値を持つカラムはmta_taxとpayment_typeの2つだけです。残りのフィールドはNullableカラムを使用すべきではありません。

低いカーディナリティ​

文字列に対する簡単な最適化は、LowCardinalityデータ型を最大限に活用することです。LowCardinalityに関する文書で説明されているように、ClickHouseはLowCardinalityカラムに辞書コーディングを適用し、クエリパフォーマンスを大幅に向上させます。

LowCardinalityに適したカラムを判断する簡単なルールは、ユニークな値が10,000未満のカラムは理想的な候補です。

以下のSQLクエリを使用して、ユニークな値が少ないカラムを見つけることができます。

低いカーディナリティを持つこれらの4つのカラム、ratecode_id、pickup_location_id、dropoff_location_id、およびvendor_idは、LowCardinalityフィールドタイプの良い候補です。

データ型の最適化​

ClickHouseは、多くのデータ型をサポートしています。ユースケースに適合する、できるだけ小さなデータ型を選択してパフォーマンスを最適化し、ディスク上のデータストレージスペースを削減してください。

数値の場合は、データセット内の最小/最大値を確認して、現在の精度がデータセットの実際の値に合っているかを確認することができます。

日付の場合は、データセットにマッチする精度を選択し、実行予定のクエリに最適なものを選びましょう。

最適化を適用​

最適化されたスキーマを使用するために新しいテーブルを作成し、データを再取り込みましょう。

新しいテーブルを使用してクエリを再実行して、改善されたかどうかを確認します。

名前初回実行 - 経過時間経過時間処理された行数ピークメモリ
クエリ11.699 秒1.353 秒329.04百万行337.12 MiB
クエリ21.419 秒1.171 秒329.04百万行531.09 MiB
クエリ31.414 秒1.188 秒329.04百万行265.05 MiB

クエリ処理時間とメモリ使用量の改善が見られます。データスキーマの最適化により、データの全体量が減少し、メモリ消費が改善され、処理時間が短縮されました。

テーブルのサイズを確認してみましょう。違いがあるか見てみます。

新しいテーブルは以前のものよりかなり小さくなっています。テーブルのディスクスペースが約34％削減（7.38 GiB対4.89 GiB）されていることが分かります。

プライマリーキーの重要性​

ClickHouseにおけるプライマリーキーは、ほとんどの従来のデータベースシステムとは異なる動作をします。これらのシステムでは、プライマリーキーは一意性とデータの整合性を強制します。重複するプライマリーキー値を挿入しようとすれば、拒否され、通常は高速検索のためにBツリーまたはハッシュベースのインデックスが作成されます。

ClickHouseでは、プライマリーキーの目的が異なり、一意性を強制したり、データの整合性を助けるものではありません。代わりに、クエリパフォーマンスを最適化することを目的としています。プライマリーキーは、ディスク上のデータが保存される順序を定義し、各グラニュールの最初の行へのポインタを保存するスパースインデックスとして実装されます。

ClickHouseのグラニュールは、クエリ実行中に読み取られる最小のデータ単位です。これらは最大で固定数の行を含み、index_granularityによって決定され、デフォルト値は8192行です。グラニュールは連続的に保存され、プライマリキーによってソートされます。

良いプライマリーキーのセットを選択することはパフォーマンスに重要であり、特定のクエリセットを加速するために、異なるテーブルに同じデータを保存し、異なるプライマリーキーを使用することは一般的です。

他にも、ClickHouseがサポートするオプション、プロジェクションやマテリアライズドビューなどは、同じデータに異なるプライマリーキーのセットを使用することを可能にします。このブログシリーズの後半では、これをさらに詳しく説明します。

その後、クエリを再実行します。3つの実験からの結果をまとめて、経過時間、処理された行数、およびメモリ使用量の改善を確認します。 

クエリ 1
実行 1実行 2実行 3
経過時間1.699 sec1.353 sec0.765 sec
処理された行数329.04 million329.04 million329.04 million
ピークメモリ440.24 MiB337.12 MiB444.19 MiB
クエリ 2
実行 1実行 2実行 3
経過時間1.419 sec1.171 sec0.248 sec
処理された行数329.04 million329.04 million41.46 million
ピークメモリ546.75 MiB531.09 MiB173.50 MiB
クエリ 3
実行 1実行 2実行 3
経過時間1.414 sec1.188 sec0.431 sec
処理された行数329.04 million329.04 million276.99 million
ピークメモリ451.53 MiB265.05 MiB197.38 MiB

実行時間と使用メモリの両方で大きな改善が見られます。 

クエリ 2 は主キーの恩恵を最も受けています。クエリプランが以前とどう異なるか見てみましょう。

主キーのおかげで、テーブルのグラニュールのサブセットのみが選択されています。これにより、ClickHouseが処理しなければならないデータ量が著しく減少し、クエリ性能が大幅に向上します。

Next steps​

このガイドが、ClickHouseを使用して遅いクエリを調査し、それらをより高速にする方法についての良い理解を得る助けになることを願っています。このトピックについてさらに探求するには、クエリアナライザーやプロファイリングについて読み、ClickHouseがいかにしてクエリを実行しているかをより深く理解してください。

ClickHouse特有の機能に慣れてきたら、パーティショニングキーやデータスキッピングインデックスについても読んで、クエリを加速するために使用できるより高度なテクニックについて学ぶことをお勧めします。