Amazon Redshift 統計情報を自動更新する『Auto Analyze』の動きを確認してみました

はじめに

Auto Analyzeは統計情報の更新（ANALYZE）がバックグラウンドで自動実行するサービスです。本日は実際の動作を確認してみました。

Auto Analyzeとは

Amazon Redshiftは、テーブル内で、どのような値が、どのような頻度で出現するのかの情報である「統計情報」を事前に取得しておき、この情報を基に効率的にレコード操作を行う計画「実行計画」を立てて実行します。つまり最適なパフォーマンスを得るには、正確な「統計情報」が必要となります。

これまでテーブルのデータは日々更新に応じて、ANALYZEコマンドを実行する必要がありましたが、これをバックグラウンドで自動実行する機能が、『Auto Analyze』です。この機能は、クエリの負荷に基づいてスケジュール実行されるため、アドホッククエリやバッチクエリの妨げにならないので安心してご利用いただけます。

• 参考：テーブルを分析する

Auto Analyzeはすでに有効済み

Auto Analyzeは、デフォルトで有効に設定されており、すでに皆さんのRedshiftクラスタにおいてもAuto Analyzeが有効になっているはずです。パラメータauto_analyzeを変更することで自動実行をoffに設定変更も可能です。

ところが、システムビュー svv_table_infoのテーブルの統計情報の古さを示す数(stats_offは0が最新)が更新されないため、本当にAuto Analyzeが機能しているのかが判断できませんでした。（2019/05/20現在）

cmdb=# -- テーブルの統計情報の古さを示す数(stats_offは0が最新)
cmdb=# SELECT database, schema, "table", stats_off
cmdb-# FROM svv_table_info
cmdb-# WHERE "schema" = 'public' AND "table" = 'lineorder';
database | schema | table | stats_off
----------+--------+-----------+-----------
cmdb | public | lineorder | 99.99
(1 row)

以降では、Auto Analyze がどのように、どのようなタイミングで実行されるのかを確認します。

Auto Analyze 動作検証

検証前

テーブルに対してANALYZEを実行するか判定するため、変更された行の割合のしきい値を参照します。この環境ではデフォルトの10%です。つまり、レコードの更新が10%未満の場合、ANALYZEコマンドはSkipされます。

cmdb=# -- テーブルに対してANALYZEを実行するか判定するため、変更された行の割合のしきい値を参照
cmdb=# SHOW analyze_threshold_percent;
analyze_threshold_percent
---------------------------
10
(1 row)

レコード追加した新規テーブルに対する Auto Analyze 検証

検証用にテーブルを新規作成して、10000レコードを追加しました。

cmdb=# -- テーブルを新規作成する
cmdb=# DROP TABLE "public"."lineorder";
DROP TABLE
cmdb=# CREATE TABLE IF NOT EXISTS "public"."lineorder"
cmdb-# (
cmdb(# "lo_orderkey" INTEGER ENCODE lzo
cmdb(# ,"lo_linenumber" INTEGER ENCODE lzo
cmdb(# ,"lo_custkey" INTEGER ENCODE lzo
cmdb(# ,"lo_partkey" INTEGER ENCODE lzo
cmdb(# ,"lo_suppkey" INTEGER ENCODE lzo
cmdb(# ,"lo_orderdate" INTEGER ENCODE lzo
cmdb(# ,"lo_orderpriority" VARCHAR(15) ENCODE lzo
cmdb(# ,"lo_shippriority" VARCHAR(1) ENCODE lzo
cmdb(# ,"lo_quantity" INTEGER ENCODE lzo
cmdb(# ,"lo_extendedprice" INTEGER ENCODE lzo
cmdb(# ,"lo_ordertotalprice" INTEGER ENCODE lzo
cmdb(# ,"lo_discount" INTEGER ENCODE lzo
cmdb(# ,"lo_revenue" INTEGER ENCODE lzo
cmdb(# ,"lo_supplycost" INTEGER ENCODE lzo
cmdb(# ,"lo_tax" INTEGER ENCODE lzo
cmdb(# ,"lo_commitdate" INTEGER ENCODE lzo
cmdb(# ,"lo_shipmode" VARCHAR(10) ENCODE lzo
cmdb(# )
cmdb-# DISTSTYLE EVEN
cmdb-# SORTKEY(lo_orderdate);
CREATE TABLE
cmdb=#
cmdb=#
cmdb=# -- データのロード
cmdb=# INSERT INTO public.lineorder
cmdb-# SELECT * FROM public.sady_lineorder LIMIT 10000;
INSERT 0 10000
cmdb=#
cmdb=# -- 作成した時間
cmdb=# SELECT sysdate;
timestamp
----------------------------
2019-05-20 06:31:44.583968
(1 row)

下記の通り、テーブル作成直後は、システムテーブル stl_analyze に実行結果はロギングされていません。システムテーブル pg_statistic_indicator の staiins カラムの「10000」は、最後の ANALYZE 以降に挿入されたレコード数を表し、ANALYZEが実行していないことが確認できます。

cmdb=# -- テーブルに対するANLYZEの実行状況を確認
cmdb=# SELECT userid, table_id, status, rows, modified_rows, threshold_percent, is_auto, starttime, endtime, prevtime, is_background
cmdb-# FROM stl_analyze
cmdb-# WHERE table_id = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' AND "table" = 'lineorder'
cmdb(# ) ORDER BY starttime;
userid | table_id | status | rows | modified_rows | threshold_percent | is_auto | starttime | endtime | prevtime | is_background
--------+----------+--------+------+---------------+-------------------+---------+-----------+---------+----------+---------------
(0 rows)

cmdb=# /*
cmdb*# stairelid: テーブル ID
cmdb*# stairows: テーブル内の合計行数
cmdb*# staiins: 最後の ANALYZE 以降に挿入された行数
cmdb*# staidels: 最後の ANALYZE 以降に削除または更新された行数
cmdb*# */
cmdb-# SELECT * FROM pg_statistic_indicator
cmdb-# WHERE stairelid = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' and "table" = 'lineorder'
cmdb(# );
stairelid | stairows | staiins | staidels
-----------+----------+---------+----------
1484523 | 10000 | 10000 | 0
(1 row)

下記の通り、30分後に確認すると、システムテーブル stl_analyze の status カラムが 「Full」であることが確認できます。システムテーブル pg_statistic_indicator の staiins カラムは「10000」から「0」に変わり、ANALYZEが実行していることが確認できました。

cmdb=# -- テーブルに対するANLYZEの実行状況を確認
cmdb=# SELECT userid, table_id, status, rows, modified_rows, threshold_percent, is_auto, starttime, endtime, prevtime, is_background
cmdb-# FROM stl_analyze
cmdb-# WHERE table_id = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' AND "table" = 'lineorder'
cmdb(# ) ORDER BY starttime;
userid | table_id | status | rows | modified_rows | threshold_percent | is_auto | starttime | endtime | prevtime | is_background
--------+----------+-----------------+-------+---------------+-------------------+---------+----------------------------+----------------------------+----------------------------+---------------
1 | 1484523 | Full | 10000 | 10000 | 10 | t | 2019-05-20 06:43:08.006384 | 2019-05-20 06:43:14.82531 | 2000-01-01 00:00:00 | t
1 | 1484523 | Skipped | 10000 | 0 | 10 | t | 2019-05-20 06:43:15.200541 | 2019-05-20 06:43:15.200903 | 2019-05-20 06:43:14.824429 | t
1 | 1484523 | Skipped | 10000 | 0 | 10 | t | 2019-05-20 06:43:15.336093 | 2019-05-20 06:43:15.336359 | 2019-05-20 06:43:14.824429 | t
(3 rows)

cmdb=# /*
cmdb*# stairelid: テーブル ID
cmdb*# stairows: テーブル内の合計行数
cmdb*# staiins: 最後の ANALYZE 以降に挿入された行数
cmdb*# staidels: 最後の ANALYZE 以降に削除または更新された行数
cmdb*# */
cmdb-# SELECT * FROM pg_statistic_indicator
cmdb-# WHERE stairelid = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' and "table" = 'lineorder'
cmdb(# );
stairelid | stairows | staiins | staidels
-----------+----------+---------+----------
1484523 | 10000 | 0 | 0
(1 row)

レコード追加した既存テーブルに対する Auto Analyze 検証

先程のテーブルに対して、更に10000レコード追加して、システムテーブル pg_statistic_indicator の staiins カラムは「10000」になりました。

cmdb=# -- データのロード
cmdb=# INSERT INTO public.lineorder
cmdb-# SELECT * FROM public.sady_lineorder LIMIT 10000;
INSERT 0 10000
cmdb=#
cmdb=# -- 作成した時間
cmdb=# SELECT sysdate;
timestamp
----------------------------
2019-05-20 07:34:04.631308
(1 row)

cmdb=# -- テーブルに対するANLYZEの実行状況を確認
cmdb=# SELECT userid, table_id, status, rows, modified_rows, threshold_percent, is_auto, starttime, endtime, prevtime, is_background
cmdb-# FROM stl_analyze
cmdb-# WHERE table_id = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' AND "table" = 'lineorder'
cmdb(# ) ORDER BY starttime;
userid | table_id | status | rows | modified_rows | threshold_percent | is_auto | starttime | endtime | prevtime | is_background
--------+----------+-----------------+-------+---------------+-------------------+---------+----------------------------+----------------------------+----------------------------+---------------
1 | 1484523 | Full | 10000 | 10000 | 10 | t | 2019-05-20 06:43:08.006384 | 2019-05-20 06:43:14.82531 | 2000-01-01 00:00:00 | t
1 | 1484523 | Skipped | 10000 | 0 | 10 | t | 2019-05-20 06:43:15.200541 | 2019-05-20 06:43:15.200903 | 2019-05-20 06:43:14.824429 | t
1 | 1484523 | Skipped | 10000 | 0 | 10 | t | 2019-05-20 06:43:15.336093 | 2019-05-20 06:43:15.336359 | 2019-05-20 06:43:14.824429 | t
(3 rows)

cmdb=# /*
cmdb*# stairelid: テーブル ID
cmdb*# stairows: テーブル内の合計行数
cmdb*# staiins: 最後の ANALYZE 以降に挿入された行数
cmdb*# staidels: 最後の ANALYZE 以降に削除または更新された行数
cmdb*# */
cmdb-# SELECT * FROM pg_statistic_indicator
cmdb-# WHERE stairelid = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' and "table" = 'lineorder'
cmdb(# );
stairelid | stairows | staiins | staidels
-----------+----------+---------+----------
1484523 | 20000 | 10000 | 0
(1 row)

下記の通り、30分後に確認すると、システムテーブル stl_analyze の最後の行の status カラムが 「Full」であることが確認できます。システムテーブル pg_statistic_indicator の staiins カラムは「10000」から「0」に変わり、ANALYZEが実行していることが確認できました。

cmdb=# -- テーブルに対するANLYZEの実行状況を確認
cmdb=# SELECT userid, table_id, status, rows, modified_rows, threshold_percent, is_auto, starttime, endtime, prevtime, is_background
cmdb-# FROM stl_analyze
cmdb-# WHERE table_id = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' AND "table" = 'lineorder'
cmdb(# ) ORDER BY starttime;
userid | table_id | status | rows | modified_rows | threshold_percent | is_auto | starttime | endtime | prevtime | is_background
--------+----------+-----------------+-------+---------------+-------------------+---------+----------------------------+----------------------------+----------------------------+---------------
1 | 1484523 | Full | 10000 | 10000 | 10 | t | 2019-05-20 06:43:08.006384 | 2019-05-20 06:43:14.82531 | 2000-01-01 00:00:00 | t
1 | 1484523 | Skipped | 10000 | 0 | 10 | t | 2019-05-20 06:43:15.200541 | 2019-05-20 06:43:15.200903 | 2019-05-20 06:43:14.824429 | t
1 | 1484523 | Skipped | 10000 | 0 | 10 | t | 2019-05-20 06:43:15.336093 | 2019-05-20 06:43:15.336359 | 2019-05-20 06:43:14.824429 | t
1 | 1484523 | Skipped | 20000 | 10000 | 10 | t | 2019-05-20 07:43:08.327161 | 2019-05-20 07:43:08.32758 | 2019-05-20 06:43:14.824429 | t
1 | 1484523 | Skipped | 20000 | 10000 | 10 | t | 2019-05-20 07:43:12.855413 | 2019-05-20 07:43:12.855833 | 2019-05-20 06:43:14.824429 | t
1 | 1484523 | Full | 20000 | 10000 | 10 | t | 2019-05-20 07:43:12.997927 | 2019-05-20 07:43:17.431165 | 2019-05-20 06:43:14.824429 | t
(6 rows)

cmdb=# /*
cmdb*# stairelid: テーブル ID
cmdb*# stairows: テーブル内の合計行数
cmdb*# staiins: 最後の ANALYZE 以降に挿入された行数
cmdb*# staidels: 最後の ANALYZE 以降に削除または更新された行数
cmdb*# */
cmdb-# SELECT * FROM pg_statistic_indicator
cmdb-# WHERE stairelid = (
cmdb(# SELECT table_id FROM svv_table_info
cmdb(# WHERE "schema" = 'public' and "table" = 'lineorder'
cmdb(# );
stairelid | stairows | staiins | staidels
-----------+----------+---------+----------
1484523 | 20000 | 0 | 0
(1 row)

最後に

Redshiftクラスタの負荷状況によりますが、Auto Analyzeは30分程度で自動実行することが確認できました。Auto Analyzeの実行の有無は、pg_statistic_indicator テーブルの staiins カラム もしくは staidels カラムが0になることで、実行されたと判断したことができます。ANALYZEコマンドを実行したときと同様に、analyze_threshold_percentのデフォルトは10%ですので、更新レコードが10%未満の場合は実行されません。stl_analyzeテーブルは、実行した時間を確認できますが、データの保存期間が数日であることから数日以降の動作確認には向いていませんのでご注意ください。

デフォルト設定では、Auto Analyzeが有効になっていますので、ANALYZEコマンドの実行は不要です。もし、任意のタイミングでANALYZEを実行したい場合は、パラメータauto_analyzeを変更することで自動実行をoffに設定してください。