Redshift と上手に付き合うために心がけていること

こんにちは。最近Redshiftと付き合う時間が増え、良い面も悪い面も少しずつわかってきました。忘れないようにまとめてみます。

Redshift周辺で気をつけていることと心がけていること

COMMITは直列で処理

Redshift のクラスターはノードがいくつあろうとコミット処理は直列で処理されます。これは、WLM(Work Load Management)で同時実行可能クエリ数を増やそうと減らそうと変わりません。コミット処理だけは直列です。その結果、コミットキューにたまりクエリの処理時間がより待たされる可能性があります。

これは時々STL_QUERYというシステムログテーブルでわかるクエリ処理時間よりも、クライアント側で処理完了まで待たされる時間が長いことに疑問を感じて調べてわかることかもしれません。ちなみに、コミット待ちはSTL_COMMIT_STATSというシステムログテーブルを参照すれば詳細がわかります。

COMMITの処理時間を監視

いろいろあって、Redshiftのクラスター毎のコミット処理時間を1時間毎に集計し監視対象にすることにしました。Redshiftに対して様々な集計処理をかけていますが、時間毎のコミット処理時間が55分を超えてきたらアラートを上げています。また、新しい集計処理を追加する場合も可能な限りCOMMITを行わない形にするようにしました。コミット処理時間の集計は STL_COMMIT_STATS から可能です。

時間帯毎のコミット統計を取得するSQL文

SELECT EXTRACT(hour from CONVERT_TIMEZONE('JST',startqueue)) AS hour, count(xid) AS ct, 
(sum(datediff(ms,startqueue,startwork)) / 1000 / 60)  AS total_queue_minutes,
(sum(datediff(ms, startwork, endtime)) / 1000 / 60) AS total_work_minutes, 
(max(datediff(ms,startqueue,startwork)) / 1000 )  AS max_queue_seconds, 
(max(datediff(ms, startwork, endtime)) / 1000 ) AS max_work_seconds 
FROM stl_commit_stats as stats  
WHERE 
stats.startqueue >= CONVERT_TIMEZONE('JST','UTC','%%FROM%%')  
and 
stats.startqueue < CONVERT_TIMEZONE('JST','UTC','%%TO%%') 
GROUP BY hour ORDER BY hour
;

ユーザー毎・時間帯毎のコミット統計を取得するSQL文

ユーザー毎に異なる種類の集計処理を実行している場合に使います。xidを使い STL_COMMIT_STATS と SVL_STATEMENTTEXT を連結してuseridを取得します。

SELECT userid, EXTRACT(hour from CONVERT_TIMEZONE('JST',startqueue)) AS hour, count(xid) AS ct, 
(sum(datediff(ms,startqueue,startwork)) / 1000 / 60)  AS total_queue_minutes,
(sum(datediff(ms, startwork, endtime)) / 1000 / 60) AS total_work_minutes,
(max(datediff(ms,startqueue,startwork)) / 1000 )  AS max_queue_seconds,
(max(datediff(ms, startwork, endtime)) / 1000 ) AS max_work_seconds 
FROM (
  SELECT DISTINCT q.userid, stats.startqueue, stats.xid, stats.startwork, stats.endtime 
  FROM stl_commit_stats as stats INNER JOIN SVL_STATEMENTTEXT as q on stats.xid = q.xid 
  WHERE 
    stats.startqueue >= CONVERT_TIMEZONE('JST','UTC','%%FROM%%') 
    and
    stats.startqueue < CONVERT_TIMEZONE('JST','UTC','%%TO%%')
  ) 
GROUP BY userid, hour ORDER BY userid, hour
;

処理時間の詳細の分析

Redshiftのクラスター上で集計処理を行うと、集計処理の実行側（クライアント）からは、クエリ待ち（キュー）時間＋クエリ実行時間＋コミット待ち（キュー）時間＋コミット処理時間 （※コミットが伴わない場合もある）が集計処理時間となります。この4つを監視対象にして日々以下の指標に沿うように監視とチューニングを行っています。

| 対象 | 方針 |
|---+---|
|クエリ待ち時間 | クエリ待ち時間が無い（0）になるようにする。待ち時間が発生する場合はWLMにより同時実行可能クエリ数を増やす、遅すぎるクエリを見つけ出してチューニングするといった対応を行う。WLMの同時実行可能クエリ数を増やすとクエリあたりで利用可能なメモリ数が減るのでむやみに増やすのは控える。 |
|クエリ実行時間|1時間毎のクエリ実行時間を集計し同時実行可能クエリ数x60分を超えるようなら遅いクエリから順にチューニングを行う。実行計画の確認、STL_ALERT_EVENT_LOGの確認を行いチューニング方針を決定する。|
|コミット待ち時間|可能な限りゼロに近づけたいが、fluentd等からデータを随時投入している場合は待ち時間無しは現実的ではない。キューの最大数と最大待ち時間を STL_COMMIT_STATS から確認し集計処理実行タイミングの見直し、クエリ自体のチューニングを行う。|
|コミット処理時間|1時間毎のコミット処理時間を集計し60分を超えないようにする。55分を超えたらアラートを上げ、ノードの追加、チューニングの検討を行う。チューニングが無理ならノードを追加し対応する。|

STL_ALERT_EVENT_LOG との付き合い方

STL_ALERT_EVENT_LOG には実行されたSQLの問題点とその解決方法が記録されています。solution として記録されていることを実行すると必ずしも改善するわけではありませんが、問題の発見の糸口として定期的に確認するのが良いと思います。

ただ、処理の高速化を考える際にはSTL_ALERT_EVENT_LOGで問題を見つけるよりも根本的なRedshiftの使い方に問題が無いか？を見てみる必要があるかもしれません。

スナップショットがコミットに影響？

Redshiftは５GB程度データに変更があるとスナップショットを取得するようになっています。このスナップショットの生成処理がコミット処理に影響する可能性があることがわかっています（近々改善されるかもしれません 2016/7/20時点）。

ちなみにスナップショット取得処理の詳細は STL_UTILITYTEXT に

xpx 'backup sys-20160714200514'

という形で記録されています。コンソールからスナップショットの一覧を確認できますが、コンソール上の情報よりスナップショットの生成時間ではSTL_UTILITYTEXTの情報が詳細です。コミット処理が予期せず遅い（待たされる）ようなときにはスナップショットが影響している可能性も考える必要があるかもしれません。

SSDにするのか？

後で書く

INTERLEAVED SORTKEY を使う？

2015年（昨年）の後半に新機能としてINTERLEAVED SORTKEYが追加されました。なかなか思ったような効果がでないので使いドコロに困っています。INTERLEAVEDにしたことでREAD対象のブロック数が増えディスクIOが増えることで、逆に遅くなるということもなんとなくわかっています（内部的な動作を確かめたわけではないので推測の域を出ないのですが）。

fluentd 経由でのデータ投入遅延

Redshiftへのデータ投入方法は

• COPYコマンドを用いたS3から
• INSERTクエリ

の2つが主な方法ですが、後者は遅いので推奨されていません。現在稼働中のシステムではfluentdのredshiftプラグインを使いデータの投入を行っています。このプラグインは

• S3へのアップロード
• COPYコマンドでRedshiftにデータ投入

の2つのステップでデータ投入を完了します。可能な限りリアルタイムな集計処理ができるようにしたいのですが、S3へのアップロードに遅延が発生する可能性も考慮する必要があります。そこで、Redshiftに投入されるデータの投入遅延時間も測定し、ビジネス要件から許される遅延が発生しないか監視するようにしています。

サポートの方とは根気強く付き合う

これはRedshiftのサポートに限りませんが、考えうる限りのチューニング検討を行い試行を繰り返した後Redshift自体の問題に気づくことがあるかもしれません。その場合、AWSのサポートから問い合わせを行うわけですが、例えば気づいた問題で「予期せぬ処理の遅延」が発生していたとしても、Redshiftで処理の遅延が発生する可能性は様々ありなかなかこちらが気づいたRedshift自体の問題に向き合ってくれないことがあります。

• VACCUMしましょう
• ANALYZEしましょう
• COMMITしすぎです

等、一般的なよくある解決案を提示されることがあります。

仮にそのような事があっても根気強くデータを提示し、また、調査方法に関するアドバイスを得ながら何回かやり取りをすれば解決に向け建設的なやり取りを行うことが可能です。こちらとしては、プロダクション環境で動いているシステムに影響が出ているので感情的には「ムカつく」のですが、動いていただかなければ解決できない以上根気強く対応するしか無いのです。

メンテ前後のチューニングは行わない

Redshiftは週1回自動でメンテナンスになります。AWS側からメンテナンスによる細かい対応内容は発表されていませんが、メンテナンス後からパフォーマンスが見違える事があります（たいてい良い結果なのですが）。これまで、メンテナンス後から集計処理のパフォーマンスが4倍に向上したことがあり、データ投入漏れを疑い調査したこともありました。この経験を踏まえ、メンテ前後24時間はチューニング関連の集計処理の修正は行わないようにしています。