Amazon Redshift Query profiler でクエリ性能診断を簡素化

本記事は、Simplify your query performance diagnostics in Amazon Redshift with Query profiler を翻訳したものです。

Amazon Redshift は、大規模にデータを分析できる高速、スケーラブル、安全、完全に管理された、クラウドデータウェアハウスです。Amazon Redshift Serverless では、プロビジョニングされたデータウェアハウスに必要なよくある設定作業なしで、データにアクセスして分析できます。リソースは自動的にプロビジョニングされ、データウェアハウスの能力は、最も要求が厳しく予測不能なワークロードに対しても高速なパフォーマンスを提供するように、賢くスケールします。Amazon Redshift リソースを手動で管理したい場合は、データクエリの要求に応えるためのプロビジョニングされたクラスターを作成できます。詳細については、Amazon Redshift クラスターを参照してください。

Amazon Redshift は、プロビジョニングされたクラスター、サーバーレスのワークグループ、およびデータベースのヘルスとパフォーマンスを追跡できるようにパフォーマンスメトリクスとデータを提供します。Amazon Redshift コンソールで使用できるパフォーマンスデータは、2 つのカテゴリーに分類されます。

Amazon CloudWatch メトリクス – クラスターやサーバーレスのリソース利用率、レイテンシー、スループットなど、物理的な側面を監視できます。
クエリとロードのパフォーマンスデータ – データベースアクティビティを監視し、クエリのパフォーマンス問題を検査および診断できます。

Amazon Redshift は、Query profiler と呼ばれる新機能を提供します。Query profiler は、クエリのコンポーネントとパフォーマンスを分析するための、グラフィカルなツールです。この機能は Amazon Redshift コンソールの一部で、クエリの実行順序、実行プラン、さまざまな統計情報を視覚的かつグラフィカルに表示します。Query profiler によって、ユーザーはクエリを理解して問題を特定しやすくなります。

このブログでは、クエリのパフォーマンスをトラブルシューティングする 2 つの一般的なユースケースについて説明します。Query profiler を使って長時間実行されるクエリを分析・トラブルシューティングする手順を、ステップバイステップでお見せします。

概要

Amazon Redshift Serverless では、Query profiler にはサーバーレスコンソールからアクセスできます。Query and database monitoring を選択し、クエリを選択して Query plan タブに移動してください。クエリプランが利用可能であれば、子クエリのリストが表示されます。Query profiler を表示するには、クエリを選択してください。

プロビジョニングされた Amazon Redshift では、プロビジョニングされたクラスターのダッシュボードから Query profiler にアクセスできます。Query and loads を選択し、クエリを選択します。Query plan タブに移動します。クエリプランが利用可能な場合、子クエリのリストが表示されます。クエリを選択すると、Query profiler を表示できます。

前提条件

以下のようなサンプルのAWS Identity and Access Management(IAM) ポリシーを使用して、IAM ユーザまたはロールに最低限の権限を設定し、AWS コンソールから Query profiler にアクセスできます。プロビジョニングされた Redshift クラスターのダッシュボードのQyery and loads セクション、またはサーバーレス Redshift のダッシュボードの Query and database monitoring セクションへのアクセス権限がすでに IAM ユーザまたはロールにある場合は、追加の権限は必要ありません。

{
    "Version": "2012-10-17",
    "Statement": [ 
        {
            "Effect": "Allow",
            "Action": [ 
                "redshift:DescribeClusters",
                "redshift-serverless:ListNamespaces",
                "redshift-serverless:ListWorkgroups",
                "redshift-data:ExecuteStatement",
                "redshift-data:DescribeStatement",
                "redshift-data:GetStatementResult"
            ],
            "Resource": [ 
                "arn:aws:redshift-serverless:",
                "arn:aws:redshift-serverless:",
                "arn:aws:redshift:"
            ] 
        }
    ] 
}

既存の Amazon Redshift データウェアハウスとクエリを使用してご自身のアカウントで Query profiler を使うことができます。ただし、既存の Amazon Redshift データウェアハウスでこのデモを実装したい場合は、Redshift Query Editer v2 ノートブック Redshift Query profiler demo をダウンロードし、後続の データの読み込みセクションを参照してください。
ユーザーが実行したクエリを表示するには、データベース認証情報を使用してクラスターに接続し、データベースユーザーに sys:operator または sys:monitor ロールを付与する必要があります。

データの読み込み

Amazon Redshift Query Editor v2 にはサンプルデータが付属しており、サンプルデータベースと対応するスキーマにロードできます。サンプルデータに対して Query profiler をテストするには、tpcds サンプルデータをロードし、サンプルデータに対してクエリを実行してください。

tpcds サンプルデータをロードするには、Redshift Query Editor v2 を起動し、データベース sample_data_dev を展開します。
tpcdsのアイコンを選択します。
Query Editor v2 がデータをデータベース sample_data_dev 内のスキーマ tpcds にロードします。

次のスクリーンショットにこれらの手順が示されています。

次のスクリーンショットに示すように、以下のサンプルクエリを実行してデータを確認してください。

select count(*) from sample_data_dev.tpcds.customer;

ユースケース

このブログでは、クエリのパフォーマンスに関する 2 つの一般的なユースケースと Query profiler を利用してパフォーマンスの問題をトラブルシューティングする方法について説明します。

ネストされたループ結合 – この結合の種類は、可能な結合の種類の中で最も遅いものです。ネストされたループ結合は、結合条件のないクロス結合となり、2 つのテーブルのデカルト積となります。
最適ではないデータ分散 – データ分散が適切でない場合、大きなテーブルを結合するときに、コンピューティングノード間でのデータの大量ブロードキャストまたは再分散が発生する可能性があります。

ユースケース 1 : ネストされたループ結合

ネストされたループ結合のパフォーマンス問題をトラブルシューティングするには、次の手順に従って Query profiler を使用してください。

上述の前提条件セクションでダウンロードしたノートブックを、Redshift Query Editor v2 にインポートします。
Query Editor v2 でデータベースのコンテキストを sample_data_dev に設定します。次のスクリーンショットを参照してください。
デモノートブックからセル #3 を実行し、ネストされたループ結合に関連するクエリのパフォーマンスの問題を診断します。

クエリを実行するのに約 12 秒かかります。次のスクリーンショットの Query Editor v2 の結果パネルに示されています。

セル #5 を実行して、 SYS_QUERY_HISTORY から query id を取得します。前の手順で設定したクエリラベルを使ってフィルタリングしています。
Amazon Redshift コンソールのナビゲーションペインで、 Query and loads を選択し、次のスクリーンショットに示すように、クエリが実行されたクラスター名を選択します。
これにより新しい Query profiler が開かれます。 Query history セクションで Connect to database を選択します。データベースへの接続に成功すると、ステータスが Connected と表示され、次のスクリーンショットのようクエリ履歴が表示されます。
クエリは Query ID または Process ID で検索できます。次のスクリーンショットのように、先ほど取得した Query ID を入力して、長時間実行されたクエリをフィルタリングし、対応する Query ID を選択します。
次のスクリーンショットのように、 Query plan タブで Child query 1 を選択します。複数の Child query がある場合は、パフォーマンスの問題がないかそれぞれを検査する必要があります。
ツリービューでクエリプランが表示され、サイドパネルに追加のメトリクスが表示されます。これにより、クエリストリーム、セグメント、ステップを簡単に分析できます。ストリーム、セグメント、ステップの詳細については、Amazon Redshift Database Developer Guide の Query planning and execution workflow を参照してください。
ストリームの表示を有効にし、Streams パネルで実行時間が最も長いストリームを特定します。この場合、Streams ID 5 がクエリの実行時間の大部分を占めています。次のスクリーンショットを参照してください。
Streams パネルの ID で 5 を選択し、Stream 5 を分析対象に設定します。Stream 5 には Nestloop ステップがあります。次のスクリーンショットを参照してください。
Nestloop ステップを選択して詳細を分析します。サイドパネルが変わり、ステップの詳細とネストされたループ結合の追加メトリックスが表示されます。
Step details – nestloop を見ると、Input rows と Output rows を比較できます。次のスクリーンショットのように、Store_returns テーブルとのクロス結合のため、287,514 の入力行が 950,233,770 行に増えており、これがクエリの実行が遅くなった原因です。
store_sales と store_returns の間に結合条件を追加してクエリを修正します。Query editor v2 デモノートブックの セル #7を実行します。修正したクエリは 307 ミリ秒 で実行されます。

ユースケース 2 : 最適ではないデータ分散

最適ではないデータ分散をデモンストレーションするために、 web_sales と web_returns テーブルの分散スタイルを EVEN に変更します。 Query editor v2 デモノートブックの セル #10 を実行してください。

セル #12 を実行します。次の Query Editor v2 のスクリーンショットの経過時間に示されているように、クエリの実行には 409 ミリ秒 かかります。
以下の手順に従って ユースケース 1 のステップ 3 〜 10 を実行し、前のクエリの query_id を特定し、Query profiler を開いてください。
Query profiler ページで View streams をオンにします。 Streams サイドパネルで実行時間が最も長いストリームを調べて特定してください。このケースでは、ストリーム ID 6 がクエリの実行時間の大半を占めていることが、次のスクリーンショットから分かります。
Streams サイドパネルで ID の下から 6 を選択して、さらに分析します。

Stream 6 はハッシュ結合のステップを示しています。これは両方が再分散された 2 つのテーブルのハッシュ結合を含んでいます。次のスクリーンショットの Explain plan node information に Hash Right Join DS_DIST_BOTH と示されているところから判断できます。通常、これらの再分散は、テーブルが分散キーで結合されていないか、正しい分散形式でないためです。大規模なテーブルの場合、これらの再分散はパフォーマンスの大幅な低下につながる可能性があるため、そのようなステップを特定して修正し、クエリのパフォーマンスを最適化することが重要です。

web_sales テーブルおよび web_returns テーブルで適切な分散キーを選択して、この最適ではないデータ分散パターンを修正します。分散スタイルを変更するには、デモノートブックのセル #14 を実行してテーブルを変更します。
コマンドの実行が完了したら、セル #16 を実行して Select クエリを再実行します。次の Query Editor のスクリーンショットに示されているように、 web_sales テーブルと web_returns テーブルの分散スタイルをキー分散に更新した後では、同じクエリが 244 ミリ秒で完了しました。
Query profiler で、 View streams をオンにすると、 Stream 5 に最も時間がかかっていることがわかります。完了までに 8 ミリ秒かかりましたが、前の手順では 13 ミリ秒でした。
Streams サイドパネルの ID の下で、 5 を選択してさらにドリルダウンし、 Hashjoin を選択します。次のスクリーンショットに示すように、web_sales テーブルと web_return テーブルの両方で分散スタイルをキー分散に変更すると、クエリ実行時にテーブルを再分散する必要がなくなり、パフォーマンスが最適化されます。

考慮事項

Query profiler を使用する場合は、以下の詳細に注意してください。

Query profiler は、SYS_QUERY_HISTORY、SYS_QUERY_EXPLAIN、SYS_QUERY_DETAIL、SYS_CHILD_QUERY_TEXT ビューから返される情報を表示します。
Query profiler は、データベース上で直近で実行されたクエリの情報のみを表示します。事前にキャッシュされた結果セットを使用してクエリが完了した場合、Amazon Redshift はそのようなクエリに対してクエリプランを生成しないため、Query profiler にはその情報が表示されません。
Query profiler によって実行されるクエリ情報を返すクエリは、ユーザー定義のクエリと同じデータウェアハウス上で実行されます。

クリーンアップ

予期しないコストが発生するのを防ぐため、作成したリソースを削除するには以下の手順を実行してください。
tpcds スキーマの下にある sample_data_dev 内のテーブルをすべてドロップしてください。

まとめ

このブログでは、Amazon Redshift Query Profiler を使って長時間実行されるクエリを監視とトラブルシューティングする方法を説明しました。クエリ実行プランと統計を調べ、クエリの速度低下の根本原因を特定することで、クエリパフォーマンスを分析するステップバイステップのアプローチを示しました。この機能をみなさんの環境で試していただき、フィードバックをお寄せください。

翻訳は Solutions Architect の池田が担当しました。

Amazon Web Services ブログ