ablog

不器用で落着きのない技術者のメモ

Redshift で COPY コマンド実行時の query_temp_blocks_to_disk を調べる

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  714 05:43 lineorder.gz
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0000_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0001_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0002_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0003_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0004_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0005_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  2021 lineorder0006_part_00
-rw-rw-r-- 1 ec2-user ec2-user 7.1G  91  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 コマンドはデフォルトキューで実行されている。