【AWSRedshift】ソートキーまとめ

AWS AWS Redshift Database

AWS Redshiftの「ソートキー」の概要、チューニングの勘所、デメリットを解説。

ソートキーとは何か

AWS Redshift特有のテーブルへの設定項目。標準SQLにはない。

CREATE TABLE accesslog (
  createat timestamp  ENCODE raw,
  srcip varchar(16)  ENCODE raw,
  useragent varchar(255)  ENCODE text255,
  path varchar(1024) ENCODE zstd
)
SORTKEY (createat)
;

ソートキーを指定することで、行が、物理的に並び替えられる。

本当にそれだけ。インデックスのような高等な機能ではなく、データの更新行数が多かった時にVACUUMのソート処理によってえげつない時間リソース消費と他VACUUMの利用制限がかかるデメリットもある。

データブロック、ゾーンマップ

ソートキーで何が改善するかを知るにはまずRedshift内のデータ保存構造を知る必要がある。

下記は、上記の accesslog テーブルに仮に20万レコードあった場合の、createat列とsrcip列のディスク保存イメージ図。

f:id:packpak:20190616115409p:plain

Redshiftは、1の列のみを1MBに詰めるだけ詰めて並べて、それぞれをデータブロックとして保存する。

  • 列ごとに、独立したブロックを持つ。
  • データブロックの個数は、列ごとにばらばら。

また、データブロックごとに「先頭データの行番号」と「末尾データの行番号」「含まれるデータの数」そして「データブロック中の最大値と最小値(先頭8バイトまで)」が記録されており、 AWS Redshiftでは「ゾーンマップ」と呼ばれる、メモリ上に存在するのメタデータになる。

補足：データブロックは下記のように更新されていく。

  • INSERT文を実行したとき、まだ空きのある一番最後尾のブロックにデータが追加されたバージョンのものが作成され、古いデータブロックと入れ替わる
  • UPDATE文を実行したとき、削除対象のレコードには削除フラグがつけられて領域が残ったまま未使用となり、UPDATE後新しい状態の行は新しいレコードとして一番最後尾のブロックにINSERT同様追加される。
  • DELETE文を実行したとき、削除対象のレコードには削除フラグがつけられて領域が残ったまま未使用となる。

データがソート済みであると検索が早い理由

SELECTやINSERTなどで、scanするときのデータブロックのfilter処理は、まずゾーンマップの「最大値と最小値」を見て、検索対象の値がデータブロックに入っているかを判断する。

上記図の例で createat BETWEEN '2019-05-02 00:00:00' AND '2019-05-03 00:00:00' を探そうとすると、下記のような動き無なる。

  • ゾーンマップの最大値最小値より createat列の「DataBlock1」には含まれ「DataBlock2」には含まれない
  • srcipのデータブロックは、createatのゾーンマップ参照からわかった row番号 1～125000を含む、DataBlock 1～2 のみを読み出す。

この例では４日間のみのアクセスログを取り扱う例だが、過去5年分蓄積したデータから探そうとすると、ソート済みであることは絶大な効果をもたらす。

ソートキーの効果

アクセスログのタイムスタンプのように、INSERT順がSELECTで条件指定したい列の値の順序に直接つながるものは放っておいてもソートされるが、 DELETE-INSERT やUPDATEを繰り返して、値の順序がばらばらになっているとき、フィルタによるデータブロックの読み出し制限が効かなくなってくる。

そこで、ソートキーが出てくる。

ソートキーを設定すると、下記の挙動になる

  • VACUUM実行時、ソート処理が追加される。
    • 前回VACUUMを実行したときからデータが新規発生し未ソート領域が生じている場合、その領域をソートし、前回ソート済みの領域にマージする必要があればマージが発生する。
  • データ0件からCOPYやINSERTを実行したとき、ソートしながらデータ追加する。

（なお、テーブル結合する際にソートキー・分散キーが２テーブル間で一致した場合にMERGE JOINという最速JOINが発生するが、後述する）

ソートキーの種類

ソートキーにはいくつか種類がある。

下記の記事が詳しい。

https://qiita.com/t11a/items/a0b175e66ebc623f5cbe

Single-column Sort Key
column 単位で設定された Key。

Compound Sort Key
table 単位で設定されたもので、2つ以上の column に対してセットされた Key。いわゆる複合 Key で　Primary, Secondary と Key 間に優先順位があります。

Interleaved Sort Key
table 単位で設定されたもので、1つ以上の column に対してセットされた Key。

SIngle-column Sort Key や Compound Sort Key は、Excelやデータベースのindexのように、第一ソートキー・第二ソートキーといった具合に段階的にソートする。 先頭に指定したキーほど影響が大きく、普通は複数列指定する必要はない。

Interleaved Sort Key について

Interleaved Sort Key は 、第一ソートキー・第二ソートキーといった具合に段階的にソートするが、全体を複数区画にわけてソートする。

たとえば、2つのキー(x,y)でInterleaved Sort Key を指定すると、下記の順でデータが並ぶ。 - A区画：yの上位0～25%の値を持ち、この区画内でxが完全に昇順ソート - B区画：yの上位25～50%の値を持ち、この区画内でxが完全に昇順ソート - C区画：yの上位50～75%の値を持ち、この区画内でxが完全に昇順ソート - D区画：yの上位75～100%の値を持ち、この区画内でxが完全に昇順ソート

SELECT時は

  • xを検索キーとしたときは、A,B,C,Dそれぞれの区画内で適切にゾーンマップに準じたscan skipが発生する。
  • yを検索キーとしたとき、例えばyが全体をソートしたうち上から 30% の大きさの値の値だったとき、上位25%~50%の値をすべて持つBのデータブロックのスキャンのみで済む。

このように、検索条件に選択肢があるが、これを指定しないとパフォーマンスがとても使えないようなテーブルに指定する。

（個人的には、VACUUMの負荷がどうしても高くなるため好きではない。これを使わざるをえないとき、実はテーブルを２つに分割するとか用途別に小分け集計・別テーブル化するのがパフォーマンス上ベストだったりするのではないか）

ソートキーのデメリット

デメリットはただ一つだが、非常に重い。

  • 定期的なVACUUM実行が必須であり、かつVACUUMにSortやMergeといった非常に重い処理が追加される。
    • VACUUMは1度に1つしか実行できない、かつ負荷がかなり高い。

このMergeは、テーブルの列が多いほど時間がかかる。 2019年6月中に試験したなかでは、下記条件の全件ソートで30時間かかった。

  • テーブル項目：200弱
  • 4億7千万レコード
  • 列圧縮タイプ全lzo
  • Redshift上のテーブルサイズ500GB程度
  • コンピューティングノード：ds2.xlarge × 1

※ちなみに、同条件で0件から全件INSERTしたときは、5時間程度で済んだ。ある程度Mergeにて更新するレコード量が増えると、TRUNCATE - INSERT のほうが効率が良いようだ。

ソートキーと相性の悪いもの

全件スキャンする前提のテーブル

  • どうせ全件スキャンするのであれば、ソートキーの効果はほとんどない。

高圧縮の列圧縮エンコーディングでソートキーにする

  • 列圧縮エンコーディングは、1列あたりのディスク使用率を下げ1データブロック当たりのデータ数を増やす設定。
  • スキップするデータブロックが減り逆効果。公式で、rawが推奨されているのはこのため。

ソートキーで指定した列をWHERE句にいれないクエリ

  • ソートキーの意味がないどころか、意図的に並べ替えを発生させるため値の最大値・最小値の密度が下がり、だいたい5～10％ほどの性能劣化を招く。

8バイトを超える列

  • ゾーンマップの最大値・最小値のメタ情報は、前8バイトしか保持しない。

Merge Joinについて

2つのテーブルを結合するとき、Join条件が互いのテーブルのソートキー・分散キーと一致すると「Merge Join」という処理ステップになる。

結論から言うと、MergeJoinのためにソートキーを設定しても、JOINにかかる処理速度が1~2割程度向上するのみにとどまる。

理由は下記とおり。

  • RedshiftはデフォルトではHashJoinする。
    • HashJoinは、「最初に結合列をすべてHash関数を通してHash値をすべての行で生成し、結合条件に使う」操作。
    • MergeJoinに比べるとHash関数の実行分がロスだが、列ごとの読み出しに特化したRedshiftにおいて、Hashステップは全然気にならないぐらい速い。（svl_query_summary で観測可能）
  • 他のRDBMSで忌み嫌われるNested-loop結合は、JOIN条件を指定する限りほとんど発生しない。
  • そもそも、JoinのほかにScanやAggr、Returnといった実行すステップが他にもあり、また、Joinステップ自体はSQL実行の全体の処理時間に占める割合が多いかというとそうでもない。