この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。三上です。
パンジーに裏表があることを、花屋さんに教わりました。(そういえば、みんな同じ方向向いてる@@
やりたいこと
- Redshiftのテーブル設計を学びたい!(チューニングもできるようになりたい
- AWS Solutions Architect ブログ Amazon Redshift テーブル設計詳細ガイド:Part 1 序文、事前準備、優先順位付けで紹介されてた、最適化が必要なテーブルを探す拡張view(v_extended_table_info)の中身を見てみたい!
やってみよう
viewをつくる
ちら見してみます。
CREATE OR REPLACE VIEW admin.v_extended_table_info AS
WITH tbl_ids AS
(
SELECT DISTINCT oid
FROM pg_class c
WHERE relowner > 1
AND relkind = 'r'
),
scan_alerts AS
(
(中略)
FROM svv_table_info ti
LEFT JOIN colenc ON colenc.attrelid = ti.table_id
LEFT JOIN stp ON stp.id = ti.table_id
LEFT JOIN tbl_scans ON tbl_scans.tbl = ti.table_id
LEFT JOIN rr_scans ON rr_scans.tbl = ti.table_id
LEFT JOIN pcon ON pcon.conrelid = ti.table_id
LEFT JOIN scan_alerts ON scan_alerts.table = ti.table_id
CROSS JOIN cluster_info
WHERE ti.SCHEMA NOT IN ('pg_internal')
ORDER BY ti.pct_used DESC;
→うーん(よく分かんない。。
とりま、DDL流してみます。。
まずは、adminスキーマを作って、
defaultdb=# CREATE SCHEMA admin;
CREATE SCHEMA
defaultdb=# ¥dn
List of schemas
name | owner
--------+-------
admin | root
public | rdsdb
(2 rows)
いただいてきたDDLをコピペ。
→拡張viewができました。
defaultdb=# ¥d admin.*
View "admin.v_extended_table_info"
Column | Type | Modifiers
-----------------------+-----------------------------+-----------
database | text |
table_id | oid |
tablename | text |
columns | text |
pk | text |
fk | text |
max_varchar | integer |
diststyle | text |
sortkey | text |
size | text |
tbl_rows | numeric(38,0) |
unsorted | numeric(5,2) |
stats_off | numeric(5,2) |
scans:rr:filt:sel:del | text |
last_scan | timestamp without time zone |
viewをひもとく
viewの各カラムの説明はコメントに記載していただいてますが、何をどう見れば・・・?(あせ
/**********************************************************************************************
Purpose: Return extended table information for permanent database tables.
Columns:
database: database name
table_id: table oid
tablename: Schema qualified table name
columns: encoded columns / unencoded columns
pk: Y if PK constraint exists, otherwise N
fk: Y if FK constraint exists, otherwise N
max_varchar: Size of the largest column that uses a VARCHAR data type.
diststyle: diststyle(distkey column): distribution skew ratio
sortkey: sortkey column(sortkey skew)
size: size in MB / minimum table size (percentage storage used)
tbl_rows: Total number of rows in the table
unsorted: Percent of rows in the unsorted space of the table
stats_off: Number that indicates how stale the table's statistics are; 0 is current, 100 is out of date.
scans:rr:filt:sel:del :
scans: number of scans against the table
rr: number of range restricted scans (scans which leverage the zone maps) against the table
filt: number of scans against the table which leveraged filter criteria
sel: number of scans against the table which triggered an alert for selective query filter
del: number of scans against the table which triggered an alert for scanning number of deleted rows
last_scan: last time the table was scanned
Notes:
History:
2016-09-12 chriz-bigdata created
**********************************************************************************************/
select句
それぞれのカラム値をどこから取ってきていて、どんな意味があるのか、みてみることにします。
- データベース名
- テーブルID
- テーブル名
- 最大カラムサイズ
- 分散スタイル
- ソートキー
- サイズ
を取ってきてます。
(前略)
SELECT ti.database,
ti.table_id,
ti.SCHEMA || '.' || ti."table" AS tablename,
colenc.encoded_cols || '/' || colenc.cols AS "columns",
pcon.pk,
pcon.fk,
ti.max_varchar,
CASE
WHEN ti.diststyle NOT IN ('EVEN','ALL') THEN ti.diststyle || ': ' || ti.skew_rows
ELSE ti.diststyle
END AS diststyle,
CASE
WHEN ti.sortkey1 IS NOT NULL AND ti.sortkey1_enc IS NOT NULL THEN ti.sortkey1 || '(' || nvl (skew_sortkey1,0) || ')'
WHEN ti.sortkey1 IS NOT NULL THEN ti.sortkey1
ELSE NULL
END AS "sortkey",
ti.size || '/' || CASE
WHEN stp.sum_r = stp.sum_sr OR stp.sum_sr = 0 THEN
CASE
WHEN "diststyle" = 'EVEN' THEN (stp.pop_slices*(colenc.cols + 3))
WHEN SUBSTRING("diststyle",1,3) = 'KEY' THEN (stp.pop_slices*(colenc.cols + 3))
WHEN "diststyle" = 'ALL' THEN (cluster_info.node_count*(colenc.cols + 3))
END
ELSE
CASE
WHEN "diststyle" = 'EVEN' THEN (stp.pop_slices*(colenc.cols + 3)*2)
WHEN SUBSTRING("diststyle",1,3) = 'KEY' THEN (stp.pop_slices*(colenc.cols + 3)*2)
WHEN "diststyle" = 'ALL' THEN (cluster_info.node_count*(colenc.cols + 3)*2)
END
END|| ' (' || ti.pct_used || ')' AS size,
ti.tbl_rows,
ti.unsorted,
ti.stats_off,
Nvl(tbl_scans.num_scans,0) || ':' || Nvl(rr_scans.rr_scans,0) || ':' || Nvl(rr_scans.filtered_scans,0) || ':' || Nvl(scan_alerts.selective_scans,0) || ':' || Nvl(scan_alerts.delrows_scans,0) AS "scans:rr:filt:sel:del",tbl_scans.last_scan
FROM svv_table_info ti
(後略)
他には、
pg_attribute(colenc)からエンコード情報、
(前略)
colenc AS
(
SELECT attrelid,
SUM(CASE WHEN a.attencodingtype = 0 THEN 0 ELSE 1 END) AS encoded_cols,
COUNT(*) AS cols
FROM pg_attribute a
WHERE a.attrelid IN (SELECT oid FROM tbl_ids)
AND a.attnum > 0
GROUP BY a.attrelid
),
(中略)
SELECT ti.database,
ti.table_id,
ti.SCHEMA || '.' || ti."table" AS tablename,
colenc.encoded_cols || '/' || colenc.cols AS "columns",
(後略)
pg_constraint (pcon)からテーブルの制約情報、
(前略)
pcon AS
(
SELECT conrelid,
CASE
WHEN SUM(
CASE
WHEN contype = 'p' THEN 1
ELSE 0
END
) > 0 THEN 'Y'
ELSE NULL
END pk,
CASE
WHEN SUM(
CASE
WHEN contype = 'f' THEN 1
ELSE 0
END
) > 0 THEN 'Y'
ELSE NULL
END fk
FROM pg_constraint
WHERE conrelid > 0
AND conrelid IN (SELECT oid FROM tbl_ids)
GROUP BY conrelid
),
(中略)
SELECT ti.database,
ti.table_id,
ti.SCHEMA || '.' || ti."table" AS tablename,
colenc.encoded_cols || '/' || colenc.cols AS "columns",
pcon.pk,
pcon.fk,
(後略)
stl_alert_event_log (scan_alerts)と stl_scan (tbl_scans, rr_scans)から、実行されたクエリの情報を取得しているようです。
(前略)
scan_alerts AS
(
SELECT s.tbl AS TABLE,
Nvl(SUM(CASE WHEN TRIM(SPLIT_PART(l.event,':',1)) = 'Very selective query filter' THEN 1 ELSE 0 END),0) AS selective_scans,
Nvl(SUM(CASE WHEN TRIM(SPLIT_PART(l.event,':',1)) = 'Scanned a large number of deleted rows' THEN 1 ELSE 0 END),0) AS delrows_scans
FROM stl_alert_event_log AS l
JOIN stl_scan AS s
ON s.query = l.query
AND s.slice = l.slice
AND s.segment = l.segment
AND s.step = l.step
WHERE l.userid > 1
AND s.slice = 0
AND s.tbl IN (SELECT oid FROM tbl_ids)
AND l.event_time >= Dateadd (DAY,-7,CURRENT_DATE)
AND TRIM(SPLIT_PART(l.event,':',1)) IN ('Very selective query filter','Scanned a large number of deleted rows')
GROUP BY 1
),
tbl_scans AS
(
SELECT tbl,
MAX(endtime) last_scan,
Nvl(COUNT(DISTINCT query || LPAD(segment,3,'0')),0) num_scans
FROM stl_scan s
WHERE s.userid > 1
AND s.tbl IN (SELECT oid FROM tbl_ids)
GROUP BY tbl
),
rr_scans AS
(
SELECT tbl,
NVL(SUM(CASE WHEN is_rrscan='t' THEN 1 ELSE 0 END),0) rr_scans,
NVL(SUM(CASE WHEN p.info like 'Filter:%' and p.nodeid > 0 THEN 1 ELSE 0 END),0) filtered_scans,
Nvl(COUNT(DISTINCT s.query || LPAD(s.segment,3,'0')),0) num_scans
FROM stl_scan s
JOIN stl_plan_info i on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step)
JOIN stl_explain p on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid )
WHERE s.userid > 1
AND s.type = 2
AND s.slice = 0
AND s.tbl IN (SELECT oid FROM tbl_ids)
GROUP BY tbl
),
(中略)
SELECT ti.database,
(中略)
Nvl(tbl_scans.num_scans,0) || ':' || Nvl(rr_scans.rr_scans,0) || ':' || Nvl(rr_scans.filtered_scans,0) || ':' || Nvl(scan_alerts.selective_scans,0) || ':' || Nvl(scan_alerts.delrows_scans,0) AS "scans:rr:filt:sel:del",tbl_scans.last_scan
(後略)
ちなみに、PostgreSQL共通のシステムカタログ(pg_*)、Redshiftでは微妙にテーブル定義違うのですね。。
pg_attributeテーブルのattencodingtypeカラム、PostgreSQLリファレンスには見当たりません。。
でもRedshiftでテーブル定義見てみたら、ちゃんといました。。
defaultdb=# ¥d pg_attribute
Table "pg_catalog.pg_attribute"
Column | Type | Modifiers
-----------------+----------+-----------
attrelid | oid | not null
attname | name | not null
atttypid | oid | not null
attstattarget | integer | not null
attlen | smallint | not null
attnum | smallint | not null
attndims | integer | not null
attcacheoff | integer | not null
atttypmod | integer | not null
attbyval | boolean | not null
attstorage | "char" | not null
attalign | "char" | not null
attnotnull | boolean | not null
atthasdef | boolean | not null
attisdropped | boolean | not null
attislocal | boolean | not null
attinhcount | integer | not null
attisdistkey | boolean | not null
attispreloaded | boolean | not null
attsortkeyord | integer | not null
attencodingtype | smallint | not null
attencrypttype | smallint | not null
Indexes:
"pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
"pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
wehere句
レコード抽出条件を見てみます。
(前略)
FROM svv_table_info ti
LEFT JOIN colenc ON colenc.attrelid = ti.table_id
LEFT JOIN stp ON stp.id = ti.table_id
LEFT JOIN tbl_scans ON tbl_scans.tbl = ti.table_id
LEFT JOIN rr_scans ON rr_scans.tbl = ti.table_id
LEFT JOIN pcon ON pcon.conrelid = ti.table_id
LEFT JOIN scan_alerts ON scan_alerts.table = ti.table_id
CROSS JOIN cluster_info
WHERE ti.SCHEMA NOT IN ('pg_internal')
ORDER BY ti.pct_used DESC;
→ユーザー定義テーブルを対象としている模様。。
viewにデータをいれる
作成したviewの中身を見てみます。
defaultdb=# select count(*) from admin.v_extended_table_info;
count
-------
0
(1 row)
→何も入ってません。。(まだテーブル作ってないので。。
抽出元の svv_table_info にも、
defaultdb=# select count(*) from svv_table_info;
count
-------
0
(1 row)
やはり何もありません。
→サンプルデータを用意します。
サンプルデータ
以下のcsvデータを使います。
- 全国の郵便番号データcsv : 日本郵便 郵便番号データダウンロード
- 全国の住所データcsv: 住所.jp 住所データCSV
テーブル作成&データロード
以下の手順で、Redshiftにデータを入れました。
1)csvファイル文字コード変換(sjis→utf8)
$ iconv -c -f SJIS -t UTF8 KEN_ALL.CSV > KEN_ALL_utf8.CSV
$ iconv -c -f SJIS -t UTF8 zenkoku.csv > zenkoku_utf8.csv
2)ファイルアップロード(local→S3)
$ aws --profile mikami s3 cp --recursive data s3://cm-mikami-test/
※ AWS CLIのconfigにprofile(mikami)を設定済み
※ コマンド実行時のカレントディレクトリ配下の data フォルダにアップロードするファイル(KEN_ALL_utf8.CSV, zenkoku_utf8.csv)を配置済み
3)データロード(S3→Redshift)
PGPASSWORD=[password] psql -f execute_sql.sql -h [End Point] -U [user] -d [dbname] -p 5439
execute_sql.sql
create table if not exists m_postal_code(
id varchar(255),
code_pre varchar(255),
code varchar(255),
pref_kana varchar(255),
city_kana varchar(255),
addr_kana varchar(255),
pref varchar(255),
city varchar(255),
addr varchar(255),
col1 varchar(255),
col2 varchar(255),
col3 varchar(255),
col4 varchar(255),
col5 varchar(255),
col6 varchar(255)
)
DISTSTYLE EVEN
SORTKEY (code);
create table m_addr(
addr_cd varchar(255),
pref_cd varchar(255),
city_cd varchar(255),
area_cd varchar(255),
postal_cd varchar(255),
office_cd varchar(255),
delete_flg varchar(255),
pref varchar(255),
pref_kana varchar(255),
city varchar(255),
city_kana varchar(255),
addr varchar(255),
addr_kana varchar(255),
addr_description varchar(255),
col1 varchar(255),
col2 varchar(255),
col3 varchar(255),
col4 varchar(255),
col5 varchar(255),
col6 varchar(255),
col7 varchar(255),
col8 varchar(255)
)
DISTSTYLE EVEN
SORTKEY (postal_cd);
copy m_postal_code from 's3://cm-mikami-test/KEN_ALL_utf8.CSV' CREDENTIALS 'aws_access_key_id=[access_key];aws_secret_access_key=[secret_key]' delimiter ',' REMOVEQUOTES TRUNCATECOLUMNS;
copy m_addr from 's3://cm-mikami-test/zenkoku_utf8.csv' CREDENTIALS 'aws_access_key_id=[access_key];aws_secret_access_key=[secret_key]' delimiter ',' REMOVEQUOTES TRUNCATECOLUMNS;
拡張viewを再確認
先ほど何も入っていなかった拡張view(v_extended_table_info)をもう一度確認してみます。
defaultdb=# select count(*) from admin.v_extended_table_info;
count
-------
2
(1 row)
defaultdb=# select * from admin.v_extended_table_info;
database | table_id | tablename | columns | pk | fk | max_varchar | diststyle | sortkey | size | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del | last_scan
-----------+----------+----------------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
defaultdb | 100189 | public.m_addr | 22/22 | | | 255 | EVEN | postal_cd(1.00) | 100/50 (0.0262) | 148848 | 0.00 | 0.00 | 26:0:0:0:0 | 2016-12-19 05:53:06.467259
defaultdb | 100187 | public.m_postal_code | 15/15 | | | 255 | EVEN | code(1.00) | 72/36 (0.0188) | 124056 | 0.00 | 0.00 | 19:0:0:0:0 | 2016-12-19 05:53:00.423304
(2 rows)
→テーブル情報が格納されました!
viewのデータを更新
作成したテーブル(m_addr, m_postal_code)に対して、適当に何本かクエリを投げた後、拡張viewを見てみると
defaultdb=# select * from admin.v_extended_table_info;
database | table_id | tablename | columns | pk | fk | max_varchar | diststyle | sortkey | size | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del | last_scan
-----------+----------+----------------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
defaultdb | 100189 | public.m_addr | 22/22 | | | 255 | EVEN | postal_cd(1.00) | 100/50 (0.0262) | 148848 | 0.00 | 0.00 | 35:4:5:1:0 | 2016-12-19 08:14:35.072146
defaultdb | 100187 | public.m_postal_code | 15/15 | | | 255 | EVEN | code(1.00) | 72/36 (0.0188) | 124056 | 0.00 | 0.00 | 21:2:2:0:0 | 2016-12-19 08:15:02.444509
(2 rows)
→scans:rr:filt:sel:del と last_scan のカラム値が更新されています。
scans:rr:filt:sel:del カラムが大事っぽい(?!
それぞれの数値の意味を見直してみます。
- scans:スキャン総数
- rr:範囲検索実行回数
- filt:フィルタを使った検索の実行回数(インデックスが効かなかった検索の実行回数?
- sel:select時にアラート(Very selective query filter)が発生した検索の実行回数(SORTキー設定を推奨
- del:delete時にアラート(Scanned a large number of deleted rows)が発生した検索の実行回数(VACUUMコマンド実行を推奨
viewをつかう
最適化の優先順位を決めるには・・・?
Amazon Redshift テーブル設計詳細ガイド:Part 1 序文、事前準備、優先順位付け
最適化をする対象の表の重要性を見積もるには「スキャン頻度」と「テーブルサイズ」が最も関連性が高い指標
だそうです。
拡張viewを使ったサンプルクエリを見てみます。
基本:最適化が必要なテーブルの優先度付け(特に遅い処理があるわけではない場合)
以下のクエリを実行します。
SELECT
*
FROM
admin.v_extended_table_info
WHERE
table_id IN (
SELECT
DISTINCT tbl
FROM
stl_scan
WHERE
type = 2
)
ORDER BY
SPLIT_PART("scans:rr:filt:sel:del", ':', 1):: int DESC,
size DESC;
実行結果
database | table_id | tablename | columns | pk | fk | max_varchar | diststyle | sortkey | size | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del | last_scan
-----------+----------+----------------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
defaultdb | 100189 | public.m_addr | 22/22 | | | 255 | EVEN | postal_cd(1.00) | 100/50 (0.0262) | 148848 | 0.00 | 0.00 | 35:4:5:1:0 | 2016-12-19 08:14:35.072146
defaultdb | 100187 | public.m_postal_code | 15/15 | | | 255 | EVEN | code(1.00) | 72/36 (0.0188) | 124056 | 0.00 | 0.00 | 21:2:2:0:0 | 2016-12-19 08:15:02.444509
(2 rows)
→ユーザー定義テーブルで、スキャン回数が多く、サイズが大きいテーブルから順に表示されました。
クエリ限定:遅いクエリが分かっている場合
ブログのサンプルクエリでは、クエリIDが指定されています。
クエリIDは実行環境依存なので、ID指定部を STL_SCAN から遅いクエリを取得するサブクエリに置き換えて実行してみました。
SELECT
*
FROM
admin.v_extended_table_info
WHERE
table_id IN (
SELECT
DISTINCT tbl
FROM
stl_scan
WHERE
type = 2
AND query = (
select
distinct query
from
stl_scan
where
(
tbl = 100187
or tbl = 100189
)
and (endtime - starttime > 1000000)
order by
(endtime - starttime) desc
limit
1
)
)
ORDER BY
SPLIT_PART("scans:rr:filt:sel:del", ':', 1):: int DESC,
size DESC;
実行結果
database | table_id | tablename | columns | pk | fk | max_varchar | diststyle | sortkey | size | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del | last_scan
-----------+----------+---------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
defaultdb | 100189 | public.m_addr | 22/22 | | | 255 | EVEN | postal_cd(1.00) | 100/50 (0.0262) | 148848 | 0.00 | 0.00 | 35:4:5:1:0 | 2016-12-19 08:14:35.072146
(1 row)
→指定クエリが実行されたテーブルが表示されました。
トランザクション限定:遅いトランザクションが分かっている場合
クエリ限定版と同様、トランザクションIDを STL_QUERY から取得した実行環境のIDと置き換えて実行してみます。
SELECT
*
FROM
admin.v_extended_table_info
WHERE
table_id IN (
SELECT
DISTINCT tbl
FROM
stl_scan
WHERE
type = 2
AND query IN (
SELECT
query
FROM
stl_query
WHERE
xid = 4333
)
)
ORDER BY
SPLIT_PART("scans:rr:filt:sel:del", ':', 1):: int DESC,
size DESC;
→該当トランザクション実行時のテーブルが表示されました。
※サンプルデータ(テーブル)少なすぎましたが。。(あせ
まとめ(分かったこと)
- 最適化が必要なテーブルの優先度付けに大切なのは
- スキャン頻度
- テーブルサイズ
- v_extended_table_infoのscans:rr:filt:sel:delカラムで、スキャン頻度やSORT_KEYの見直し、VACCUMEの必要性がわかる
- スキャン情報は STL_SCAN で確認できる
- トランザクション情報は STL_QUERY で確認できる
-
STL_ALERT_EVENT_LOG に、アラートが発生したクエリが記録される
- システムカタログはPostgreSQL標準よりもRedshiftオリジナル(STL_*, STV_*)を参照した方が良さげ