デフォルトは自動WLM
-- 1. パラメータグループ作成
aws redshift create-cluster-parameter-group \
--parameter-group-name pg01 \
--parameter-group-family redshift-1.0 \
--description pg01
aws redshift describe-cluster-parameter-groups
vim a.json
[
{
"ParameterName": "wlm_json_configuration",
"ParameterValue": "[{\"user_group\":,\"query_group\":[\"test\"],\"name\":\"Queue1\",\"memory_percent_to_use\":30,\"query_concurrency\":2},{\"user_group\":[\"admin\"],\"query_group\":,\"name\":\"Queue2\",\"memory_percent_to_use\":30,\"query_concurrency\":2},{\"user_group\":,\"query_group\":,\"name\":\"Defaultqueue\",\"memory_percent_to_use\":30,\"query_concurrency\":2},{\"auto_wlm\":false},{\"short_query_queue\":true}]"
}
]
aws redshift modify-cluster-parameter-group \
--parameter-group-name pg01 \
--parameters file://a.json
-- 2. クラスター作成
aws redshift create-cluster \
--db-name test \
--cluster-identifier redshift01 \
--cluster-type single-node \
--node-type dc2.large \
--master-username test \
--master-user-password 'password' \
--no-allow-version-upgrade \
--no-publicly-accessible \
--automated-snapshot-retention-period 1 \
--cluster-parameter-group-name pg01
aws redshift describe-clusters
aws redshift describe-clusters --cluster-identifier redshift01
-- 3. クエリグループキューを使ってクエリを実行する
psql -h redshift01.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -p 5439 -d test -U test
set query_group to test;
select query, (service_class-5) as queue, slot_count, trim(wlm_start_time) as start_time, trim(state) as state
from stv_wlm_query_state;
select (config.service_class-5) as queue
, trim (class.condition) as description
, config.num_query_tasks as slots
, state.num_executed_queries executed
from
STV_WLM_CLASSIFICATION_CONFIG class,
STV_WLM_SERVICE_CLASS_CONFIG config,
STV_WLM_SERVICE_CLASS_STATE state
where
class.action_service_class = config.service_class
and class.action_service_class = state.service_class
and config.service_class > 4
order by config.service_class;
reset query_group;
-- 4. ユーザーグループキューを使ってクエリを実行する
create user adminuser createuser password 'password';
create group admin;
alter group admin add user adminuser;
set session authorization 'adminuser';
select query, (service_class-5) as queue, slot_count, trim(wlm_start_time) as start_time, trim(state) as state
from stv_wlm_query_state;
select (config.service_class-5) as queue
, trim (class.condition) as description
, config.num_query_tasks as slots
, state.num_executed_queries executed
from
STV_WLM_CLASSIFICATION_CONFIG class,
STV_WLM_SERVICE_CLASS_CONFIG config,
STV_WLM_SERVICE_CLASS_STATE state
where
class.action_service_class = config.service_class
and class.action_service_class = state.service_class
and config.service_class > 4
order by config.service_class;
set session authorization 'test';
-- 5. wlm_query_slot_count を使用して同時実行レベルを一時的に変更
show wlm_query_slot_count;
set wlm_query_slot_count to 2;
select query, (service_class-5) as queue, slot_count, trim(wlm_start_time) as start_time, trim(state) as state
from stv_wlm_query_state;
select (config.service_class-5) as queue
, trim (class.condition) as description
, config.num_query_tasks as slots
, state.num_executed_queries executed
from
STV_WLM_CLASSIFICATION_CONFIG class,
STV_WLM_SERVICE_CLASS_CONFIG config,
STV_WLM_SERVICE_CLASS_STATE state
where
class.action_service_class = config.service_class
and class.action_service_class = state.service_class
and config.service_class > 4
order by config.service_class;
reset wlm_query_slot_count;
-- 6. クリーンアップ
-- クラスター削除
aws redshift delete-cluster \
--cluster-identifier redshift01 \
--skip-final-cluster-snapshot
-- パラメータグループ削除
aws redshift delete-cluster-parameter-group \
--parameter-group-name pg01
aws redshift describe-cluster-parameter-groups
