AWS Summit Tokyo 2023 大薗さんの「AWS でミッションクリティカルなデータウェアハウスを構築する方法 (AWS-35)」が再利用性が高いが、よくどこに資料があるか見失うので、メモしておく。
Level 300: 中級者向け
AWS でミッションクリティカルなデータウェアハウスを構築する方法 (AWS-35)昨今、データ活用の取り組みを進める企業や組織が増えるにしたがい、データウェアハウスにも高可用性、グローバル・レジリエンス、セキュリティガバナンスの堅牢性といった要素が求められるようになってきました。本セッションでは AWS のクラウドデータウェアハウスサービスである Amazon Redshift をミッションクリティカルなワークロードで構築するための最新の便利機能や特徴、ベストプラクティスについてご紹介します。
アマゾン ウェブ サービス ジャパン合同会社 データ事業本部 シニアソリューションアーキテクト 大薗 純平
re:Invent に参加されていた AWS エンジニアのみなさま、おつかれさまでしたm(._.)m
このエントリは AWS Analytics Advent Calendar 2024 の9日目の記事です。
8日目は 細かすぎて伝わらない Amazon Redshift のシステムテーブル／ビュー選手権: SYS_QUERY_HISTORY に user_query_hash と generic_query_hash が追加された - ablog でした（ワシやないかい！）。
細かすぎて伝わらない Amazon Redshift のシステムテーブル／ビュー選手権: SYS_QUERY_DETAIL に plan_node_id 列が追加された - ablog でシステムテーブル／ビューを使って、クエリのボトルネック分析でどのセグメント・ステップで時間を要しているか特定した後、実行計画のどのオペレーションと対応するかを解説しましたが、Query Profiler の登場でサクッと GUI でできるようになりました（先週、飲み会で大薗さんに教えてもらいましたw）！
ボトルネックの要点のみ解説しましたが、Query Profiler の画面への辿り着き方は [新機能] Amazon Redshift Query Profilerを試してみました | DevelopersIO、Amazon Redshift のクエリのコンパイルから実行までの流れや Stream って何という方は AWS Summit Tokyo 2023 で発表した "Amazon Redshift クエリパフォーマンスチューニング Deep Dive" のスライド - ablog をご覧ください。
10日目は @nogamincho さんです、いってらっしゃい！
このエントリは AWS Analytics Advent Calendar 2024 の8日目の記事です。
7日目は AWS Japan の Analytics Specialist SA ヨータ・ハマオカの AWS上でIcebergテーブルを作成する方法についての検討メモ #iceberg - Qiita でした。
SYS_QUERY_HISTORY に user_query_hash 列と generic_query_hash 列が追加されました。このエントリではこの追加された列をどう使うか具体例を紹介します。
クエリハッシュは、データウェアハウスで実行されたクエリに対して生成される一意のクエリ署名です。クエリハッシュを使用すると、一定期間にわたるクエリの傾向分析を実行したり、異なる期間のクエリパフォーマンスを比較したりして、クエリパフォーマンスを調査できます。この機能により、SYS_QUERY_HISTORY ビューに、クエリリテラルを含むハッシュである user_query_hash と、クエリリテラルを含まないハッシュである generic_query_hash という 2 つの新しい列が追加されました。
Amazon Redshift がクエリパフォーマンスのモニタリングを改善するためのクエリ識別子を導入 - AWS
Oracle Database でいう SQL_ID ですね。user_query_hash は where 句などのリテラルが異なれば別物、generic_query_hash はリテラル値は異なってもクエリ文が同じであれば同一値になります、Oracle Database でいうとバインド変数を使った場合の SQL_ID みたいなイメージですね。クエリリテラルを含まない generic_query_hash を生成してくれるのは非常に便利ですね。
例えば、generic_query_hash を使って、平均と合計の elapsed_time が長い上位クエリをピックアップすることができます。
select generic_query_hash, count(generic_query_hash) cnt, min(query_type) query_type, avg(elapsed_time)/1000/1000 "avg_elapsed_time(s)", avg(execution_time)/1000/1000 "avg_execution_time(s)", avg(queue_time)/1000 "avg_queue_time(ms)", avg(compile_time)/1000 "avg_compile_time(ms)", avg(planning_time)/1000 "avg_planning_time(ms)", avg(lock_wait_time)/1000 "avg_lock_wait_time(ms)" from sys_query_history group by generic_query_hash order by avg(elapsed_time) desc limit 10;
generic_query_hash | cnt | query_type | avg_elapsed_time(s) | avg_execution_time(s) | avg_queue_time(ms) | avg_compile_time(ms) | avg_planning_time(ms) | avg_lock_wait_time(ms) ------------------------------------------+-----+------------+---------------------+-----------------------+--------------------+----------------------+-----------------------+------------------------ YyRSNHHwZ0Y= | 1 | SELECT | 146 | 127 | 167 | 19725 | 26 | 0 yIMt4+EVnu4= | 1 | SELECT | 142 | 133 | 0 | 9827 | 25 | 0 EfHHVqvIamU= | 1 | SELECT | 141 | 132 | 0 | 8483 | 26 | 0 pDVobZbCr3I= | 1 | SELECT | 139 | 131 | 200 | 9917 | 25 | 0 EgrXtVMUTcg= | 1 | SELECT | 136 | 0 | 135762 | 1 | 170 | 0 sG6Fzj+JOo0= | 1 | SELECT | 132 | 132 | 0 | 0 | 25 | 0 AjKWNCQ0bk4= | 1 | SELECT | 132 | 132 | 0 | 0 | 27 | 0 6K5zz0x21hA= | 1 | SELECT | 132 | 132 | 95 | 0 | 25 | 0 68oFgnOWby0= | 8 | SELECT | 128 | 128 | 201 | 434 | 16 | 0 rxGgdkiTvbQ= | 1 | SELECT | 127 | 127 | 0 | 0 | 24 | 0 (10 rows)
select generic_query_hash, count(generic_query_hash) cnt, min(query_type) query_type, sum(elapsed_time)/1000/1000 "sum_elapsed_time(s)", avg(elapsed_time)/1000/1000 "avg_elapsed_time(s)", avg(execution_time)/1000/1000 "avg_execution_time(s)", avg(queue_time)/1000 "avg_queue_time(ms)", avg(compile_time)/1000 "avg_compile_time(ms)", avg(planning_time)/1000 "avg_planning_time(ms)", avg(lock_wait_time)/1000 "avg_lock_wait_time(ms)" from SYS_QUERY_HISTORY group by generic_query_hash order by sum(elapsed_time) desc limit 10;
generic_query_hash | cnt | query_type | sum_elapsed_time(s) | avg_elapsed_time(s) | avg_execution_time(s) | avg_queue_time(ms) | avg_compile_time(ms) | avg_planning_time(ms) | avg_lock_wait_time(ms) ------------------------------------------+-----+------------+---------------------+---------------------+-----------------------+--------------------+----------------------+-----------------------+------------------------ 68oFgnOWby0= | 8 | SELECT | 1030 | 128 | 128 | 201 | 434 | 16 | 0 9uU/vUfAYWQ= | 8 | SELECT | 849 | 106 | 103 | 128 | 2099 | 104451 | 0 FvVb53c411c= | 9 | SELECT | 651 | 72 | 72 | 0 | 0 | 10 | 0 Qp1LFBVz0ag= | 8 | SELECT | 638 | 79 | 78 | 0 | 1669 | 21 | 0 YP3i/XrP+1g= | 9 | SELECT | 533 | 59 | 57 | 0 | 2018 | 22 | 0 k+7kl7fqUtA= | 9 | SELECT | 366 | 40 | 35 | 0 | 5124 | 20 | 0 E7UqcVBHI64= | 8 | SELECT | 353 | 44 | 43 | 67 | 1042 | 18 | 0 mMl0zjIizfo= | 8 | SELECT | 333 | 41 | 39 | 48 | 1994 | 14 | 0 DWUnAfgTCKY= | 4 | SELECT | 330 | 82 | 80 | 1372 | 771 | 13 | 0 A7lpACU8NBc= | 4 | SELECT | 321 | 80 | 80 | 0 | 0 | 13 | 0 (10 rows)
elapsed_time で集計（GROUP BY）していますが、execution_time など他の列で集計すれば別の軸でのランキングを出すことが可能です。
9日目は...
AWS エンジニアの皆様、AWS サービスアップデートのキャッチアップおつかれさまですm(._.)m
このエントリは AWS Analytics Advent Calendar 2024 の4日目の記事です。
3日目は 大薗さんの状況に合わせて、とても素晴らしい食事プランを提案してくれる 有益なエントリでした！
AWS Summit Tokyo 2023 で "Amazon Redshift クエリパフォーマンスチューニング Deep Dive" というタイトルで、Amazon Redshift でシステムテーブル／ビューを使ってシステマチックにクエリのボトルネックを特定・チューニングする手法を発表しましたが、最近のすばらしいアップデートを共有します。
SYS_QUERY_DETAIL に plan_node_id 列が追加され、これまで クエリのボトルネック分析でどのセグメント・ステップで時間を要しているか特定した後、実行計画のどのオペレーションと対応するかはオペレーション名で紐づける必要がありましたが、SYS_QUERY_DETAIL に plan_node_id 列が追加され機械的に実行計画 SYS_QUERY_EXPLAIN の plan_node_id 列と結合してマッピングできるようになりました！
実行計画のオペレーション名で紐付けしないといけなかったのですが、
SYS_QUERY_DETAIL と SYS_QUERY_EXPLAIN を plan_node_id で機械的に結合できます。
tpch_100gb awsuser 20241204_15:27:57 =# select a.query_id, a.stream_id, a.segment_id, a.step_id, a.step_name, a.duration, a.input_bytes, a.output_bytes, a.input_rows, a.output_rows, b.plan_parent_id, substring(b.plan_node,1,100) plan_node from sys_query_detail a, sys_query_explain b where a.query_id = 3178 and a.query_id = b.query_id and b.plan_node_id = a.plan_node_id order by a.query_id, a.stream_id, a.segment_id, a.step_id; query_id | stream_id | segment_id | step_id | step_name | duration | input_bytes | output_bytes | input_rows | output_rows | plan_parent_id | plan_node ----------+-----------+------------+---------+------------+----------+-------------+--------------+------------+-------------+----------------+----------------------------------------------------------------------------------------------- 3178 | 0 | 0 | 0 | scan | 1612796 | 0 | 269433360 | 10745485 | 7484260 | 5 | -> XN Seq Scan on lineitem (cost=0.00..120535.76 rows=8035718 width=40) 3178 | 0 | 0 | 2 | distribute | 1612796 | 0 | 179622240 | 0 | 7484260 | 2 | -> XN Hash Join DS_DIST_INNER (cost=172290.28..9643380731.17 rows=8035718 width=56) 3178 | 0 | 1 | 0 | scan | 1627169 | 0 | 179622240 | 0 | 7484260 | 2 | -> XN Hash Join DS_DIST_INNER (cost=172290.28..9643380731.17 rows=8035718 width=56) 3178 | 0 | 1 | 2 | hash | 1627169 | 0 | 239496320 | 0 | 7484260 | 3 | -> XN Hash (cost=120535.76..120535.76 rows=8035718 width=40) 3178 | 1 | 2 | 0 | scan | 939133 | 0 | 753563993 | 20000000 | 18560685 | 3 | -> XN Seq Scan on part (cost=0.00..200000.00 rows=20000000 width=32) 3178 | 1 | 2 | 3 | hashjoin | 939133 | 0 | 0 | 0 | 7484260 | 2 | -> XN Hash Join DS_DIST_INNER (cost=172290.28..9643380731.17 rows=8035718 width=56) 3178 | 1 | 2 | 6 | aggregate | 939133 | 0 | 768 | 0 | 16 | 1 | -> XN Aggregate (cost=9643420909.76..9643420909.78 rows=1 width=56) 3178 | 2 | 3 | 0 | scan | 2445 | 0 | 768 | 0 | 16 | 1 | -> XN Aggregate (cost=9643420909.76..9643420909.78 rows=1 width=56) 3178 | 2 | 3 | 1 | return | 2445 | 0 | 768 | 0 | 16 | 1 | -> XN Aggregate (cost=9643420909.76..9643420909.78 rows=1 width=56) 3178 | 2 | 4 | 0 | scan | 1577 | 0 | 768 | 0 | 16 | 1 | -> XN Aggregate (cost=9643420909.76..9643420909.78 rows=1 width=56) 3178 | 2 | 4 | 1 | aggregate | 1577 | 0 | 48 | 0 | 1 | 1 | -> XN Aggregate (cost=9643420909.76..9643420909.78 rows=1 width=56) 3178 | 3 | 5 | 0 | scan | 191 | 0 | 48 | 0 | 1 | 1 | -> XN Aggregate (cost=9643420909.76..9643420909.78 rows=1 width=56) (12 rows) Time: 9170.031 ms (00:09.170)
5日目は @tdmnishi さんの Amazon DataZone 経由で SageMaker Canvas へデータインポート #AWS - Qiita です、いってらっしゃい！
「アドベントカレンダー」（Advent Calendar）とは、クリスマスまでの期間（待降節＝アドベント）をより楽しく過ごすため、12月1日から24日までの間カウントダウンしていく“日めくりカレンダー”のことです。
(中略)
IT業界では、このアドベントカレンダーの風習に習って、12月1〜24日の間、何かのテーマや、何らかの制限事項（縛り）を設けてWebにコラム記事を書くというイベントを楽しむようになりました（なかには25日や年末まで続けるものもあるようです）。師走を楽しもう。技術系アドベントカレンダーの魅力とは：安藤幸央のランダウン（59） - ＠IT
師走を楽しもう。技術系アドベントカレンダーの魅力とは：安藤幸央のランダウン（59） - ＠IT
"ls -1|sort -V" で OK。
$ ls -1|sort|head -10 orders.tbl.1 orders.tbl.10 orders.tbl.100 orders.tbl.101 orders.tbl.102 orders.tbl.103 orders.tbl.104 orders.tbl.105 orders.tbl.106 orders.tbl.107
$ ls -1|sort -V|head -10 orders.tbl.1 orders.tbl.2 orders.tbl.3 orders.tbl.4 orders.tbl.5 orders.tbl.6 orders.tbl.7 orders.tbl.8 orders.tbl.9 orders.tbl.10
EC2（c5.4xlarge/Amazon Linux 2023）に EBS ボリューム（st1）をアタッチして xfs でフォーマットしてマウントした手順。
$ lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINTS nvme0n1 259:0 0 50G 0 disk ├─nvme0n1p1 259:2 0 50G 0 part / ├─nvme0n1p127 259:3 0 1M 0 part └─nvme0n1p128 259:4 0 10M 0 part /boot/efi nvme1n1 259:1 0 10T 0 disk [ec2-user@ip-10-0-12-234 ~]$ sudo lsblk -f NAME FSTYPE FSVER LABEL UUID FSAVAIL FSUSE% MOUNTPOINTS nvme0n1 ├─nvme0n1p1 xfs / af805cc0-8447-4b55-8c57-ea294e4bea9c 47.6G 5% / ├─nvme0n1p127 └─nvme0n1p128 vfat FAT16 94FC-EE88 8.7M 13% /boot/efi nvme1n1
$ sudo /sbin/ebsnvme-id /dev/nvme1n1 Volume ID: vol-0ad46191e2316f4df sdb
$ sudo mkfs -t xfs /dev/sdb meta-data=/dev/sdb isize=512 agcount=32, agsize=83886080 blks = sectsz=512 attr=2, projid32bit=1 = crc=1 finobt=1, sparse=1, rmapbt=0 = reflink=1 bigtime=1 inobtcount=1 data = bsize=4096 blocks=2684354560, imaxpct=5 = sunit=1 swidth=1 blks naming =version 2 bsize=4096 ascii-ci=0, ftype=1 log =internal log bsize=4096 blocks=521728, version=2 = sectsz=512 sunit=1 blks, lazy-count=1 realtime =none extsz=4096 blocks=0, rtextents=0
$ sudo mkdir /data
$ sudo mount /dev/sdb /data
$ mount ... /dev/nvme1n1 on /data type xfs (rw,relatime,seclabel,attr2,inode64,logbufs=8,logbsize=32k,sunit=8,swidth=8,noquota)
$ sudo cp /etc/fstab /etc/fstab.orig
$ sudo blkid ... /dev/nvme1n1: UUID="c36a424f-a3f2-4447-a1cc-41f11c7f310d" BLOCK_SIZE="512" TYPE="xfs"
$ sudo vim /etc/fstab ... UUID=c36a424f-a3f2-4447-a1cc-41f11c7f310d /data xfs defaults,nofail 0 2
