ショートクエリアクセラレーション（SQA）とは
-
実行時間が短い一部のクエリを、実行時間が長いクエリよりも優先するように専用キューで実行する機能
- ロングクエリ実行中に、ショートクエリがブロックされるシチューエーションを回避出来る
-
デフォルトでは、WLM は、クラスターのワークロードの分析に基づいて、SQA 最大実行時間の値を動的に割り当て
- 特にユーザは意識する必要はない
実機確認
-
下記クエリで動的に割り当てられたショートクエリの実行時間がわかる
select least(greatest(percentile_cont(0.7) within group (order by total_exec_time / 1000000) + 2, 2), 20) from stl_wlm_query where userid >= 100 and final_state = 'Completed';mydb=# tpcds_100gb=# select least(greatest(percentile_cont(0.7) mydb(# tpcds_100gb(# within group (order by total_exec_time / 1000000) + 2, 2), 20) mydb(# tpcds_100gb-# from stl_wlm_query mydb(# tpcds_100gb-# where userid >= 100 mydb(# tpcds_100gb-# and final_state = 'Completed'; mydb(# least mydb(# ------- mydb(# 8.0 mydb(# (1 row) -
有効化確認
select * from stv_wlm_service_class_config where service_class = 14;mydb=# select * from stv_wlm_service_class_config mydb-# where service_class = 14; -[ RECORD 1 ]------------+----------------------------------------------------------------- service_class | 14 queueing_strategy | Predicted Time queue policy num_query_tasks | 6 target_num_query_tasks | 6 evictable | true eviction_threshold | 0 query_working_mem | 264 target_query_working_mem | 264 min_step_mem | 5 name | Short query queue max_execution_time | 0 user_group_wild_card | false query_group_wild_card | false concurrency_scaling | off query_priority | Normal -
各クエリキュー (サービスクラス) を通過したクエリ確認
select final_state, service_class, count(*), avg(total_exec_time), percentile_cont(0.9) within group (order by total_queue_time), avg(total_queue_time) from stl_wlm_query where userid >= 100 group by 1,2 order by 2,1;mydb=# select final_state, service_class, count(*), avg(total_exec_time), mydb-# percentile_cont(0.9) within group (order by total_queue_time), avg(total_queue_time) mydb-# from stl_wlm_query where userid >= 100 group by 1,2 order by 2,1; final_state | service_class | count | avg | percentile_cont | avg ------------------+---------------+-------+---------+-----------------+-------- Completed | 100 | 283 | 1789760 | 550958.6 | 421287 (1 row) -
SOAによって正常完了したクエリの情報
select a.queue_start_time, a.total_exec_time,trim(querytxt) from stl_wlm_query a, stl_query b where a.query = b.query and a.service_class = 14 and a.final_state = 'Completed' order by a.queue_start_time desc limit 1;mydb=# select a.queue_start_time, a.total_exec_time,trim(querytxt) mydb-# from stl_wlm_query a, stl_query b mydb-# where a.query = b.query and a.service_class = 14 and a.final_state = 'Completed' mydb-# order by a.queue_start_time desc limit 1; queue_start_time | total_exec_time | btrim 2021-06-11 12:38:23.935373 | 1983019 | with /* TPC-DS query27a.tpl 0.16 */ results as (select i_item_id, s_state, 0 as g_state, ss_quantity agg1, ss_list_price agg2, ss_coupon_amt agg3, ss_sales_price agg4 from store_sales, customer_demographics, date_dim, store, item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and ss_store_sk = s_store_sk and ss_cdemo_sk = cd_demo_sk and cd_gender = 'F' and cd_marital_status = 'S' and cd_education_status = 'Primary' and d_year = 1999 and s_state in ('FL','NC', 'MN', 'CO', 'MI', 'SC') ) select i_item_id, s_state, g_state, agg1, agg2, agg3, agg4 from ( select i_item_id, s_state, 0 as g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4 from results group by i_item_id, s_state union all select i_item_id, NULL AS s_state, 1 AS g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4 from results group by i_item_id union all select NULL AS i_item_id, NULL as s_state, 1 as g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4 from results ) fo o order by i_item_id, s_state limit 100; (1 row)