テーブル設計チューニング』を実践してみたAWS特集Redshift2014年08月11日しんや(674)134134件のシェア(ちょっぴり話題の記事)この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。Redshiftに於いて『パフォーマンスチューニング』は重要なトピックの1つです。Redshiftクラスタを立ち上げて、データを投入して、実際使ってみたものの思ったような速度・レスポンスが返って来ない...という状況も時折遭遇する事と思います。AWS公式ドキュメント(英語版)を漁ってみると、まさにその『パフォーマンスチューニング』に焦点を当てたチュートリアルが公開されているではありませんか!当エントリではそのドキュメントを参考にひと通り実践してみた内容をまとめてみました。各種手順を1エントリに集約したので超長いエントリとなってしまいましたが、その辺りは目を瞑りつつ実践内容を順を追ってご覧頂ければと思います。Redshift:目次1.テスト用データセットの作成2.ベースラインを作るためのシステムパフォーマンステスト実践3.ソートキーの選定4.分散キーの選定5.圧縮エンコーディングのレビュー6.テスト用データセットの再作成7.システムパフォーマンステスト実践(再)8.結果検証9.リソースのお掃除1.テスト用データセットの作成DWH(データウェアハウス)では一般的に『スタースキーマ』でテーブル設計を行うのが良いとされています。これは、中央に『ファクトテーブル』(分析の中心となるデータ)、ファクトテーブルを囲む形で『ディメンションテーブル』(ファクトテーブルの項目に対する、説明的な詳細属性情報を含むテーブル)で構成される形となります。ファクトテーブルはディメンションテーブルに対し、[ファクトテーブル.外部キー]=[ディメンションテーブル.主キー]の形で結合します。このチュートリアルで用いるテーブル設計は以下のようなものとなります。Redshiftクラスタを構築しましょう。所定のVPC、セキュリティグループ(ポート番号:5439を許可)、クラスタサブネットグループ、クラスタパラメータグループを作成した後、クラスタ本体を作成。Type:dw2.large、ノード数:4で進める形で記載されていますので、ここでも同様の構成でクラスタを起動してみます。(※東京リージョン(ap-northeast-1)で起動してみる事にします)クラスタが立ち上がりました。psqlコマンド等でデータベースにログインし、所定のテーブルを作成します。NULL);次いで、S3の所定バケットからデータをCOPYします。計6つのリージョン向けにサンプルデータが既にアップロードされていますのでそのまま使いましょう。東京リージョンの場合はバケット名はawssampledbapnortheast1となります。シークレットキー及びアクセスキーも任意の情報を設定した上でCOPY処理を実行します。off;それぞれのテーブルに関しては何度か投入を繰り返して時間を計測してみました。(※後程比較に使いますので、実行結果は控えておきましょう。ms投入が完了したら、各テーブルの件数を確認してみます。結果は以下の様になりました。前項でも述べましたが、テーブルのチューニング前後でのシステムパフォーマンスを比較する為に、詳細な記録を付けておく必要があります。ポイントは以下3点です。4ノードで構築した場合の結果に基づいています。この結果は同じクラスタ設定だったとしても(当ドキュメントの)結果と異なる場合もあるかも知れませんが、システムパフォーマンスは多くの要因に拠って影響を受けます。同じ様な結果になるのだな、程度の認識で進めて頂ければと思います。前項でのテーブルデータのロード時間を控えて置いたかと思いますので、次はストレージ利用量に関する情報を取得します。以下クエリはテーブル毎に1MBサイズのディスクスペースが使われているか、を取得するものです。STV_BLOCKLISTテーブルを参照する事で得られる情報です。asc;結果は以下の様になりました。行)任意のクエリ実行時のパフォーマンスも計測しておきます。Redshiftはコードをコンパイルし、そのコンパイル済のコードを計算ノードにそれぞれ配布します。この事により初回実行は時間が掛かるので、クエリの実行時間を計測する場合は、初回実行時の記録は使うべきではありません。この辺りの詳細は以下参照。desc;実行結果は以下の様になりました。『チューニング前』の結果はこの様な状況となりました。(※計測時間は直近4回の平均を採用)3.ソートキーの選定テーブル作成時、1つ以上のソートキーをテーブルに対して設定する事が出来ます。Redshiftはソートキーの設定に基づいて、データをディスクに格納します。どのようにデータがソートされるのか、という点は、ディスクI/Oや列圧縮、クエリパフォーマンスに重要な影響を与えます。以下の条件に合う項目があれば、それらはソートキー指定対象の候補となります。直近のデータが頻繁にアクセスされるようなケースであれば、TIMESTAMP列をソートキーの先頭に指定しましょう。ある列で範囲指定や等価判定を行う場合、その項目をソートキーに指定しましょう。(ディメンション)テーブルと頻繁に結合を行う場合、その結合列をソートキーに指定しましょう。結果をフィルタリングするTIMESTAMP列を見つける為に、クエリを評価してみます。LINEORDERテーブルではlo_orderdateを使った等値判定を良く行います。1997範囲指定と等価判定で使用されている列を探します。LINEORDERテーブルはlo_orderdateを範囲指定でも利用しています。2つのベストプラクティスに基づき、lo_orderdateが最善のソートキーとなりそうです。チューニングテーブル内には、LINEORDERに対するソートキーとしてlo_orderkeyを指定します。残りのテーブルも3つめのベストプラクティスに基づき、主キーをソートキーとして指定します。以下の様にソートキーが指定出来ました。次のステップでは分散スタイルを埋めて行きます。Redshiftはテーブルで指定されている分散スタイルに基づいて、テーブルの行を各ノードスライスに分散します。スライスの数は、ノード上のプロセッサコアの数と同じです。当チュートリアルで使用しているdw2.largeクラスタは4つのノード、8つのスライスを持っています。全てのノードがスライスに分散するデータを取り込み、並列クエリの実行に参加します。結合と集計を実行する必要性に応じたクエリを実行すると、クエリオプティマイザは計算ノードに対して行の再分散を行います。この再分散は結合に対して特定の行をノードに送るかも知れないし、またテーブル全体を全てのノードに配信する事になるかも知れません。ゴールを達成する為には、分散スタイルを割り当てる必要があります。結合テーブルから、行を併置(コロケーション)する:列に対する結合行が同じスライスに有る場合、クエリ実行時のデータ移動は少なくて済みます。クラスタ内の均等に対し、均等にデータを配置する:もしデータが均等に分散されている場合、全てのスライスに均等にワークロードが割り当てられます。これらのゴールは幾つかのケースで競合する可能性があり、システム全体にとってどの戦略がベストチョイスなのかを評価する必要性があります。EVEN分散はは同じスライスに全て一致した列の値を配置出来ます。この手順では、データ分散のゴールに関してSSBテーブルの分散を評価し、テーブルに最適な分散スタイルを選択します。分散スタイル分散スタイルは以下3つが指定可能です。Distribution)]この設定を行うと、行はある列の値に従って分散されます。リーダーノードは同じノードスライスに一致する値を配置しようと試みます。2つのテーブルをキーで結合して分散させる場合、リーダーノードは結合した列の値に基づいて行をスライスに併置(コロケーション)します。共通列の値が一致することで、物理的にデータが同じ場所に格納されます。Distribution)テーブル全体のコピーが全てのノードに分散されます。EVEN分散またはKEY分散がノードに対しテーブルの特定行を配置するのに対し、ALL分散は全ての行が、テーブルが関わっている全ての結合に併置(コロケーション)される事を保証します。Distribution)行は特定の列の値に関係なく、ラウンドロビン形式でスライスに分散されます。指定が無い場合(デフォルト指定)はこの形式が採用されます。この辺の詳細については以下参照。Redshift分散スタイルの選択クエリ実行時、クエリオプティマイザは結合や集約の必要に応じて計算ノードに対し行の再分散を行います。クエリが実行される前に、あるべき場所にデータを配置しておく事で、再分散のインパクトを最小限に抑える事が出来ます。最初のゴールはテーブル結合により一致した行データを併置(コロケーション)された状態でデータを分散させる事です。この事は、テーブル結合で一致した行が同じノードのスライス上にあることを意味します。1.クエリプランの再分散ステップを見てみるには、以下のようにEXPLAINコマンドを実行します。この例では、3つあるうちのクエリから、2つめのものを実行しています。p_brand1;実行例。ラベルがDS_BCASTで始まる形で結果が弾き出されました。DS_BCAST_INNERは、内部結合テーブルが全てのスライスに展開された事を示しています。DS_DIST_BOTHは、もしあった場合、外部結合テーブルと内部結合テーブルがスライス全体に再分散されている事を示します散布(broadcasting)や再分散(redistribution)はクエリパフォーマンスの面からすると、高コストとなります。散布や再分散のステップをを減らしたり無くしたりするような戦略を選択したくなるでしょう。EXPLAINに関するより詳しい情報については、以下を御参照ください。Redshift2.ファクトテーブルとディメンションテーブルを共通の列で分散します。以下ダイアグラムはファクトテーブルのLINEORDERと、SSBスキーマのディメンションテーブルとの関連を示しています。それぞれのテーブルは1つだけ分散キーを持つ事が出来ます。この事は、共通の列でスキーマ内に併置(コロケーション)されうるテーブルのペアは1対だけである事を意味します。中央のファクトテーブルは明確な最初の選択となります。ペアに於ける2つ目のテーブルは、通常ファクトテーブルと結合する最も大きなディメンション(テーブル)を選択します。この設計では、LINEORDERがファクトテーブルとなり、PARTテーブルが最も大きなディメンション(テーブル)となります。PARTテーブルはLINEORDERテーブルと、主キーであるp_partkeyで結合します。lo_partkeyをLINEORDERに対する分散キーとして指定し、p_partkeyをPARTの分散キーとして指定します。これでデータが読み込まれる時、結合キー項目で一致した値の行は同じスライスに併置(コロケーション)されるようになります。3.幾つかのディメンションテーブルはALL分散に切り替えます。ディメンションテーブルがファクトテーブルやその他の重要な結合テーブルと併置(コロケーション)出来ない場合、対象テーブル全体を全てのノードに配布する事によってクエリパフォーマンスを改善する事が出来ます。"ALL分散"は列の結合が全てのスライス上で行われ、併置(コロケーション)される事を保証します。ALL分散を使う場合事前にそのテーブルの大きさを確認しておきましょう。全てのノードスライスに併置(コロケーション)させるため、単純にストレージ容量はN倍されます。ロードタイムやシステムメンテナンスの時間についてもテーブルが増える分、増加します。DWDATEの各テーブルはLINEORDERテーブルとプライマリーキーで結合しています。ALLを使う事になるでしょう。これは、テーブルのサイズが比較的小さく、また頻繁には更新されないため、ALL分散を使っても最小限のストレージ及びロード時間のインパクトで済むからです。4.その他テーブルはEVEN分散を用います。全てのテーブルがDISTKEY指定、またはALL指定の分散スタイルで定義されました。EVENについては指定するものは今回はありません。パフォーマンス結果を評価してみて、幾つかのテーブルはALLからEVENに変更する事はあるかもしれません。ここまでの経過で定まった分散スタイルの一覧が以下となります。圧縮(Compression)はデータ格納時にサイズを減らすために行われるカラムレベルの操作です。ストレージを節約し、読み込み時のサイズを削減します。ディスクI/Oのパフォーマンス改善に繋がります。Redshiftはそのデータを非圧縮状態(raw)で格納します。Redshiftでテーブルを作成する際、カラム毎に列圧縮タイプを指定する事が可能です。この辺りの詳しい情報については以下を御参照ください。Redshiftテーブル作成時に列圧縮タイプを指定出来る他、COPYコマンドでデータ内容を分析し、自動的にオススメの列圧縮タイプを割り出す事も可能です。圧縮エンコーディングをレビューする1.テーブル列毎にどの程度スペースを使用しているかを調べるシステムビュー:STV_BLOCKLISTを調べる事で、列毎に1MBブロックをどの程度使用資しているかを確認する事が出来ます。MAX集計関数はカラム毎で一番多く使っている数値を返します。2862.異なるエンコーディングを試してみるこのステップでは『同一の列名』且つ『異なる圧縮エンコーディング』のテーブルを作成します。PARTテーブルのp_name列から同じデータを大量にINSERTします。最後にテーブル間の結果を比較するという流れです。a.比較用に、異なる圧縮エンコーディングを設定したテーブルを作成。SELECT文の実行。このSQLは若干時間が掛かります。200000000;c.どの程度1MBブロックを使用しているかを確認するために以下クエリを実行。col;結果は以下の様な形で返ってきます。クラスタ構成によって結果は異なってくるかも知れませんが、だいたいこんな感じで返ってくるかと。rows)順序はこうでした。RawBytedictLZORunlengthText255Text32Kこれらの結果から、Bytedictが一番良い結果を弾き出しました。raw(221)と比べるとおよそ8分1という形となります。項目によってこれらの算出結果は異なって来ます。COMPRESSIONを使う以下コマンドを実行してみます。lineorder;結果はこんな感じで返ってくるはず。COMPRESSIONはlo_shipmode列に対してBYTEDICTエンコーディングを選出しました。この辺りの手順、項目毎の圧縮エンコーディングを個別・手動で定めていく流れについては以下のページを参考に進めてみると良いでしょう。RAWエンコーディング(無圧縮)以外の圧縮エンコーディングでは無い空のテーブルにデータを読み込む際、自動的に圧縮エンコーディングを適用します。6.テスト用データセットの再作成ここまでの過程で、テーブル毎に分散キー、ソートキーを選択して来ました。テーブルの再作成を行い、データをリロードしてみます。COPYコマンド実行時に、ロードデータを分析し圧縮エンコーディングをデータに対して自動的に適用させる事が出来ます。TABLE文実行の前に、これまで作成していたテーブルを一旦削除します。TABLE文を実行します。null);3.データをロードします。offの指定をしてましたが、今回はそれを外します。これでCOPYコマンドが圧縮エンコーディングを自動で適用するようになります。gzip;ロード時間を計測してみましょう。各テーブルの計測時間は以下のようになりました。初回実行はチューニング後よりも時間が掛かっている様ですが、2回目以降は改善されていますね。(とは言え、実行時間が長くなったテーブルもあるようです。ms上記結果を反映した表は以下の様になりました。(※計測時間は直近4回の平均を採用)7.システムパフォーマンステスト実践(再)ソートキー・分散キーや圧縮エンコーディングの設定を付与してデータセットを再作成したら、再テストを実施します。チューニング後のパフォーマンステストを再実施1.ストレージ使用量を計測します。asc;結果はこうなりました。skew)』の確認を行います。不均一な分散またはデータ分散スキューは、クエリパフォーマンスを制限する点に加えてより多くの作業を幾つかのノードに強いる事となります。分散スキューを確認するには、システムビューのSVV_DISKUSAGEを参照します。このテーブルの各行は1つのディスクブロックの統計情報を記録します。num_values列はディスクブロック内の行数を返し、sum(num_values)はスライス毎の行数を返します。SSBデータベース内のテーブルの全ての分散状況を参照するためのクエリを実行します。slice;実行結果は以下のような形で返ってきます。rows)以下グラフは大きなテーブル上位3つの分散状況を図示したものです。CUSTOMERテーブルはALL分散を使用しているので、1ノードに対して1つだけに分散されています。この分散状況は比較的均一と言えます。分布のスキューを調整する必要はありません。3.クエリに対するクエリプランを確認するためにEXPLAINコマンドを実行します。以下例はQuery2に対するEXPLAINコマンド実行です。p_brand1;以下が実行結果となります。DS_BCAST_INNERラベルがDS_DIST_ALL_NONEとDS_DIST_NONEに置き換わっています。これは再分散の必要が無くなり、クエリはより高速化されるだろう、という分析結果を意味しています。width=8)4.時間計測用にクエリを再度実行。比較用に前以て実行していたクエリ群を再度実行します。desc;結果を確認します。幾つかの要因を変更した事でパフォーマンスが改善されている事が確認出来ました。ms検証結果を集計した比較内容は以下の通りとなりました。8.結果検証ここまでロード時間、ストレージ容量、クエリ実行について、チューニング前後の記録を比較する事で検証を重ねてきました。今回このドキュメントを参考に実践してみた結果は以下のようになります。データのロード時間ソートキー、分散キー、列圧縮タイプを設定した事により、若干時間が増えました。この場合、圧縮エンコード設定がされていないテーブル毎に対してロード時間が掛かる事になる自動圧縮を用いていたのもありました。今後、このテーブルに対する読み込みは高速化されていくでしょう。分散スタイル:ALLを指定した事も時間が増えた要因の1つとなります。幾つかのテーブルについては分散スタイル:EVENまたはDISTKEYを使う事でロード時間を減らす事も出来ました。この決定はクエリパフォーマンスが遅くなる可能性もあるので状況に応じて云々。ストレージ使用量カラム圧縮の利用によるストレージ部分の改善部分の幾らかは分散スタイル:ALLの使用によって総裁されました。繰り返しますが、分散スタイル:EVENもしくはDISTKEYの指定を使う事によって幾つかのテーブルについては改善を行う事が出来ますが、その替わりにパフォーマンスが犠牲になるでしょう。分散方法を選択・指定した結果、傾斜分布が無い事は確認出来ました。EXPLAINでのチェックによって、テストクエリに対する幾つかのデータ再分散が除去する事が出来ました。ソートキー、分散スタイル、圧縮エンコーディングの組み合わせによるものでした。Management)の設定によって改善する事が出来ます。詳細な情報については、以下ドキュメントを御参照ください。Redshift9.リソースのお掃除クラスタは起動し続けている限り、料金が発生し続けます。今回検証用に構築したクラスタ環境であれば、忘れずにクラスタを削除しておきましょう。TABLE文を実行し、テーブルを削除してください。cascade;まとめ以上、公式ドキュメント記載の実行記録でした。行う事で、パフォーマンスチューニングの勘所も掴み易くなる事が実感出来ると思います。こちらからは以上です。6,745,157まとめ職人→出張ブロガー(初代)→AWSエンジニア→ビッグデータ系エンジニアDevelopers.IO全投稿のうち1,000本を書いています。〜ITエンジニアのお仕事とホンネ〜2月19日に新卒向けの会社説明会〜ITエンジニアのお仕事とホンネ〜を開催します。リモート】クラスメソッドの会社説明会を開催しますクラスメソッドでは、AWS・AI・ビッグデータ・モバイルなどの技術職や営業のメンバーとして一緒にお仕事をする仲間を大募集しています。#cmdevio新型コロナウイルスの感染拡大を受け、本イベントを延期させていただくことになりました(開催時期未定)。お申し込みいただいた皆様には大変申し訳ございませんが、何卒ご理解いただけますようよろしくお願いいたします。AWSについてライトに色んな情報交換をしましょう。カフェ店長のオカノです。使いたいときの注意点を書きました。EC2)環境において、不要な踏み台サーバをなくすことができる便利なアップデートです。環境をDockerで構築してみました。臼田佳祐295ワークフロー自動化OSSのn8nをAWS環境で建てて検証してみました。非常に期待できるサービスです。Fargateの勉強をしています。使ってみたい方は参考にしてください。洋介252SnowflakeがAWSの東京リージョン上で稼働するようになりました。データを日本に置けます。芳賀健231こんにちは!猫様の忠実なるしもべ、芳賀健です。Tokyoで開催されました。クラスメソッド株式会社が運営するAWS・iOS・HTML5に関する技術情報サイトです。