Redshift で VACUUM が Scaling Cluster にルーティングされるか

Redshift で VACUUM が Scaling Cluster にルーティングされるか検証してみた。
結果としては、Primary でしか実行されない。理由は concurrency_scaling_status = 3（Concurrency Scaling ineligible query - Query is an Unsupported DML）。

シナリオ

VACUUM を同時多重実行して、Scaling Cluster にルーティングされるか確認する。

lineorder_vacuum1.sql ... lineorder_vacuum25.sql

vacuum lineorder1;

...

vacuum lineorder25;

lineorder_vacuum.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_vacuum${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

lineorder_vacuum.sh を実行して VACUUM を 25 多重で実行する

$ ./lineorder_vacuum.sh

Scaling Cluster で VACUUM が実行されるか確認する

Primary でしか実行されない。理由は concurrency_scaling_status = 3（Concurrency Scaling ineligible query - Query is an Unsupported DML）

select a.compute_type, b.concurrency_scaling_status, substring(query_text, 0, 7), count(a.query_id)  
from sys_query_history a, stl_query b 
where 
a.transaction_id = b.xid
and a.query_type = 'UTILITY'
and a.query_text like '%vacuum%'
group by a.compute_type,  b.concurrency_scaling_status, substring(query_text, 0, 7);

 compute_type | concurrency_scaling_status | substring | count
--------------+----------------------------+-----------+-------
 primary      |                          3 | vacuum    |  2558
(1 row)

select a.query_id, a.transaction_id, a.query_type, a.query_text, a.compute_type, b.concurrency_scaling_status  
from sys_query_history a, stl_query b 
where 
a.transaction_id = b.xid
and a.query_type = 'UTILITY'
limit 5;

 query_id | transaction_id | query_type |     query_text     | compute_type | concurrency_scaling_status
----------+----------------+------------+--------------------+--------------+----------------------------
 68130845 |      194640401 | UTILITY    | vacuum lineorder9; | primary      |                          3
 68130845 |      194640401 | UTILITY    | vacuum lineorder9; | primary      |                          3
 68130845 |      194640401 | UTILITY    | vacuum lineorder9; | primary      |                          3
 68130845 |      194640401 | UTILITY    | vacuum lineorder9; | primary      |                          3
 68130845 |      194640401 | UTILITY    | vacuum lineorder9; | primary      |                          3
(5 rows)