Amazon Redshift Advisor のレコメンデーション

Amazon Redshift Advisor は、Amazon Redshift クラスターを最適化してパフォーマンスを高め、運用コストを低減するための方法に関するレコメンデーションを提供します。前述したように、各レコメンデーションの説明はコンソールに表示されます。これらのレコメンデーションの詳細は、以下のセクションで説明します。

COPY によってロードされた Amazon S3 ファイルオブジェクトを圧縮する

COPY コマンドは Amazon Redshift の超並列処理 (MPP) アーキテクチャを活用して、データを並列で読み込み、ロードします。このコマンドは Amazon S3 のファイル、DynamoDB テーブル、および 1 つ以上のリモートホストからのテキスト出力を読み込むことができます。

大量のデータをロードするときは、COPY コマンドを使用して、S3 から圧縮されたデータファイルをロードすることを強くお勧めします。大きなデータセットを圧縮することで、ファイルを Amazon S3 にアップロードする時間を短縮できます。また、COPY を使えば、読み込み時にファイルが解凍され、ロードプロセスが高速化されます。

分析

大きな非圧縮データセットをよくロードする、長時間実行される COPY コマンドでは、かなりパフォーマンスが向上する可能性があります。Advisor の分析では、大きな非圧縮データセットをロードする COPY コマンドが識別されます。このような場合、Advisor は Amazon S3 のソースファイルで圧縮を実装するためのレコメンデーションを生成します。

レコメンデーション

大量のデータをロードするか、かなり長時間実行される各 COPY で、Amazon S3 から圧縮されたデータオブジェクトが取り込まれることを確認します。スーパーユーザーとして次の SQL コマンドを実行して、Amazon S3 から大きな非圧縮データセットをロードする COPY コマンドを識別できます。

SELECT
    wq.userid, query, exec_start_time AS starttime, COUNT(*) num_files,
    ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs,
    ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb,
    SUBSTRING(querytxt,1,60) copy_sql
FROM stl_s3client s
JOIN stl_query q USING (query)
JOIN stl_wlm_query wq USING (query)
WHERE s.userid>1 AND http_method = 'GET'
    AND POSITION('COPY ANALYZE' IN querytxt) = 0
    AND aborted = 0 AND final_state='Completed'
GROUP BY 1, 2, 3, 7
HAVING SUM(transfer_size) = SUM(data_size) 
AND SUM(transfer_size)/(1024*1024) >= 5
ORDER BY 6 DESC, 5 DESC;         
          

ロード後に、ステージングされたデータが Amazon S3 に残る場合 (データレイクアーキテクチャでは一般的です)、圧縮形式でこのデータを保存すると、ストレージコストを削減できます。

実装のヒント

複数のアクティブなデータベースを分離する

ベストプラクティスとして、Amazon Redshift のデータベースを相互に分離することをお勧めします。特定のデータベースで実行されるクエリは、クラスターの他のデータベースからのデータにアクセスできません。ただし、クラスターのすべてのデータベースで実行するクエリは、基盤となる同じストレージスペースとコンピューティングリソースを共有します。1 つのクラスターに複数のアクティブなデータベースが含まれる場合、そのワークロードは通常は無関係です。

分析

Advisor の分析では、同時に実行中のアクティブなワークロードに対するクラスター上のすべてのデータベースが確認されます。同時に実行中のアクティブなワークロードがある場合、Advisor は別の Amazon Redshift クラスターへのデータベースの移行を検討するためのレコメンデーションを生成します。

レコメンデーション

アクティブにクエリが実行されている各データベースを、別の専用クラスターに移動することを検討します。別のクラスターを使用するとリソースの競合が減少し、クエリのパフォーマンスが向上します。これが可能であるのは、各ワークロードのストレージ、コスト、およびパフォーマンスのニーズに対して各クラスターのサイズを設定できるためです。また、多くの場合、関連しないワークロードでさまざまなワークロード関連設定の利点を得ることができます。

アクティブに使用されているデータベースを識別するには、スーパーユーザーとしてこの SQL コマンドを実行できます。

SELECT database,
  COUNT(*) as num_queries,
  AVG(DATEDIFF(sec,starttime,endtime)) avg_duration,
  MIN(starttime) as oldest_ts,
  MAX(endtime) as latest_ts
FROM stl_query
WHERE userid > 1
GROUP BY database;            
         

実装のヒント

ワークロード管理 (WLM) メモリを再割り当てする

Amazon Redshift はユーザークエリを処理のために 手動 WLM を実装する にルーティングします。ワークロード管理 (WLM) は、クエリがキューにルーティングされる方法を定義します。Amazon Redshift は、各キューにクラスターの使用可能なメモリの一部を割り当てます。キューのメモリは、キューのクエリスロットに分けられます。

ワークロードが必要とするよりも多くのスロットでキューが設定されると、それらの未使用スロットに割り当てられたメモリの使用率が低くなります。設定されたスロットをワークロードの要件に合わせて減らすと、使用率の低いメモリがアクティブなスロットに再分散され、クエリのパフォーマンスが向上します。

分析

Advisor の分析では、ワークロードの同時実行要件を確認し、未使用スロットがあるクエリキューを識別します。Advisor は、以下が見つかると、キューのスロット数を減らすためのレコメンデーションを生成します。

レコメンデーション

設定されたスロットをピーク時のワークロード要件に合わせて減らすと、使用率が低いメモリがアクティブなスロットに再分散されます。スロットが十分に利用されていないキューに対して設定されたスロット数を減らすことを検討します。それらのキューを確認するには、スーパーユーザーとして次の SQL コマンドを実行することにより、各キューの 1 時間あたりのピークスロット要件を比較できます。

WITH
 generate_dt_series AS (select sysdate - (n * interval '5 second') as dt from (select row_number() over () as n from stl_scan limit 17280)),
 apex AS (
     SELECT iq.dt, iq.service_class, iq.num_query_tasks, count(iq.slot_count) as service_class_queries, sum(iq.slot_count) as service_class_slots
     FROM
         (select gds.dt, wq.service_class, wscc.num_query_tasks, wq.slot_count
         FROM stl_wlm_query wq
         JOIN stv_wlm_service_class_config wscc ON (wscc.service_class = wq.service_class AND wscc.service_class > 5)
         JOIN generate_dt_series gds ON (wq.service_class_start_time <= gds.dt AND wq.service_class_end_time > gds.dt)
         WHERE wq.userid > 1 AND wq.service_class > 5) iq
     GROUP BY iq.dt, iq.service_class, iq.num_query_tasks),
     maxes as (SELECT apex.service_class, trunc(apex.dt) as d, date_part(h,apex.dt) as dt_h, max(service_class_slots) max_service_class_slots
                     from apex group by apex.service_class, apex.dt, date_part(h,apex.dt))
SELECT apex.service_class - 5 AS queue, apex.service_class, apex.num_query_tasks AS max_wlm_concurrency, maxes.d AS day, maxes.dt_h || ':00 - ' || maxes.dt_h || ':59' as hour, MAX(apex.service_class_slots) as max_service_class_slots
FROM apex
JOIN maxes ON (apex.service_class = maxes.service_class AND apex.service_class_slots = maxes.max_service_class_slots)
GROUP BY  apex.service_class, apex.num_query_tasks, maxes.d, maxes.dt_h
ORDER BY apex.service_class, maxes.d, maxes.dt_h;            
         

max_service_class_slots 列はその時間のクエリキューの WLM クエリスロットの最大数を表します。使用率の低いキューが存在する場合は、「Amazon Redshift 管理ガイド」に記載されているようにパラメータグループを変更して、スロット削減の最適化を実装します。

実装のヒント

COPY 中に圧縮分析をスキップする

COPY コマンドで宣言された圧縮エンコードで空のテーブルにデータをロードすると、Amazon Redshift はストレージの圧縮を適用します。この最適化により、エンドユーザーによってロードされた場合でも、クラスターのデータが効率的に保存されます。圧縮を適用するために必要な分析には、かなり時間がかかる場合があります。

分析

Advisor の分析では、自動圧縮分析で遅れた COPY 操作をチェックします。この分析では、ロード中のデータをサンプリングして、圧縮エンコードが決定されます。このサンプリングは、ANALYZE COMPRESSION コマンドで実行されるサンプリングに似ています。

夜間の抽出、変換、ロード (ETL) バッチなどで、構造化プロセスの一部としてデータをロードする場合は、事前に圧縮を定義できます。また、悪影響なくこのフェーズを完全にスキップするようにテーブル定義を最適化することもできます。

レコメンデーション

圧縮分析フェーズをスキップして COPY の応答性を向上させるには、次の 2 つのオプションのいずれかを実装します。

通常、最適なソリューションはテーブルの作成中に列のエンコーディングを使用することです。この手法により、圧縮されたデータをディスクに保存する利点も維持できます。ANALYZE COMPRESSION コマンドを使用して圧縮エンコーディングを提案できますが、これらのエンコーディングを適用するには、テーブルを再作成する必要があります。このプロセスを自動化するには、GitHub にある AWS の ColumnEncodingUtility を使用できます。

自動圧縮分析をトリガーした最近の COPY 操作を特定するには、次の SQL コマンドを実行します。

  WITH xids AS (
    SELECT xid FROM stl_query WHERE userid>1 AND aborted=0 
    AND querytxt = 'analyze compression phase 1' GROUP BY xid
    INTERSECT SELECT xid FROM stl_commit_stats WHERE node=-1)
SELECT a.userid, a.query, a.xid, a.starttime, b.complyze_sec, 
    a.copy_sec, a.copy_sql
FROM (SELECT q.userid, q.query, q.xid, date_trunc('s',q.starttime) 
    starttime, substring(querytxt,1,100) as copy_sql, 
    ROUND(datediff(ms,starttime,endtime)::numeric / 1000.0, 2) copy_sec
    FROM stl_query q JOIN xids USING (xid)
    WHERE (querytxt ilike 'copy %from%' OR querytxt ilike '% copy %from%') 
    AND querytxt not like 'COPY ANALYZE %') a
LEFT JOIN (SELECT xid, 
    ROUND(sum(datediff(ms,starttime,endtime))::numeric / 1000.0,2) complyze_sec 
    FROM stl_query q JOIN xids USING (xid)
    WHERE (querytxt like 'COPY ANALYZE %' 
    OR querytxt like 'analyze compression phase %') 
    GROUP BY xid ) b ON a.xid = b.xid
WHERE b.complyze_sec IS NOT NULL ORDER BY a.copy_sql, a.starttime;
         

実装のヒント

COPY によってロードされた Amazon S3 オブジェクトを分割する

COPY コマンドは Amazon Redshift の超並列処理 (MPP) アーキテクチャを活用して、Amazon S3 のファイルからデータを読み込み、ロードします。COPY コマンドは、複数のファイルから同時にデータをロードして、クラスターのノード間でワークロードを分散します。最適なスループットを達成するには、データを複数のファイルに分割し、並列処理の長所を最大限に活用することをお勧めします。

分析

Advisor の分析では、Amazon S3 でステージングされた少数のファイルに含まれる大きなデータセットをロードする COPY コマンドが識別されます。少数のファイルから大きなデータセットをよくロードする、長時間実行される COPY コマンドでは、かなりパフォーマンスが向上する可能性があります。これらの COPY コマンドにかなり長い時間がかかっていると Advisor が判断した場合、Amazon S3 の追加のファイルにデータを分割して、並列処理性を高めるためのレコメンデーションが作成されます。

レコメンデーション

この場合は、次のアクションを実行することをお勧めします。これらは優先順位が高い順に示されています。

特定の COPY コマンドは、大量のデータをロードするか、かなり長時間実行されます。これらのコマンドについては、クラスターのスライス数の倍数と等しい数のデータオブジェクトを Amazon S3 からロードすることをお勧めします。COPY コマンドでロードされた S3 オブジェクトの数を確認するため、スーパーユーザーとして次の SQL コードを実行します。

SELECT
    query, COUNT(*) num_files,
    ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs,
    ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb,
    SUBSTRING(querytxt,1,60) copy_sql
FROM stl_s3client s
JOIN stl_query q USING (query)
JOIN stl_wlm_query wq USING (query)
WHERE s.userid>1 AND http_method = 'GET'
    AND POSITION('COPY ANALYZE' IN querytxt) = 0
    AND aborted = 0 AND final_state='Completed'
GROUP BY query, querytxt
HAVING (SUM(transfer_size)/(1024*1024))/COUNT(*) >= 2
ORDER BY CASE
WHEN COUNT(*) < (SELECT max(node)+1 FROM stv_slices) THEN 1
WHEN COUNT(*) < (SELECT COUNT(*) FROM stv_slices WHERE node=0) THEN 2
ELSE 2+((COUNT(*) % (SELECT COUNT(*) FROM stv_slices))/(SELECT COUNT(*)::DECIMAL FROM stv_slices))
END, (SUM(transfer_size)/(1024.0*1024.0))/COUNT(*) DESC;   
            
         

実装のヒント

テーブル統計の更新

Amazon Redshift はコストベースのクエリオプティマイザを使用して、クエリに対して最適な実行プランを選択します。コストの予測は、ANALYZE コマンドを使用中に収集されたテーブル統計に基づいています。統計が古いか、ない場合、データベースではクエリを実行するために効率の低いプランが選択される可能性があります (特に複雑なクエリの場合)。現在の統計を維持すると、可能な限り短時間で複雑なクエリを実行するうえで役立ちます。

分析

Advisor の分析では、統計が古いか、欠落しているテーブルが追跡されます。複雑なクエリに関連するテーブルアクセスのメタデータが確認されます。複雑なパターンで頻繁にアクセスされるテーブルに統計がない場合、Advisor は ANALYZE を実行するための重要なレコメンデーションを作成します。複雑なパターンで頻繁にアクセスされるテーブルに古い統計がある場合、Advisor は ANALYZE を実行するためのレコメンデーション提案を作成します。

レコメンデーション

テーブルの内容が大きく変わるたびに、ANALYZE で統計を更新します。COPY または INSERT コマンドで、既存のテーブルにかなりの数の新しいデータ行をロードするたびに、ANALYZE を実行することをお勧めします。また、UPDATE または DELETE コマンドを使用してかなりの数の行を変更するたびに、ANALYZE を実行することをお勧めします。統計がない、または統計が古いテーブルを識別するには、スーパーユーザーとして次の SQL コマンドを実行します。結果は、最も大きなテーブルから最も小さいテーブルの順にソートされます。

統計がない、または統計が古いテーブルを識別するには、スーパーユーザーとして次の SQL コマンドを実行します。結果は、最も大きなテーブルから最も小さいテーブルの順にソートされます。

SELECT
   ti.schema||'.'||ti."table" tablename,
   ti.size table_size_mb,
   ti.stats_off statistics_accuracy
 FROM svv_table_info ti
 WHERE ti.stats_off > 5.00
 ORDER BY ti.size DESC;          
         

実装のヒント

デフォルトの ANALYZE のしきい値は 10 パーセントです。これは、最後の ANALYZE から変更されたテーブルの行が 10 パーセント未満の場合、ANALYZE コマンドは指定されたテーブルをスキップすることを意味します。その結果、各 ETL プロセスの最後に ANALYZE コマンドを発行する選択ができます。この手法では、ANALYZE は頻繁にスキップされますが、必要なときには ANALYZE が確実に実行されます。

ANALYZE の統計は、結合で使用される列 (例: JOIN tbl_a ON col_b)、または述語として使用される列 (例: WHERE col_b = 'xyz') に対して最も大きな影響があります。デフォルトでは、ANALYZE は指定されたテーブルのすべての列の統計を収集します。必要に応じて、最も影響が大きい列に対してのみ ANALYZE を実行して、ANALYZE の実行に必要な時間を短縮できます。述語として使用される列を識別するには、次の SQL コマンドを実行できます。また、ANALYZE PREDICATE COLUMNS を指定して、分析する列を Amazon Redshift で自動的に選択することもできます。

WITH predicate_column_info as (
SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num,  a.attname as col_name,
        CASE
            WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') 
            WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||')
            WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||')
            WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||')
            ELSE NULL::varchar
        END AS pred_ts
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_namespace ns ON c.relnamespace = ns.oid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum)
SELECT schema_name, table_name, col_num, col_name,
       pred_ts NOT LIKE '2000-01-01%' AS is_predicate,
       CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use,
       CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze
FROM predicate_column_info;

詳細については、「テーブルを分析する」を参照してください。

ショートクエリアクセラレーションの有効化

ショートクエリアクセラレーション (SQA) は、実行時間が短い一部のクエリを、実行時間が長いクエリよりも優先します。SQA では実行時間が短いクエリを専用領域で実行します。このため SQA クエリは、実行時間が長いクエリをキューで待機するよう強制されません。SQA は、実行時間が短く、ユーザー定義のキュー内にあるクエリのみを優先します。SQA によって実行時間が短いクエリの実行開始が早くなり、ユーザーへの結果表示も早くなります。

SQA を有効にすると、ショートクエリの実行に割り当てられるワークロード管理 (WLM) キューを減らす、またはなくすことができます。さらに、キュー内のスロットに対する実行時間が長いクエリとショートクエリの競合が不要になるため、WLM キューが使用するクエリスロットの数を少なく設定できます。同時実行数が減るとクエリのスループットが向上し、大部分のワークロードに関するシステム全体のパフォーマンスも向上します。詳細については、「ショートクエリアクセラレーション」を参照してください。

分析

Advisor はワークロードパターンを確認し、SQA がレイテンシーと SQA 対象クエリの日次キュー時間を減らす最近のクエリの数を報告します。

レコメンデーション

WLM 設定を変更して SQA を有効にします。Amazon Redshift は、機械学習アルゴリズムを使用して適格な各クエリを分析します。クエリのパターンを SQA が学習するため、予測精度は向上します。詳細については、「ワークロード管理の設定」を参照してください。

SQA を有効にすると、WLM は、ショートクエリの最大実行時間をデフォルトで動的に設定します。SQA の最大実行時間の動的設定を保持することをお勧めします。

実装のヒント

SQA が有効になっているかどうか確認するには、以下のクエリを実行します。クエリが 1 行を返した場合、SQA は有効です。

select * from stv_wlm_service_class_config 
where service_class = 14;

詳細については、「SQA のモニタリング」を参照してください。

テーブルの分散キーを変更する

Amazon Redshift は、テーブルディストリビューションスタイルに従ってクラスター全体にテーブル行を分散します。キー分散を含むテーブルでは、分散キー (DISTKEY) として列が必要です。テーブル行は、その DISTKEY 列の値に基づいてクラスターのノードスライスに割り当てられます。

適切な DISTKEY によって、各ノードスライスに同様の数の行が配置され、結合条件で頻繁に参照されます。結合は、テーブルが DISTKEY 列で結合されるときに最適化され、その結果クエリのパフォーマンスは向上します。

分析

Advisor は、クラスターのワークロードを分析して、キー分散スタイルから大きなメリットが得られるテーブルに最適な分散キーを識別します。

レコメンデーション

Advisor には、分析に基づいてテーブルの DISTSTYLE および DISTKEY を変更する ALTER TABLE ステートメントが用意されています。パフォーマンスのメリットを大幅に向上させるには、レコメンデーショングループ内の SQL ステートメントをすべて実装する必要があります。

ALTER TABLE を使用して大きなテーブルを再分散すると、クラスターリソースが消費され、一時的なテーブルロックがさまざまな時点で必要になります。他のクラスターのワークロードが軽いときに、レコメンデーションの各グループを実装します。テーブル分散プロパティの最適化の詳細については、Amazon Redshift エンジニアリングの高度なテーブル設計の計画: ディストリビューションスタイルとディストリビューションキーを参照してください。

ALTER DISTSYLE および DISTKEY の詳細については、ALTER TABLE を参照してください。

テーブルのソートキーを変更する

Amazon Redshift は、テーブルのソートキーに従ってテーブル行をソートします。テーブル行のソートは、ソートキー列の値に基づいています。

テーブルを適切なソートキーでソートすると、ディスクから読み取るテーブルブロックが少なくなるため、特に範囲が制限された述語を持つクエリのパフォーマンスが向上します。

分析

Advisor は数日にわたってクラスターのワークロードを分析し、テーブルにとって有益なソートキーを特定します。

レコメンデーション

Advisor は、分析に基づいてテーブルのソートキーを変更するために、以下の 2 グループの ALTER TABLE ステートメントを提供します。

ALTER TABLE を使用して大きなテーブルをソートすると、クラスターリソースが消費され、さまざまなタイミングでテーブルロックが必要になります。クラスターのワークロードが中程度の場合は、各レコメンデーションを実装します。テーブルソートキー設定の最適化の詳細については、Amazon Redshift エンジニアリングの高度なテーブル設計プレイブック: 複合ソートキーおよびインターリーブソートキーを参照してください。

ALTER SORTKEY の詳細については、ALTER TABLE を参照してください。

列の圧縮エンコードを変更する

圧縮は、データの保存時にそのサイズを小さくする列レベルの操作です。Amazon Redshift では、ディスクの I/O 量を減らすことによってストレージ領域を節約し、クエリパフォーマンスを向上させるために、圧縮を使用します。各列のデータ型とクエリパターンに基づいて、最適な圧縮エンコードを行うことをお勧めします。最適な圧縮を行えば、クエリをより効率的に実行でき、データベースが占めるストレージ領域を最小限に抑えることができます。

分析

Advisor は、クラスターのワークロードとデータベーススキーマの分析を継続的に実行し、各テーブルの列に最適な圧縮エンコードを特定します。

レコメンデーション

Advisor は、分析に基づいて特定の列の圧縮エンコードを変更する ALTER TABLE ステートメントを提供します。

ALTER TABLE で列圧縮エンコードを変更すると、クラスターのリソースが使用され、さまざまな時点でテーブルロックが必要になります。クラスターのワークロードが軽い場合は、レコメンデーションを実装することをお勧めします。

参考までに、ALTER TABLE の例 は列のエンコードを変更するステートメントをいくつか示します。

データ型のレコメンデーション

Amazon Redshift には、さまざまなユースケースに対応する SQL データ型のライブラリがあります。これらのデータ型には、INT のような整数型や、VARCHAR のような文字を保存する型などがあります。Redshift は、迅速なアクセスと優れたクエリパフォーマンスを実現するために、最適化された方法でデータ型を保存します。Redshift は特定のデータ型に対する関数も提供し、これらはクエリ結果のフォーマットや計算に使用できます。

分析

Advisor は、クラスターのワークロードとデータベーススキーマの分析を継続的に実行して、データ型の変更によって大きなメリットが得られる列を特定します。

レコメンデーション

Advisor は、提案されたデータ型を使用した新しい列を追加する ALTER TABLE ステートメントを提供します。それに付随する UPDATE ステートメントは、既存の列のデータを新しい列にコピーします。新しい列を作成してデータをロードしたら、新しい列にアクセスするようにクエリと取り込みスクリプトを変更します。次に、新しいデータ型に特化した機能と関数を活用します。これらは「SQL 関数リファレンス」に記載されています。

既存のデータを新しい列にコピーするには、時間がかかる場合があります。Advisor のレコメンデーションは、それぞれクラスターのワークロードが軽いときに実装することをお勧めします。「データ型」で、利用可能なデータ型のリストを参照してください。

Advisor は、十分なデータがない場合、またはデータ型を変更することで期待されるメリットが少ない場合にはレコメンデーションを提供しません。