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

S3の挿入および読み取りパフォーマンスの最適化

このセクションでは、s3 テーブル関数を使用して、S3からデータを読み込み、挿入するときのパフォーマンスを最適化する方法に焦点を当てます。

参考

このガイドに記載されているレッスンは、GCSやAzure Blob ストレージのような独自の専用テーブル関数を持つ他のオブジェクトストレージ実装に適用できます。

スレッドやブロックサイズを調整して挿入パフォーマンスを向上させる前に、ユーザーはS3挿入のメカニズムを理解することをお勧めします。挿入メカニズムに精通している場合や、すぐに役立つヒントが必要な場合は、下記の例にスキップしてください。example。

挿入メカニズム（単一ノード）​

ハードウェアサイズに加えて、ClickHouseのデータ挿入メカニズム（単一ノード）のパフォーマンスとリソース使用に影響を与える主な要因は2つです：挿入ブロックサイズと挿入の並列性。

挿入ブロックサイズ​

ClickHouseにおける挿入ブロックサイズメカニズム

INSERT INTO SELECTを実行すると、ClickHouseはデータの一部を受け取り、①受信したデータから（少なくとも）1つのメモリ内挿入ブロック（パーティショニングキーごとに）を形成します。ブロックのデータはソートされ、テーブルエンジン固有の最適化が適用されます。データは圧縮された後、②新しいデータパーツの形でデータベースストレージに書き込まれます。

挿入ブロックサイズは、ClickHouseサーバのディスクファイルI/O使用量とメモリ使用量の両方に影響を与えます。大きな挿入ブロックはより多くのメモリを使用しますが、初期パーツが大きくなり、数が少なくなります。ClickHouseが大量のデータをロードするために作成する必要があるパーツが少ないほど、ディスクファイルI/Oや自動バックグラウンドマージが必要になります。

INSERT INTO SELECTクエリを統合テーブルエンジンまたはテーブル関数と組み合わせて使用する場合、データはClickHouseサーバによって取得されます：

ClickHouseにおける外部ソースからのデータ取得

データが完全に読み込まれるまで、サーバはループを実行します：

①では、サイズは挿入ブロックサイズに依存し、2つの設定で制御できます：

挿入ブロックに指定された行数が収集されるか、設定されたデータ量に達した（どちらか早く発生した方）場合、新しいパートに書き込まれるトリガーとなります。挿入ループはステップ①で続行されます。

min_insert_block_size_bytesの値は、圧縮されていないメモリ内ブロックサイズを示すことに注意してください（圧縮されたディスク上のパートサイズではない）。また、生成されたブロックやパーツは、ClickHouseがデータを行-ブロック単位でストリーミングおよび処理を行うため、設定された行数またはバイトの数を正確に含むことはあまりありません。このため、これらの設定は最小しきい値を指定します。

マージに注意する​

設定されている挿入ブロックサイズが小さいほど、大量のデータロードに対してより多くの初期パーツが作成され、データ取り込みと同時にバックグラウンドパートマージがより多く実行されます。これにより、リソースの競合（CPUとメモリ）が発生し、取り込みが終了した後に（健全な状態である3000個のパーツ）に達するのに追加の時間が必要になる場合があります。

参考

パート数が推奨リミットを超えると、ClickHouseのクエリパフォーマンスが悪化します。

ClickHouseは、より大きなパーツに継続的にマージを行い、達するまで約150 GiBの圧縮サイズにします。この図は、ClickHouseサーバがどのようにパーツをマージするかを示しています：

ClickHouseにおけるバックグ��ラウンドマージ

単一のClickHouseサーバは、いくつかのバックグラウンドマージスレッドを使用して並行してパートマージを実行します。各スレッドはループを実行します：

増加する CPUコア数とRAMのサイズは、バックグラウンドマージスループットを増加させます。

より大きなパーツにマージされたパーツは非アクティブとしてマークされ、最終的には構成可能な分の時間が経過した後に削除されます。これは、時間の経過とともにマージされたパーツのツリーを作成します（これがMergeTreeテーブルの名前の由来です）。

挿入の並列性​

挿入の並列性におけるリソース使用量

ClickHouseサーバは、データを並行して処理し、挿入することができます。挿入の並列性レベルは、ClickHouseサーバの取り込みスループットとメモリ使用量に影響を与えます。データを並行してロードおよび処理するためには、より多くのメインメモリが必要ですが、データがより早く処理されるため、取り込みスループットが向上します。

s3のようなテーブル関数では、グロブパターンを介して読み込むファイル名のセットを指定できます。グロブパターンが複数の既存ファイルに一致する場合、ClickHouseはこれらのファイル間およびファイル内での読み取りを並列化し、サーバごとに並行して挿入スレッドを利用してテーブルにデータを挿入します：

ClickHouseにおける並行挿入スレッド

すべてのファイルからのデータが処理されるまで、各挿入スレッドはループを実行します：

このような並列挿入スレッドの数は、max_insert_threads設定で構成できます。デフォルト値は、オープンソースのClickHouseでは1、ClickHouse Cloudでは4です。

大量のファイルがある場合、複数の挿入スレッドによる並列処理がうまく機能します。これは、利用可能なCPUコアとネットワーク帯域幅（並行したファイルダウンロードのため）を完全に飽和させることができます。少数の大きなファイルをテーブルにロードするシナリオでは、ClickHouseは自動的に高いデータ処理の並列性を確立し、大きなファイル内の異なる範囲を並行して読み取るために、各挿入スレッドごとに追加のリーダースレッドを生成してネットワーク帯域幅の使用を最適化します。

s3関数およびテーブルの個別ファイルの並列ダウンロードは、max_download_threadsおよびmax_download_buffer_sizeの値によって決まります。ファイルのサイズが2 * max_download_buffer_sizeより大きい場合にのみ、ファイルは並行してダウンロードされます。デフォルトでは、max_download_buffer_sizeは10MiBに設定されています。場合によっては、バッファサイズを50MB（max_download_buffer_size=52428800）に安全に増加させ、各ファイルが1つのスレッドによってダウンロードされることを確実にすることができます。これにより、各スレッドがS3コールを行う時間が短縮され、S3の待機時間も短縮されます。さらに、並行して読み取るには小さすぎるファイルの場合、ClickHouseは自動的にデータをプリアンシェンシを使用して先読みします。

パフォーマンスの測定​

S3テーブル関数を使用したクエリのパフォーマンスを最適化する必要があります。これは、データがそのまま使用される場合、つまりClickHouseコンピュートのみが使用され、データがS3内にそのままの形式で残る場合、またはS3からClickHouseのMergeTreeテーブルエンジンにデータを挿入する場合に行われます。特に指定がない限り、以下の推奨事項は両方のシナリオに適用されます。

ハードウェアサイズの影響​

ハードウェアサイズがClickHouseのパフォーマンスに与える影響

利用可能なCPUコア数とRAMのサイズは、以下に影響を与えます：

したがって、全体的な取り込みスループットにも影響を及ぼします。

リージョンのローカリティ​

バケットがClickHouseインスタンスと同じリージョンにあることを確認してください。この単純な最適化により、特にAWSインフラストラクチャにClickHouseインスタンスを展開する場合、スループットのパフォーマンスを劇的に改善できます。

フォーマット​

ClickHouseは、s3関数およびS3エンジンを使用して、S3バケットに保存されたファイルをサポートされているフォーマットで読み込むことができます。生のファイルを読み込む場合、これらのフォーマットの中には明確な利点があるものもあります：

  • Native、Parquet、CSVWithNames、TabSeparatedWithNamesのようなエンコードされたカラム名を持つフォーマットは、ユーザーがs3関数でカラム名を指定する必要がないため、クエリが冗長になりません。カラム名により、この情報が推測可能になります。
  • フォーマットによって、読み取りおよび書き込みのスループットに関するパフォーマンスが異なります。NativeおよびParquetは、すでに列指向であり、よりコンパクトであるため、読み取りパフォーマンスに最も最適なフォーマットです。Nativeフォーマットは、ClickHouseがメモリ内にデータを格納する方法と整合しているため、データがClickHouseにストリーミングされる際の処理オーバーヘッドを削減することにも利点があります。
  • ブロックサイズはしばしば大きなファイルの読み取りのレイテンシに影響を与えます。これは、データをサンプリングする場合、例えば、上位N行を返すだけの場合には非常に顕著です。CSVやTSVのようなフ ォーマットでは、行のセットを返すためにファイルを解析する必要があります。しかし、NativeやParquetのようなフォーマットでは、結果としてより早くサンプリングすることができます。
  • 各圧縮フォーマットには長所と短所があり、パフォーマンスによるバイアスを持つ圧縮レベルのバランスをとります。生のファイル（CSVやTSVなど）を圧縮する場合、lz4は圧縮レベルを犠牲にしても最も速い解凍パフォーマンスを提供します。Gzipは通常、わずかに遅い読み取り速度の代償に、より良い圧縮を提供します。Xzはさらに進んで、通常は最も良い圧縮を提供しますが、最も遅い圧縮および解凍パフォーマンスを持っています。エクスポートの場合、Gzとlz4は comparable圧縮速度を提供します。これを接続速度と比較して調整してください。解凍や圧縮のスピードからの利得は、S3バケットへの接続が遅いと簡単に相殺されます。
  • NativeやParquetのようなフォーマットは通常、圧縮のオーバーヘッドを正当化することはありません。データサイズの削減はこれらのフォーマットが本質的にコンパクトであるため、最小限になる可能性が高いです。圧縮と解凍にかかる時間は、ネットワーク転送時間を相殺することはほとんどありません。特にS3はグローバルに利用可能で、高いネットワーク帯域幅を持つためです。

例データセット​

さらなる最適化の可能性を示すため、Stack Overflowデータセットの投稿を使用します。これは、このデータのクエリと挿入パフォーマンスを最適化する目的があります。

このデータセットは、2008年7月から2024年3月までの毎月の1つのParquetファイルから構成されています。

パフォーマンスのためにParquetを使用し、前述の推奨事項に従って、バケットと同じリージョンにあるClickHouseクラスタで全てのクエリを実行します。このクラスタは3ノードで構成され、各ノードには32GiBのRAMと8 vCPUがあります。

チューニングなしで、このデータセットをMergeTreeテーブルエンジンに挿入するパフォーマンスと、最も多くの質問をしたユーザーを計算するクエリを実行するパフォーマンスを示します。これらのクエリは、意図的にデータの完全なスキャンを必要とします。

この例では、いくつかの行だけを返します。大量のデータがクライアントに戻される場合のSELECTクエリのパフォーマンスを測定する場合、クエリでnullフォーマットを利用するか、結果をNullエンジンに直接送ることを検討してください。これにより、クライアントがデータで圧倒されることやネットワーク飽和を避けることができます。

参考

クエリから読み取りを行うとき、初期クエリは同じクエリを繰り返すよりも遅く見えることがあります。これはS3自身のキャッシュだけでなく、ClickHouseスキーマ推論キャッシュにも起因しています。これは、ファイルの推論されたスキーマを格納し、次回のアクセス時に推論ステップをスキップできるため、クエリ時間を短縮します。

読み取り専用スレッドの使用​

S3での読み取りパフォーマンスは、コア数に比例してスケールします。ただし、ネットワーク帯域幅やローカルI/Oが制限されていない場合に限ります。スレッド数を増やすこともメモリオーバーヘッドの組み合わせを持つため、ユーザーは注意が必要です。以下を変更して、読み取りスループットパフォーマンスを改善できます：

  • 通常、max_threadsのデフォルト値は十分であり、つまりコア数です。クエリで使用されるメモリ量が多い場合、これを減らす必要がある場合や、結果に対するLIMITが少ない場合、この値を低く設定できます。メモリが十分にあるユーザーは、S3からの読み取りスループットを向上させるために、この値を増やして実験することを希望するかもしれません。通常、これはコア数が少ないマシン（すなわち、< 10）でのみ有効です。他のリソース（ネットワークやCPU競合など）がボトルネックとなるため、さらに並列化から得られる利益は一般的に減少します。
  • 22.3.1以前のClickHouseでは、s3関数やS3テーブルエンジンを使用した場合にのみ、複数のファイルにわたって読み取りを並列化していました。これには、ユーザーがS3でファイルをチャンクに分割し、グロブパターンを使用して最適な読み取りパフォーマンスを実現する必要がありました。後のバージョンでは、ファイル内でのダウンロードが並列化されるようになりました。
  • スレッド数が少ないシナリオでは、ユーザーはremote_filesystem_read_methodを"read"に設定することで、S3からのファイルの同期読み込みを引き起こすことができるかもしれません。
  • s3関数およびテーブルにおける個別ファイルの並列ダウンロードは、max_download_threadsおよびmax_download_buffer_sizeの値によって決まります。[max_download_threads](/operations/settings/settings#max_download_threads)は使用するスレッドの数を制御しますが、ファイルのサイズが2 * max_download_buffer_sizeより大きくない限り、ファイルは並行してダウンロードされません。デフォルトでは、max_download_buffer_sizeは10MiBに設定されています。場合によっては、バッファサイズを50MB（max_download_buffer_size=52428800）に安全に増加させ、小さなファイルが1つのスレッドによってのみダウンロードされるようにできます。これにより、各スレッドがS3コールを行う時間が短縮され、S3の待機時間も低下します。この点に関しては、このブログ投稿を参照してください。

パフォーマンスを向上させるための変更を加える前に、適切に測定を行ってください。S3 APIコールはレイテンシに敏感で、クライアントのタイミングに影響を与える可能性があるため、パフォーマンスメトリックの取得にはクエリログを使用してください。すなわち、system.query_logを参照してください。

前述のクエリを考慮し、max_threadsを16（デフォルトのmax_threadはノードのコア数です）に倍増させると、メモリが増える代償として読み取りクエリのパフォーマンスが2倍になります。max_threadsをさらに増やすことは、次のようにリターンが減少します。

挿入用のスレッドとブロックサイズの調整​

最大のデータ取り込み性能を達成するには、（1）挿入ブロックサイズ、（2）利用可能なCPUコアおよびRAMの量に基づいた適切な挿入の並列性レベルを選択する必要があります。要約すると：

これら2つのパフォーマンス要因の間には競合するトレードオフがあります（加えてバックグラウンドパートマージとのトレードオフもあります）。ClickHouseサーバーのメインメモリの量には限りがあります。ブロックが大きくなればなるほど、メインメモリをより多く使用し、それにより利用可能な挿入並列スレッドの数が制限されます。逆に、より高い数の並列挿入スレッドは、メモリをより多く必要とします。挿入スレッドの数が、メモリ内で同時に作成される挿入ブロックの数を決定するためです。これにより挿入ブロックの可能なサイズが制限されます。さらに、挿入スレッドとバックグラウンドマージスレッドの間でリソース競合が発生する可能性があります。設定された挿入スレッドの数が（1）マージする必要があるパーツが増え、（2）バックグラウンドマージスレッドからCPUコアとメモリ空間を奪います。

これらのパラメータの動作がパフォーマンスとリソースに与える影響についての詳細な説明については、このブログ投稿を読むことをお勧めします。 このブログ投稿では、チューニングは2つのパラメータの慎重なバランスを伴う場合があると説明されています。この徹底的なテストは非常に実用的ではなく、要するに、次のようにお勧めします：

この式を使用して、min_insert_block_size_rowsを0（行ベースのしきい値を無効にするため）に設定し、max_insert_threadsを選択した値に設定し、min_insert_block_size_bytesを上記の式から計算した結果に設定できます。

先ほどのStack Overflowの例を使用して、この式を使用します。

  • max_insert_threads=4（ノードあたり8コア）
  • peak_memory_usage_in_bytes - 32 GiB（ノードリソースの100％）または34359738368バイト。
  • min_insert_block_size_bytes = 34359738368/(3*4) = 2863311530

設定を調整することで、挿入パフォーマンスが33％以上向上したことが示されています。読者は、単一ノード性能をさらに改善できるかどうか試してみてください。

リソースとノードとのスケーリング​

リソースとノードとのスケーリングは、読み取りおよび挿入クエリの両方に適用されます。

垂直スケーリング​

これまでの全てのチューニングとクエリは、ClickHouse Cloudクラスタ内の単一ノードを使用して実行されてきました。ユーザーはたいていClickHouseのノードが複数あります。まずは、ユーザーが垂直にスケールすることを推奨し、コア数に応じてS3スループットを線形に改善します。以前の挿入および読み取りクエリを、適切な設定を使用してリソースが2倍のClickHouse Cloudノードで繰り返すと、両方のクエリが約2倍の速度で実行されます。

注記

個別のノードが、ネットワークやS3 GETリクエストによってボトルネックになることもあり、垂直スケーリングのパフォーマンスの線形スケーリングを妨げる場合があります。

水平スケーリング​

最終的に、ハードウェアの可用性とコスト効率のために水平スケーリングが必要です。ClickHouse Cloudでは、プロダクションクラスターは少なくとも3ノードで構成されています。したがって、ユーザーは挿入にすべてのノードを利用したいと考えるかもしれません。

S3読み込みのためにクラスターを利用するには、クラスターの利用で説明されているようにs3Cluster関数を使用する必要があります。これにより、読み取りがノード間で分配されます。

最初に挿入クエリを受け取ったサーバは、最初にグロブパターンを解決し、その後、各一致するファイルの処理を自身と他のサーバに動的に分配します。

ClickHouseにおけるs3Cluster関数

以前の読み取りクエリを繰り返す際に、ワークロードを3ノードに分配し、クエリをs3Clusterを使用するように調整します。ClickHouse Cloudでは、この操作が自動的に行われ、defaultクラスタを参照します。

クラスターの利用で指摘されているように、この作業はファイルレベルで分散されています。この機能を利用するためには、ユーザーは十分な数のファイル、つまりノードの数よりも多い必要があります。

同様に、挿入クエリも分配できます。これは、単一ノードのために特定された改善された設定を使用します。

読者は、ファイルの読み込みがクエリを改善しましたが、挿入パフォーマンスには改善が見られないことに注意するでしょう。デフォルトでは、読み取りはs3Clusterを使用して分散されますが、挿入はイニシエーター ノードに対して行われます。これは、すべてのノードで読み取られるのに対し、結果となる行がイニシエーターに送信されるためです。高スループットシナリオでは、これがボトルネックになる可能性があります。この問題に対処するためには、s3cluster関数のparallel_distributed_insert_selectパラメータを設定します。

これをparallel_distributed_insert_select=2に設定すると、SELECTとINSERTが各ノードの分散エンジンの基盤テーブルから各シャードに対して実行されることが保証されます。

予想通り、これにより挿入パフォーマンスが3倍低下します。

さらなる調整​

デュプリケート除去の無効化​

挿入操作は、タイムアウトなどのエラーにより失敗することがあります。挿入が失敗した場合、データが正常に挿入されたかどうかは明確ではありません。クライアントによって挿入を安全に再試行できるように、ClickHouse Cloudのような分散デプロイメントでは、デフォルトでClickHouseはデータがすでに正常に挿入されたかどうかを判断しようとします。挿入されたデータが重複としてマークされている場合、ClickHouseはそれを宛先テーブルに挿入しません。ただし、ユーザーにはデータが通常のように挿入されたかのように、正常な操作ステータスが返されます。

この動作は、クライアントやバッチからデータを読み込むときには意味がありますが、オブジェクトストレージからのINSERT INTO SELECTを実行する場合には不必要な場合があります。この機能を挿入時に無効にすることで、パフォーマンスを向上させることができます。以下に示します：

挿入時の最適化​

ClickHouseでは、optimize_on_insert設定は、データパーツが挿入プロセス中にマージされるかどうかを制御します。有効にすると（デフォルトでoptimize_on_insert = 1）、小さなパーツが挿入される際に大きなパーツにマージされ、読み取る必要のあるパーツの数が減ることでクエリ性能が向上します。ただし、このマージは挿入プロセスにオーバーヘッドを追加し、高スループットの挿入が遅くなる可能性があります。

この設定を無効にすると（optimize_on_insert = 0）、挿入中のマージをスキップし、特に頻繁な小さな挿入を扱う場合にデータがより迅速に書き込まれるようになります。マージプロセスはバックグラウンドに遅延されるため、より良い挿入性能が得られますが、一時的に小さなパーツの数が増加し、バックグラウンドマージが完了するまでクエリが遅くなることがあります。この設定は、挿入性能が優先され、バックグラウンドマージプロセスが後で効果的に最適化を処理できるときに理想的です。以下に示すように、設定を無効にすることで挿入スループットが向上する可能性があります：

その他の注意事項​

  • メモリが少ないシナリオでは、S3への挿入時にmax_insert_delayed_streams_for_parallel_writeを低くすることを検討してください。