はじめに

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)

テストデータ概要

今回利用するテストデータはTPC-DSをベースにクラウドデータウェアハウス用にカスタマイズしたAWS公式データです。バージョンは 2.13 です。
- https://github.com/awslabs/amazon-redshift-utils/tree/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCDS
TCP-DSとは
- データ分析基盤向けのスノーフレークスキーマで構成されたベンチマーク用データセットです。
- TCP-DSについては以下がわかりやすいかと思います。
  - https://qiita.com/manabian/items/93d9330cdb607383c2b9
  - https://www.slideshare.net/kaigai/tpcdspostgresql/5
- 公式情報は以下です。ER図も載っています。
  - https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.1.0.pdf
- BigQueryでTCP-DSのベンチマークを行った結果などもここにありました。
  - データサイズは今回の私の検証より小さい2TBになっています。
  - BigQueryで利用しているデータもBigQuery用にカスタマイズされたTPC-DSであるため、Redshift用の検証と似てはいるものの比較はできません。
  - ただcastや結合、一部クエリの差はあれど、AWSのものと大きな差はないように見えました。(sqlはこちら)
今回使用したデータ
- 今回はAWSが提供するデータセットのうち、10TBのものを選び、以下のDDL文を実行しテーブルを作成しました。
  - https://github.com/awslabs/amazon-redshift-utils/blob/master/src/CloudDataWarehouseBenchmark/Cloud-DWB-Derived-from-TPCDS/10TB/ddl.sql
- テストデータのソースはUS-EAST-1のS3にあります。
  - 今回のように東京リージョンのクラスタで試す場合は、リージョン間通信の時間的オーバーヘッドと、転送コストがかかります。
  - 直接データを確認する場合は、AWSマネジメントコンソールログイン後、こちらのページを確認ください。

テストデータ詳細

テーブルの行数、特徴

今回のデータセット(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と表記されています。これは上記表に記載した「テーブルサイズ」の合計値とおおよそ合致します。

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

結果と推移

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クエリ単位での負荷とストレージ容量グラフは以下の通りです。
- [使用されたストレージ容量]
  - 右端がディスクフルとなった時間です。上端の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     |

上記のSQL文や、ディスクフルとなった場合の対策などは公式の以下ページに詳しく記載されています。
- https://repost.aws/ja/knowledge-center/redshift-high-disk-usage

まとめ

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

Appendix.

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

店舗販売のER図
- - [店舗販売]は[Promotion(販促)]テーブルと連結されているのが特徴かと思います。
店舗返品のER図
- - 店舗販売と異なり店舗返品は、[Reason(返却理由)]テーブルと連結されているのが特徴かと思います。
カタログ販売のER図
- - 店舗と異なりカタログは、[Ship_Mode][Call_Center][Warehouse]テーブルと連結されているのが特徴かと思います。
カタログ返品のER図
Web販売のER図
- - 店舗やカタログと異なりWebは、[Web_Page]テーブルと連結されているのが特徴かと思います。
Web返品のER図
在庫のER図