Amazon Redshift クエリパフォーマンスチューニング ベストプラクティスを読んでみた
Amazon Redshiftでは『ベストプラクティス』なるテーマで、それぞれの局面でのお作法的な設定、改善の為のノウハウがドキュメントとして適宜追加or更新されています。ちょうど去年の8月にクラスメソッドにジョインした際もこの辺りのドキュメントについて幾らか目を通して参りましたが、英語ドキュメントを訪れてみると更に充実した形で『ベストプラクティス』が整備されているようです。そこで当エントリでは『クエリパフォーマンスチューニング』という切り口で更新されている以下ドキュメントを改めて読み直してみたいと思います。
目次
1.パフォーマンスを考慮したテーブル設計を行う
Amazon Redshiftでのテーブル設計時には、パフォーマンスに影響を及ぼすような重要な設計上の決定事項が存在しています。これらの設計に於ける選択はまた、I/Oアクセス数を減らしたりクエリ処理時に必要とされるメモリを最小化する事でクエリパフォーマンスに影響を与えるストレージ要件にもに重大な影響を及ぼします。テーブル設計時のポイントは以下。
1-a.最善のソートキーを選択する
Amazon Redshiftではソートキーの内容に基づいてデータをディスクに格納し、Redshiftのクエリオプティマイザはクエリプランを決定する際、このソート順序を用いています。
- 直近のデータが頻繁に照会されるようなケースの場合はTIMESTAMP項目をソートキーの先頭列に設定すべし
- (日時的に)範囲外のデータブロック全体をスキップする事が出来、クエリの効率化が図れます。
- 範囲によるフィルタリングや等値フィルタリングを行う項目であれば、その項目をソートキーとして指定すべし
- Amazon Redshiftでは、各ブロックに格納されている列の最小値と最大値を追跡し、範囲外のブロックをスキップするこことが出来るので、結果としてブロック全体をスキップする事が出来ます。
- 頻繁に表の結合を行う場合、その結合に用いる項目をソートキー・分散キーの両方に指定すべし
- この設定を行うと、データは既に結合キーによってソートされる形となり、クエリオプティマイザはソートマージ結合でのソートする手順をバイパス(迂回)する事が出来るようになり、クエリオプティマイザが遅いハッシュ結合の替わりにソートマージ結合を選択する事が出来るようになります。
※なお、ここで出て来る『ハッシュ結合』『ソートマージ結合』については以下を御参照ください。
1-b.最善の分散キーを選択する
クエリ実行時に結合と集計を実行する必要がある場合、オプティマイザは計算ノードに対して行データの再分散を行います。テーブルの分散スタイルを選択する目的は、この再分散(即ちデータ移動)のインパクトを最小限に抑える事です。
- ファクトテーブルとディメンションテーブルを共通の列で分散する
- ファクトテーブルは1つだけ分散キーを持つ事が出来ます。その他に分散キーを持つ事は出来ません。
- どれ位の頻度で結合されるか、また結合列のサイズに基づいてディメンションを選択します。
- ディメンションテーブルの主キー(PRIMARY KEY)と、対応するファクトテーブルの外部キーの両方を分散キー(DISTKEY)として指定しましょう。
- フィルタされたデータセットのサイズに基づき、もっとも大きいディメンションを選択
- 結合で使われる行のみ、分散される形となります。なので、テーブルそのもののデータサイズではなく、フィルタリングされたあとのデータ・セットサイズで考慮しましょう。
- 幾つかのディメンションテーブルを分散タイプ：ALLに変更する
- もしディメンションテーブルがファクトテーブルや他の重要な結合テーブルと併置出来ないような場合、全てのノードにそのテーブルを丸ごと分散配置させる事でクエリパフォーマンスを改善する事が出来ます。
- 分散スタイル:ALLを使うとその分ストレージ容量が必要となり、ロードタイムやメンテナンス操作の時間も増える事となるので、設定の際にはこれらの要件について比較検討する必要があります。
分散スタイル指定については以下に記載アリ。
また、ここで言及されているファクトテーブル・ディメンションテーブルと言うのは、『スタースキーマ』設計に於ける各々のテーブルを指しています。下記エントリのフレーズを拝借するならば、ファクトテーブルは『分析したい情報を含むテーブル』、ディメンションテーブルは『ファクトテーブルで参照するマスタ情報を構成するテーブル』でしょうか。ファクトテーブル同士、ディメンションテーブル同士は結合せず、上記ルールに則った結合の形＝スター(星形)スキーマとなる、という事ですね。
1-c.COPY時に『自動圧縮あり』でデータをロードし、オススメの列圧縮タイプを参考にする
COPYコマンドではロードする内容を自動判断し、オススメの列圧縮タイプを提示してくれるオプションがあります。 詳しくは過去エントリにて採り上げていますのでそちらを御覧ください。
1-d.主キー(PRIMARY KEY)と外部キー(FOREIGN KEY)を定義する
テーブル間の適切な場所に、主キー(PRIMARY KEY)と外部キー(FOREIGN KEY)を定義します。
実際は参考情報としてしか利用されません(キーとしての役目は果たしません)が、クエリオプティマイザは効率的なクエリプランを作成する際、これらの情報を利用します。(※ちなみにこれらの項目は設定必須ではありません。)
一方で、この設定値が無効な値、設計上誤った値である場合(例えば主キーが一意でない場合)は結果も異なる場合がありえるので設定値が疑わしい場合は逆に設定すべきでない様です。
1-e.列のサイズは可能な限り小さくなるように設計する
Amazon Redshiftでは列圧縮を効率的に行うため、列のサイズ、桁数を可能な限り小さく収めるような設計が推奨されています。例えばVARCHAR型の項目について、無闇に最大値や極端に大きな桁数とせず、列に入っているデータの最大桁数を見極め、その最大桁数となる値が入るような(バッファがあまり多くなり過ぎないような)桁数にする、といった形です。不必要に大きなサイズはその分、メモリと一時ディスク領域を必要とし、結果としてクエリパフォーマンスにも影響して来ます。
1-f.日付項目にはDATE/TIMESTAMP型を使う
Amazon Redshiftでは、DATE/TIMESTAMP型データをCHAR/VARCHAR型データよりも効率的・効果的に扱う事ができます。対象となる項目が日付時刻情報を意味するものである場合、データ型にDATE/TIMESTAMPを設定する事を検討しましょう。
2.テーブル設計チュートリアルをやってみる
AWS公式ドキュメント(英語版)では、以下にテーブル設計に於けるチュートリアルが展開されています。各種重要事項設定のステップを踏み、実践前後の内容を比較する事でその手法と効果を確認する事が出来ます。
先日投下した以下のブログエントリは、その実践結果となります。宜しければ御覧ください。
3.データベースに対しVACUUM処理を実施する
ソートキーの指定通りにデータをロードしないのであれば、大量データを追加・削除・変更した際は、VACUUMコマンドを実行しましょう。VACUUMコマンドはソート順序の維持・パフォーマンスの回復の為にデータを再編成します。
データをソートキーの順序通りもしくはシーケンシャルブロック内でにロードする事で、VACUUMの必要性を減らす、或いは無くす事が出来ます。この辺りの詳細については以下ドキュメントが参考になります。
VACUUM処理の頻度に関する詳細については以下ドキュメントが参考になります。
4.ディープコピーを実行する
ディープコピーは自動的にテーブルを再ソートするバルクインサートを行う事で、テーブルの再生成・再設定を行います。もしソートされていない領域がある場合、ディープコピーはVACUUM処理を行うよりも高速です。トレードオフとしては、VACUUMはその最中に更新操作が行えますが、ディープコピーは行えない点です。この辺りの詳しい情報については、以下に展開されています。
5.利用可能なメモリを増やす
もしデータロードやVACUUM処理に時間が掛かる場合、wlm_query_slot_countを増やす事でCOPYやVACUUM処理に利用出来るメモリを増やしましょう。データロードやVACUUM処理はメモリを食います。COPY処理やVACUUM処理にはメモリを割り当てる事が出来、このパラメータ値を増やす事でパフォーマンス改善を行う事が可能です。
しかし、wlm_query_slot_countの値を増やす事は一方でクラスタに於けるクエリ同時実行数を減らしてしまう事になるという点に注意してください。詳しくは以下。
6.最新のテーブル統計を維持して行く
現在のテーブル統計情報が最新である状態を保たせるために、データに大きな変更が加わった際にはANALYZEコマンドを実行ようにしましょう。このコマンドを実行するとAmazon Redshiftのクエリオプティマイザは統計メタデータに基づいて最適なクエリプランを決定します。
STATUPDATE=ONでのCOPY処理、INSERT INTO SELECTコマンド、CREATE TABLE ASコマンドを使う際、コマンド終了後にAmazon Redshiftは自動で統計情報を生成します。しかし、データの追加(INSERT)、更新(MODIFY=UPDATE)、削除(DELETE)の場合はこの処理は行いません。
詳細についてはAnalyzing tables - Amazon Redshiftをご参考に。
7.ソート列に冗長な述語を指定
テーブル結合時は、ファクトテーブル若しくは一番大きなテーブルで主となるソート列の先頭で述語(predicate)を使いましょう。述語って何ぞや？というのは以下の内容を確認。引用すると『入力項に対し、命題の真理値(true/false/unknown)を出力する特殊な関数』の意味となります。
また述語が冗長な場合であったとしても、結合処理を行っている他のテーブルをフィルタリングするために述語を用いましょう。大きなファクトテーブルが復数のディメンションテーブルと結合しているようなスタースキーマやそれに類する設計において、大きなテーブルのソート列でフィルタリングするために述語をWHERE句に追加した時、クエリプランで大幅にブロックスキャンをスキップさせる事が出来ます。
フィルタが無い場合、クエリ実行エンジンはテーブル全体をスキャンしてしまいます。テーブルの行数が大きくなるに従い、クエリパフォーマンスも低下して行ってしまうでしょう。
クエリ結合時に既に述語が指定されているが、そのクエリ内での他のテーブルとの結合に推移的結合が適用されている場合、その部分に対して冗長な述語を指定する事によってパフォーマンスを向上させる事が出来、Amazon Redshiftは効率的にブロック単位でのスキャンをスキップさせる事が出来ます。
例えば以下クエリ。tab1とtab2を結合させる為にtab1.timestampの条件を指定しています。
SELECT * FROM tab1, tab2 WHERE tab1.key = tab2.key AND tab1.timestamp > '1/1/2013';
このような形でtab2.timestampを含まない場合、実行エンジンは強制的にテーブル全体をスキャンします。
もし結合結果が所定の条件を満たすようであれば(この場合、tab2.timestampが2013/01/01以降のものを含むケースであれば)冗長であってもtab2.timestampに関するフィルタは追加するようにしましょう。
SELECT * FROM tab1, tab2 WHERE tab1.key = tab2.key AND tab1.timestamp > '1/1/2013' AND tab2.timestamp > '1/1/2013';
まとめ
という訳でざっと読んでみました。Redshift単体もそうですし、BIツールとの絡みでこの辺りの『パフォーマンスチューニング』ネタは情報源としてとても助かりますし、日々更新や機能追加が成されるAmazon Redshiftに於いては適宜ウォッチすべきテーマでもあります。『こうすればどの局面でもだいじょうぶ！』という対処法はありません。こちらのクエリでは調子良いけど、こっちでは全然...という事も良くある事です。局面に応じて原因を突き止め／切り分け、適切な対応を行って改善を重ねて行きたいところですね。こちらからは以上です。
![[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
