ablog

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

Begin-End でトランザクションスコープに囲んだクエリが Scaling Cluster にルーティングされるか

AWS Redshift

Begin-End でトランザクションスコープに囲んだクエリが Redshift の Scaling Cluster にルーティングされるか検証してみた。

検証結果

Begin-End でトランザクションスコープを囲んだ DELETE/INSERT のみを実行している状態で、スケーリングクラスターでクエリが実行されている。

DELETE が Scaling Cluster(primary-scaled) で実行されている

select query_id, transaction_id, query_text, compute_type 
from sys_query_history 
where 
compute_type = 'primary-scaled' and
query_type = 'DELETE'
limit 5;

 query_id | transaction_id |                           query_text                            |  compute_type
----------+----------------+-----------------------------------------------------------------+----------------
 67158600 |      191297793 | delete from public.lineorder22 where lo_orderdate < '19940101'; | primary-scaled
 67158790 |      191298752 | delete from public.lineorder16 where lo_orderdate < '19940101'; | primary-scaled
 67158826 |      191298842 | delete from public.lineorder20 where lo_orderdate < '19940101'; | primary-scaled
 67159012 |      191299588 | delete from public.lineorder21 where lo_orderdate < '19940101'; | primary-scaled
 67159014 |      191299589 | delete from public.lineorder20 where lo_orderdate < '19940101'; | primary-scaled
(5 rows)

INSERT が Scaling Cluster(primary-scaled) で実行されている

select query_id, transaction_id, query_text, compute_type 
from sys_query_history 
where 
compute_type = 'primary-scaled' and
query_type = 'INSERT'
limit 5;

 query_id | transaction_id |                                             query_text                                              |  compute_type
----------+----------------+-----------------------------------------------------------------------------------------------------+----------------
 67158663 |      191297793 | insert into public.lineorder22 (select * from public.lineorder_bk where lo_orderdate < '19940101'); | primary-scaled
 67158666 |      191297795 | insert into public.lineorder25 (select * from public.lineorder_bk where lo_orderdate < '19940101'); | primary-scaled
 67158856 |      191298844 | insert into public.lineorder19 (select * from public.lineorder_bk where lo_orderdate < '19940101'); | primary-scaled
 67158853 |      191298845 | insert into public.lineorder25 (select * from public.lineorder_bk where lo_orderdate < '19940101'); | primary-scaled
 67159040 |      191299590 | insert into public.lineorder25 (select * from public.lineorder_bk where lo_orderdate < '19940101'); | primary-scaled
(5 rows)

検証手順

処理内容

lineorder_begin-end1.sql ... lineorder_begin-end20.sql

begin;
delete from public.lineorder1 where lo_orderdate < '19940101';
insert into public.lineorder1 (select * from public.lineorder_bk where lo_orderdate < '19940101');
end;

lineorder_begin-end.sh

#!/usr/bin/env bash

export LC_ALL=C
SCRIPT_BASE_NAME=$(basename $0 .sh)
CURRENT_DATE=`date '+%Y%m%d_%H%M%S'`
BASE_DIR=$(cd $(dirname $0);pwd)
LOG_DIR=${LOG_DIR:-log}

PG_HOST=${PG_HOST:-redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com}
PG_USER=${PG_USER:-awsuser}
PG_DB=${PG_DB:-dev}
PG_PORT=${PG_PORT:-5439}

# create log directory, if not exist.
if [ ! -d "${LOG_DIR}" ]
then
    mkdir ${LOG_DIR}
fi

cd $BASE_DIR

for i in {1..25}
do
        nohup pgbench -r -c 1 -j 1 -n -t 100 -f lineorder_begin-end${i}.sql -U ${PG_USER} -h ${PG_HOST} -d ${PG_DB} -p ${PG_PORT} >> "${LOG_DIR}/${SCRIPT_BASE_NAME}_${CURRENT_DATE}_${i}.log" 2>&1 &
done

検証実行手順

delete/insert を同時多重発行する。

$ ./lineorder_begin-end.sh

データ準備

データ範囲を調べる

select date_part(year, TO_date(lo_orderdate, 'YYYYMMDD')), count(*) 
from lineorder
group by date_part(year, TO_date(lo_orderdate, 'YYYYMMDD'));

 pgdate_part |   count
-------------+-----------
        1998 | 106736576
        1997 | 182101680
        1996 | 182603584
        1993 | 182014976
        1994 | 182088428
        1995 | 182032872
        1992 | 182497688
(7 rows)

バックアップテーブルを作成する

create table lineorder_bk as select * from lineorder;

以下の要領で、lineorder1 ... lineorder25 を作成する。

create table lineorder25 (like lineorder);

参考