Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@jnitin株式会社BeeX

Redshiftクラスタに10TBのデータを入れてみる

Last updated at Posted at 2023-08-03

はじめに

  • Redshiftのra3系ノードに10TBのテストデータを入れた結果をまとめます。
    • あまりこういう検証データが世の中になかったので。
      • かつ、検証で数百ドルを消費したことの所属会社への贖罪も兼ねて。

環境情報

VPC内に立てたクラスタ系Redshift（サーバレスではないほう）で以下の2パターンを検証しました。

  • トライ1

    • ra3.xlplusの1台構成
      • 1台構成の場合、最大で4TBまでストレージを利用可能。
      • 1台でノードを作成すると、リーダーノードとコンピュートノードが1台に同居する形で、合計1台になります。

  • トライ2

    • ra3.xlplusの2台構成
      • 2台構成の場合、最大で64TBまでストレージを利用可能。(詳しくはこちら
      • 2台でノードを作成すると、リーダーノード1台(無料) + コンピュートノード2台で、合計3台になります。

  • 当然、ra3.xlplusの1台構成(4TB上限)で10TBのデータを入れようとしてもストレージに入り切りません。そのあたりもどのような挙動になるか検証してみました。

  • また、ra3系クラスタは従来のdc2系クラスタとはストレージ周りのアーキテクチャが大きく異なります。dc2系だと大きく異なる結果が出るかもしれませんのでご注意ください。

  • その他環境情報

    • リージョン：東京リージョン
    • クラスタのパラメータ
      • デフォルト(自動 WLM)

テストデータ概要

テストデータ詳細

テーブルの行数、特徴

  • 今回のデータセット(10TB)のテーブル名、行数、Redshiftのテーブルパラメータ(key設定)、スキーマ分類を下表にしました。
    • ※スキーマ分類は、TCP-DS公式のPDFに基づいて記載しています。
テーブル名 行数 key設定 スキーマ分類※
call_center 54 diststyle all Dimension
catalog_page 40,000 diststyle all Dimension
catalog_returns 1,440,033,112 distkey(cr_item_sk) sortkey(cr_returned_date_sk) Fact
catalog_sales 14,399,964,710 distkey(cs_item_sk) sortkey(cs_sold_date_sk) Fact
customer 65,000,000 distkey(c_customer_sk) Dimension
customer_address 32,500,000 distkey(ca_address_sk) Dimension
customer_demographics 1,920,800 distkey (cd_demo_sk) Dimension
date_dim 73,049 diststyle all Dimension
household_demographics 7,200 diststyle all Dimension
income_band 20 diststyle all Dimension
inventory 1,311,525,000 distkey(inv_item_sk) sortkey(inv_date_sk) Fact
item 402,000 distkey(i_item_sk) sortkey(i_category) Dimension
promotion 2,000 diststyle all Dimension
reason 70 diststyle all Dimension
ship_mode 20 diststyle all Dimension
store 1,500 diststyle all Dimension
store_returns 2,879,356,097 distkey(sr_item_sk) sortkey(sr_returned_date_sk) Fact
store_sales 28,801,286,459 distkey(ss_item_sk) sortkey(ss_sold_date_sk) Fact
time_dim 86,400 diststyle all Dimension
warehouse 25 diststyle all Dimension
web_page 4,002 diststyle all Dimension
web_returns 720,020,485 distkey(wr_order_number) sortkey(wr_returned_date_sk) Fact
web_sales 7,199,963,324 distkey(ws_order_number) sortkey(ws_sold_date_sk) Fact
web_site 78 diststyle all Dimension
  • テーブルの特徴
    • 最大で288億レコードのテーブル(store_sales)があります。
    • すべてのテーブルにRedshiftの明示的な分散スタイルが設定されています。（つまりデフォルトのAUTO設定ではない）
      • 行数が比較的少なめなディメンションテーブルはRedshiftの分散スタイルが「ALL分散(diststyle all)」となっており、全てのRedshiftノードに同じデータが配置される設計になっています。
      • 一方、行数が多めなファクトテーブルは分散スタイルが「キー分散(distkey)」となっており、JOINで使われるサロゲートキーなどがキーとして指定されています。
    • また、ソートキーもwhere句で使われがちなdate系のサロゲートキーなどに設定されています。
    • 上記のとおり、Redshiftのベストプラクティスに沿った設計になっています。

データの概要

  • 小売販売のデータです。
    • 3つの販売チャネルを持っています。
      • 店舗(store)
      • カタログ(catalog)
      • Web(web)
    • それぞれの販売チャネルで販売(sales)、返品(returns)のデータを持っています。

スキーマ構造

  • 販売履歴や、返品履歴を中心としたスノーフレークスキーマ構造となっています。
  • スノーフレークスキーマなので正規化がかなり行われています。
    • 例えばDateだけを持つディメンジョンテーブル(Date_Dim)、Timeだけを持つディメンジョンテーブル(Time_Dim)などが独立しています。それぞれDate_Dimの場合1行が1日、Time_Dimの場合1行が1秒で構成されています。
      • つまり分析用のETL処理が行われた後のスキーマ構造となっています。ここまで正規化されたテーブルは現実には分析用のデータマートとしても見かけることは少ないのではないかと思います。
    • それぞれのテーブルはサロゲートキーで結合されています。特にファクトテーブルの多くは半分程度のカラムがサロゲートキーで構成されています。データモデリングとしてサロゲートキーを徹底すべきなのかという観点についてはこことかここが個人的に参考になりました。
  • ER図は本記事末尾のAppendixに転記してあります。

結果

COPY完了時間

  • 留意事項
    • 元データはUS-EAST-1のS3上にあります。東京リージョンへの転送オーバーヘッドがわずかに発生します。
    • 元データはgzファイル形式で細分化されています。大きいテーブルだと約100MBのgzファイルが複数存在します。効率よく読み込めるサイズになっていると思われます。そのため、1ファイルがもっと巨大なケースとCOPY速度を比較する際は注意してください。
      • 公式ブログに高速化のコツが記載されています。

        • 1つのテーブルに複数のファイルをロードする場合は、複数のCOPYコマンドではなく、テーブルに対して1つのCOPYコマンドを使用します。 Amazon Redshiftはデータの取り込みを自動的に並列化します。 1つのCOPYコマンドを使用してデータをテーブルにバルクロードすると、クラスタリソースの最適な使用と可能な限り高いスループットが可能となります。

結果

  • ra3.xlplusの 1台構成と2台構成でそれぞれCOPY時間を計測しました。
  • 所要時間が1時間以上かかったものを太字にしています。
  • 記載の時間は目安であり、特に長時間のものはわかりやすいように丸めています。
  • テーブルサイズはデータ投入後、システムテーブルの「svv_diskusage」から取得したものです。
    • おそらく列圧縮がかかった状態のサイズと思われます。
  • テーブル毎に上からCOPY文を順次実行しています。「N/A」は、ストレージが逼迫したためCOPYを実施していないことを示しています。
テーブル名 行数 テーブルサイズ(MB) ra3.xlplus x 1 ra3.xlplus x 2
call_center 54 68 3 seconds 3 seconds
catalog_page 40,000 26 4 seconds 3 seconds
catalog_returns 1,440,033,112 101,290 4 hours 2 hours
catalog_sales 14,399,964,710 1,231,329 Error (29 hours) 13 hours
customer 65,000,000 4,280 4 minutes 2 minutes
customer_address 32,500,000 1,508 1 minutes 41 seconds
customer_demographics 1,920,800 60 8 seconds 8 seconds
date_dim 73,049 62 6 seconds 4 seconds
household_demographics 7,200 16 3 seconds 2 seconds
income_band 20 12 3 seconds 2 seconds
inventory 1,311,525,000 18,828 23 minutes 8 minutes
item 402,000 244 12 seconds 7 seconds
promotion 2,000 44 5 seconds 3 seconds
reason 70 12 4 seconds 2 seconds
ship_mode 20 18 3 seconds 2 seconds
store 1,500 64 5 seconds 3 seconds
store_returns 2,879,356,097 160,714 N/A 3 hours
store_sales 28,801,286,459 1,829,366 N/A 22 hours
time_dim 86,400 26 N/A 3 seconds
warehouse 25 34 N/A 2 seconds
web_page 4,002 34 N/A 2 seconds
web_returns 720,020,485 48,137 N/A 1 hours
web_sales 7,199,963,324 619,858 N/A 6 hours
web_site 78 58 N/A 2 seconds

  • 所感

    • 今回の結果をみる限り、2台構成の場合はCOPY文も早くなることがわかります。
      • 特に大きいテーブルでは分散の効果が顕著であり、完了時間が2倍以上早くなっています。
        • catalog_returnsテーブル、inventoryテーブル等
    • COPY完了時間は行数よりも、データサイズのほうが相関が強いようです。
      • 行数が近しい catalog_returns テーブルと、inventory テーブルを比較。
      • ここでは投入後のテーブルサイズで比較しているため、あくまで参考値となります。元データの合計サイズを測れば、もう少し正確な相関度がわかるかと思います。

  • データ投入後のRedshift使用ストレージ

    • 2台構成で、10TB全てのデータのCOPYが正常に完了したのちマネジメントコンソールを確認すると、以下のように約 4TBと表記されています。これは上記表に記載した「テーブルサイズ」の合計値とおおよそ合致します。
      • image.png

ディスクフルとなるまでの過程

結果と推移

  • ra3.xlplus x 1の構成では、catalog_returnsテーブル(約144億レコード)のCOPY実行後、約29時間後にSQL文が失敗しています。

    • catalog_returnsテーブルは、正常にRedshiftに投入できた場合、Redshift上で約1.2TB(圧縮後)のデータ量を持つテーブルです。それまでCOPYした分を合計しても1.3TB程度ですので、今回の構成におけるRedshiftのストレージ上限の4TBより下回っています。
    • つまりデータ圧縮はCOPYを実施しながら行われているのではなく、データがRedshiftに入った後に行われるので、元データの合計が4TBを越えているものをCOPYするとエラーになると推測されます。
      • このあたりは裏取りしたわけではありません。特にこういったCOPY処理時に、以下の点をもう少し調べたいと思っています。
        • RMSだけでなく、コンピュートノードのローカルSSDキャッシュが使われているのかどうか
        • 分散スタイルによるRMS/ローカルSSDキャッシュ利用への影響

  • COPYクエリ単位での負荷とストレージ容量グラフは以下の通りです。

    • image.png
    • [使用されたストレージ容量]
      • 右端がディスクフルとなった時間です。上端の100%は、ra3の1台構成のため4TBを示しています。
      • グラフの真ん中あたりで、メトリクスが乱高下している箇所があります。推測ですが内部で自動的に圧縮エンコードが発動したのではないかと思われます。
        • 圧縮エンコードはcreate tables時に各カラムにたいして指定することができますが、今回のテーブルは圧縮エンコードを指定していないため、デフォルトの"ENCODE AUTO"に設定されています。
        • 圧縮エンコード後のサイズについてはこちらの記事も参考になります。
    • [CPU使用率]
      • 圧縮エンコードが発動したと思われるタイミングでCPU使用率のスパイクが発生しています。

  • COPY文でディスクフルが発生すると、COPY文がロールバックされます。つまりそのCOPY文で投入しようとしていたデータはすべてRedshiftから削除されます。

    • ストレージ使用率のグラフもロールバックによりメトリクスが急激に下がりますので、過去の時間帯に発生したディスクフルを見逃してしまうことも考えられます。長期間のスケールでストレージ容量のグラフを確認する癖をつけておいたほうがよいと思います。

ディスクフルの判断方法

  • ディスクフルの判断方法について
    • SQLクライアントでエラー発生時までセッションが残っている場合は、SQLクエリの標準エラーとして「ERROR: Disk Full」という文字列が表示されるようです。
      • 私の場合はAWSマネジメントコンソールのRedshift クエリエディタ v2で実行していたため、SQL実行中にマネジメントコンソール自体のセッションが切れてしまい、標準エラー文字列を確認できませんでした。
    • 後から判断する場合は、Redshiftのシステムテーブル[STL_DISK_FULL_DIAG]を見ることで、ディスクフルを引き起こしたクエリのIDを確認することができます。
select '2000-01-01'::timestamp + (currenttime/1000000.0)* interval '1 second' as currenttime,node_num,query_id,temp_blocks from pg_catalog.stl_disk_full_diag;

上記SQLの出力例は以下の通り。

| currenttime                | node_num | query_id | temp_blocks |
|----------------------------|----------|----------|-------------|
| 2023-07-14 11:31:14.603769 | 0        | 6803581  | 2857137     |

まとめ

  • COPYはノード数を増やすと早くなる。
  • COPYによるディスクフルは気づきにくいため、こまめなグラフ確認やSQL文での裏取りを心がける

Appendix.

TCP-DSのER図(公式PDFより)

  • 店舗販売のER図

    • image.png
      • [店舗販売]は[Promotion(販促)]テーブルと連結されているのが特徴かと思います。

  • 店舗返品のER図

    • image.png
      • 店舗販売と異なり店舗返品は、[Reason(返却理由)]テーブルと連結されているのが特徴かと思います。

  • カタログ販売のER図

    • image.png
      • 店舗と異なりカタログは、[Ship_Mode][Call_Center][Warehouse]テーブルと連結されているのが特徴かと思います。

  • カタログ返品のER図

    • image.png

  • Web販売のER図

    • image.png
      • 店舗やカタログと異なりWebは、[Web_Page]テーブルと連結されているのが特徴かと思います。

  • Web返品のER図

    • image.png

  • 在庫のER図

    • image.png

2台構成におけるCOPY文のメトリクス推移

参考までに、載せておきます。

image.png

以上です。

6
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?