Amazon Redshift DB開発者ガイド – データのロード処理(4).テーブル分析

Amazon Redshift DB開発者ガイド、データのロード処理編はまだもうちっとだけ続きます。Redshiftの重要な部分・機能だけあって割いてるボリュームも多いですね。当エントリは『テーブル分析』についてです。

目次

• テーブル分析
• ANALYZEコマンド履歴
• 新規テーブルの自動分析

テーブル分析

あなたは定期的にクエリプランナが最適な計画を構築し、選択する為に使用する統計的なメタデータを更新する必要があります。そのためには、テーブルを分析しましょう。

あなたは、ANALYZEコマンドを実行して明示的にテーブルを分析する事が出来ます。COPYコマンドを使用してデータをロードする時は、STATUPDATEをONに設定する事で自動的に分析を実行出来ます。デフォルトでは、空のテーブルにデータをロードした後にCOPYコマンドは分析を実行します。STATUPDATEをONに設定する事でテーブルが空かどうかに関わらず、分析を強制出来ます。そしてSTATUPDATEをOFFに設定した場合、分析は実行されません。

唯一のテーブル所有者またはスーパーユーザーはANALYZEコマンドを実行したり、STATUPDATEをONに設定してCOPYコマンドを実行する事が出来ます。

もし、データが最初にロードされた後、分析されていない新しいテーブルクエリを実行すると、警告メッセージが表示されます。しかし、その後に更新又はロードを行った後に照会を行うと、警告メッセージは表示されません。分析されていないテーブルを含むクエリにEXPLAINコマンドを実行した時と同じ挙動が発生します。

空でないテーブルに対してテーブルのサイズをかなり変えるようなデータを追加する時には、我々はANALYZEコマンドを実行するか、COPYコマンドでSTATUPDATEをONにして統計を更新する事をお勧めします。

パフォーマンスの低下は、データの統計プロファイルで非効率なデータストレージまたは重大な結果の変化を引き起こします。統計を構築または更新するには、これらの要素に対してANALYZEコマンドを実行します。

• 現在のデータベース全体
• 単一のテーブル
• 単一のテーブル内にある1つ以上の特定の列

ANALYZEコマンドは、テーブルから行のサンプルを取得し、幾つかの計算を行い、列の統計情報を結果として保存します。デフォルトでは、Amazon Redshiftは、DISTKEY列とテーブル内のその他全ての列に対し、サンプルパスを実行します。列のサブセットの統計情報を生成する場合は、カンマで区切られた列のリストを指定する事が出来ます。

ANALYZE操作はリソースを集め、テーブルや実際に統計の更新を必要とする列に実行を行います。定期的に全てのテーブル又は同じスケジュールで全ての列を解析する必要はありません。データが変更された場合は、実質的に、下記に於いて頻繁に使用される列を解析します。

• ソートとグループ化の操作
• 結合
• クエリ述語

頻繁な分析を必要とする可能性が低い列は、事実の表示や対策、また実際に照会される事の無い任意の関連属性(例えば大規模なVARCHAR列等)です。例えば、TICKITデータベース内のリスト表を考えてみます。

select "column", type, encoding, distkey, sortkey
from pg_table_def where tablename = 'listing';

column         |        type        | encoding | distkey | sortkey 
---------------+--------------------+----------+---------+---------
listid         | integer            | none     | t       | 1       
sellerid       | integer            | none     | f       | 0       
eventid        | integer            | mostly16 | f       | 0       
dateid         | smallint           | none     | f       | 0       
numtickets     | smallint           | mostly8  | f       | 0       
priceperticket | numeric(8,2)       | bytedict | f       | 0       
totalprice     | numeric(8,2)       | mostly32 | f       | 0       
listtime       | timestamp with...  | none     | f       | 0

もしテーブルが毎日、新しいレコードと一緒に多数ロードされるようであれば、頻繁に結合キーとして使用されるLISTID列等は定期的に分析する必要があるでしょう。TOTALPRICEとLISTTIME、クエリで頻繁に使用される制約がある場合は、平日にそれらの列と分散キーを解析できます。

analyze listing(listid, totalprice, listtime);

アプリケーションに於けるsellerとeventsがより静的であり、日付IDが2つ又は3つの年を固定的に参照している場合、一意の値のインスタンスの数は着実に増加しますが、列の一意の値の数は大幅に変更されません。また、NUMTICKETとPRICEPERTICKETがTOTALPRICE列に比べてまれに照会されるのであれば、日々分析されてない5つの列に対して毎週末に一度、統計を更新するテーブル全体に対しANALYZEコマンドを実行出来ます。

analyze listing;

テーブルの現在の統計を維持するには：

• クエリ実行を行う前にANALYZEコマンドを実行。
• 全ての定期的なロードや更新サイクルの終了時に、普段使っているデータベースに対してANALYZEコマンドを実行。
• 新たに作成したテーブル、または著しく変化をするような既存のテーブルまたは列に対してANALYZEコマンドを実行。
• 利用するクエリと変更の状況に応じて、テーブルと列の種類ごとに異なるスケジュールでANALYZE操作を実行する事を検討。

ANALYZEコマンド履歴

テーブルやデータベースに対して、一番最後にANALYZEコマンドが実行されたのは何時なのかを知っておくと便利です。ANALYZEコマンドを実行すると、Amazon Redshiftは以下のようにクエリを実行します。

redshift_fetch_sample: select * from table_name

ANALYZEコマンドが実行された時に調べるには、STL_QUERYやSVL_STATEMENTTETのようなシステムテーブルやビューを照会する事が出来、padb_fetch_sampleの制限等も含める事が出来ます。例えば、SALESテーブルが最後いつ分析されたのかを調べるには、以下のクエリを実行します。

select query, rtrim(querytxt), starttime 
from stl_query
where querytxt like 'padb_fetch_sample%' and querytxt like '%sales%'
order by query desc;

query |                     rtrim                      |       starttime
------+------------------------------------------------+----------------------
81 | redshift_fetch_sample: select * from sales        | 2012-04-18 12:...
80 | redshift_fetch_sample: select * from sales        | 2012-04-18 12:...
79 | redshift_fetch_sample: select count(*) from sales | 2012-04-18 12:...
(3 rows)

また、ANALYZEコマンドが含まれていた全ての完了トランザクションで実行した全てのステートメントを返し、より複雑なクエリを実行する事が出来ます。

select xid, to_char(starttime, 'HH24:MM:SS.MS') as starttime,
date_diff('sec',starttime,endtime ) as secs, substring(text, 1, 40)
from svl_statementtext
where sequence = 0
and xid in (select xid from svl_statementtext s where s.text like 'redshift_fetch_sample%' )
order by xid desc, starttime;

xid  |  starttime   | secs |                  substring
-----+--------------+------+------------------------------------------
1338 | 12:04:28.511 |    4 | Analyze date
1338 | 12:04:28.511 |    1 | redshift_fetch_sample: select count(*) from
1338 | 12:04:29.443 |    2 | redshift_fetch_sample: select * from date
1338 | 12:04:31.456 |    1 | redshift_fetch_sample: select * from date
1337 | 12:04:24.388 |    1 | redshift_fetch_sample: select count(*) from
1337 | 12:04:24.388 |    4 | Analyze sales
1337 | 12:04:25.322 |    2 | redshift_fetch_sample: select * from sales
1337 | 12:04:27.363 |    1 | redshift_fetch_sample: select * from sales
...

新規テーブルの自動分析

Amazon Redshiftは、以下のコマンドで作成したテーブルを自動分析します。

• CREATE TABLE AS (CTAS)
• CREATE TEMP TABLE AS
• SELECT INTO

テーブルが上記コマンドで最初に作成されている場合、これらのテーブルに対してANALYZEコマンドを実行する必要はありません。それらを変更する場合、他のテーブルと同じようにそれらを分析する必要があります。