Amazon Redshiftのパフォーマンスチューニングテクニック Top 10
AWS Bigdata BlogよりIan Meyers著"Top 10 Performance Tuning Techniques for Amazon Redshift"を翻訳しました。
原文)https://aws.amazon.com/jp/blogs/big-data/top-10-performance-tuning-techniques-for-amazon-redshift/
翻訳)岩永亮介、下佐粉昭
※2017/02/20更新:原文ポストURL変更に伴い、翻訳側のリンク先を更新
著者Ian MeyersはAmazon Web Servicesのプリンシパルソリューションアーキテクト
Zach ChristophersonはAmazon Redshiftデータベースエンジニアで、このポストに協力しました
Amazon Redshiftはフルマネージドな、ペタバイト規模でも使える、MPP(Massively Parallel Processing)のデータウェアハウスで、運用の簡単さと高いパフォーマンスを提供しています。 ビッグデータ分析のユースケースで、スケールやウェブログの投入に苦労している既存のデータベース環境を加速させるためにAmazon Redshiftを使われているお客様もいます。Amazon Redshiftは業界標準のJDBC/ODBCドライバーのインタフェースを提供しているので、お客様は既存のBIツールや分析クエリをそのまま再利用することができます。
第3正規化された本番のトランザクションシステムのデータ・モデルから、スタースキーマやスノーフレークスキーマ、さらには単純なフラットなテーブルま で、いかなるデータ・モデルでも動かすことができます。Amazon Redshiftを使い込んでくると、自身のデータモデルがデータベース上で正しく分散され管理されているかを保証するために、そのアーキテクチャを考慮する必要がでてきます。今回のポストでは、Amazon Redshiftを使い込むとお客様が直面する最も一般的な課題を見ていって、それぞれにどの様に対処していくかの具体的なガイダンスをお伝えします。も しこれらの課題に対処できれば、最適なパフォーマンスのクエリを実現でき、要求にあわせて効率てきにスケールすることができるはずです。
課題 #1: 間違った列の圧縮方式
Amazon Redshiftは列指向(カラムナ)のデータベースで、データを行毎にディスクに保存するのではなく列毎に保存し、行はカラム毎のストレージから実行時に取り出されます。このアーキテクチャは大量のカラムを持つテーブルの分析クエリに特に最適で、なぜなら多くのクエリは全ての列のいくつかにしかアクセスしないからです。Amazon RedshiftはクエリのSELECTやWHERE句に含まれる列のディスクブロックにのみアクセスすることができ、全てのテーブルデータを読む必要がありません。列毎に保存されるデータは、圧縮されるべきで(Amazon Redshift Database Developer Guideの列圧縮タイプの選択をご覧ください)、それによって高い圧縮率を実現し、高い読み込みパフォーマンスを得られます。これはさらに言うと、 Amazon Redshiftではインデックスの作成や運用が必要ないとも言えます。全ての列は自身のインデックスのようなもので、データが保存されるための適切な構造になっています。
列の圧縮をせずにAmazon Redshiftクラスタを利用するのはベストプラクティスではないですし、列圧縮を最適化した場合には高いパフォーマンス向上を体感できます。ベストプラクティスから逸脱していないかを確認するために、以下のクエリを実行して列が圧縮されていない表を確認します:
1
2
3
4
|
SELECT database , schema || '.' || "table" AS "table" , encoded, size FROM svv_table_info WHERE encoded= 'N' ORDER BY 2; |
その後、圧縮されていない表と列をレビューするために以下のクエリを実行します:
1
2
3
4
|
SELECT trim(n.nspname || '.' || c.relname) AS "table" ,trim(a.attname) AS "column" ,format_type(a.atttypid, a.atttypmod) AS "type" , format_encoding(a.attencodingtype:: integer ) AS "encoding" , a.attsortkeyord AS "sortkey" FROM pg_namespace n, pg_class c, pg_attribute a WHERE n.oid = c.relnamespace AND c.oid = a.attrelid AND a.attnum > 0 AND NOT a.attisdropped and n.nspname NOT IN ( 'information_schema' , 'pg_catalog' , 'pg_toast' ) AND format_encoding(a.attencodingtype:: integer ) = 'none' AND c.relkind= 'r' AND a.attsortkeyord != 1 ORDER BY n.nspname, c.relname, a.attnum; |
もし最適な列圧縮になっていない表を見つけたら、AWS LabのGitHubにあるAmazon Redshift Column Encoding Utilityを使ってみましょう。このコマンドラインツールはANALYZE COMPRESSIONコマンドを各表に使います。もし圧縮が必要であれば、正しい圧縮型を持つ新しい表を作り、全データを新しい表に複製 し、トランザクションを使って新しい表を元々のデータがある古い名前にリネームするSQLのスクリプトを生成してくれます。(注: コンパウンドソートキーの第1カラムは圧縮すべきでなく、このツールではそれは圧縮されません。)
課題 #2 - 偏ったテーブルデータ
Amazon Redshiftは分散、シェアードナッシング型のデータベースアーキテクチャで、クラスタの各ノードはデータの一部分を保持しています。表を作成する時に、 ノード間で均等に分散するか(デフォルト)、列の値に基いてノードに配置するかを決めます。分散用の列としてよくJOINに使われるものを選ぶこ とで、JOINの時のネットワーク越しのデータ転送量を最小化することができます。こういったクエリにおいては列の値に基づいた分散をすることで非常に高いパフォーマンスが得られます。
良い分散キーの選び方は、最適な分散スタイルの選択を含む、多くのAWSの記事でも話題になっています。スタースキーマでの分散とソートのガイダンスの決定版としてOptimizing for Star Schemas and Interleaved Sorting on Amazon Redshiftのブログ記事をご覧下さい。一般的には、良い分散キーは以下の様な特徴を持っているべきです:
- 高いカーディナリティ - クラスタのノードの数と比較して、その列の値が非常に多種なユニークな値を持っていること
- 正規分布/低い偏り - 分散キーの各ユニークな値が、均等な回数、表内に現れること。これによってAmazon Redshiftは同じ数のレコードをクラスタ内の各ノードに配置することができます。
- 頻繁にJOINされる - 分散キーの列は他の表とのJOINによく使われること。もしこの条件に当てはまりそうな列が多数あった場合は、最も大きい表とJOINするときに使われる列を選びます。
偏った分散キーになってしまうと、各ノードでクエリの実行が均等にならず、CPUやメモリの要求もバラバラになり、究極的には最も遅いノードの実行速度までしかクエリが速くなりません。:
もしも偏りが問題となっていたら、クラスタ内でノードのパフォーマンスが均等でない状況がよく見られます。Amazon Redshift UtilsのGitHubレポジトリの中の管理スクリプト、例えばtable_inspector.sqlを使うと、分散キーのデータブロックがどのよう にクラスタ内のスライスとノードに配置されているかを見ることができます。
もし偏った分散キーを持つ表を発見したら、高いカーディナリティと正規分布を持つ列に分散キーを変更することを検討しましょう。分散キーの列の候補を評価するために、CTAS(CREATE TABLE ... AS ...)を使って新しい表を作ります:
1
|
CREATE TABLE MY_TEST_TABLE DISTKEY (< COLUMN NAME >) AS SELECT * FROM < TABLE NAME >; |
新しい表にも再度table_inspector.sqlを実行してみて、データの偏りを分析します。
もし分散キーに適したカラムがなければ、EVEN(均等)分散を利用した方が、単一ノードがホットスポットにならないので、良くなることがあります。小さい表については、DISTSTYLE ALLを使ってクラスタ内の全てのノードに表データを配置することもできます。
課題#3-ソートキーの恩恵を受けられないクエリ
Amazon Redshiftの表にはソートキー列を定義することができます。これは他のデータベースでいうところのインデックスに近い動きをしますが、それらのようにストレージ利用量の増加は発生しません(詳細はソートキーの選択をご覧ください)。ソートキーはWHERE句でもっとも頻繁に利用される列を指定して作られるべきです。もしどういったクエリが実行されるか分かっているのであれば、コンパウンドソートキーを利用することでベストパフォーマンスを得られます;もしエンドユーザが異なるクエリーをバラバラに実行するのであれば、インターリーブドソートキーを利用してください。
ソートキーを持っていない表を判別するために、およびどの程度頻繁にクエリされているかを知るために、以下のクエリを実行してください:
1
2
3
4
5
6
7
8
9
|
SELECT database , table_id, schema || '.' || "table" AS "table" , size , nvl(s.num_qs,0) num_qs FROM svv_table_info t LEFT JOIN ( SELECT tbl, COUNT ( distinct query) num_qs FROM stl_scan s WHERE s.userid > 1 AND s.perm_table_name NOT IN ( 'Internal Worktable' , 'S3' ) GROUP BY tbl) s ON s.tbl = t.table_id WHERE t.sortkey1 IS NULL ORDER BY 5 desc ; |
Amazon Redshiftデベロッパーガイドのチュートリアルを確認して、ソートされていない表にどう取り組むべきかを確認することができます。また、クエリーのアクティビティを基にソートキーを推奨してくれるGitHubにある管理スクリプトを活用することもできます。気をつけないといけないのは、ソートキーの利用時に関数を利用しないようにすることです。代わりにソートキーの列を直接比較するようにしてください。これはTIMESTAMP列をソートキーとして利用する場合によく現れることが分かっています。
(訳注:左の例ではソートキーが利用されて効率的に実行されるが、右の例では利用されない)
課題#4-統計情報が無い表、もしくはVACCUMが必要な表
他のデータベースと同様に、Amazon Redshiftもクエリーを計画する際に良い選択をするため、表やストアされたブロックの圧縮といった統計情報を必要とします(より詳細な情報はテーブルを分析するを御覧ください)。適切な統計情報が無い場合、オプティマイザーはどの表にアクセスするか、もしくはどのデータセットをジョインするかという選択で、次善のもしくは良くない選択をしてしまう可能性があります。
Amazon RedshiftデベロッパーガイドのANALYZEコマンド履歴には、統計情報が無い、もしくは古くなった表を探すことを手伝うクエリーが掲載されています。もしくは、シンプルに missing_table_stats.sql 管理スクリプトを実行することで、表に統計情報が無い、もしくは古い統計情報をもっている表を判別することができます:
1
2
3
4
|
SELECT database , schema || '.' || "table" AS "table" , stats_off FROM svv_table_info WHERE stats_off > 5 ORDER BY 2; |
Amazon Redshiftでは、データブロックが処理の最小単位です。行が削除もしくは更新された場合、それらは単純に論理的な削除(削除されたフラグが付く)が実行されますが、物理的にはディスクから消えません。UPDATEを実行すると新しいデータブロックが作成され、追記(アペンド)されます。これら両方のオペレーションは古いヴァージョンの行によってディスクを消費し続け、クエリーが表をスキャンする範囲にも残り続けます。この結果、表のストレージスペースは増加し、スキャン時の不要なディスクI/Oによりパフォーマンスは低下します。VACUUMコマンドはこの削除済の不要な領域を開放し、データをソート順に並べて格納し直します。
この統計情報が無い、もしくは古い表、およびVACUUMが必要とされる表に関する問題に対応するために、AWS LabユーティリティのAnalyze & Vacuum Schemaが利用できます。このユーティリティを使うと、統計情報を常に最新に更新し、再編成が必要な表にのみVACUUM TABLEを実行しつづける事ができます。
課題#5-とても大きなVARCHAR列を含む表
複雑なクエリーを処理している途中で、中間のクエリー結果とテンポラリーブロック(ディスク)にストアする必要になる場合があります。これらテンポラリー表は圧縮されません。つまり不必要なサイズをもつ列は必要以上のメモリとテンポラリーディスクスペースを消費し、これによりクエリーパフォーマンスに影響を与えます。より詳細な情報は最小列サイズの使用をご覧ください。
以下のクエリーを利用して、最大サイズの列を持っている表のリストを作成することができます:
1
2
3
4
|
SELECT database , schema || '.' || "table" AS "table" , max_varchar FROM svv_table_info WHERE max_varchar > 150 ORDER BY 2; |
表のリストを得たら、どの列が大きなVARCHARを指定されているかを確認して、それぞれの列が本当に必要な最大サイズをもっているのかを以下のクエリーで確認します:
1
2
|
SELECT max (len(rtrim(column_name))) FROM table_name; |
場合によっては、巨大なVARCHAR型列を保持する必要もあります。例えばJSONの構成要素を表に保持して、JSON関数でクエリーしているようなケースです。 top_queries.sql 管理スクリプトを使ってデータベースにおける良く実行されているクエリーを検索する場合は、JSONの断片要素を格納した列をSELECT * しているクエリーに特別の注意を払ってください。もしエンドユーザがこれら巨大列をクエリーするが、JSON関数をこれらの列に適用していないようであれば、それらの列を、もとの表のプライマリーキー列とJSON列のみにした別表に移動しする事を検討してください。
もし、これら表の列が必要以上に長い場合は、ディープコピーを使って必要なサイズの幅をもった列で表を作りなおす必要があるでしょう。
課題#6-キュースロットをウエイトしているクエリ
Amazon RedshiftはWLM (Workload Management、ワークロード管理)というキューイングシステムを内蔵しています。最大で8つのキューまで定義をすることができ、それぞれのワークロードを分離し、各キューでの並列度(同時実行レベル)を必要なスループットに見合うように調整することができます。
時折、キューにアサインされたクエリ、もしくはユーザが極めて重い処理を実行し、他ユーザのクエリーがそのキュースロットが空くのを待つことになる場合があります。この間はクエリーを全て実行できておらず、これは並列度を増加させる必要性のサインです。
まず、queuing_queries.sql 管理スクリプトを使ってどのクエリーがキューイングされているかを判断する必要があります。利用しているクラスターがこれまでどれだけの並列性を必要としかたを、 wlm_apex.sqlで確認し、wlm_apex_hourly.sqlを使って1時間単位での利用履歴分析と突き合わせます。並列性の増加はより多くのクエリーを実行可能にしますが、一方で割当られたメモリ空間を共有しているためにクエリあたりのメモリが減少する(メモリ割当てを増やさない限り)という点には注意が必要です。並列性を増加させると、いくつかのクエリを実行するためにテンポラリーディスクへの書き出しが必要になる場合があります。この調整は次の課題で検討します。
課題#7-ディスクベースのクエリー
クエリーがメモリ内で実行しきれない場合、実行計画の一部としてディスクベースのテンポラリストレージが必要になります。この追加のディスクI/Oはクエリーの速度を低下させますが、セッションあたりのメモリ量を増加させることでこれを調整することが可能です(より詳細な情報はWLMの動的なメモリ割りてをご確認ください)。
どのクエリーがディスクに書き込みをしているのかを判別するには、以下のクエリーを実行してください:
1
2
3
4
5
|
SELECT q.query, trim(q.cat_text) FROM ( SELECT query, replace ( listagg(text, ' ' ) WITHIN GROUP ( ORDER BY sequence ), '\\n' , ' ' ) AS cat_text FROM stl_querytext WHERE userid>1 GROUP BY query) q JOIN ( SELECT distinct query FROM svl_query_summary WHERE is_diskbased= 't' AND (LABEL LIKE 'hash%' OR LABEL LIKE 'sort%' OR LABEL LIKE 'aggr%' ) AND userid > 1) qs ON qs.query = q.query; |
ユーザの定義もしくはキュー割り当てのルールをベースに、選択したキューでクエリーが実行完了までにディスクへのI/Oを発生させないよう、メモリの割り当てを増加させることが可能です。セッションごとにWLM_QUERY_SLOT_COUNT(http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_wlm_query_slot_count.html)を増加させることが可能で、デフォルトである1からキューでの最大並列度数まで調整可能です。課題#6に示したように、これはクエリをキューイングさせます、つまり使用には注意が必要です。
課題#8-コミットキューのウエイト
Amazon Redshiftはトランザクション処理のクエリーよりも、分析クエリー用にデザインされています。相対的に見てCOMMITのコストは高く、必要以上にCOMMITを実行することは、コミットキューへのアクセスをウエイトさせることになります。
もしデータベースで必要以上にCOMMITを実行した場合、コミットキューでのウエイトが増加し始めます。これはcommit_stats.sql 管理スクリプトで確認することができます。このスクリプトは過去2日間での最長のキュー長と、クエリ時間を表示します。もしコミットキューをウエイトするクエリーがあった場合は、何度もCOMMITを実行しているセッション、例えばETLジョブで処理経過をロギングしたり、非効率的なをデータロード(次項も参照)を行っているといったセッションを探しあててください。
課題#9-非効率的なデータロード
Amazon Redshiftのベストプラクティスでは、データロードにCOPYコマンドを利用する事を推奨しています。このAPI操作はクラスター上の全コンピュートノードをAmazon S3やAmazon DynamoDB、Amazon EMR HDFS、もしくはSSH接続からパラレルにデータロードするために使用します。
データロードを実行する際には、可能な限りロードするファイルを圧縮してください;Amazon RedshiftはGZIPとLZO圧縮をサポートしています(訳注:元記事執筆後にBZIP2サポートの追加も発表されました)。1つの巨大ファイルをロードするより、小さく分割されたファイルをロードする方が効率的であり、そのファイル数はスライス数に等しいのが理想的です。ノードあたりいくつスライスがあるかは、クラスターのノード種別によって異なります。例えばDS1.xlargeコンピュートノードは2つのスライスを持ち、DS1.8xlargeコンピュートノードは16スライスを持っています。ファイル数をスライスの整数倍にすることで、COPYコマンドはクラスターリソースを平等に使い切ることができ、COPYの完了が最も早くなります。
アンチパターンは、INSERT文でAmazon Redshiftに一行づつ、もしくは最大16MBまで可能なマルチバリューINSERTステートメントによって一度にデータをインサートしようとすることです。これはリーダーノードで処理されるオペレーションであり、リーダーノードのCPUやメモリを最大限まで利用することで、大きなパフォーマンスボトルネックを作成してしまいます。
課題#10-テンポラリ表の非効率的な利用
Amazon Redshiftではテンポラリ表を利用可能です。これは、通常の表と同じですが、同セッションの中でのみアクセス可能であるという点が異なります。ユーザがセッションの接続を解除すると表は自動的に削除されます。テンポラリ表はCREATE TEMPORARY TABLEを使うか、もしくはSELECT .. INTO #TEMP_TABLEとクエリすることで作成できます。CREATE TABLEステートメントはテンポラリ表を作成する際の定義を全てコントロールできます。一方でSELECT ... やC(T)TAS(※CREATE TEMPORARY TABLE AS SELECT ...)は、入力データを基に列名や列サイズ、データ型を決定し、データ格納のプロパティはデフォルトを使用します。
デフォルトのデータ格納のプロパティは良く検討して利用しないと、問題を引き起こす場合があります。Amazon Redshiftのデフォルトの表定義はEVENディストリビューションを使用し、列のエンコーディング(圧縮)は無しです。これは、多くのクエリにとって次善のデータ構造であり、SELECT ... INTOシンタックスを使う場合はエンコーディングや、ディストリビューション、ソートキーを指定出来ないということです。
全てのSELECT ... INTOクエリは、CREATEステートメントに置き換える事が強く推奨されます。変更することで、テンポラリ表の列にエンコーディング(圧縮)がセットでき、ディストリビューションの変更は、ワークフロー上の他の要素にも連鎖的な影響を与えます。ステートメントを変換するには以下のように行います:
1
|
select column_a, column_b into #my_temp_table from my_table; |
テンポラリ表の最適なエンコーディングを得るためにANALYZEを実行します:
そしてSELECT ... INTOステートメントを以下のように変換します:
1
2
3
4
5
6
7
8
9
|
BEGIN ; create temporary table my_temp_table( column_a varchar (128) encode lzo, column_b char (4) encode bytedict) distkey (column_a) -- colum_aはJOINで使用されると想定している sortkey (column_b); -- column_bでソートされる、もしくはグルーピングされると想定している insert into my_temp_table select column_a, column_b from my_table; COMMIT ; |
テンポラリ表が後続のクエリーのジョイン対象になる場合は、統計情報を更新する必要があるかもしれません:
1
|
analyze my_temp_table; |
この方法によって、テンポラリ表を使用しつつ、ディストリビューションキー指定によるデータの配置や、カラムエンコーディング(圧縮)によるAmazon Redshiftの列指向のアドバンテージを得ることが可能になります。
Tip: オプティマイザのアラートを利用する
最後のTipは、クエリー実行時の診断情報を利用することです。この情報は大変使いやすいSTL_ALERT_EVENT_LOGビューに格納されています。perf_alert.sql 管理スクリプトを利用することで、過去7日間でクラスターに発生した問題を診断することができます。これはみなさんのクラスターがこれまでどのように使われてきたのかを理解する大変有用なリソースです。
サマリ
Amazon Redshiftはパワフルで、フルマネージドなデータウェアハウスであり、高いパフォーマンス拡張と、低いコストをクラウド上で提供します。Amazon Redshiftは多様なデータモデルを分析できるだけでなく、データがどう格納され管理されているかに注意することで、パフォーマンス低下やコスト上昇といった落とし穴を避けることが可能です。一般的な課題用の診断クエリを実行し、可能な限りベストのパフォーマンスが得られるようにしてください。
もしご質問やご意見がありましたら、コメントを残してください。
------------------------------------
関連情報(英文)
Best Practices for Micro-Batch Loading on Amazon Redshift
コメント