Amazon Redshift Interleaved Sortkey と VACUUM 実行のポイント

#Amazon Redshift

#AWS

石川覚

2015.06.24

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

先日ご紹介しました新機能 Interleaved Sortkey ですが、実際に使ったところ検索性能が良くならないという事態に陥りました。従来と同じようにデータの取り扱いをするとソートされないことがわかりましたので、その原因と対処方法としての VACUUM REINDEX についてご紹介します。

データコピーと同時にソートして保存する方法

本来はデータをテーブルに保存した後に、VACUUM（未使用領域、データソート）や ANALYZEなどが必要になりますが、これらの手間は避けたいものです。なので、これまではデータコピーと同時にソートして保存する方法として、以下の２つの方法がありました。そして、これらの方法はCompound sortkeyにおけるVACUUM処理を不要にするデータ投入のベストプラクティスでもあります。

１．COPYによるデータ投入２．ディープコピーによるデータ投入（INSERT INTO ... SELECT）

	COPYによるデータ投入	ディープコピーによるデータ投入
未使用領域	なし	なし
データソート	済み	済み
ANALYZE	済み	未

ディープコピーによるデータ投入では検索が遅い

以前にご紹介した【新機能】Amazon Redshift の Interleaved Sorting機能を試してみたでは、１の方法によって、データを投入して検索性能が向上することを確認しています。

一方、２の方法でデータを投入したところ検索性能が向上しませんでした。以下の通り、Sortkeyなしよりも Interleaved Sortkey を設定した方が検索結果が遅くなっています。

-- ディープコピーによるデータ投入（INSERT INTO ... SELECT）
INSERT INTO lineorder_il SELECT * from lineorder;

-- Sortkeyなし
SELECT COUNT(*) FROM lineorder
WHERE lo_orderdate = 19971025 and lo_custkey = 1717;
count
-------
7
(1 行)
時間: 5373.971 ms

-- Compound Sortkey
SELECT COUNT(*) FROM lineorder_compound
WHERE lo_orderdate = 19971025 and lo_custkey = 1717;
count
-------
7
(1 行)
時間: 88.844 ms

-- Interleaved Sortkey
SELECT COUNT(*) FROM lineorder_interleaved
WHERE lo_orderdate = 19971025 and lo_custkey = 1717;
count
-------
7
(1 行)
時間: 5401.437 ms

原因調査

Redshiftでは各ブロックに格納されているデータの最小値、最大値を保持しており、これらに基づいた範囲を限定したスキャン（Range Restriceted Scan）を行うことで、ブロックを効果的に絞り込みパフォーマンスの向上を実現しています。

２の方法ではソートしてデータが格納されないのではないかと考えられますので、SVV_DISKUSAGE に対するクエリでこの状況を確認します。

SELECT trim(name), count(*), col, sum(num_values), min(minvalue), max(maxvalue)
FROM svv_diskusage
WHERE name LIKE 'lineorder_interleaved%' AND col IN (2,5)
GROUP BY name, col
ORDER BY name, col;

btrim                 | count | col | sum      | min | max
----------------------+-------+-----+----------+-----+-----
lineorder_interleaved |   292 |   2 | 75004738 |   0 |   0
lineorder_interleaved |   292 |   5 | 75004738 |   0 |   0
(2 rows)

(補足) - col in (2,5): interleaved sortkeyの対象カラム - minvalue, maxvalue: 各ブロックに記録されているレコードの最小値、最大値

minvalueやmaxvalueが適切に記録されておらず、結果全てのブロックのスキャンとなりソートキー無しのテーブルと同等のパフォーマンスとなっているのではないかと考えられます。Interleaved sortkey ディープコピーでデータを挿入してもソートされないようです。

VACUUM FULLを実行

先の結果、ソートされていないことがわかったのでVACUUM FULLを実行する。

VACUUM FULL lineorder_interleaved;

SELECT trim(name), count(*), col, sum(num_values), min(minvalue), max(maxvalue)
FROM svv_diskusage
WHERE name LIKE 'lineorder_interleaved%' AND col IN (2,5)
GROUP BY name, col
ORDER BY name, col;

btrim                 | count | col | sum      | min | max
----------------------+-------+-----+----------+-----+-----
lineorder_interleaved |   292 |   2 | 75004738 |   0 |   0
lineorder_interleaved |   292 |   5 | 75004738 |   0 |   0
(2 rows)

相変わらずminvalueやmaxvalueが適切に記録されていませんので、ソートされていない状態です。VACUUM FULL は lineorder_interleaved に効果がありませんでした。つまり、"FULL"は「全部」ではなかったということです。

VACUUM REINDEXの実行【解決】

次にlineorder_interleaved 導入時に追加となったオプション VACUUM REINDEX を実行します。

VACUUM REINDEX lineorder_interleaved;

確認したところ、VACUUM REINDEXでは、Interleaved sortkeyの解析と合わせて FULL VACUUM相当の処理が行われることがわかりました。よって、実行後にVACUUM FULLやVACUUM DELETE ONLYを実行は不要となります。

SELECT trim(name), count(*), col, sum(num_values), min(minvalue), max(maxvalue)
FROM svv_diskusage
WHERE name LIKE 'lineorder_interleaved%' AND col IN (2,5)
GROUP BY name, col
ORDER BY name, col;

btrim                 | count | col | sum      | min           | max
----------------------+-------+-----+----------+---------------+-----------------
lineorder_interleaved |   292 |   2 | 75004738 | 1099511632800 | 281474973957841
lineorder_interleaved |   292 |   5 | 75004738 | 1099511632800 | 281474973957841
(2 rows)

設定されていることが確認できました。

-- Interleaved Sortkey
SELECT COUNT(*) FROM lineorder_interleaved
WHERE lo_orderdate = 19971025 and lo_custkey = 1717;
count
-------
7
(1 行)
時間: 92.385 ms

処理時間が「COPYによるデータ投入」した時と同じレベルに改善できました。Interleaved sortkey のテーブルに「ディープコピーによるデータ投入（INSERT INTO ... SELECT）」した場合には、VACUUM REINDEXが必要であることがわかりました。

VACUUM REINDEX の実行結果を確認したところ、Interleaved sortkeyの解析と合わせて FULL VACUUM相当の処理が行われることがわかりました。なので、VACUUM REINDEX の前後で VACUUM FULL や VACUUM DELETE ONLYは冗長なので実行しても無駄な処理となりますので実行不要です。

今後のVACUUM処理の方針

従来のソートキー（Compound sortkey）は従来通り、VACUUM [FULL]で変更ありませんが、Interleaved sortkeyはVACUUM [FULL]の代わりにVACUUM REINDEXを実行してください。

Sortkeyなし：VACUUM [FULL], VACUUM DELETE ONLY
Compound sortkey：VACUUM [FULL]
Interleaved sortkey：VACUUM REINDEX

VACUUM REINDEXは、"REINDEX"という響きから「インデックスを再作成する」という印象を受けますが、Interleaved sortkey 専用のVACUUM FULL 同等のものだと思ってください。逆に従来のソートキー（Compound sortkey）に対して、VACUUM REINDEXを実行しても一瞬で終わり何も処理されませんでした。

VACUUM REINDEX の処理時間

問題は解決しましたが、調査の過程で気になったのが、VACUUM REINDEX の処理時間です。以下のいずれの場合において、データ投入時間よりも VACUUM REINDEX の方がより多くの時間が必要となっています。また、Compound sortkeyに対して、Interleaved sortkey では累積データ量に比例して、VACUUM REINDEXに要する時間が増加する点が異なります。

１回にまとめてVACUUM REINDEXする場合

データをCOPYで３回投入して、最後にまとめてVACUUM REINDEXします。

copy lineorder_il_6 from 's3://awssampledb/ssbgz/lineorder0000_part_00.gz'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy'
gzip compupdate off;
INFO: Load into table 'lineorder_il_6' completed, 75004738 record(s) loaded successfully.
COPY
Time: 347049.526 ms

copy lineorder_il_6 from 's3://awssampledb/ssbgz/lineorder0001_part_00.gz'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy'
gzip compupdate off;
INFO: Load into table 'lineorder_il_6' completed, 75004737 record(s) loaded successfully.
COPY
Time: 334638.018 ms

copy lineorder_il_6 from 's3://awssampledb/ssbgz/lineorder0002_part_00.gz'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy'
gzip compupdate off;
INFO: Load into table 'lineorder_il_6' completed, 75004738 record(s) loaded successfully.
COPY
Time: 428333.451 ms

VACUUM REINDEX
Time: 1515384.136 ms

３回に分けてVACUUM REINDEXする場合

COPY文とVACUUM REINDEX を順に３回繰り返してデータ投入します。

copy lineorder_il_6x from 's3://awssampledb/ssbgz/lineorder0000_part_00.gz'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy'
gzip compupdate off;
INFO: Load into table 'lineorder_il_6x' completed, 75004738 record(s) loaded successfully.
COPY
Time: 304784.241 ms

（ここでソート割合0%）

VACUUM REINDEX lineorder_il_6x;
Time: 319876.509 ms

（ここでソート割合100%）

copy lineorder_il_6x from 's3://awssampledb/ssbgz/lineorder0001_part_00.gz'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy'
gzip compupdate off;
INFO: Load into table 'lineorder_il_6x' completed, 75004737 record(s) loaded successfully.
COPY
Time: 361507.463 ms

（ここでソート割合50%）

VACUUM REINDEX lineorder_il_6x;
Time: 668971.484 ms

（ここでソート割合100%）

copy lineorder_il_6x from 's3://awssampledb/ssbgz/lineorder0002_part_00.gz'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy'
gzip compupdate off;
INFO: Load into table 'lineorder_il_6x' completed, 75004738 record(s) loaded successfully.
COPY
Time: 428049.277 ms

（ここでソート割合66%）

VACUUM REINDEX lineorder_il_6x;
Time: 1494202.976 ms

（ここでソート割合100%）

効率的にVACUUM REINDEXするには一括実行

Interleaved sortkey では累積データ量に比例して、VACUUM REINDEXに要する時間が増加します。つまり、Compound sortkeyのようにインクリメンタルなVACUUM運用はできないということです。なので、データを投入する度にVACUUM REINDEXを実行するのではなく、全データ投入後に一括で実施するのが最も効率のよい方法となります。

VACUUMは時間のかかる処理なので、Comound sortkeyではデータを投入した後にこまめにVACUUMすることが推奨されていた点が異なりますのでご注意ください。

最後に

Interleaved sortkeyは Compound sortkeyと異なる点が多いので、熟知したベテランが思わずハマってしまうのではないかと感じました。また、Interleaved sortkeyはインデックスコストは小さくないので、集計テーブルやマートなど、VACUUM REINDEX が容易にかけられるコンパクトなテーブルに対して適用するのが効果的と考えています。Interleaved sortkeyはすぐにでも実戦配備して、M-OLAPのCUBEを作成し、データディスカバリにお役立てください。

現在（2015/6）は、日本語マニュアルが殆ど無く、また、英語マニュアルにおいても Sortkey については Compound sortkey に関する情報と読み替えてください。Interleaved sortkeyに関する情報は英語マニュアルに別途記載されています。今は従来のソートキーの情報が殆どで混在していますので、ソートキーの選定のポイントや扱いについてのベストプラクティスが改めて整理してご紹介したいと考えています。