Redshift でロックを保持しているセッションとロック待ちしているセッションは lock_wait.sql で確認すると便利。マネジメントコンソールで Redshift の「クエリ」タブではロックを保持しているクエリは表示されるがロック待ちしているクエリは表示されない。
準備
- テーブルを作成してレコードを insert する。
CREATE TABLE testtab1(id int primary key, name varchar(32)); INSERT INTO testtab1 (id, name) VALUES(1, 'foo'); INSERT INTO testtab1 (id, name) VALUES(2, 'bar');
検証
- セッション1でトランザクションを実行する（コミットしない）。
BEGIN; UPDATE testtab1 SET name='fff' WHERE id=1;
- セッション2でトランザクションを実行する。
UPDATE testtab1 SET name='bbb' WHERE id=2;
確認
- ロックを保持しているセッションと待たされているセッションを確認する。
- pid: 75517 は granted が True なのでロックを保持していて、pid:79568 は False なので待たされている。
- 一行目の pid: 75517 は num_blocking から1セッションを待たせていて、pidlist から pid:79568 を待たされていることが分かる。
- 三行目の pid:79568 は waiting から 2239秒（37分）待たされていることが分かる。
# \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 --------+-------+----------+--------+----------+------------+------------+-----------------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+--------- 863579 | 75517 | awsuser | mydb | 170038 | public | testtab1 | ShareRowExclusiveLock | True | relation | 2018-07-21 14:17:07.367098 | 2307.24 | 38.45 | 0.64 | | 2239.24 | 1 | 79568★待たされているセッションの pid 863579 | 75517 | awsuser | mydb | 170038 | public | testtab1 | AccessShareLock | True | relation | 2018-07-21 14:17:07.367098 | 2307.24 | 38.45 | 0.64 | | | | 863608 | 79568 | awsuser | mydb | 170038 | public | testtab1 | ShareRowExclusiveLock | False | relation | 2018-07-21 14:18:11.388639 | | | | 2239.24★待たされている秒数 | | | (3 rows)
- lock_wait.sql の各列の意味は以下の通り。
/********************************************************************************************** Purpose: Return instances of table filter for all or a given table in the past 7 days Columns: pid: Process/Session Id username: User name dbname: Database relation: Object id schemaname: Schema objectname: Object Name mode: Lock Mode (AcessShareLock, AccessExclusiveLock, etc) granted: Granted (True or False) obj_type: Type of Object txn_start: Start Time of the transaction that asked for the lock block_sec: Seconds Holding the Lock block_min: Minutes Holding the lock block_hr: Hours Holding the lock waiting: Seconds waiting for the lock max_sec_blocking: Peak of seconds blocking other sessions num_blocking: Number of sessions blocked by this lock pidlist: List of Sessions being blocked by this lock
- マネジメントコンソールにはロック待ちしているセッションは表示されない。
参考
2023/4/20 追記:
ロック待ち時間は Queue time、Exec time にも入らない。
- 12分間ロック待ちさせ、commit してロックを解放する。
# \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 ---------+------------+----------+--------+----------+------------+------------+-----------------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+------------ 1778410 | 1073988027 | awsuser | dev | 287674 | public | testtab1 | ShareRowExclusiveLock | True | relation | 2023-04-19 22:41:16.415865 | 729.97 | ★12.17 | 0.2 | | 700.97 | 1 | 1073857081 1778410 | 1073988027 | awsuser | dev | 287674 | public | testtab1 | AccessShareLock | True | relation | 2023-04-19 22:41:16.415865 | 729.97 | ★12.17 | 0.2 | | | | 1778509 | 1073857081 | awsuser | dev | 287674 | public | testtab1 | ShareRowExclusiveLock | False | relation | 2023-04-19 22:41:45.922862 | | | | 700.97 | | |
- "UPDATE testtab1 SET name='bbb' WHERE id=2" の開始時間はロック解放後の時間になっている。
# select database, query, substring(querytxt, 1, 50), starttime, endtime from stl_query where database = 'dev' and querytxt like '%UPDATE%' order by starttime; database | query | substring | starttime | endtime ----------------------------------+---------+----------------------------------------------------+----------------------------+---------------------------- dev | 2411256 | /* hash: 7e521551146c1324c214c9dd6647f563 */ SELEC | 2023-04-18 22:55:35.932989 | 2023-04-18 22:55:37.244375 dev | 2600136 | UPDATE testtab1 SET name='fff' WHERE id=1; | 2023-04-19 22:41:16.432228 | 2023-04-19 22:41:16.674499 dev | 2600292 | select database, substring(querytxt, 1, 50) , star | 2023-04-19 22:53:05.817674 | 2023-04-19 22:53:09.358149 dev | 2600305 | ★UPDATE testtab1 SET name='bbb' WHERE id=2; | ★2023-04-19 22:53:34.806416 | 2023-04-19 22:53:35.155323 dev | 2600311 | select database, substring(querytxt, 1, 50) , star | 2023-04-19 22:53:48.480882 | 2023-04-19 22:53:48.878474 dev | 2600421 | select query, database, substring(querytxt, 1, 50) | 2023-04-19 23:03:45.042256 | 2023-04-19 23:03:50.617899
- STL_WLM_QUERY で Queue time、Exec time を確認する。
=# select * from stl_wlm_query where query = 2600305; -[ RECORD 1 ]------------+----------------------------------------------------------------- userid | 100 xid | 1778509 task | 2600157 query | 2600305 service_class | 100 slot_count | 1 service_class_start_time | 2023-04-19 22:53:34.807131 queue_start_time | 2023-04-19 22:53:34.808162 ★ queue_end_time | 2023-04-19 22:53:34.808162 total_queue_time | 0 ★ exec_start_time | 2023-04-19 22:53:34.808169 ★ exec_end_time | 2023-04-19 22:53:35.154447 total_exec_time | 346278 ★ 346ms（1秒未満） service_class_end_time | 2023-04-19 22:53:35.154447 final_state | Completed est_peak_mem | 44040192 query_priority | Normal service_class_name | Default queue
- マネジメントコンソールの Duration はロック待ち時間を含むが、
- runtime にはロック待ち時間は含まれない。
