Redshift で COPY コマンド実行時に query_temp_blocks_to_disk が使われることがあることを確認した。
QMR(Query Monitoring Rule) で query_temp_blocks_to_disk > 1MB のクエリを中止するよう設定した上で、分散キー・ソートキーが指定されたテーブルに非圧縮で100GB超のファイルを COPY コマンドでロードしたところ、ディスクベースのソートが発生して、クエリが中止された。
- エラーメッセージ
dev# copy lineorder from 's3://awssampledb-yoheia/large_file/lineorder' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' gzip compupdate off region 'ap-northeast-1'; ERROR: Query (1267776) cancelled by WLM abort action of Query Monitoring Rule "temp_usage_default_queue". DETAIL: ----------------------------------------------- error: Query (1267776) cancelled by WLM abort action of Query Monitoring Rule "temp_usage_default_queue". code: 1078 context: Query (1267776) cancelled by WLM abort action of Query Monitoring Rule "temp_usage_default_queue". query: 0 location: wlm_query_action.cpp:156 process: wlm [pid=7108] -----------------------------------------------
- マネジメントコンソール-[Query history]
- workmem が使われ、is_diskbased が t(true) になっており、ディスクベースでソートが行われている。
select * from SVL_QUERY_REPORT where query = 1299500 order by segment, step, slice; userid | query | slice | segment | step | start_time | end_time | elapsed_time | rows | bytes | label | ★is_diskbased | ★workmem | is_rrscan | is_delayed_scan | rows_pre_filter --------+---------+-------+---------+------+----------------------------+----------------------------+--------------+------------+--------------+-----------------------------------------+--------------+------------+-----------+-----------------+----------------- （中略） 100 | 1299500 | 0 | 1 | 2 | 2022-07-14 21:55:44.624783 | 2022-07-14 22:32:17.525437 | 2192900654 | 150059982 | 14405750496 | ★sort tbl=998 | ★t | ★1670774784 | f | f | 0 100 | 1299500 | 1 | 1 | 2 | 2022-07-14 21:55:44.625378 | 2022-07-14 22:32:17.709882 | 2193084504 | 150118484 | 14411432312 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 2 | 1 | 2 | 2022-07-14 21:55:44.625435 | 2022-07-14 22:32:17.806525 | 2193181090 | 150476344 | 14445721336 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 3 | 1 | 2 | 2022-07-14 21:55:44.62553 | 2022-07-14 22:32:17.693182 | 2193067652 | 150130832 | 14412584320 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 4 | 1 | 2 | 2022-07-14 21:55:44.623351 | 2022-07-14 22:32:16.530293 | 2191906942 | 149603690 | 14361910096 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 5 | 1 | 2 | 2022-07-14 21:55:44.62382 | 2022-07-14 22:32:17.081277 | 2192457457 | 150354106 | 14433997256 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 6 | 1 | 2 | 2022-07-14 21:55:44.623856 | 2022-07-14 22:32:16.090342 | 2191466486 | 149263550 | 14329268424 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 7 | 1 | 2 | 2022-07-14 21:55:44.624086 | 2022-07-14 22:32:16.990926 | 2192366840 | 150068816 | 14406598992 | sort tbl=998 | t | 1670774784 | f | f | 0
準備
- QMR で 1MB 超の query_temp_blocks_to_disk を使用するとクエリが中止されるよう設定する。
- サンプルデータを取得して、1ファイルのサイズを大きくする。
$ aws s3 cp --recursive s3://awssampledb ./ $ cd ssbgz/ $ gunzip lineorder*.gz $ nohup cat lineorder000* > lineorder $ nohup cat lineorder000* >> lineorder $ nohup gzip lineorder
- 非圧縮で100GB超、圧縮済（gz）で45GBのファイルになる。
$ ls -lh lineorder* -rw-rw-r-- 1 ec2-user ec2-user 45G 7月 14 05:43 lineorder.gz -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0000_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0001_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0002_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0003_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0004_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0005_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0006_part_00 -rw-rw-r-- 1 ec2-user ec2-user 7.1G 9月 1 2021 lineorder0007_part_00
- Redshift に接続して、
$ psql "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"
- 分散キー・ソートキーが指定されたテーブルを作成して、
CREATE TABLE lineorder ( lo_orderkey integer not null, lo_linenumber integer not null, lo_custkey integer not null, lo_partkey integer not null distkey, lo_suppkey integer not null, lo_orderdate integer not null sortkey, lo_orderpriority varchar(15) not null, lo_shippriority varchar(1) not null, lo_quantity integer not null, lo_extendedprice integer not null, lo_ordertotalprice integer not null, lo_discount integer not null, lo_revenue integer not null, lo_supplycost integer not null, lo_tax integer not null, lo_commitdate integer not null, lo_shipmode varchar(10) not null );
検証
- COPY コマンドでロードする。
copy lineorder from 's3://awssampledb-yoheia/large_file/lineorder' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' gzip compupdate off region 'ap-northeast-1';
- workmem が使われ、is_diskbased が t(true) になっており、ディスクベースでソートが行われている。
select * from SVL_QUERY_REPORT where query = 1299500 order by segment, step, slice; userid | query | slice | segment | step | start_time | end_time | elapsed_time | rows | bytes | label | ★is_diskbased | ★workmem | is_rrscan | is_delayed_scan | rows_pre_filter --------+---------+-------+---------+------+----------------------------+----------------------------+--------------+------------+--------------+-----------------------------------------+--------------+------------+-----------+-----------------+----------------- （中略） 100 | 1299500 | 0 | 1 | 2 | 2022-07-14 21:55:44.624783 | 2022-07-14 22:32:17.525437 | 2192900654 | 150059982 | 14405750496 | sort tbl=998 | ★t | ★1670774784 | f | f | 0 100 | 1299500 | 1 | 1 | 2 | 2022-07-14 21:55:44.625378 | 2022-07-14 22:32:17.709882 | 2193084504 | 150118484 | 14411432312 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 2 | 1 | 2 | 2022-07-14 21:55:44.625435 | 2022-07-14 22:32:17.806525 | 2193181090 | 150476344 | 14445721336 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 3 | 1 | 2 | 2022-07-14 21:55:44.62553 | 2022-07-14 22:32:17.693182 | 2193067652 | 150130832 | 14412584320 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 4 | 1 | 2 | 2022-07-14 21:55:44.623351 | 2022-07-14 22:32:16.530293 | 2191906942 | 149603690 | 14361910096 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 5 | 1 | 2 | 2022-07-14 21:55:44.62382 | 2022-07-14 22:32:17.081277 | 2192457457 | 150354106 | 14433997256 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 6 | 1 | 2 | 2022-07-14 21:55:44.623856 | 2022-07-14 22:32:16.090342 | 2191466486 | 149263550 | 14329268424 | sort tbl=998 | t | 1670774784 | f | f | 0 100 | 1299500 | 7 | 1 | 2 | 2022-07-14 21:55:44.624086 | 2022-07-14 22:32:16.990926 | 2192366840 | 150068816 | 14406598992 | sort tbl=998 | t | 1670774784 | f | f | 0
- COPY コマンドはデフォルトキューで実行されている。
