この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第3弾です。課題#3の『ソートキーの恩恵を受けられないクエリ』について内容を見て行きたいと思います。
『課題 #3: ソートキーの恩恵を受けられないクエリ』を実践してみる
この項では、ソートキーの指定を行っていないテーブルを洗い出し、適切なソートキーを設定した方が良いよ、という内容について言及しています。適切なソートキーの選択については以下公式ドキュメントの内容をご参照ください。
また、少し前に新しいソートキーの指定方法として『Interleaved Sorting』というものが利用出来るようになっています。利用・活用シーンに合わせて適切な指定を行なうようにしてください。
- Amazon Web Services ブログ: 【AWS発表】Interleaved Sorting機能で Amazon Redshiftの効果的な検索フィルタリングが可能に
- 【新機能】Amazon Redshift の Interleaved Sorting機能を試してみた ｜ Developers.IO
- Amazon Redshift Interleaved Sortkey と VACUUM 実行のポイント ｜ Developers.IO
- Redshift の Interleaved Sort Key について解説してみる - Qiita
以下のSQLでは、利用回数が多い、且つソートキーの指定が無い(t.sortkey1がNULLである＝ソートキー指定されている項目(の1つ目)が存在しない、即ちソートキー指定がされていない)データについて、テーブル毎にその情報を表示しています。
# SELECT
database,
table_id,
schema || '.' || "table" AS "table",
size,
nvl(s.num_qs,0) num_qs
FROM
svv_table_info t
LEFT JOIN
(SELECT tbl, COUNT(distinct query) num_qs
FROM stl_scan s
WHERE s.userid > 1 AND s.perm_table_name NOT IN ('Internal Worktable','S3')
GROUP BY tbl
) s
ON s.tbl = t.table_id
WHERE t.sortkey1 IS NULL
ORDER BY 5 desc;
database | table_id | table | size | num_qs
-------------+----------+----------------------------------------+-------+--------
xxxxxxxxxxx | 280226 | zzzzzzz.m_user | 36 | 777
xxxxxxxxxxx | 313692 | aaaaaa.test1 | 15 | 555
xxxxxxxxxxx | 108359 | bbbbbbbbbbbb.xxxxxxxxxx_log | 14 | 123
xxxxxxxxxxx | 108977 | zzzzzzz.orders | 96 | 10
:
xxxxxxxxxxx | 253188 | public.test_table | 15 | 0
(xx rows)上記SQLで参照しているテーブルを個別に確認してみると、確かにsortkey1の値には何も入っていません。まずそもそもの『ソートキー』を設定する事から始めようよ、という事ですね。
# SELECT
*
FROM
svv_table_info
WHERE
schema = 'zzzzzzz'
AND svv_table_info.table = 'm_user';
database | schema | table_id | table | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows
-------------+---------+----------+--------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+-----------
xxxxxxxxxxx | zzzzzzz | 280226 | m_user | Y | EVEN | | 50 | | 0 | 36 | 0.0094 | 0 | | 100.00 | 145 | |
(1 row)件数をカウントしているテーブルはSTL_SCANというテーブルになります。こちらをテーブルID単独で指定して内容を見てみると、以下の様な内容でどの様なSQLがそのテーブルに対して発行されていたかの情報を確認する事が出来ます。
SELECT
s.tbl,
s.query,
TRIM(q.querytxt) AS querytxt
FROM
stl_scan s
INNER JOIN stl_query q ON (s.query = q.query)
WHERE
s.tbl = '280226'
tbl | query | querytxt
--------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
280226 | 603814 | fetch 10000 in "SQL_CUR0x7f89c431dfd0"
280226 | 608402 | SELECT COUNT(*) FROM zzzzzzz.m_user;
280226 | 601699 | fetch 10000 in "SQL_CUR0x7fede16297c0"
280226 | 601699 | fetch 10000 in "SQL_CUR0x7fede16297c0"
:
280226 | 604742 | fetch 10000 in "SQL_CUR0x7f8122fa11a0"
280226 | 608149 | SELECT (中略) FROM zzzzzzz.aaaaaaaaaa INNER JOIN zzzzzzz.m_user ON (後略);そして関連するGitHubに登録されている以下のSQL。テーブルに対してどの様なフィルタリング条件が設定されていたか等の情報を確認する事が出来ます。この辺りで得られた情報を元に、実際にどの様なソートキーを定めて行くかを決めて行く、という流れですね。
/**********************************************************************************************
Purpose: Return instances of table filter for all or a given table in the past 7 days
Columns:
table: Table Name
filter: Text of the filter from explain plan
secs: Number of seconds spend scaning the table
num: Number of times that filter occured
query: Latest query id of a query that used that filter on that table
Notes:
Use the perm_table_name fileter to narrow the results
History:
2015-02-09 ericfe created
2015-11-20 ericfe filter off nodeid 0 rows and non proper filter plan info
**********************************************************************************************/
select
trim(s.perm_Table_name) as table ,
substring(trim(info),1,580) as filter,
sum(datediff(seconds,starttime,case when starttime > endtime then starttime else endtime end)) as secs,
count(distinct i.query) as num,
max(i.query) as query
from
stl_explain p
join stl_plan_info i on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid )
join stl_scan s on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step)
where
s.starttime > dateadd(day, -7, current_Date)
and s.perm_table_name not like 'Internal Worktable%'
and p.info like 'Filter:%'
and p.nodeid > 0
and s.perm_table_name like '%m_user%' -- choose table(s) to look for
group by
1,2
order by
1, 3 desc , 4 desc;まとめ
以上、『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』トピック3つめ、"ソートキーの恩恵を受けられないクエリ"に関する対処方法のご紹介でした。4つ目以降のトピックについても、こんな感じで読み解きつつ実践して行きたいと思います。
24
