『Amazon Redshift チュートリアル: テーブル設計チューニング』を実践してみた
Amazon Redshiftに於いて『パフォーマンスチューニング』は重要なトピックの1つです。Redshiftクラスタを立ち上げて、データを投入して、実際使ってみたものの思ったような速度・レスポンスが返って来ない...という状況も時折遭遇する事と思います。
AWS公式ドキュメント(英語版)を漁ってみると、まさにその『パフォーマンスチューニング』に焦点を当てたチュートリアルが公開されているではありませんか！当エントリではそのドキュメントを参考にひと通り実践してみた内容をまとめてみました。各種手順を1エントリに集約したので超長いエントリとなってしまいましたが、その辺りは目を瞑りつつ実践内容を順を追ってご覧頂ければと思います。
目次
- 1.テスト用データセットの作成
- 2.ベースラインを作るためのシステムパフォーマンステスト実践
- 3.ソートキーの選定
- 4.分散キーの選定
- 5.圧縮エンコーディングのレビュー
- 6.テスト用データセットの再作成
- 7.システムパフォーマンステスト実践(再)
- 8.結果検証
- 9.リソースのお掃除
1.テスト用データセットの作成
DWH(データウェアハウス)では一般的に『スタースキーマ』でテーブル設計を行うのが良いとされています。これは、中央に『ファクトテーブル』(分析の中心となるデータ)、ファクトテーブルを囲む形で『ディメンションテーブル』(ファクトテーブルの項目に対する、説明的な詳細属性情報を含むテーブル)で構成される形となります。ファクトテーブルはディメンションテーブルに対し、[ファクトテーブル.外部キー]＝[ディメンションテーブル.主キー]の形で結合します。
このチュートリアルで用いるテーブル設計は以下のようなものとなります。
テスト用データセットの作成
では実際にデータが入る器、Amazon Redshiftクラスタを構築しましょう。所定のVPC、セキュリティグループ(ポート番号:5439を許可)、クラスタサブネットグループ、クラスタパラメータグループを作成した後、クラスタ本体を作成。チュートリアルではNode Type:dw2.large、ノード数:4で進める形で記載されていますので、ここでも同様の構成でクラスタを起動してみます。(※東京リージョン(ap-northeast-1)で起動してみる事にします)
クラスタが立ち上がりました。psqlコマンド等でデータベースにログインし、所定のテーブルを作成します。
CREATE TABLE part ( p_partkey INTEGER NOT NULL, p_name VARCHAR(22) NOT NULL, p_mfgr VARCHAR(6) NOT NULL, p_category VARCHAR(7) NOT NULL, p_brand1 VARCHAR(9) NOT NULL, p_color VARCHAR(11) NOT NULL, p_type VARCHAR(25) NOT NULL, p_size INTEGER NOT NULL, p_container VARCHAR(10) NOT NULL ); CREATE TABLE supplier ( s_suppkey INTEGER NOT NULL, s_name VARCHAR(25) NOT NULL, s_address VARCHAR(25) NOT NULL, s_city VARCHAR(10) NOT NULL, s_nation VARCHAR(15) NOT NULL, s_region VARCHAR(12) NOT NULL, s_phone VARCHAR(15) NOT NULL ); CREATE TABLE customer ( c_custkey INTEGER NOT NULL, c_name VARCHAR(25) NOT NULL, c_address VARCHAR(25) NOT NULL, c_city VARCHAR(10) NOT NULL, c_nation VARCHAR(15) NOT NULL, c_region VARCHAR(12) NOT NULL, c_phone VARCHAR(15) NOT NULL, c_mktsegment VARCHAR(10) NOT NULL ); CREATE TABLE dwdate ( d_datekey INTEGER NOT NULL, d_date VARCHAR(19) NOT NULL, d_dayofweek VARCHAR(10) NOT NULL, d_month VARCHAR(10) NOT NULL, d_year INTEGER NOT NULL, d_yearmonthnum INTEGER NOT NULL, d_yearmonth VARCHAR(8) NOT NULL, d_daynuminweek INTEGER NOT NULL, d_daynuminmonth INTEGER NOT NULL, d_daynuminyear INTEGER NOT NULL, d_monthnuminyear INTEGER NOT NULL, d_weeknuminyear INTEGER NOT NULL, d_sellingseason VARCHAR(13) NOT NULL, d_lastdayinweekfl VARCHAR(1) NOT NULL, d_lastdayinmonthfl VARCHAR(1) NOT NULL, d_holidayfl VARCHAR(1) NOT NULL, d_weekdayfl VARCHAR(1) NOT NULL ); CREATE TABLE lineorder ( lo_orderkey INTEGER NOT NULL, lo_linenumber INTEGER NOT NULL, lo_custkey INTEGER NOT NULL, lo_partkey INTEGER NOT NULL, lo_suppkey INTEGER NOT NULL, lo_orderdate INTEGER NOT NULL, lo_orderpriority VARCHAR(15) NOT NULL, lo_shippriority VARCHAR(1) NOT NULL, lo_quantity INTEGER NOT NULL, lo_extendedprice INTEGER NOT NULL, lo_ordertotalprice INTEGER NOT NULL, lo_discount INTEGER NOT NULL, lo_revenue INTEGER NOT NULL, lo_supplycost INTEGER NOT NULL, lo_tax INTEGER NOT NULL, lo_commitdate INTEGER NOT NULL, lo_shipmode VARCHAR(10) NOT NULL );
次いで、S3の所定バケットからデータをCOPYします。計6つのリージョン向けにサンプルデータが既にアップロードされていますのでそのまま使いましょう。東京リージョンの場合はバケット名はawssampledbapnortheast1となります。シークレットキー及びアクセスキーも任意の情報を設定した上でCOPY処理を実行します。
copy customer from 's3://awssampledbapnortheast1/ssbgz/customer' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip compupdate off; copy dwdate from 's3://awssampledbapnortheast1/ssbgz/dwdate' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip compupdate off; copy lineorder from 's3://awssampledbapnortheast1/ssbgz/lineorder' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip compupdate off; copy part from 's3://awssampledbapnortheast1/ssbgz/part' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip compupdate off; copy supplier from 's3://awssampledbapnortheast1/ssbgz/supplier' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip compupdate off;
それぞれのテーブルに関しては何度か投入を繰り返して時間を計測してみました。(※後程比較に使いますので、実行結果は控えておきましょう。)
# \timing タイミングは on です。 << CUSTOMER >> # copy customer from 's3://awssampledbapnortheast1/ssbgz/customer'... INFO: Load into table 'customer' completed, 3000000 record(s) loaded successfully. 時間: 6869.708 ms 時間: 5761.899 ms 時間: 4655.569 ms 時間: 4262.411 ms 時間: 4547.109 ms 時間: 4945.159 ms << DWDATE >> # copy dwdate from 's3://awssampledbapnortheast1/ssbgz/dwdate'... INFO: Load into table 'dwdate' completed, 2556 record(s) loaded successfully. 時間: 1183.848 ms 時間: 1990.743 ms 時間: 783.742 ms 時間: 1169.219 ms 時間: 1150.823 ms 時間: 852.049 ms << LINEORDER >> # copy lineorder from 's3://awssampledbapnortheast1/ssbgz/lineorder'... Load into table 'lineorder' completed, 600037902 record(s) loaded successfully. 時間: 820481.337 ms 時間: 788918.708 ms 時間: 945216.918 ms 時間: 947621.563 ms << PART >> # copy part from 's3://awssampledbapnortheast1/ssbgz/part'... INFO: Load into table 'part' completed, 1400000 record(s) loaded successfully. 時間: 4447.001 ms 時間: 4643.314 ms 時間: 3918.008 ms 時間: 4530.556 ms 時間: 4461.159 ms << SUPPLIER >> # copy supplier from 's3://awssampledbapnortheast1/ssbgz/supplier'... INFO: Load into table 'supplier' completed, 1000000 record(s) loaded successfully. 時間: 2760.094 ms 時間: 3844.060 ms 時間: 2937.730 ms 時間: 3022.458 ms 時間: 2810.537 ms 時間: 3405.167 ms
投入が完了したら、各テーブルの件数を確認してみます。結果は以下の様になりました。
# select count(*) from LINEORDER; count ----------- 600037902 (1 行) 時間: 894.963 ms # SELECT COUNT(*) FROM lineorder; count ----------- 600037902 (1 行) 時間: 882.130 ms # SELECT COUNT(*) FROM part; count --------- 1400000 (1 行) 時間: 134.306 ms # SELECT COUNT(*) FROM customer; count --------- 3000000 (1 行) 時間: 157.337 ms # SELECT COUNT(*) FROM supplier; count --------- 1000000 (1 行) 時間: 134.040 ms # SELECT COUNT(*) FROM dwdate; count ------- 2556 (1 行) 時間: 155.553 ms #
2.ベースラインを作るためのシステムパフォーマンステスト実践
前項でも述べましたが、テーブルのチューニング前後でのシステムパフォーマンスを比較する為に、詳細な記録を付けておく必要があります。ポイントは以下3点です。
- ロードタイム(Load Time)
- ストレージ使用量(Storage Use)
- クエリパフォーマンス(Query Performance)
今回の例ではdw2.large 4ノードで構築した場合の結果に基づいています。この結果は同じクラスタ設定だったとしても(当ドキュメントの)結果と異なる場合もあるかも知れませんが、システムパフォーマンスは多くの要因に拠って影響を受けます。凡そ同じ様な結果になるのだな、程度の認識で進めて頂ければと思います。
ベースライン確立の為のシステムパフォーマンステストの実践
前項でのテーブルデータのロード時間を控えて置いたかと思いますので、次はストレージ利用量に関する情報を取得します。以下クエリはテーブル毎に1MBサイズのディスクスペースが使われているか、を取得するものです。STV_BLOCKLISTテーブルを参照する事で得られる情報です。
select
stv_tbl_perm.name as table,
count(*) as mb
from
stv_blocklist,
stv_tbl_perm
where
stv_blocklist.tbl = stv_tbl_perm.id
and stv_blocklist.slice = stv_tbl_perm.slice
and stv_tbl_perm.name in ('lineorder','part','customer','dwdate','supplier')
group by
stv_tbl_perm.name
order by
1 asc;
結果は以下の様になりました。
table | mb
--------------+-------
customer | 384
dwdate | 160
lineorder | 51024
part | 200
supplier | 152
(5 行)
任意のクエリ実行時のパフォーマンスも計測しておきます。クエリの初回実行時、Amazon Redshiftはコードをコンパイルし、そのコンパイル済のコードを計算ノードにそれぞれ配布します。この事により初回実行は時間が掛かるので、クエリの実行時間を計測する場合は、初回実行時の記録は使うべきではありません。この辺りの詳細は以下参照。
-- Query 1 -- Restrictions on only one dimension. select sum(lo_extendedprice*lo_discount) as revenue from lineorder, dwdate where lo_orderdate = d_datekey and d_year = 1997 and lo_discount between 1 and 3 and lo_quantity < 24; -- Query 2 -- Restrictions on two dimensions select sum(lo_revenue), d_year, p_brand1 from lineorder, dwdate, part, supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand1 order by d_year, p_brand1; -- Query 3 -- Drill down in time to just one month select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer, lineorder, supplier, dwdate where lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_orderdate = d_datekey and (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997' group by c_city, s_city, d_year order by d_year asc, revenue desc;
実行結果は以下の様になりました。
# \timing
タイミングは on です。
<< Query 1 >>
# select sum(lo_extendedprice*lo_discount) as revenue
# from lineorder, dwdate
# where lo_orderdate = d_datekey
# and d_year = 1997
# and lo_discount between 1 and 3
# and lo_quantity < 24;
revenue
----------------
32879160652772
(1 行)
時間: 8516.798 ms
時間: 6194.914 ms
時間: 6174.647 ms
時間: 6198.564 ms
時間: 6120.653 ms
#
<< Query 2 >>
# select sum(lo_revenue), d_year, p_brand1
# from lineorder, dwdate, part, supplier
# where lo_orderdate = d_datekey
# and lo_partkey = p_partkey
# and lo_suppkey = s_suppkey
# and p_category = 'MFGR#12'
# and s_region = 'AMERICA'
# group by d_year, p_brand1
# order by d_year, p_brand1;
sum | d_year | p_brand1
-------------+--------+-----------
50258511019 | 1992 | MFGR#121
51638981598 | 1992 | MFGR#1210
54435432115 | 1992 | MFGR#1211
51810546242 | 1992 | MFGR#1212
53250409709 | 1992 | MFGR#1213
:
:
31460340551 | 1998 | MFGR#128
28629104373 | 1998 | MFGR#129
(280 行)
時間: 16349.402 ms
時間: 10878.786 ms
時間: 10771.186 ms
時間: 10811.213 ms
時間: 10960.680 ms
<< Query 3 >>
# select c_city, s_city, d_year, sum(lo_revenue) as revenue
# from customer, lineorder, supplier, dwdate
# where lo_custkey = c_custkey
# and lo_suppkey = s_suppkey
# and lo_orderdate = d_datekey
# and (c_city='UNITED KI1' or
# c_city='UNITED KI5')
# and (s_city='UNITED KI1' or
# s_city='UNITED KI5')
# and d_yearmonth = 'Dec1997'
# group by c_city, s_city, d_year
# order by d_year asc, revenue desc;
c_city | s_city | d_year | revenue
------------+------------+--------+-----------
UNITED KI5 | UNITED KI5 | 1997 | 373974482
UNITED KI1 | UNITED KI5 | 1997 | 365124652
UNITED KI1 | UNITED KI1 | 1997 | 358824801
UNITED KI5 | UNITED KI1 | 1997 | 355652352
(4 行)
時間: 16170.027 ms
時間: 12959.871 ms
時間: 12785.167 ms
時間: 13248.973 ms
時間: 13332.809 ms
#
『チューニング前』の結果はこの様な状況となりました。(※計測時間は直近4回の平均を採用)
3.ソートキーの選定
テーブル作成時、１つ以上のソートキーをテーブルに対して設定する事が出来ます。Amazon Redshiftはソートキーの設定に基づいて、データをディスクに格納します。どのようにデータがソートされるのか、という点は、ディスクI/Oや列圧縮、クエリパフォーマンスに重要な影響を与えます。以下の条件に合う項目があれば、それらはソートキー指定対象の候補となります。
- 直近のデータが頻繁にアクセスされるようなケースであれば、TIMESTAMP列をソートキーの先頭に指定しましょう。
- ある列で範囲指定や等価判定を行う場合、その項目をソートキーに指定しましょう。
- (ディメンション)テーブルと頻繁に結合を行う場合、その結合列をソートキーに指定しましょう。
ソートキーの選択
結果をフィルタリングするTIMESTAMP列を見つける為に、クエリを評価してみます。例えば、LINEORDERテーブルではlo_orderdateを使った等値判定を良く行います。
where lo_orderdate = d_datekey and d_year = 1997
範囲指定と等価判定で使用されている列を探します。LINEORDERテーブルはlo_orderdateを範囲指定でも利用しています。
where lo_orderdate = d_datekey and d_year >= 1992 and d_year <= 1997
2つのベストプラクティスに基づき、lo_orderdateが最善のソートキーとなりそうです。チューニングテーブル内には、LINEORDERに対するソートキーとしてlo_orderkeyを指定します。
残りのテーブルも3つめのベストプラクティスに基づき、主キーをソートキーとして指定します。以下の様にソートキーが指定出来ました。次のステップでは分散スタイルを埋めて行きます。
| テーブル名 | ソートキー | 分散スタイル |
|---|---|---|
| LINEORDER | lo_orderdate | |
| PART | p_partkey | |
| CUSTOMER | c_custkey | |
| SUPPLIER | s_suppkey | |
| DWDATE | d_datekey |
4.分散キーの選定
データをテーブルにロードする際、Amazon Redshiftはテーブルで指定されている分散スタイルに基づいて、テーブルの行を各ノードスライスに分散します。スライスの数は、ノード上のプロセッサコアの数と同じです。例えば、当チュートリアルで使用しているdw2.largeクラスタは4つのノード、8つのスライスを持っています。全てのノードがスライスに分散するデータを取り込み、並列クエリの実行に参加します。
結合と集計を実行する必要性に応じたクエリを実行すると、クエリオプティマイザは計算ノードに対して行の再分散を行います。この再分散は結合に対して特定の行をノードに送るかも知れないし、またテーブル全体を全てのノードに配信する事になるかも知れません。
ゴールを達成する為には、分散スタイルを割り当てる必要があります。
- 結合テーブルから、行を併置(コロケーション)する：列に対する結合行が同じスライスに有る場合、クエリ実行時のデータ移動は少なくて済みます。
- クラスタ内の均等に対し、均等にデータを配置する：もしデータが均等に分散されている場合、全てのスライスに均等にワークロードが割り当てられます。
これらのゴールは幾つかのケースで競合する可能性があり、システム全体にとってどの戦略がベストチョイスなのかを評価する必要性があります。例えば、EVEN分散はは同じスライスに全て一致した列の値を配置出来ます。この手順では、データ分散のゴールに関してSSBテーブルの分散を評価し、テーブルに最適な分散スタイルを選択します。
分散スタイル
分散スタイルは以下3つが指定可能です。
- KEY分散(KEY Distribution)]
- この設定を行うと、行はある列の値に従って分散されます。リーダーノードは同じノードスライスに一致する値を配置しようと試みます。もし2つのテーブルをキーで結合して分散させる場合、リーダーノードは結合した列の値に基づいて行をスライスに併置(コロケーション)します。
- 共通列の値が一致することで、物理的にデータが同じ場所に格納されます。
- ALL分散(ALL Distribution)
- テーブル全体のコピーが全てのノードに分散されます。EVEN分散またはKEY分散がノードに対しテーブルの特定行を配置するのに対し、ALL分散は全ての行が、テーブルが関わっている全ての結合に併置(コロケーション)される事を保証します。
- EVEN分散(EVEN Distribution)
- 行は特定の列の値に関係なく、ラウンドロビン形式でスライスに分散されます。指定が無い場合(デフォルト指定)はこの形式が採用されます。
この辺の詳細については以下参照。
分散スタイルの選択
クエリ実行時、クエリオプティマイザは結合や集約の必要に応じて計算ノードに対し行の再分散を行います。クエリが実行される前に、あるべき場所にデータを配置しておく事で、再分散のインパクトを最小限に抑える事が出来ます。
最初のゴールはテーブル結合により一致した行データを併置(コロケーション)された状態でデータを分散させる事です。 この事は、テーブル結合で一致した行が同じノードのスライス上にあることを意味します。
1.
クエリプランの再分散ステップを見てみるには、以下のようにEXPLAINコマンドを実行します。この例では、3つあるうちのクエリから、2つめのものを実行しています。
explain select sum(lo_revenue), d_year, p_brand1 from lineorder, dwdate, part, supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand1 order by d_year, p_brand1;
実行例。ラベルがDS_BCASTで始まる形で結果が弾き出されました。
QUERY PLAN
XN Merge (cost=1038007224737.84..1038007224738.54 rows=280 width=20)
Merge Key: dwdate.d_year, part.p_brand1
-> XN Network (cost=1038007224737.84..1038007224738.54 rows=280 width=20)
Send to leader
-> XN Sort (cost=1038007224737.84..1038007224738.54 rows=280 width=20)
Sort Key: dwdate.d_year, part.p_brand1
-> XN HashAggregate (cost=38007224725.76..38007224726.46 rows=280
-> XN Hash Join DS_BCAST_INNER (cost=30674.95..38007188507.46
Hash Cond: ("outer".lo_orderdate = "inner".d_datekey)
-> XN Hash Join DS_BCAST_INNER (cost=30643.00..37598119820.65
Hash Cond: ("outer".lo_suppkey = "inner".s_suppkey)
-> XN Hash Join DS_BCAST_INNER
Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
-> XN Seq Scan on lineorder
-> XN Hash (cost=17500.00..17500.00 rows=56000
-> XN Seq Scan on part (cost=0.00..17500.00
Filter: ((p_category)::text =
-> XN Hash (cost=12500.00..12500.00 rows=201200
-> XN Seq Scan on supplier (cost=0.00..12500.00
Filter: ((s_region)::text = 'AMERICA'::text)
-> XN Hash (cost=25.56..25.56 rows=2556 width=8)
-> XN Seq Scan on dwdate (cost=0.00..25.56 rows=2556
- DS_BCAST_INNERは、内部結合テーブルが全てのスライスに展開された事を示しています。
- DS_DIST_BOTHは、もしあった場合、外部結合テーブルと内部結合テーブルがスライス全体に再分散されている事を示します
- 散布(broadcasting)や再分散(redistribution)はクエリパフォーマンスの面からすると、高コストとなります。
散布や再分散のステップをを減らしたり無くしたりするような戦略を選択したくなるでしょう。EXPLAINに関するより詳しい情報については、以下を御参照ください。
2.
ファクトテーブルとディメンションテーブルを共通の列で分散します。以下ダイアグラムはファクトテーブルのLINEORDERと、SSBスキーマのディメンションテーブルとの関連を示しています。
それぞれのテーブルは1つだけ分散キーを持つ事が出来ます。この事は、共通の列でスキーマ内に併置(コロケーション)されうるテーブルのペアは1対だけである事を意味します。中央のファクトテーブルは明確な最初の選択となります。ペアに於ける2つ目のテーブルは、通常ファクトテーブルと結合する最も大きなディメンション(テーブル)を選択します。この設計では、LINEORDERがファクトテーブルとなり、PARTテーブルが最も大きなディメンション(テーブル)となります。PARTテーブルはLINEORDERテーブルと、主キーであるp_partkeyで結合します。
lo_partkeyをLINEORDERに対する分散キーとして指定し、p_partkeyをPARTの分散キーとして指定します。これでデータが読み込まれる時、結合キー項目で一致した値の行は同じスライスに併置(コロケーション)されるようになります。
3.
幾つかのディメンションテーブルはALL分散に切り替えます。
ディメンションテーブルがファクトテーブルやその他の重要な結合テーブルと併置(コロケーション)出来ない場合、対象テーブル全体を全てのノードに配布する事によってクエリパフォーマンスを改善する事が出来ます。
"ALL分散"は列の結合が全てのスライス上で行われ、併置(コロケーション)される事を保証します。ALL分散を使う場合事前にそのテーブルの大きさを確認しておきましょう。全てのノードスライスに併置(コロケーション)させるため、単純にストレージ容量はN倍されます。また、ロードタイムやシステムメンテナンスの時間についてもテーブルが増える分、増加します。
CUSTOMER, SUPPLIER, DWDATEの各テーブルはLINEORDERテーブルとプライマリーキーで結合しています。しかし、LINEORDERはPARTテーブルと併置(コロケーション)されているため、残りのテーブルについてはDISTSTYLE ALLを使う事になるでしょう。これは、テーブルのサイズが比較的小さく、また頻繁には更新されないため、ALL分散を使っても最小限のストレージ及びロード時間のインパクトで済むからです。
4.
その他テーブルはEVEN分散を用います。
全てのテーブルがDISTKEY指定、またはALL指定の分散スタイルで定義されました。ですので、EVENについては指定するものは今回はありません。パフォーマンス結果を評価してみて、幾つかのテーブルはALLからEVENに変更する事はあるかもしれません。
ここまでの経過で定まった分散スタイルの一覧が以下となります。
| テーブル名 | ソートキー | 分散スタイル |
|---|---|---|
| LINEORDER | lo_orderdate | DISTKEY:lo_partkey |
| PART | p_partkey | DISTKEY:p_partkey |
| CUSTOMER | c_custkey | ALL |
| SUPPLIER | s_suppkey | ALL |
| DWDATE | d_datekey | ALL |
5.圧縮エンコーディングのレビュー
圧縮(Compression)はデータ格納時にサイズを減らすために行われるカラムレベルの操作です。ストレージを節約し、読み込み時のサイズを削減します。ディスクI/Oのパフォーマンス改善に繋がります。
デフォルトではAmazon Redshiftはそのデータを非圧縮状態(raw)で格納します。Amazon Redshiftでテーブルを作成する際、カラム毎に列圧縮タイプを指定する事が可能です。この辺りの詳しい情報については以下を御参照ください。
テーブル作成時に列圧縮タイプを指定出来る他、COPYコマンドでデータ内容を分析し、自動的にオススメの列圧縮タイプを割り出す事も可能です。
圧縮エンコーディングをレビューする
1.テーブル列毎にどの程度スペースを使用しているかを調べる
システムビュー：STV_BLOCKLISTを調べる事で、列毎に1MBブロックをどの程度使用資しているかを確認する事が出来ます。MAX集計関数はカラム毎で一番多く使っている数値を返します。以下例ではシステム生成の列を除外する為にWHERE句にcol < 6を使っています。
コマンド実行例：
select col, max(blocknum) from stv_blocklist b, stv_tbl_perm p where (b.tbl=p.id) and name ='lineorder' and col < 6 group by name, col order by col;
結果はこの様に返ってきます：
col | max -----+----- 0 | 286 1 | 286 2 | 286 3 | 286 4 | 286 5 | 286
2.異なるエンコーディングを試してみる
このステップでは『同一の列名』且つ『異なる圧縮エンコーディング』のテーブルを作成します。PARTテーブルのp_name列から同じデータを大量にINSERTします。 そして最後にテーブル間の結果を比較するという流れです。
a.比較用に、異なる圧縮エンコーディングを設定したテーブルを作成。
create table encodingshipmode ( moderaw varchar(22) encode raw, modebytedict varchar(22) encode bytedict, modelzo varchar(22) encode lzo, moderunlength varchar(22) encode runlength, modetext255 varchar(22) encode text255, modetext32k varchar(22) encode text32k);
b.INSERT - SELECT文の実行。このSQLは若干時間が掛かります。(実際実行した際は1分30秒程掛かりました)
insert into encodingshipmode select lo_shipmode as moderaw, lo_shipmode as modebytedict, lo_shipmode as modelzo, lo_shipmode as moderunlength, lo_shipmode as modetext255, lo_shipmode as modetext32k from lineorder where lo_orderkey < 200000000;
c.どの程度1MBブロックを使用しているかを確認するために以下クエリを実行。
select col, max(blocknum) from stv_blocklist b, stv_tbl_perm p where (b.tbl=p.id) and name = 'encodingshipmode' and col < 6 group by name, col order by col;
結果は以下の様な形で返ってきます。クラスタ構成によって結果は異なってくるかも知れませんが、だいたいこんな感じで返ってくるかと。
col | max -----+----- 0 | 221 1 | 26 2 | 61 3 | 192 4 | 54 5 | 105 (6 rows)
順序はこうでした。
- Raw
- Bytedict
- LZO
- Runlength
- Text255
- Text32K
これらの結果から、Bytedictが一番良い結果を弾き出しました。raw(221)と比べるとおよそ8分1という形となります。勿論、項目によってこれらの算出結果は異なって来ます。
3.既存テーブルに対してオススメのエンコーディングを確認するためにANALYZE COMPRESSIONを使う
以下コマンドを実行してみます。
analyze compression lineorder;
結果はこんな感じで返ってくるはず。
Table | Column | Encoding ----------+------------------+------------------- lineorder lo_orderkey delta lineorder lo_linenumber delta lineorder lo_custkey raw lineorder lo_partkey raw lineorder lo_suppkey raw lineorder lo_orderdate delta32k lineorder lo_orderpriority bytedict lineorder lo_shippriority runlength lineorder lo_quantity delta lineorder lo_extendedprice lzo lineorder lo_ordertotalprice lzo lineorder lo_discount delta lineorder lo_revenue lzo lineorder lo_supplycost delta32k lineorder lo_tax delta lineorder lo_commitdate delta32k lineorder lo_shipmode bytedict
ANALYZE COMPRESSIONはlo_shipmode列に対してBYTEDICTエンコーディングを選出しました。
この辺りの手順、項目毎の圧縮エンコーディングを個別・手動で定めていく流れについては以下のページを参考に進めてみると良いでしょう。
4.自動圧縮をSSBテーブル群に適用させる
デフォルトでは、COPYコマンドは RAWエンコーディング(無圧縮)以外の圧縮エンコーディングでは無い空のテーブルにデータを読み込む際、自動的に圧縮エンコーディングを適用します。
6.テスト用データセットの再作成
ここまでの過程で、テーブル毎に分散キー、ソートキーを選択して来ました。テーブルの再作成を行い、データをリロードしてみます。COPYコマンド実行時に、ロードデータを分析し圧縮エンコーディングをデータに対して自動的に適用させる事が出来ます。
データセットの再生成
1.CREATE TABLE文実行の前に、これまで作成していたテーブルを一旦削除します。
drop table part cascade; drop table supplier cascade; drop table customer cascade; drop table dwdate cascade; drop table lineorder cascade;
2.ソートキー・分散キーを指定したCREATE TABLE文を実行します。
CREATE TABLE part ( p_partkey integer not null sortkey distkey, p_name varchar(22) not null, p_mfgr varchar(6) not null, p_category varchar(7) not null, p_brand1 varchar(9) not null, p_color varchar(11) not null, p_type varchar(25) not null, p_size integer not null, p_container varchar(10) not null ); CREATE TABLE supplier ( s_suppkey integer not null sortkey, s_name varchar(25) not null, s_address varchar(25) not null, s_city varchar(10) not null, s_nation varchar(15) not null, s_region varchar(12) not null, s_phone varchar(15) not null) diststyle all; CREATE TABLE customer ( c_custkey integer not null sortkey, c_name varchar(25) not null, c_address varchar(25) not null, c_city varchar(10) not null, c_nation varchar(15) not null, c_region varchar(12) not null, c_phone varchar(15) not null, c_mktsegment varchar(10) not null) diststyle all; CREATE TABLE dwdate ( d_datekey integer not null sortkey, d_date varchar(19) not null, d_dayofweek varchar(10) not null, d_month varchar(10) not null, d_year integer not null, d_yearmonthnum integer not null, d_yearmonth varchar(8) not null, d_daynuminweek integer not null, d_daynuminmonth integer not null, d_daynuminyear integer not null, d_monthnuminyear integer not null, d_weeknuminyear integer not null, d_sellingseason varchar(13) not null, d_lastdayinweekfl varchar(1) not null, d_lastdayinmonthfl varchar(1) not null, d_holidayfl varchar(1) not null, d_weekdayfl varchar(1) not null) diststyle all; CREATE TABLE lineorder ( lo_orderkey integer not null sortkey, lo_linenumber integer not null, lo_custkey integer not null, lo_partkey integer not null distkey, lo_suppkey integer not null, lo_orderdate integer not null, lo_orderpriority varchar(15) not null, lo_shippriority varchar(1) not null, lo_quantity integer not null, lo_extendedprice integer not null, lo_ordertotalprice integer not null, lo_discount integer not null, lo_revenue integer not null, lo_supplycost integer not null, lo_tax integer not null, lo_commitdate integer not null, lo_shipmode varchar(10) not null );
3.データをロードします。以前のSQLではcompupdate offの指定をしてましたが、今回はそれを外します。 これでCOPYコマンドが圧縮エンコーディングを自動で適用するようになります。
copy customer from 's3://<region-specific-bucket-name>/ssbgz/customer' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip; copy dwdate from 's3://<region-specific-bucket-name>/ssbgz/dwdate' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip; copy lineorder from 's3://<region-specific-bucket-name>/ssbgz/lineorder' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip; copy part from 's3://<region-specific-bucket-name>/ssbgz/part' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip; copy supplier from 's3://<region-specific-bucket-name>/ssbgz/supplier' credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' gzip;
ロード時間を計測してみましょう。各テーブルの計測時間は以下のようになりました。初回実行はチューニング後よりも時間が掛かっている様ですが、2回目以降は改善されていますね。(とは言え、実行時間が長くなったテーブルもあるようです。)
<< CUSTOMER >> 時間: 53674.706 ms 時間: 13065.793 ms 時間: 12435.864 ms 時間: 12090.514 ms 時間: 12249.941 ms << DWDATE >> 時間: 28246.456 ms 時間: 2727.605 ms 時間: 966.135 ms 時間: 948.880 ms 時間: 953.681 ms 時間: 949.184 ms << LINEORDER >> 時間: 940926.593 ms 時間: 940926.131 ms 時間: 909363.441 ms 時間: 1064005.981 ms 時間: 1062814.205 ms << PART >> 時間: 26005.555 ms 時間: 4638.184 ms 時間: 3834.709 ms 時間: 3894.809 ms 時間: 3377.251 ms << SUPPLIER >> 時間: 26566.266 ms 時間: 5358.111 ms 時間: 4186.689 ms 時間: 5238.045 ms 時間: 4467.133 ms
上記結果を反映した表は以下の様になりました。(※計測時間は直近4回の平均を採用)
7.システムパフォーマンステスト実践(再)
ソートキー・分散キーや圧縮エンコーディングの設定を付与してデータセットを再作成したら、再テストを実施します。
チューニング後のパフォーマンステストを再実施
1.
ストレージ使用量を計測します。
select
stv_tbl_perm.name as "table",
count(*) as "blocks (mb)"
from
stv_blocklist,
stv_tbl_perm
where
stv_blocklist.tbl = stv_tbl_perm.id
and stv_blocklist.slice = stv_tbl_perm.slice
and stv_tbl_perm.name in ('customer', 'part', 'supplier', 'dwdate', 'lineorder')
group by
stv_tbl_perm.name
order by
1 asc;
結果はこうなりました。
table | blocks (mb)
-------------+-------------
customer | 604
dwdate | 160
lineorder | 27152
part | 200
supplier | 236
(5 行)
2.
『傾斜分布(distribution skew)』の確認を行います。
不均一な分散またはデータ分散スキューは、クエリパフォーマンスを制限する点に加えてより多くの作業を幾つかのノードに強いる事となります。
分散スキューを確認するには、システムビューのSVV_DISKUSAGEを参照します。このテーブルの各行は1つのディスクブロックの統計情報を記録します。num_values列はディスクブロック内の行数を返し、sum(num_values)はスライス毎の行数を返します。
SSBデータベース内のテーブルの全ての分散状況を参照するためのクエリを実行します。
select
trim(name) as table,
slice,
sum(num_values) as rows,
min(minvalue),
max(maxvalue)
from
svv_diskusage
where
name in ('customer', 'part', 'supplier', 'dwdate', 'lineorder')
and col =0
group by
name, slice
order by
name, slice;
実行結果は以下のような形で返ってきます。
table | slice | rows | min | max -----------+-------+----------+----------+----------- customer | 0 | 3000000 | 1 | 3000000 customer | 2 | 3000000 | 1 | 3000000 customer | 4 | 3000000 | 1 | 3000000 customer | 6 | 3000000 | 1 | 3000000 dwdate | 0 | 2556 | 19920101 | 19981230 dwdate | 2 | 2556 | 19920101 | 19981230 dwdate | 4 | 2556 | 19920101 | 19981230 dwdate | 6 | 2556 | 19920101 | 19981230 lineorder | 0 | 75029991 | 3 | 599999975 lineorder | 1 | 75059242 | 7 | 600000000 lineorder | 2 | 75238172 | 1 | 599999975 lineorder | 3 | 75065416 | 1 | 599999973 lineorder | 4 | 74801845 | 3 | 599999975 lineorder | 5 | 75177053 | 1 | 599999975 lineorder | 6 | 74631775 | 1 | 600000000 lineorder | 7 | 75034408 | 1 | 599999974 part | 0 | 175006 | 15 | 1399997 part | 1 | 175199 | 1 | 1399999 part | 2 | 175441 | 4 | 1399989 part | 3 | 175000 | 3 | 1399995 part | 4 | 175018 | 5 | 1399979 part | 5 | 175091 | 11 | 1400000 part | 6 | 174253 | 2 | 1399969 part | 7 | 174992 | 13 | 1399996 supplier | 0 | 1000000 | 1 | 1000000 supplier | 2 | 1000000 | 1 | 1000000 supplier | 4 | 1000000 | 1 | 1000000 supplier | 6 | 1000000 | 1 | 1000000 (28 rows)
以下グラフは大きなテーブル上位3つの分散状況を図示したものです。CUSTOMERテーブルはALL分散を使用しているので、1ノードに対して1つだけに分散されています。この分散状況は比較的均一と言えます。ですので分布のスキューを調整する必要はありません。
3.
クエリに対するクエリプランを確認するためにEXPLAINコマンドを実行します。以下例はQuery2に対するEXPLAINコマンド実行です。
explain select sum(lo_revenue), d_year, p_brand1 from lineorder, dwdate, part, supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand1 order by d_year, p_brand1;
以下が実行結果となります。DS_BCAST_INNERラベルがDS_DIST_ALL_NONEとDS_DIST_NONEに置き換わっています。これは再分散の必要が無くなり、クエリはより高速化されるだろう、という分析結果を意味しています。
QUERY PLAN
XN Merge (cost=1000014243538.45..1000014243539.15 rows=280 width=20)
Merge Key: dwdate.d_year, part.p_brand1
-> XN Network (cost=1000014243538.45..1000014243539.15 rows=280 width=20)
Send to leader
-> XN Sort (cost=1000014243538.45..1000014243539.15 rows=280 width=20)
Sort Key: dwdate.d_year, part.p_brand1
-> XN HashAggregate (cost=14243526.37..14243527.07 rows=280 width=20)
-> XN Hash Join DS_DIST_ALL_NONE (cost=30643.30..14211277.03 rows=4299912 width=20)
Hash Cond: ("outer".lo_orderdate = "inner".d_datekey)
-> XN Hash Join DS_DIST_ALL_NONE (cost=30611.35..14114497.06 rows=4299912 width=20)
Hash Cond: ("outer".lo_suppkey = "inner".s_suppkey)
-> XN Hash Join DS_DIST_NONE (cost=17640.00..13758507.64 rows=24001516 width=24)
Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
-> XN Seq Scan on lineorder (cost=0.00..6000378.88 rows=600037888 width=16)
-> XN Hash (cost=17500.00..17500.00 rows=56000 width=16)
-> XN Seq Scan on part (cost=0.00..17500.00 rows=56000 width=16)
Filter: ((p_category)::text = 'MFGR#12'::text)
-> XN Hash (cost=12500.00..12500.00 rows=188541 width=4)
-> XN Seq Scan on supplier (cost=0.00..12500.00 rows=188541 width=4)
Filter: ((s_region)::text = 'AMERICA'::text)
-> XN Hash (cost=25.56..25.56 rows=2556 width=8)
-> XN Seq Scan on dwdate (cost=0.00..25.56 rows=2556 width=8)
4.
時間計測用にクエリを再度実行。比較用に前以て実行していたクエリ群を再度実行します。
-- Query 1 -- Restrictions on only one dimension. select sum(lo_extendedprice*lo_discount) as revenue from lineorder, dwdate where lo_orderdate = d_datekey and d_year = 1997 and lo_discount between 1 and 3 and lo_quantity < 24; -- Query 2 -- Restrictions on two dimensions select sum(lo_revenue), d_year, p_brand1 from lineorder, dwdate, part, supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand1 order by d_year, p_brand1; -- Query 3 -- Drill down in time to just one month select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer, lineorder, supplier, dwdate where lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_orderdate = d_datekey and (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997' group by c_city, s_city, d_year order by d_year asc, revenue desc;
結果を確認します。幾つかの要因を変更した事でパフォーマンスが改善されている事が確認出来ました。
<< Query 1 >> 時間: 5627.639 ms 時間: 2730.031 ms 時間: 2747.846 ms 時間: 2629.781 ms 時間: 2709.245 ms << Query 2 >> 時間: 11928.977 ms 時間: 8798.777 ms 時間: 8996.882 ms 時間: 9189.499 ms 時間: 8914.112 ms << Query 3 >> 時間: 15024.798 ms 時間: 11051.512 ms 時間: 11790.093 ms 時間: 11555.179 ms 時間: 11352.935 ms
検証結果を集計した比較内容は以下の通りとなりました。
8.結果検証
ここまでロード時間、ストレージ容量、クエリ実行について、チューニング前後の記録を比較する事で検証を重ねてきました。
今回このドキュメントを参考に実践してみた結果は以下のようになります。
データのロード時間
ソートキー、分散キー、列圧縮タイプを設定した事により、若干時間が増えました。特にこの場合、圧縮エンコード設定がされていないテーブル毎に対してロード時間が掛かる事になる自動圧縮を用いていたのもありました。今後、このテーブルに対する読み込みは高速化されていくでしょう。分散スタイル：ALLを指定した事も時間が増えた要因の1つとなります。
幾つかのテーブルについては分散スタイル：EVENまたはDISTKEYを使う事でロード時間を減らす事も出来ました。しかしこの決定はクエリパフォーマンスが遅くなる可能性もあるので状況に応じて云々。
ストレージ使用量
カラム圧縮の利用によるストレージ部分の改善部分の幾らかは分散スタイル：ALLの使用によって総裁されました。
繰り返しますが、分散スタイル：EVENもしくはDISTKEYの指定を使う事によって幾つかのテーブルについては改善を行う事が出来ますが、その替わりにパフォーマンスが犠牲になるでしょう。
分散
分散方法を選択・指定した結果、傾斜分布が無い事は確認出来ました。EXPLAINでのチェックによって、テストクエリに対する幾つかのデータ再分散が除去する事が出来ました。
クエリ実行時間
クエリパフォーマンスの改善は ソートキー、分散スタイル、圧縮エンコーディングの組み合わせによるものでした。
多くの場合、クエリパフォーマンスはクエリの書き換えやWLM(Workload Management)の設定によって改善する事が出来ます。より詳細な情報については、以下ドキュメントを御参照ください。
9.リソースのお掃除
クラスタは起動し続けている限り、料金が発生し続けます。今回検証用に構築したクラスタ環境であれば、忘れずにクラスタを削除しておきましょう。
クラスタは残すけれども今回使ったデータは要らないという場合は以下DROP TABLE文を実行し、テーブルを削除してください。
drop table part cascade; drop table supplier cascade; drop table customer cascade; drop table dwdate cascade; drop table lineorder cascade;
まとめ
以上、公式ドキュメント記載の実行記録でした。実際にサンプルデータを用いて問題点の特定・改善作業・検証を行う事で、パフォーマンスチューニングの勘所も掴み易くなる事が実感出来ると思います。
こちらからは以上です。
