Redshift に負荷をかける
EC2に git、psql、pgbench をインストールする
$ sudo yum -y install postgresql $ sudo yum -y install postgresql-contrib $ sudo yum -y install git
Redshift で負荷かけの準備を行う
- EC2 から Redshift にスーパーユーザーで接続する
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
- ベンチマーク用のグループとユーザーを作成する
- キューの同時実行数を超えると情報取得クエリもキューで待たされるため、負荷かけ実行ユーザーを別に作成してキューを分ける
# create group bench; # create user benchuser password '********' in group bench;
- ベンチマーク用ユーザーのリザルトキャッシュを無効化する
# ALTER USER benchuser SET enable_result_cache_for_session = off; # \q
- ベンチマーク用ユーザーでログインする
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=benchuser dbname=mydb 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, lo_suppkey INTEGER NOT NULL, lo_orderdate INTEGER NOT NULL, 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 lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' gzip compupdate off region 'us-west-2'; # \q
Redshift に pgbench で負荷をかける
- lineorder.sql を作成する
$ cat << EOF > lineorder.sql select count(a.*) from lineorder a, lineorder b; EOF
- pgbench で lineorder.sql を10並列で合計50回実行する
$ pgbench -r -c 10 -j 10 -n -t 50 -f lineorder.sql -U awsuser -h ds28xl4n.******.us-west-2.redshift.amazonaws.com -d mydb -p 5439
- running_queues.sql で実行中のクエリのPIDを確認して cancel すると実行中のクエリが終了して、キュー待ちしていたクエリが実行される様子を見る事ができる。
# cancel 44143;
性能情報の見え方
マネジメントコンソール
- マネジメントコンソールの「クエリ」タブにはキュー待ちのクエリは表示されない。
- マネジメントコンソールの「クエリ」タブの「実行時間」はキュー待ち時間を含む時間、「開始時刻」はクエリが発行された時刻(キュー待ちが終わって実行フェーズに変わった時刻ではなくクエリが発行された時刻)が表示される。
システムテーブル・システムビュー
- スーパーユーザーでログインする（全ユーザーの情報を見るため）
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
- running_queues.sql でキュー待ちと実行中のクエリを確認する
- state が Queue だとキュー待ち、Run だと実行中。q_sec がキュー待ち時間（秒）、exe_sec が実行時間（秒）。
- CPU時間（cpu_sec）が exe_sec より長いことがあるのはクエリがマルチプロセッサで並列実行された時間の合計のためと思われる。
- read_mb はディスク読込み量、spill_mb はディスク書込み量。
ds28xl4n-r awsuser 00:10 =# \i running_queues.sql user | pid | xid | query | q | slt | start | state | q_sec | exe_sec | cpu_sec | read_mb | spill_mb | ret_rows | nl_rows | sql | alert -----------+-------+--------+-------+---+-----+---------------------+-------+-------+---------+---------+---------+----------+----------+----------------+--------------------------------------------------------------------------------------------+------- benchuser | 34229 | 762470 | 89556 | 6 | 1 | 2018-07-16 21:39:43 | Run | 0 | 9074 | 267373 | 128 | | | 37471050468695 | select count(a.*) from lineorder a, lineorder b; | NL benchuser | 34230 | 762472 | 89557 | 6 | 1 | 2018-07-16 21:39:43 | Run | 0 | 9074 | 253425 | 128 | | | 35415740526019 | select count(a.*) from lineorder a, lineorder b; | NL benchuser | 34231 | 762474 | 89558 | 6 | 1 | 2018-07-16 21:39:43 | Run | 0 | 9074 | 274471 | 128 | | | 38407248173760 | select count(a.*) from lineorder a, lineorder b; | NL benchuser | 34228 | 762476 | 89559 | 6 | 1 | 2018-07-16 21:39:43 | Run | 0 | 9074 | 255883 | 128 | | | 35736062097462 | select count(a.*) from lineorder a, lineorder b; | NL benchuser | 34232 | 762478 | 89560 | 6 | 1 | 2018-07-16 21:39:43 | Run | 0 | 9074 | 250486 | 128 | | | 34941414838224 | select count(a.*) from lineorder a, lineorder b; | NL benchuser | 34233 | 762480 | 89561 | 6 | 1 | 2018-07-16 21:39:43 | Queue | 9074 | 0 | | | | | | select count(a.*) from lineorder a, lineorder b; | benchuser | 34235 | 762482 | 89562 | 6 | 1 | 2018-07-16 21:39:43 | Queue | 9074 | 0 | | | | | | select count(a.*) from lineorder a, lineorder b; | benchuser | 34234 | 762484 | 89563 | 6 | 1 | 2018-07-16 21:39:43 | Queue | 9074 | 0 | | | | | | select count(a.*) from lineorder a, lineorder b; | benchuser | 34236 | 762486 | 89564 | 6 | 1 | 2018-07-16 21:39:43 | Queue | 9074 | 0 | | | | | | select count(a.*) from lineorder a, lineorder b; | benchuser | 34237 | 762487 | 89565 | 6 | 1 | 2018-07-16 21:39:43 | Queue | 9074 | 0 | | | | | | select count(a.*) from lineorder a, lineorder b; | awsuser | 34063 | 765301 | 90186 | 7 | 1 | 2018-07-17 00:10:57 | Run | 0 | 0 | 0 | 0 | | | | /***************************************************************************************** | (11 rows) Time: 1024.246 ms
- lock_wait.sql でロック待ち状況を確認する
ds28xl4n-r awsuser 21:42 =# \i lock_wait.sql xid | pid | username | dbname | relation | schemaname | objectname | mode | granted | obj_type | txn_start | block_sec | block_min | block_hr | waiting | max_sec_blocking | num_blocking | pidlist --------+-------+-----------+--------+----------+------------+------------+-----------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+--------- 762484 | 34234 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.911033 | 197.83 | 3.3 | 0.05 | | | | 762478 | 34232 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.905794 | 197.83 | 3.3 | 0.05 | | | | 762482 | 34235 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.909771 | 197.83 | 3.3 | 0.05 | | | | 762474 | 34231 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.90356 | 197.83 | 3.3 | 0.05 | | | | 762476 | 34228 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.904795 | 197.83 | 3.3 | 0.05 | | | | 762486 | 34236 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.912798 | 197.83 | 3.3 | 0.05 | | | | 762480 | 34233 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.907442 | 197.83 | 3.3 | 0.05 | | | | 762487 | 34237 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.914389 | 197.83 | 3.3 | 0.05 | | | | 762470 | 34229 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.900309 | 197.83 | 3.3 | 0.05 | | | | 762472 | 34230 | benchuser | mydb | 153654 | public | lineorder | AccessShareLock | True | relation | 2018-07-16 21:39:43.90117 | 197.83 | 3.3 | 0.05 | | | | (10 rows) Time: 40773.823 ms
- top_queries.sql で実行時間の長いクエリを調べる
- クエリテキストで集計した実行時間の長いトップ50が表示される。
- n_qry が実行回数、min/max/avg が実行時間の最小/最大/平均で total が実行時間の合計。
- max_query_id がクエリIDが最大のもので、last_run がクエリが最後に実行された日付。
ds28xl4n-r awsuser 00:48 =# \i top_queries.sql db | n_qry | qrytext | min | max | avg | total | max_query_id | last_run | aborted | event ------+-------+----------------------------------------------------------------------------------+---------+---------+---------+---------+--------------+------------+---------+------------- mydb | 1 | select count(a.*) from lineorder a, lineorder b; | 1603.00 | 1603.00 | 1603.00 | 1603.00 | 80506 | 2018-07-16 | 1 | Nested Loop mydb | 1 | select * from STV_EXEC_STATE ORDER BY segment, step, slice; | 305.00 | 305.00 | 305.00 | 305.00 | 80640 | 2018-07-16 | 0 | mydb | 1 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' credentials '' gzi | 179.00 | 179.00 | 179.00 | 179.00 | 85025 | 2018-07-16 | 0 | mydb | 1 | /******************************************************************************* | 100.00 | 100.00 | 100.00 | 100.00 | 89635 | 2018-07-16 | 0 | Nested Loop mydb | 1 | select * from lineorder; | 53.00 | 53.00 | 53.00 | 53.00 | 89549 | 2018-07-16 | 1 | mydb | 1 | /******************************************************************************* | 50.00 | 50.00 | 50.00 | 50.00 | 89648 | 2018-07-16 | 0 | mydb | 2 | /******************************************************************************* | 2.00 | 46.00 | 24.00 | 48.00 | 90184 | 2018-07-17 | 0 | mydb | 1 | /******************************************************************************* | 41.00 | 41.00 | 41.00 | 41.00 | 89582 | 2018-07-16 | 0 | mydb | 5 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.s | 1.00 | 29.00 | 6.60 | 33.00 | 87441 | 2018-07-16 | 0 | mydb | 1 | /******************************************************************************* | 20.00 | 20.00 | 20.00 | 20.00 | 89626 | 2018-07-16 | 0 | mydb | 1 | /******************************************************************************* | 20.00 | 20.00 | 20.00 | 20.00 | 89610 | 2018-07-16 | 0 | mydb | 1 | select * from SVL_QUERY_QUEUE_INFO; | 17.00 | 17.00 | 17.00 | 17.00 | 85321 | 2018-07-16 | 0 | mydb | 1 | select * from SVL_QUERY_REPORT; | 17.00 | 17.00 | 17.00 | 17.00 | 82789 | 2018-07-16 | 1 | mydb | 1 | select trim(database) as DB, count(query) as n_qry, max(substring (qrytext,1,80) | 15.00 | 15.00 | 15.00 | 15.00 | 82847 | 2018-07-16 | 0 | mydb | 1 | select * from admin.v_check_wlm_query_time; | 12.00 | 12.00 | 12.00 | 12.00 | 85257 | 2018-07-16 | 0 | mydb | 2 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.s | 0.00 | 11.00 | 5.50 | 11.00 | 85266 | 2018-07-16 | 0 | mydb | 1 | select * from SVV_QUERY_STATE; | 11.00 | 11.00 | 11.00 | 11.00 | 82820 | 2018-07-16 | 0 | mydb | 1 | SELECT date_trunc('hour',a.service_class_start_time) AS hour, a.service_class, b | 10.00 | 10.00 | 10.00 | 10.00 | 89658 | 2018-07-16 | 0 | mydb | 6 | /******************************************************************************* | 1.00 | 1.00 | 1.00 | 6.00 | 90186 | 2018-07-17 | 0 | mydb | 1 | SELECT *, DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF FROM STL_QUERY WHERE QUERY | 6.00 | 6.00 | 6.00 | 6.00 | 82812 | 2018-07-16 | 0 | mydb | 1 | SELECT s.process AS pid ,date_Trunc ('second',s.starttime) AS S_START ,datediff( | 6.00 | 6.00 | 6.00 | 6.00 | 82831 | 2018-07-16 | 0 | mydb | 1 | /******************************************************************************* | 6.00 | 6.00 | 6.00 | 6.00 | 89606 | 2018-07-16 | 0 | mydb | 2 | select * from STV_EXEC_STATE ; | 0.00 | 5.00 | 2.50 | 5.00 | 82795 | 2018-07-16 | 0 | mydb | 1 | select * from stl_query; | 5.00 | 5.00 | 5.00 | 5.00 | 82816 | 2018-07-16 | 0 | mydb | 1 | padb_fetch_sample: select * from lineorder | 5.00 | 5.00 | 5.00 | 5.00 | 85039 | 2018-07-16 | 0 | Filter mydb | 50 | select count(1) from lineorder; | 0.00 | 2.00 | 0.08 | 4.00 | 80493 | 2018-07-16 | 0 | mydb | 2 | /* query showing queries which are waiting on a WLM Query Slot */ SELECT w.query | 0.00 | 1.00 | 0.50 | 1.00 | 90180 | 2018-07-17 | 0 | mydb | 1 | select * from STV_INFLIGHT; | 1.00 | 1.00 | 1.00 | 1.00 | 82788 | 2018-07-16 | 0 | mydb | 1 | SELECT TRIM(DATABASE) AS DB, w.query, SUBSTRING(q.querytxt,1,100) AS querytxt, w | 1.00 | 1.00 | 1.00 | 1.00 | 89655 | 2018-07-16 | 0 | mydb | 1 | /******************************************************************************* | 1.00 | 1.00 | 1.00 | 1.00 | 89597 | 2018-07-16 | 0 | mydb | 4 | select * from stv_wlm_query_state; | 0.00 | 1.00 | 0.25 | 1.00 | 87438 | 2018-07-16 | 0 | mydb | 1 | select count(*) from lineorder; | 0.00 | 0.00 | 0.00 | 0.00 | 89553 | 2018-07-16 | 0 | mydb | 1 | /* Query showing information about sessions with currently running queries */ SE | 0.00 | 0.00 | 0.00 | 0.00 | 89592 | 2018-07-16 | 0 | (33 rows) Time: 531.186 ms
- top_queries_and_cursors.sql で実行時間の長いクエリを調べる
- クエリテキストで集計した実行時間の長いトップ50が表示される。
- top_queries.sql と似ているが、CPU時間(cpu)、CPU使用率(cpupct)、ディスク読込み量(mb_read)、一時領域使用量(spill)など情報量が多い。
ds28xl4n-r awsuser 00:58 =# \i top_queries_and_cursors.sql db | n_qry | qrytext | max | avg | total | queue | cpu | cpupct | spill | mb_read | rows_ret | max_query | last_run | aborted | alert ------+-------+--------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------+-------+--------+-------+---------+----------+-----------+------------+---------+------- mydb | 1 | select count(a.*) from lineorder a, lineorder b; | 1603.00 | 1603.00 | 1603.00 | 6 | 64935 | 63.29 | | 128 | | 80506 | 2018-07-16 | 1 | NL mydb | 1 | select * from STV_EXEC_STATE ORDER BY segment, step, slice; | 305.00 | 305.00 | 305.00 | | | | | | | 80640 | 2018-07-16 | 0 | mydb | 1 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' credentials '' gzip compupdate off region 'us-west-2'; | 179.00 | 179.00 | 179.00 | 6 | 1809 | 14.42 | | 0 | | 85025 | 2018-07-16 | 0 | mydb | 1 | /********************************************************************************************** Purpose: Returns the hig | 100.00 | 100.00 | 100.00 | 7 | 122 | 7.24 | | 0 | | 89635 | 2018-07-16 | 0 | NL mydb | 1 | /********************************************************************************************** Purpose: Return Alerts f | 68.00 | 68.00 | 68.00 | 7 | 17 | 20.58 | | 0 | | 90369 | 2018-07-17 | 0 | NL mydb | 2 | /********************************************************************************************** WLM QMR Rule Candidates | 50.00 | 28.50 | 57.00 | 7 | 1 | 0.05 | | 0 | | 90365 | 2018-07-17 | 0 | mydb | 1 | select * from lineorder; | 53.00 | 53.00 | 53.00 | 7 | 56 | 1.74 | | 1850 | 23561221 | 89549 | 2018-07-16 | 1 | mydb | 2 | /********************************************************************************************** Purpose: Returns the per | 46.00 | 24.00 | 48.00 | 7 | | | | 0 | | 90184 | 2018-07-17 | 0 | mydb | 1 | /********************************************************************************************** Purpose: Return instance | 41.00 | 41.00 | 41.00 | 7 | | | | 0 | | 89582 | 2018-07-16 | 0 | mydb | 3 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.slot_count as slt, date_trunc('second',q. | 29.00 | 10.33 | 31.00 | 6 | 1 | 0.37 | | 0 | | 82846 | 2018-07-16 | 0 | mydb | 1 | /********************************************************************************************** Purpose: Return the top | 20.00 | 20.00 | 20.00 | 7 | | | | 0 | | 89626 | 2018-07-16 | 0 | mydb | 1 | /********************************************************************************************** Purpose: Return instance | 20.00 | 20.00 | 20.00 | 7 | 18 | 22.67 | | 0 | | 89610 | 2018-07-16 | 0 | mydb | 1 | select * from SVL_QUERY_QUEUE_INFO; | 17.00 | 17.00 | 17.00 | 7 | | | | 0 | | 85321 | 2018-07-16 | 0 | mydb | 1 | select * from SVL_QUERY_REPORT; | 17.00 | 17.00 | 17.00 | | | | | | | 82789 | 2018-07-16 | 1 | mydb | 1 | select trim(database) as DB, count(query) as n_qry, max(substring (qrytext,1,80)) as qrytext, min(run_seconds) as "min" | 15.00 | 15.00 | 15.00 | 6 | | | | 0 | | 82847 | 2018-07-16 | 0 | mydb | 1 | select * from admin.v_check_wlm_query_time; | 12.00 | 12.00 | 12.00 | | | | | | | 85257 | 2018-07-16 | 0 | mydb | 1 | select * from SVV_QUERY_STATE; | 11.00 | 11.00 | 11.00 | 6 | | | | 0 | | 82820 | 2018-07-16 | 0 | mydb | 1 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS s | 11.00 | 11.00 | 11.00 | 6 | | | | 0 | | 82837 | 2018-07-16 | 0 | mydb | 1 | SELECT date_trunc('hour',a.service_class_start_time) AS hour, a.service_class, b.condition AS service_class_condition, C | 10.00 | 10.00 | 10.00 | 7 | | | | | 202 | 89658 | 2018-07-16 | 0 | mydb | 1 | SELECT *, DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF FROM STL_QUERY WHERE QUERYTXT LIKE 'SELECT * FROM%' ORDER BY START | 6.00 | 6.00 | 6.00 | | | | | | | 82812 | 2018-07-16 | 0 | mydb | 6 | /********************************************************************************************** Purpose: Return the Curr | 1.00 | 1.00 | 6.00 | 7 | 1 | 0.41 | | 0 | | 90186 | 2018-07-17 | 0 | mydb | 1 | /********************************************************************************************** Purpose: Return commit q | 6.00 | 6.00 | 6.00 | 7 | | | | 0 | | 89606 | 2018-07-16 | 0 | mydb | 1 | SELECT s.process AS pid ,date_Trunc ('second',s.starttime) AS S_START ,datediff(minutes,s.starttime,getdate ()) AS conn_ | 6.00 | 6.00 | 6.00 | | | | | | | 82831 | 2018-07-16 | 0 | mydb | 2 | select * from STV_EXEC_STATE ; | 5.00 | 2.50 | 5.00 | | | | | | | 82795 | 2018-07-16 | 0 | mydb | 1 | padb_fetch_sample: select * from lineorder | 5.00 | 5.00 | 5.00 | 6 | 136 | 98.42 | | 25890 | | 85039 | 2018-07-16 | 0 | mydb | 1 | select * from stl_query; | 5.00 | 5.00 | 5.00 | | | | | | | 82816 | 2018-07-16 | 0 | mydb | 50 | select count(1) from lineorder; | 2.00 | 0.08 | 4.00 | 6 | 1 | 85.02 | | 20 | | 80493 | 2018-07-16 | 0 | mydb | 2 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.slot_count as slt, date_trunc('second',q. | 1.00 | 1.00 | 2.00 | 7 | | | | 0 | | 87441 | 2018-07-16 | 0 | mydb | 2 | /********************************************************************************************** Purpose: Return the top | 1.00 | 1.00 | 2.00 | 7 | | | | 0 | | 90337 | 2018-07-17 | 0 | mydb | 1 | SELECT TRIM(DATABASE) AS DB, w.query, SUBSTRING(q.querytxt,1,100) AS querytxt, w.queue_start_time, w.service_class AS cl | 1.00 | 1.00 | 1.00 | | | | | | | 89655 | 2018-07-16 | 0 | mydb | 1 | select * from STV_INFLIGHT; | 1.00 | 1.00 | 1.00 | | | | | | | 82788 | 2018-07-16 | 0 | mydb | 2 | /* query showing queries which are waiting on a WLM Query Slot */ SELECT w.query ,substring(q.querytxt,1,100) AS querytx | 1.00 | 0.50 | 1.00 | 7 | 1 | 0.23 | | 0 | | 90180 | 2018-07-17 | 0 | mydb | 4 | select * from stv_wlm_query_state; | 1.00 | 0.25 | 1.00 | | | | | | | 87438 | 2018-07-16 | 0 | mydb | 1 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS s | 0.00 | 0.00 | 0.00 | 7 | 1 | 1.70 | | 0 | | 85266 | 2018-07-16 | 0 | mydb | 1 | select count(*) from lineorder; | 0.00 | 0.00 | 0.00 | | | | | | | 89553 | 2018-07-16 | 0 | mydb | 1 | /* Query showing information about sessions with currently running queries */ SELECT s.process AS pid ,date_Trunc ('seco | 0.00 | 0.00 | 0.00 | | | | | | | 89592 | 2018-07-16 | 0 | (36 rows) Time: 1141.186 ms
- STL_WLM_QUERY で実行時間の長いクエリを調べる。
# select a.userid, a.query, a.total_queue_time, a.total_exec_time, a.exec_start_time, a.exec_end_time, substring(b.text,1,100) from stl_wlm_query a, stl_querytext b where a.query = b.query order by a.total_exec_time desc limit 10; userid | query | total_queue_time | total_exec_time | exec_start_time | exec_end_time | substring --------+-------+------------------+-----------------+----------------------------+----------------------------+------------------------------------------------------------------------------------------------------ 100 | 80506 | 0 | 1602831743 | 2018-07-16 18:51:06.926251 | 2018-07-16 19:17:49.757994 | select count(a.*) from lineorder a, lineorder b; 102 | 85025 | 0 | 179448651 | 2018-07-16 20:12:48.089068 | 2018-07-16 20:15:47.537719 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder'\ncredentials ''\ngzip compupdate off r 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | /**********************************************************************************************\nPur 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | results can be used to fine tune WLM queues which contain too many or too few slots\nresulting in W 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | in the WLM configuration file.\nmax_wlm_concurrency: Current actual concurrency level of the servic 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | time.\nmax_slots_ts: Most recent time at which the max_service_class_slots occurred.\nlast_queued_ti 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | Since generate_series is unsupported in Redshift, this uses an unelegant method to generate a dt\nse 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | .\n- If SVL_QUERY_REPORT has < 604800 rows you may want to substitue SVL_QUERY_REPORT for another ta 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | un after period of heaviest query activity\nHistory:\n2015-08-31 chriz-bigdata created\n************ 100 | 89635 | 0 | 99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | dt_series AS (select sysdate - (n * interval '1 second') as dt from (select row_number() over () as (10 rows) Time: 258.192 ms
- STL_WLM_QUERY でキュー待ち時間の長いクエリを調べる。
# select a.userid, a.query, a.total_queue_time, a.total_exec_time, a.queue_start_time, a.queue_end_time, substring(b.text,1,100) from stl_wlm_query a, stl_querytext b where a.query = b.query order by a.total_queue_time desc limit 10; userid | query | total_queue_time | total_exec_time | queue_start_time | queue_end_time | substring --------+-------+------------------+-----------------+----------------------------+----------------------------+------------------------------------------------------------------------------------------------------ 100 | 80640 | 304428952 | 0 | 2018-07-16 19:20:38.746379 | 2018-07-16 19:25:43.175331 | select * from STV_EXEC_STATE ORDER BY segment, step, slice; 1 | 79172 | 0 | 103599 | 2018-07-16 13:40:59.910911 | 2018-07-16 13:40:59.910911 | elect oid from pg_namespace where nspname='pg_internal')) 1 | 79212 | 0 | 97704 | 2018-07-16 13:51:59.913595 | 2018-07-16 13:51:59.913595 | select count(distinct id) from pg_catalog.stv_tbl_perm where temp = false and db_id > 1 and id not i 1 | 79220 | 0 | 17152 | 2018-07-16 13:53:00.02343 | 2018-07-16 13:53:00.02343 | select node_num,value from stv_fdisk_stats where name='blocks_allocated_total' 1 | 79307 | 0 | 20577 | 2018-07-16 14:16:39.938937 | 2018-07-16 14:16:39.938937 | select sum(value) from stv_fdisk_stats where name = 'blocks_allocated_permanent' 1 | 79267 | 0 | 96503 | 2018-07-16 14:05:59.917467 | 2018-07-16 14:05:59.917467 | elect oid from pg_namespace where nspname='pg_internal')) 1 | 79259 | 0 | 94293 | 2018-07-16 14:03:11.801154 | 2018-07-16 14:03:11.801154 | elect oid from pg_namespace where nspname='pg_internal')) 1 | 79314 | 0 | 6204 | 2018-07-16 14:17:48.023831 | 2018-07-16 14:17:48.023831 | Select * from pg_internal.redshift_auto_health_check_386769 1 | 79493 | 0 | 16171 | 2018-07-16 15:02:00.041758 | 2018-07-16 15:02:00.041758 | select node_num,value from stv_fdisk_stats where name='blocks_allocated_total' 1 | 79360 | 0 | 6354 | 2018-07-16 14:28:48.186254 | 2018-07-16 14:28:48.186254 | Select * from pg_internal.redshift_auto_health_check_386769 Time: 4216.293 ms
追記（2020/7/27）:
半永久的に実行し続ける bash スクリプト
#!/bin/bash export LANG=C export PGPASSWORD=******** ENDPOINT=redshift-cluster-1.******.us-east-1.redshift.amazonaws.com BASE_NAME=$(basename $0) STEM="${BASE_NAME%.*}" DATE=`date '+%Y-%m-%d-%H%M%S'` while : do pgbench -n -r -c 8 -j 8 -t 10 -f lineorder.sql -U awsuser -h ${ENDPOINT} -d dev -p 5439 >> ${STEM}-${DATE}.log 2>/dev/null done
