Amazon Redshift: ソートキーを指定してないとVACUUMの恩恵に預かれないというお話(&VACUUM対象テーブル抽出改良版SQL付)

Amazon Redshiftで良く使いそうな便利系SQL

しんや

2016.09.20

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

小ネタです。

そして、言われてみれば確かにそうだよね、という割と初歩的且つシンプルなお話を。

過去のエントリにて、"VACUUM対象テーブルを洗い出す"というテーマで以下2本のエントリを投稿しました。

上記SQLを使って、VACUUM処理を必要とするテーブルに対してVACUUM処理を実行して行く過程で、何度実行しても未ソート状態が改善されないテーブルが幾つかありました。

この事象は、下記袴田のエントリ内『統計情報の鮮度とソートされていない行の割合に注意！』で示されているSQLでも同様です。VACUUMすべき対象として挙がってくるものの、VACUUMを行っても状況は変わりませんでした。

原因は至極シンプルなものでした。『テーブルにソートキー指定がされてない』が為に、VACUUM処理を行ってもソート処理がされない、ただそれだけでした。分かってみれば『そらそうよ』(by 岡田彰布元阪神・オリックス監督)な内容です。

テーブルに対してDELETE等を行っていれば、その際のゴミデータをVACUUMで除去・改善出来るのだとは思いますが、ソート状態の改善についてはキーが指定されていないためにSKIPされている、という事なのでしょう。

VACUUM・ANALYZE処理が必要なテーブルを割り出すSQL(改良版)

上記内容を踏まえ、過去エントリの内容をよりブラッシュアップさせたSQLが以下となります。以下2つのテーブル情報を結合させています。

SELECT
  to_vacuum_information.id,
  to_vacuum_information.schema_name,
  to_vacuum_information.table_name,
  to_vacuum_information.rows,
  to_vacuum_information.sorted_rows,
  to_vacuum_information.sort_percentage,
  to_vacuum_information.first_sortkey,
  to_vacuum_information.to_set_sortkey,
  CASE sort_percentage
    WHEN 0.000 THEN '03_完全未SORT(要SORT)'
    WHEN 1.000 THEN '05_完全SORT済み'
    ELSE '04_一部未SORT(要SORT)'
  END AS to_vacuum_sort,
  to_vacuum_information.stats_off,
  CASE to_vacuum_information.stats_off
    WHEN 0.00 THEN ''
    ELSE '06_要ANALYZE'
  END AS to_analyze
FROM
  (SELECT
    sorted_infomation.id,
    TRIM(svv_table_info.schema) AS schema_name,
    sorted_infomation.table_name,
    sorted_infomation.rows,
    sorted_infomation.sorted_rows,
    CASE sorted_infomation.sorted_rows
      WHEN 0 THEN 0.000
      ELSE 
        CAST(
          CAST(sorted_infomation.sorted_rows AS DOUBLE PRECISION) / CAST(sorted_infomation.rows AS DOUBLE PRECISION)
          AS DECIMAL(6,3)
        )
    END AS sort_percentage,
    TRIM(svv_table_info.sortkey1) AS first_sortkey,
    NVL2(TRIM(svv_table_info.sortkey1), '01_設定済', '00_未設定(要SORTKEY設定)') AS to_set_sortkey,
    svv_table_info.stats_off
  FROM
    (SELECT
      stv_tbl_perm.id,
      TRIM(stv_tbl_perm.name) AS table_name,
      SUM(stv_tbl_perm.rows) AS rows,
      SUM(stv_tbl_perm.sorted_rows) AS sorted_rows,
      stv_tbl_perm.db_id
    FROM
      stv_tbl_perm
    GROUP BY
      stv_tbl_perm.id,
      TRIM(stv_tbl_perm.name),
      stv_tbl_perm.db_id) sorted_infomation
        INNER JOIN svv_table_info ON sorted_infomation.id = svv_table_info.table_id
  ) to_vacuum_information
WHERE
      to_vacuum_information.schema_name = '<任意のスキーマ名>'
  AND to_vacuum_information.table_name = '<任意のテーブル名>'
ORDER BY
  to_set_sortkey ASC,
  to_vacuum_information.sort_percentage ASC,
  to_vacuum_information.rows DESC;

以下が実行結果のサンプルです。観点としては3つ。

   id   | schema_name |    table_name   |   first_sortkey   |      to_set_sortkey      |    rows    | sorted_rows | sort_percentage |    to_vacuum_sort     | stats_off |  to_analyze  
--------+-------------+-----------------+-------------------+--------------------------+------------+-------------+-----------------+-----------------------+-----------+--------------
 100001 | schema_aa   | table_aaa       |                   | 00_未設定(要SORTKEY設定) |  904901515 |           0 |           0.000 | 02_完全未SORT(要SORT) |      0.00 | 
 100002 | schema_aa   | table_bbb       |                   | 00_未設定(要SORTKEY設定) |   48495810 |           0 |           0.000 | 02_完全未SORT(要SORT) |      0.00 | 
 100003 | schema_aa   | table_ccc       |                   | 00_未設定(要SORTKEY設定) |    9840944 |           0 |           0.000 | 02_完全未SORT(要SORT) |      0.00 | 
 :
 200001 | schema_bb   | table_01        | xxx_id            | 01_SORTKEY設定済         |         47 |           0 |           0.000 | 02_完全未SORT(要SORT) |     99.99 | 05_要ANALYZE
 200002 | schema_bb   | table_02        | yyyy_id           | 01_SORTKEY設定済         |         36 |           0 |           0.000 | 02_完全未SORT(要SORT) |      0.00 | 
 :
 300001 | public      | table_zz        | aaa_date          | 01_SORTKEY設定済         |      54829 |        6853 |           0.125 | 03_一部未SORT(要SORT) |     87.49 | 05_要ANALYZE
 300002 | public      | table_yy        | bb_timestamp      | 01_SORTKEY設定済         |        472 |          59 |           0.125 | 03_一部未SORT(要SORT) |     87.50 | 05_要ANALYZE
 300004 | public      | table_xx        | ooo_id            | 01_SORTKEY設定済         |         90 |          45 |           0.500 | 03_一部未SORT(要SORT) |     54.44 | 05_要ANALYZE
 300005 | public      | table_ww        | uuid              | 01_SORTKEY設定済         |    9434783 |     5940500 |           0.630 | 03_一部未SORT(要SORT) |    100.00 | 05_要ANALYZE
 300006 | public      | table_vv        | ccc_date          | 01_SORTKEY設定済         |    2373521 |     1742388 |           0.734 | 03_一部未SORT(要SORT) |     99.99 | 05_要ANALYZE
 300007 | public      | table_uu        | ddd_timestamp     | 01_SORTKEY設定済         |  169769039 |   161609366 |           0.952 | 03_一部未SORT(要SORT) |      0.00 | 
 300008 | public      | table_tt        | eee_id            | 01_SORTKEY設定済         |     588380 |      586625 |           0.997 | 03_一部未SORT(要SORT) |      0.00 | 
 300009 | public      | table_rr        | ffff_key          | 01_SORTKEY設定済         |   25032673 |    25005037 |           0.999 | 03_一部未SORT(要SORT) |      0.00 | 
 :
 900001 | bi_cm       | table_abc       | user_id           | 01_SORTKEY設定済         | 4383172369 |  4383172369 |           1.000 | 04_完全SORT済み       |      0.19 | 05_要ANALYZE
 900002 | bi_cm       | table_xyz       | store_cd          | 01_SORTKEY設定済         | 1015605084 |  1015605084 |           1.000 | 04_完全SORT済み       |      0.00 | 
 900003 | bi_cm       | table_xxx       | xyz_date          | 01_SORTKEY設定済         |  456895134 |   456895134 |           1.000 | 04_完全SORT済み       |      0.00 |

1.テーブルのソートキー設定有無を確認

1つ目はテーブルのソートキー設定の有無。first_sortkeyとto_set_sortkey列の内容を確認します。ソートキー指定がされていないものにはfirst_sortkeyの値が設定されず、to_set_sortkeyの欄には『00_未設定(要SORTKEY設定)』というラベルが表示されます。、そもそもVACUUMしてもSORTKEYを使ってのソート処理がされない状態となるので、まずはテーブル定義を見直し適切なソートキーを設定するところから着手します。

2.テーブルのソート状態を確認

次いでテーブルのソート状態を見ます。sort_percentageとto_vacuum_sort列です。

ソート状態が0.000(%)、即ちソート済みの列が全く無い場合、VACUUM処理及びVACUUM SORT ONLYでソート処理を行う必要があります(『02_完全未SORT(要SORT)』)。1.000(=100%、100%ソート済み)に満たないもの(『03_一部未SORT(要SORT)』)についてもソート処理が必要です。Amazon Redshiftではソート対象の基準デフォルト値が95%以上のもの(未ソート状態のデータが5%に満たないもの)についてはソート処理をスキップしていますので、対象テーブルの中でソート済み状態が95%以上となっているものについては実施を保留としても良いかも知れません。

3.テーブルのANALYZE状態を確認

最後3つ目はテーブルの最新の統計情報が得られているかの確認です。stats_offとto_analyze列が該当します。『svv_table_info』テーブルのドキュメントを見ると、stats_offの値は『テーブルの統計情報の古さを示す数。0 は最新で、100 は最新でないことを示します。』という見方の解説があります。0で無いものについてはto_analyze列に『05_要ANALYZE』のラベルを表示させていますので、このラベルが表示されたテーブルについては『ANALYZE実行すべし』という事になります。

ソートキーの有無でVACUUMの効き目を確認

では、ソートキーの有無でVACUUMの実行内容がどう変わるのか確認してみましょう。以下のpublic.ordersテーブルはソートキー(sortkey)指定がありません。(全て0。1以上の数字が入っている場合、数字の順にソートキー指定となる)

# SELECT * FROM pg_table_def WHERE schemaname = 'public' AND tablename = 'orders';
 schemaname | tablename |        column        |          type          | encoding | distkey | sortkey | notnull 
------------+-----------+----------------------+------------------------+----------+---------+---------+---------
 public     | orders    | order_id             | integer                | none     | f       |       0 | t
 public     | orders    | order_date           | date                   | none     | f       |       0 | t
 public     | orders    | priority             | character varying(12)  | none     | f       |       0 | t
 public     | orders    | quantity             | smallint               | none     | f       |       0 | t
 public     | orders    | sales                | double precision       | none     | f       |       0 | f
 public     | orders    | discount_rate        | double precision       | none     | f       |       0 | f
 public     | orders    | ship_mode            | character varying(20)  | bytedict | f       |       0 | t
 public     | orders    | profit               | integer                | none     | f       |       0 | t
 public     | orders    | unit_price           | integer                | none     | f       |       0 | t
 public     | orders    | ad_expenses          | integer                | none     | f       |       0 | t
 public     | orders    | shipping_cost        | integer                | none     | f       |       0 | t
 public     | orders    | customer_name        | character varying(50)  | lzo      | f       |       0 | t
 public     | orders    | prefecture           | character varying(12)  | none     | f       |       0 | t
 public     | orders    | city                 | character varying(20)  | none     | f       |       0 | t
 public     | orders    | area                 | character varying(12)  | none     | f       |       0 | t
 public     | orders    | shop_name            | character varying(20)  | none     | f       |       0 | t
 public     | orders    | customer_segment     | character varying(30)  | bytedict | f       |       0 | t
 public     | orders    | product_category     | character varying(30)  | bytedict | f       |       0 | t
 public     | orders    | product_sub_category | character varying(100) | lzo      | f       |       0 | t
 public     | orders    | product_id           | character varying(10)  | none     | f       |       0 | t
 public     | orders    | product_name         | character varying(100) | lzo      | f       |       0 | t
 public     | orders    | product_description  | character varying(200) | lzo      | f       |       0 | t
 public     | orders    | product_container    | character varying(100) | lzo      | f       |       0 | t
 public     | orders    | base_margin          | double precision       | none     | f       |       0 | f
 public     | orders    | supplier             | character varying(30)  | none     | f       |       0 | t
 public     | orders    | deliver_date         | date                   | none     | f       |       0 | t
 public     | orders    | ship_date            | date                   | none     | f       |       0 | t
(27 rows)

# SELECT COUNT(*) FROM public.orders;
 count 
-------
  8369
(1 row)

これとは別に、ソートキー指定を行った同一内容別テーブル：public.orders_newを用意。

CREATE TABLE IF NOT EXISTS public.orders_new
 (
         order_id INTEGER NOT NULL  
         ,order_date DATE NOT NULL  
         ,priority VARCHAR(12) NOT NULL  
         ,quantity SMALLINT NOT NULL  
         ,sales DOUBLE PRECISION   
         ,discount_rate DOUBLE PRECISION   
         ,ship_mode VARCHAR(20) NOT NULL  ENCODE bytedict
         ,profit INTEGER NOT NULL  
         ,unit_price INTEGER NOT NULL  
         ,ad_expenses INTEGER NOT NULL  
         ,shipping_cost INTEGER NOT NULL  
         ,customer_name VARCHAR(50) NOT NULL  ENCODE lzo
         ,prefecture VARCHAR(12) NOT NULL  
         ,city VARCHAR(20) NOT NULL  
         ,area VARCHAR(12) NOT NULL  
         ,shop_name VARCHAR(20) NOT NULL  
         ,customer_segment VARCHAR(30) NOT NULL  ENCODE bytedict
         ,product_category VARCHAR(30) NOT NULL  ENCODE bytedict
         ,product_sub_category VARCHAR(100) NOT NULL  ENCODE lzo
         ,product_id VARCHAR(10) NOT NULL  
         ,product_name VARCHAR(100) NOT NULL  ENCODE lzo
         ,product_description VARCHAR(200) NOT NULL  ENCODE lzo
         ,product_container VARCHAR(100) NOT NULL  ENCODE lzo
         ,base_margin DOUBLE PRECISION   
         ,supplier VARCHAR(30) NOT NULL  
         ,deliver_date DATE NOT NULL  
         ,ship_date DATE NOT NULL  
         ,PRIMARY KEY (order_id)
 )
 DISTSTYLE EVEN
 SORTKEY (order_date, order_id);
CREATE

# INSERT INTO public.orders_new (SELECT * FROM public.orders ORDER BY profit DESC);
INSERT 0 8369
# INSERT INTO public.orders_new (SELECT * FROM public.orders_new ORDER BY shipping_cost, customer_name);
INSERT 0 8369
Time: 2552.128 ms
# SELECT COUNT(*) FROM public.orders_new;
 count 
-------
 16738
(1 row)

確認用SQLで内容を確認してみます。

:
WHERE
      to_vacuum_information.schema_name = 'public'
  AND to_vacuum_information.table_name like'orders%'
ORDER BY
  to_set_sortkey ASC,
  to_vacuum_information.sort_percentage ASC,
  to_vacuum_information.rows DESC;
   id   | schema_name |   table_name   |  rows   | sorted_rows | sort_percentage | first_sortkey |      to_set_sortkey      |    to_vacuum_sort     | stats_off |  to_analyze  
--------+-------------+----------------+---------+-------------+-----------------+---------------+--------------------------+-----------------------+-----------+--------------
 553671 | public      | orders         |    8369 |           0 |           0.000 |               | 00_未設定(要SORTKEY設定) | 03_完全未SORT(要SORT) |     99.99 | 06_要ANALYZE
 553675 | public      | orders_new     |   16738 |        8369 |           0.500 | order_date    | 01_設定済                | 04_一部未SORT(要SORT) |     99.99 | 06_要ANALYZE
(2 rows)

双方テーブルに対してVACUUM処理とANALYZE処理を実行。実行後の結果を改めて確認して見ると、ソートキー指定を行ったorders_newテーブルはちゃんと処理されていますが、未指定のordersは処理そのものがSKIPされている様です。(処理時間的に) ソートキー指定を行っていないのでそもそも並び替え出来ないよ、という事ですね。

# VACUUM public.orders;
VACUUM
Time: 806.793 ms
# ANALYZE public.orders;
ANALYZE
Time: 3498.997 ms
# VACUUM public.orders_new;
VACUUM
Time: 44527.891 ms
# ANALYZE public.orders_new;
ANALYZE
Time: 2223.968 ms
:
:
WHERE
      to_vacuum_information.schema_name = 'public'
  AND to_vacuum_information.table_name like'orders%'
ORDER BY
  to_set_sortkey ASC,
  to_vacuum_information.sort_percentage ASC,
  to_vacuum_information.rows DESC;
   id   | schema_name |   table_name   |  rows   | sorted_rows | sort_percentage | first_sortkey |      to_set_sortkey      |    to_vacuum_sort     | stats_off |  to_analyze  
--------+-------------+----------------+---------+-------------+-----------------+---------------+--------------------------+-----------------------+-----------+--------------
 553671 | public      | orders         |    8369 |           0 |           0.000 |               | 00_未設定(要SORTKEY設定) | 03_完全未SORT(要SORT) |      0.00 | 
 553675 | public      | orders_new     |   16738 |       16738 |           1.000 | order_date    | 01_設定済                | 05_完全SORT済み       |      0.00 | 
(2 rows)

まとめ

という訳で、

 　　　　　　r　‐､
　 　　　　　| ○ | 　　　 　  r‐‐､ 　　　　良い子の諸君！
　　　　　_,;ﾄ - ｲ、　　　　　∧l☆│ ∧ 　　
　　　 （⌒`　　　 ⌒ヽ　　　/,ﾄ.-ｲ/,l　　Redshiftでテーブル作成する時は
　　　　|ヽ　 ~~⌒γ⌒）　r'⌒｀!´ `⌒）　忘れずにソートキー指定を行っておこう。
　　　 │　ヽー―'^ー-'　（  ⌒γ⌒~~　/| 　(&分散キーの検討も忘れずに)
　　　 │　　〉　　　| │　|｀ー^ｰ―  r'| 　
　　　 │　 /───────|  | |/ |  l　 ﾄ､|　でないとVACUUMしても
　　　|　 irｰ-､ ｰ　,}  |　 / 　　　i 　　データがちゃんと整理されないぞ。
　　　| /　　　｀X´　ヽ 　/ 　 入　 |

こちらからは以上です。

Amazon Redshift: ソートキーを指定してないとVACUUMの恩恵に預かれないというお話(&VACUUM対象テーブル抽出改良版SQL付)

VACUUM・ANALYZE処理が必要なテーブルを割り出すSQL(改良版)

1.テーブルのソートキー設定有無を確認

2.テーブルのソート状態を確認

3.テーブルのANALYZE状態を確認

ソートキーの有無でVACUUMの効き目を確認

まとめ

イベント

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

Amazon Redshift: ソートキーを指定してないとVACUUMの恩恵に預かれないというお話(&VACUUM対象テーブル抽出 改良版SQL付)

VACUUM・ANALYZE処理が必要なテーブルを割り出すSQL(改良版)

1.テーブルのソートキー設定有無を確認

2.テーブルのソート状態を確認

3.テーブルのANALYZE状態を確認

ソートキーの有無でVACUUMの効き目を確認

まとめ

イベント

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS/システムアーキテクト/Android案件特集〜 を開催します

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

Invalid principal in policyが出たら確認したい！IAMロール・IAMユーザーが削除されるとPrincipal要素のARNがプリンシパルIDに置き換わります

Amazon SQSワーカーのアーキテクチャーをLambdaイベントソース/EventBridge Pipes/独自の3パターンで比較してみた

[Amazon FSx for NetApp ONTAP] SnapLockによるWORMを試してみた

AWS利用のためのBGPをざっくり理解する

Amazon Redshift: ソートキーを指定してないとVACUUMの恩恵に預かれないというお話(&VACUUM対象テーブル抽出改良版SQL付)

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩