Amazon Redshift で CPU が多く使用されている状況をトラブルシューティングするにはどうすればよいですか?

簡単な説明

Amazon Redshift は、クエリの実行中に利用可能なすべてのリソースを利用するように設計されています。これは、Redshift クラスターの CPU の使用量が急増する場合があることを意味します。CPU が多く使用される状況の原因は、クラスターのワークロード、不均衡データとソートされていないデータ、リーダーノードのタスクなどの要因によって異なります。

ただし、CPU 使用状況がクエリ時間に影響する場合は、次のアプローチを検討してください。

• Redshift クラスターのワークロードを確認する
• データハイジーンをメンテナンスします。
• テーブルデザインを更新します。
• メンテナンスの更新を確認します。
• リーダーノードの CPU 使用率の急増を確認します。
• Amazon CloudWatch を使用して CPU 使用率の急増を監視します。

解決方法

Redshift クラスターのワークロードを確認する

Redshift クラスターの CPU の使用状況には、次の要因が影響します。

• 作業負荷の増加 (実行中のクエリが増えるため)。ワークロードの増加により、データベース接続の数が増加し、より多くのクエリが同時実行されます。
• 同時実行されるクエリの数が多いほど、リソースの競合、ロックの待機時間、ワークロード管理 (WLM) キューの待機時間に影響します。
• より多くのデータベース接続。これは、クラスターにアイドルセッションが存在していることが原因である可能性があります。アイドルセッションは、さらなるロック競合の問題を引き起こす可能性があります。

クエリの実行中に、ロック情報を取得します。長時間実行されるセッションを識別するには、次の SQL クエリを使用します。

select *,datediff(s,txn_start,getdate())/86400||' days '||datediff(s,txn_start,getdate())%86400/3600||' hrs '||datediff(s,txn_start,getdate())%3600/60||' mins '||datediff(s,txn_start,getdate())%60||' secs' as "duration"
from svv_transactions where lockable_object_type='transactionid' and pid<>pg_backend_pid() order by 3;<br>

次に、PG_TERMINATE_BACKEND を実行して、長時間実行されているトランザクションを停止します。これらのセッションが開いたままにならないようにするには、すべてのトランザクションが閉じられていることを確認してください。例えば、BEGIN ステートメントで始まるすべてのトランザクションには、END ステートメントまたは COMMIT ステートメントが付属していることを確認してください。

その後、次の SQL クエリを実行して、CPU を多く使用しているクエリを特定します。

select stq.userid, stq.query, trim(stq.label) as label, stq.xid, stq.pid, svq.service_class,
query_cpu_usage_percent as "cpu_%",starttime, endtime, datediff(s,starttime, endtime) as duration_s,
substring(stq.querytxt,1,100) as querytext from stl_query stq
join svl_query_metrics svq on stq.query=svq.query 
where query_cpu_usage_percent is not null and starttime > sysdate - 1
order by query_cpu_usage_percent desc;

各クエリのセグメントおよびスライスレベルの実行ステップを分析するには、次のクエリを実行します。

select query, segment, step, label ,is_rrscan as rrS, is_diskbased as disk, is_delayed_scan as DelayS, min(start_time) as starttime, max(end_time) as endtime, datediff(ms, min(start_time), max(end_time)) as "elapsed_msecs", sum(rows) as row_s , sum(rows_pre_filter) as rows_pf, CASE WHEN sum(rows_pre_filter) = 0 THEN 100 ELSE sum(rows)::float/sum(rows_pre_filter)::float*100 END as pct_filter, SUM(workmem)/1024/1024 as "Memory(MB)", SUM(bytes)/1024/1024 as "MB_produced" from svl_query_report where query in (<query_ids>) group by query, segment, step, label , is_rrscan, is_diskbased , is_delayed_scan order by query, segment, step, label;

これらのクエリのチューニングの詳細については、「Top 10 performance tuning techniques for Amazon Redshift」(Amazon Redshift のパフォーマンスチューニングテクニックトップ 10) を参照してください。

また、wlm_query_trend_hourly ビューを使用して、Redshift クラスターのワークロードパターンを確認することもできます。その後、以下のどのアプローチをキューの待機時間の短縮に役立てることができるかを判断します。

• 各クエリスロットに多くのメモリを提供するために、キューあたりのクエリ同時実行数を削減します。この削減は、より効率的に実行するためにより多くのメモリを必要とするクエリに役立ちます。
• 短いクエリアクセラレーション (SQA) を有効にして、実行時間が長いクエリよりも実行時間が短いクエリを優先します。
• 増大するワークロードに対応するために Redshift クラスターをスケールします。クラスターをスケーリングすると、より多くのメモリとコンピューティング能力が提供され、クエリをより迅速に実行するのに役立ちます。詳細については、「Redshift クラスターのサイズ変更はどのように行いますか?」を参照してください。

データハイジーンをメンテナンスする

データの衛生は、古い統計とテーブルに存在するソートされていない行のパーセンテージによって測定されます。両方のパーセンテージが高いと、クエリオプティマイザは、クエリがテーブルを参照するときに非効率的に実行されている場合に実行プランを生成する可能性があります。また、ソートされていないデータがあることで、クエリが不要なデータブロックをスキャンし、追加の I/O オペレーションの必要性が生じる場合があります。パフォーマンスの低いクエリは、Redshift クラスターの CPU の使用状況に悪影響を及ぼします。

SVV_TABLE_INFO システムビューを使用して、テーブルの stats_off および unsorted のパーセンテージデータを取得します。これらのパーセンテージは 0 に近いままにする必要があります。パーセンテージが高い場合は、AWS Labs GitHub リポジトリから Analyze & Vacuum schema utility を実行して、テーブルを更新します。

テーブルデザインを更新する

テーブルデザインは、指定されたソートキー、分散スタイル、および分散キーによって管理されます。分散キーと分散スタイルによって、ノード間でデータを分散する方法が決定されます。

不適切なディストリビューションキーまたはディストリビューションスタイルは、ノード間でディストリビューションの不均衡を引き起こす可能性があります。データディストリビューションの不均衡を低減するには、クエリパターンと述語に基づいてディストリビューションスタイルとソートキーを選択します。分散キーは、カーディナリティの高いクエリとカラムの結合条件をサポートする必要があります。分散キーを適切に選択すると、クエリがハッシュまたはネストされたループ結合の代わりにマージ結合を実行するのに役立ちます。これは、最終的にクエリの実行時間に影響します。

分散が不均衡なテーブルを識別するには、table_inspector.sql スクリプトを使用します。次に、Amazon Redshift テーブルデザインプレイブックを使用して、テーブルに最適なソートキー、ディストリビューションキー、およびディストリビューションスタイルを選択します。

メンテナンスの更新を確認する

Amazon Redshift はコンパイルされたコードをキャッシュします。これにより、クエリは以前実行されたセグメントについてのコードを再利用することができます。その後、キャッシュは、メンテナンスの更新中に消去されます。その結果、パッチ更新後に初めて実行されるクエリは、コンパイルに時間を要します。このコンパイルオーバーヘッドは、Redshift クラスターの CPU の使用状況を増加させる可能性があります。

次の SQL クエリを使用して、1 時間ごとにコンパイルされているセグメント数を確認します。

select "hour", count(query) total_queries, count(case when is_compiled='Y' then 1 else null end ) as queries_compiled_count, sum(segements_count) total_segments_count, sum(segments_compiled_count) total_segments_compiled_count from
(
  select q.query, date_trunc('h',q.starttime) as "hour", q.starttime, q.endtime, q.aborted, (case when compiled_queries.segments_compiled_count = 0 then 'N' ELSE 'Y' end) is_compiled, compiled_queries.segements_count, compiled_queries.segments_compiled_count
  from stl_query q
  left join (select query, count(segment) segements_count, sum(compile) segments_compiled_count from svl_compile group by query) compiled_queries on q.query = compiled_queries.query
  where q.userid > 1
  and q.starttime > trunc(sysdate) -7
)
group by "hour"
order by "hour";

リーダーノードの CPU 使用率の急増を確認する

クエリの解析と最適化、コンパイルされたコードの生成、計算ノードからの結果の集約などのリーダーノードのタスクは、CPU リソースを消費します。この消費により、リーダーノードの CPU 使用率が増加します。また、クエリがシステムカタログテーブルを大量に参照したり、リーダーノードのみの機能を実行したりすると、リーダーノードのCPU使用率が上昇します。

CPU 使用状況の急増がリーダーノードによって発生する場合は、Amazon Redshift コンソールの [Events] (イベント) で確認します。Redshift クラスターでメンテナンスが行われたかどうかを確認します。「メンテナンスの更新を確認する」で提供されている SQL クエリを使用して、コンパイルされているセグメントが通常よりも多いかどうかを確認します。

CloudWatch を使用して CPU 使用率のスパイクをモニタリングする

CloudWatch メトリクスを使用して、CPUutilization と Database Connections の急増を比較します。[Workload Execution Breakdown] (ワークロード実行の内訳) グラフを確認して、ワークロードのパフォーマンスを分析します。[Workload Execution Breakdown] (ワークロード実行の内訳) グラフには、クエリが最も多くの時間を費やしているステージが表示されます。

指定された時間に CPU を最も多く消費する上位 100 位までのクエリを特定するには、次のクエリを使用します。

select qms.*, substring(q.querytxt,1,100) qtxt
from svl_query_metrics_summary qms
join stl_query q on q.query=qms.query
where q.starttime > sysdate - 1 
and q.userid>1
order by qms.query_cpu_time desc nulls last limit 100;

CPU が 100% に達したときにリソースを最も多く消費するクエリのリストを取得するには、次のクエリを使用します。

select a.userid, service_class, a.query, b.pid, b.xid, a.starttime, slices, max_rows, max_blocks_read, max_blocks_to_disk, max_query_scan_size, segment, round(max_cpu_time/(max_run_time*1.0)*100,2) as max_cpu_pcnt, round(cpu_time/(run_time*1.0)*100,2) as cpu_pcnt, max_cpu_time, max_run_time, case when segment > -1 then 'segment' else 'query' end as metric_lvl, text from pg_catalog.stv_query_metrics a left join stv_inflight b using (query) where step_type=-1 order by query, segment;

各ノードで処理されるデータの量を確認するには、次のクエリを実行します。

select iq.day_d, sl.node, sum(iq.elapsed_ms) as elapsed, sum(iq.bytes) as bytes from (select start_time::date as day_d, slice,query,segment,datediff('ms',min(start_time),max(end_time)) as elapsed_ms, sum(bytes) as bytes from svl_query_report where end_time > start_time group by 1,2,3,4) iq join stv_slices as sl on (sl.slice = iq.slice) group by 1,2 order by 1 desc, 3 desc;

クエリモニタリングルール (QMR) を使用すると、設計が不十分なクエリを識別してログに記録できます。例えば、QMR ルールを定義して、CPU を多く使用しているクエリや、長期にわたってランタイムを消費しているクエリをログに記録できます。

---