[レポート] (ANT418) Deep dive and best practices for Amazon Redshift #reinvent

AWS re:Invent 2019

#レポート

#ビッグデータ

#Amazon Redshift

#AWS

石川覚

2019.12.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

本記事はAWS re:Invent 2019のセッションレポートとなります。最新のAmazon RedshiftのDeep dive と best practicesについてのセッションのレポートです。前半はTony GibbsさんがRedshiftの基本やデータストレージ、取り込み、およびETLについて解説した後、後半は、Harshida PatelさんがWLMやサイジングについて解説しています。

概要

In this session, we take an in-depth look at best practices for data warehousing with Amazon Redshift. We show you the best way to analyze all your data, inside and outside your data warehouse, without moving the data, which helps you gain deeper insights for running your business. We also cover best practices for how to design optimal schemas, load data efficiently, and optimize your queries to deliver high throughput and performance.

このセッションでは、Amazon Redshiftを使用したデータウェアハウジングのベストプラクティスを詳しく見ていきます。データを移動することなく、データウェアハウス内外のすべてのデータを分析する最良の方法を示します。これにより、ビジネスを運営するためのより深い洞察を得ることができます。また、最適なスキーマを設計し、データを効率的にロードし、クエリを最適化して高いスループットとパフォーマンスを実現する方法のベストプラクティスも網羅しています。

スピーカー

動画

Deep dive and best practices for Amazon Redshift

アーキテクチャとコンセプト

Amazon Redshiftは、PostgreSQLをベースにAWSクラウド対応と、OLAP、MPP、カラムナを兼ね備えたDWHであり、サービス開始の2013/02から2019/12現在までに、175の重要なパッチ、300の重要な機能が追加されている

Amazon Redshiftのアーキテクチャ

超並列、シェアードナッシング、カラムナアーキテクチャ
リーダーノード
- SQLエンドポイント
- メタデータストア
- 並列SQL処理に合わせる
コンピュートノード
- カラムなストレージを内部に持つ
- 並列でクエリを実行する
- ロード、アンロード、バックアップ、リストア
Amazon Redshift Spectrum nodes
- S3に対してクエリを直接実行する

Amazon Redshift のノードタイプ

Managed Storage - RA3 (new)
- Solid-state disks + Amazon S3
Dense compute - DC2
- Solid-state disks
Dense storage - DS2
- Magnetic disks

カラムナ（列指向）

ディスク上のデータを保存するためにカラムナアーキテクチャを採用している
分析クエリのためのI/Oの削減が目的
データを行ではなくカラムごとに物理的にディスクに保存する
必要に応じてカラムデータのみを読み取る

カラム圧縮

より多くのデータ保存し、I/Oを削減してパフォーマンスを改善する目的
クラスタに２倍から４倍のデータを保存する
COPYコマンドは最初のロードの際にデフォルトで自動的にANALYZEとデータ圧縮する
ANALYZE COMPRESSIONコマンドは既存のテーブルのカラムごとに適切な圧縮エンコードを見つける
新しいAZ64は、高い圧縮率と改善されたクエリ処理を実現するために設計されたAmazon独自の圧縮エンコードアルゴリズム

Best practices: カラム圧縮

すべてのテーブルをカラム圧縮する - INT、SMALLINT、BIGINT、TIMESTAMP、TIMESTAMPTZ、DATE、NUMERICにAZ64が適している - VARCHARとCHARにはLZO/ZSTDが適している
ANALYZE COMPRESSIONコマンドを実行して、最適な圧縮アルゴリズム診断する - 値がないカラムや小さなテーブルはRAW（圧縮なし）が適している
カラムのエンコードを変更するには、テーブルの再構築が必要となる
- Amazon Redshift Column Encoding Utility

カラム圧縮を検証するコマンド:

SELECT "column", type, encoding FROM pg_table_def WHERE tablename = 'deep_dive';
column | type         | encoding
--------+--------------+----------
aid    | integer      | az64
loc    | character(3) | bytedict
dt     | date         | runlength

データブロック

カラムデータは1MBのイミュータブルなブロックに保持する
ブロックは個別にエンコードされる
ブロックには、最大数百万の値を含めることができる

ゾーンマップ

不要なI/Oを排除することが目的
インメモリのブロックメタデータ
- ブロックごとに最大値、最小値を持つ
- 全てのブロックは自動的にゾーンマップに持つ
- クエリに不必要なデータブロックを効果的に読み飛ばす

データソート

ゾーンマップの有効性を高め、I/Oを削減することにより、クエリの実行を高速化することが目的
ゾーンマップを活用して、範囲制限スキャンで不要なブロック読み込みを減らすのに効果的
テーブルに１つ以上のソートキーを定義することで達成できる
最適なソートキーは以下で選択
- クエリパターン
- ビジネス要件
- データ特性

Best practices: ソートキー

頻繁にフィルターされるカラムの中で最も低いカーディナリティーのカラムを最初のソートキーに配置する
- ほとんどのファクトテーブルでは、最初のソートキーカラムは時系列カラムである必要がある
- 高カーディナリティーのカラムのソートキー後に追加された効果がない
以下のスクリプトを使用して、最適なソートキーを見つける
- amazon-redshift-utils/src/AdminScripts/filter_used.sql
- amazon-redshift-utils/src/AdminScripts/predicate_columns.sql
設計上の考慮事項：
- ソートキーは小さなテーブルではあまり効果が期待できない
- 4つ以下のソートキーカラムを定義する、これを超えると取り込みオーバーヘッドの増加が生じる

マテリアライズカラム

ファクトテーブルでゾーンマップを活用することにより、クエリの実行を高速化する
- 頻繁にフィルター処理され、変化しないディメンション値は、ファクトテーブル内で具体化する必要がある
- 時間ディメンションテーブルでは、ファクトテーブルでの範囲が制限されたスキャンは許可されない
- ファクトテーブルで一時的な値を具体化すると、パフォーマンスが大幅に向上する
```
SELECT COUNT(*) FROM fact_dd JOIN dim_dd USING (timeid) WHERE dim_dd.ts >= '2018-11-29'; 
SELECT COUNT(*) FROM fact_dd WHERE fact_dd.timestamp >= '2018-11-29’; -- Faster
```
- 多くの場合、計算値はファクトテーブル内で具体化する必要がある
```
 SELECT COUNT(*) FROM dd WHERE EXTRACT(EPOCH FROM ts) BETWEEN 1541120959 AND 1543520959; 
 SELECT COUNT(*) FROM dd WHERE sorted_epoch BETWEEN 1541120959 AND 1543520959; -- Faster
```

スライス

スライスは仮想コンピュートノードのように考えることができる
- データパーティショニングの単位
- 並列クエリ処理
スライスに関する事実
- コンピュートノード毎に2もしくは16スライス
- テーブルの行はスライスに分散される
- スライスは自身のデータのみ処理する

データの分散

分散スタイルは、テーブルのデータがクラスター全体にどのように分散されるかを決定するテーブルプロパティ
- KEY: 値はハッシュ化、同じ値は同じ場所（スライス）に保存する
- ALL: すべてのデータはノードごとの最初のスライスに保存する
- EVEN: ラウンドロビン
- AUTO: EVENとALLの組み合わせ
目的
- 並列処理のためにデータを均等に分散する
- 並列処理している間のデータの移動を最小にする
分散キー
- 分散スタイルKEY
  - ON句で使用されるカラムに分散することにより、大きなテーブル間のJOINパフォーマンスを最適化する
  - INSERT INTO SELECTパフォーマンスを最適化する
  - GROUP BYパフォーマンスを最適化する
  - 分散されているカラムのカーディナリティは高く、行のスキューは発生しない
- 分散スタイルALL
  - ディメンションテーブルとの結合パフォーマンスを最適化する
  - 小さなテーブルのディスク使用を削減する
  - 小さなもしくは中位のテーブルに設定する（3000行未満）
- 分散スタイルEVEN
  - KEYもALLも適用されない場合
- 分散スタイルAUTO
  - デフォルトの分散タイプ、分散キー ALL and EVENを組み合わせる

Best practices: テーブル設計のサマリ

カラムに圧縮を追加する
- 可能な場合はAZ64を使用し、VARCHARやCHARにはZSTD/LZOを使用する
頻繁にフィルタリングされるカラムにソートキーを追加する
フィルター処理されたカラムをディメンションテーブルからファクトテーブルにマテリアライズする
頻繁に計算される値をテーブルにマテリアライズする
カラムがSkewを引き起こさない場合は、DISTSTYLE KEYを設定して大きなテーブルと同じ場所に配置する
時系列カラムを分散キーに設定しない
データ型を必要な大きさを指定する（但し、必要以上には大きくしない）
- VARCHAR、CHAR、及び NUMERIC

データストレージ、取り込み、およびETL

冗長性

Amazon Redshiftはローカルに接続されたストレージデバイスを利用する
グローバルコミットにより、すべての永続テーブルのブロックがクラスター内の複数のノードに書き込まれ、データの冗長性が確保される
Amazon S3への非同期バックアップブロック - 常にスナップショットは過渡的に一貫している
- 5 GBの変更データまたは8時間ごとに生成されるスナップショット
- ユーザーはオンデマンドの手動スナップショットを作成できる
- テーブルレベルでバックアップを無効化できる：CREATE TABLE example(id int) BACKUP NO;
一時テーブル
- ブロックはリモートパーティションにミラーリングしない
- 書き込みパフォーマンスが2倍速い
- 完全コミットまたはバックアップをトリガーしない

トランザクション

Amazon Redshiftは完全にトランザクション対応のACID準拠のデータウェアハウスです
- 分離レベルは serializable
- 2フェーズコミット（ローカルおよびグローバルコミットフェーズ）
クラスターのコミット統計：
- https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/commit_stats.sql
設計上の考慮事項：
- コミットのオーバーヘッドのため、明示的にトランザクションを作成してコミットを制限する

Best practices: COPY によるデータの取り込み

区切り記号付きファイルが推奨
- 単純な区切り文字'|'を選択しますまたは','または'\t'
- 単純なNULL文字（\N）を選択する
- varcharには二重引用符とエスケープ文字（'\'）を使用する
- UTF-8のvarcharカラムは、１文字ごとに4バイトを使用する
ファイルをAmazon Redshiftクラスター内のスライスの総数の倍数の数に分割する sql SELECT count(slice) from stv_slices;
gzip圧縮後のファイルサイズは1MB〜1GBの必要がある

Amazon Redshift Spectrum

Amazon S3の外部のテーブルからINSERT INTO SELECTを使用する
- 入力データを集約する
- カラムおよび/または行のサブセットを選択
- SQLを使用して入力カラムデータを操作する
- 別のファイル形式でデータをロードする：Amazon ION、Grok、RCFile、およびSequence
ベストプラクティス：
- ELTではなく、クエリとレポートのためにクラスターリソースを保存する
- 入力データのフィルタリング/集約により、COPYよりもパフォーマンスを向上させることができる
設計上の考慮事項：
- Amazon S3に対する繰り返し読み取りはトランザクションではない
- $5/TB（圧縮された）データのスキャン

データ取り込み

大規模な書き込み用に設計
- 大量のデータを処理するために最適化されたバッチ処理システム
- 1MBのイミュータブルなブロックは、断片化が生じないように書き込み時にブロックを複製する
- 小さい書き込み（〜1-10行）には、大きな書き込み（〜10万行）と同様のコストが生じる
UPDATE と DELETE
- イミュータブルなブロックは、UPDATEとDELETEでのみ論理的に行を削除する
- (Auto) VACUUMやディープコピーを用いて無効なレコード領域をテーブルから削除する

Best practices: ELT

明示的なトランザクションでワークフロー/ステートメントをラップする
DELETEの代わりにDROP TABLEまたはTRUNCATEが使えるか検討する
ステージングテーブル：
- 一時テーブルまたはBACKUP NOオプションで永続テーブルを使用する
- 可能であれば、ステージングテーブルとプロダクションテーブルの両方でDISTSTYLE KEYを使用して、INSERT INTO SELECTステートメントを高速化する
- COPYを使用刷る時に自動圧縮を無効にする（COMPUPDATE OFF）
- 実稼働テーブルから圧縮設定をコピー（LIKEキーワードを使用）またはCREATE TABLEのDDLに圧縮を手動で適用する（ANALYZE COMPRESSION出力から）
- 大量の行（数億行以上）をコピーするには、INSERT INTO SELECTの代わりにALTER TABLE APPENDの使用を検討する

VACUUM と ANALYZE

VACUUMは、削除済みとしてマークされた行を自動的に削除する
- ソートキーを持つテーブルの場合、取り込み操作は新しいデータをローカルでソートし、ソートされていない領域に書き込む
ANALYZEは、最適なクエリ計画のためにテーブル統計を収集する
ベストプラクティス：
- VACUUMは必要な場合にのみ実行する
  - 大部分のワークロードでは、AUTO VACUUM DELETEはスペースを再利用し、AUTO TABLE SORTはテーブルの必要な部分をソートする
  - ワークロードがわかっている場合
  - VACUUMは手動で実行できます（ETLサイクルの終わり、週末など）
  - VACUUM BOOSTを使用する
- 大きなテーブルやカラム数の多いテーブルはディープコピー（データの再作成とコピー）を検討する
ほとんどの場合、AUTO ANALYZEが統計収集を自動的に処理します
ANALYZEは、WHERE述部がフィルターに掛けられているカラムのみで、摂取後に定期的に実行できます。
クラスター内のすべてのテーブルでVACUUMおよびANALYZEを手動で実行するユーティリティ：
- https://github.com/awslabs/amazon-redshift-utils/tree/master/src/AnalyzeVacuumUtility

ワークロードマネジメントとクエリモニタリングルール

ワークロード管理（WLM）

さまざまなクエリワークロードの分離が可能
- 重要なクエリに優先順位を付ける
- 重要度の低いクエリのスロットル/中止
クエリの同時実行数を制御する
クラスタメモリを分割する
クエリタイムアウトを設定して、長時間実行されているクエリを中止する

動的ワークロード管理（Dynamic WLM）

手動WLMの動的プロパティ
- メモリの割合
- 同時実行/キュースロット
- 同時実行性のスケーリング
- クエリタイムアウト
- ショートクエリアクセラレーションを有効にする
- 動的プロパティの変更は、単純なAPI呼び出しのみで再起動を必要しない
動的ワークロード管理ユーティリティ
- https://github.com/awslabs/amazon-redshift-utils/tree/master/src/WorkloadManagementScheduler

自動ワークロード管理（Auto WLM）

WLMを簡素化させ、さまざまなクエリワークロードの優先順位付けが可能

実行中のクエリの同時実行数を自動的に制御する
クラスターメモリを自動的に分割する

クエリモニタリングルール（QMR）

ワークロード管理（WLM）の拡張
想定外のクエリの自動的にハンドリングする
WLMキューに適用されるルールによってクエリは次のようになる
- LOGGED
- ABORTED
- HOPPED
クラスターの無駄な使用に対する保護

同時実行スケーリング（Concurrency scaling）

同時実行スケーリング（Concurrency scaling）は、一貫した高速パフォーマンスで同時リクエストの突然の急増に対応するために、一時的なクラスターを数秒で自動的に追加する機能

Best practices: WLM と QMR

AUTO WLMを使用する
- WLMのセットアップ方法がわからない場合
- ワークロードが非常に予測不能である場合
- 古いデフォルトWLMを使用している場合
手動WLMを使用する
- ワークロードパターンを理解している場合
- 時刻に応じて特定の種類のクエリを調整する必要がある場合
WLMキューの数を最小限に抑え、通常、未使用のキューを避けるために3つだけにする
- https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/wlm_apex_hourly.sql
WLMを使用して、取り込み/ ELT同時実行を2〜3に制限する
クエリスループットを最大化するには、WLMを使用して同時クエリの数を15以下に絞る
WLMではなくQMRを使用してクエリタイムアウトを設定する
QMRを使用して長時間実行されているクエリを記録する
管理タスクとクエリのキャンセルのためにスーパーユーザーキューを用いる

クラスタのサイジングとリサイズ

本番稼働用のAmazon Redshiftクラスターのサイジング

受信データの非圧縮サイズを推定する
3倍の圧縮を想定（実際は4倍以上）
30％〜40％の空き領域を確保する（必要に応じてサイズを変更してストレージを追加/削除する）
- ディスク使用率は15％以上80％未満でなければなりません。
パフォーマンス要件に基づいて、SSDまたはHDDを選択する
- 必要に応じてノードを追加してパフォーマンスを向上させることができる
例：
- 20TBの非圧縮データ〜= 6.67TB圧縮
- パフォーマンス要件や要件に基づく推奨：
- 2xRA3.4xlarge または 4xDC2.8xlarge または 5xDS2.xlarge =最大10TBの容量

Classic Resize

データは古いクラスターから新しいクラスターに転送される（数時間以内）
ノードタイプの変更

Elastic Resize

既存のクラスターのノード追加/削除する（数分以内）

Elastic resize のノードの増減は、インスタンスタイプによって異なります。

Classic Resize と Elastic Resize の使い分け

Elastic Resizeは、ワークロードの急上昇に対応したスケールアップとダウンやストレージを段階的に追加/削除したい場合に向いています。一方、Classic Resizeは、インスタンスタイプの変更やサイズ制限のためにElastic Resizeができない場合に選択します。Elastic Resizeは、データの移動が少なくて済むため、サイズ変更中の時間が少なくて済みます。

Best practices: Cluster sizing

本番環境では、データミラーリングのために少なくとも2つのコンピュートノード（マルチノードクラスター）を使用する
- リーダーノードは追加費用なしで提供する
少なくとも20％の空き領域、または最大のテーブルの3倍のサイズを維持する
- 使用のためのスクラッチ領域、テーブルの書き換え
- VACUUMがテーブルを再ソートするには空きスペースが必要
- 中間クエリ結果に使用される一時テーブル
利用可能なAmazon Redshift Spectrumノードの最大数は、Amazon Redshiftクラスター内のスライス数
DS2インスタンスを使用している場合は、RA3に移行
DC1インスタンスを使用している場合は、DC2インスタンスタイプにアップグレードする
- DC1と同じ価格で大幅に高速化
- リザーブドインスタンスは、AWSコンソールで追加費用なしで移行できる

Amazon Redshift Advisor

Amazon Redshift Consoleで利用可能なAmazon Redshift Advisor
運用メタデータを毎日スキャンする
ベストプラクティスの観点で観察する
パフォーマンスとコスト削減のために、Amazon Redshiftクラスターを最適化するために、カスタマイズされた影響の大きい推奨事項を提供する

その他の情報

AWSLabs on GitHub - Amazon Redshift

https://github.com/awslabs/amazon-redshift-utils

https://github.com/awslabs/amazon-redshift-monitoring

https://github.com/awslabs/amazon-redshift-udfs

Admin scripts
- クラスターを診断するためのユーティリティ集
Admin views
- クラスターの管理、スキーマDDLの生成などのユーティリティ集
Analyze Vacuum utility
- クラスター内のテーブルにVACUUMやANALYZEをスケジュールするユーティリティ
Column Encoding utility
- データがすでにロードされている確立されたスキーマに最適なエンコードを適用するユーティリティ

AWS Big Data Blog - Amazon Redshift

最後に

この2年間で、Redshift Spectrum〜Concurrency Scaling〜Elastic Resize〜自動メンテナンス・自動チューニング〜など目まぐるしい進化を遂げています。このセッションでは、RA3やAQUAを除き、全てのテーマを取り扱っていますので、説明不足が否めない点がありますが、RedshiftのPoCや移行が終わり、次のステップに進みたい方には完結にまとめられた「良い」セッションだと思います。