Db2 數據庫堵塞怎么辦
作為一個數據庫管理員，工作中經常會遇到的一個問題：當數據庫出現故障的情況下，如何快速定位問題和找到解決方案。尤其是在運維非常重要系統的時候，解決問題恢復服務是分秒必爭。Db2 作為廣泛使用的商業數據庫，內部提供了眾多方法論和診斷工具等來協助分析問題。然而當問題真正發生的時候，數據庫管理員還是會手忙腳亂，不知道從何處下手。如果著手分析的方向發生了錯誤，時間更是浪費嚴重，問題得不到及時解決，甚至有可能采取了錯誤的措施，導致更嚴重的后果。
導致數據庫堵塞原因有很多，即便是現在總結，也僅僅是總結曾經遇到過的情況。即便是曾經遇到的問題重復發生的時候，快速找到源頭并處理也是很大的挑戰。這個時候腦子里想著方法論，手上敲著各種診斷工具的命令，從輸出的結果再繼續分析處理。整個過程即便是非常有經驗的數據庫管理員也需要很多操作時間。如果可以針對常見的堵塞問題，開發出一個自動分析的工具，直接展示堵塞原因和處理語句，就能夠大大加快處理的速度。這也是一直以來數據庫管理員亟需的工具。然而因為導致數據庫堵塞原因的多樣性和未知性，寫這樣一個工具并不容易，所以市場上并沒有這樣的成熟工具。
退而求其次，僅僅針對常見的堵塞問題，是可以開發出這樣的一鍵檢查處理工具的。所以我開發了一個簡單的 python 腳本，幫助分析日常工作中的遇到的數據庫問題。后續也需要慢慢加強和改進。最重要的是，寫這個文章是為了總結幾種 Db2 數據庫常見的堵塞問題并提供解決方案。
開發這個工具的時候，我聯想到在以前遇到過數據庫堵塞問題的時候，數據庫甚至都沒有辦法連接，新請求也會被堵塞住。db2top 等命令完全出不來結果。只有 db2pd 這樣的工具能夠使用。db2pd 工具是從內存直接獲取信息，不需要連接數據庫，屬于輕量級的診斷工具。所以在數據庫發生堵塞，數據庫無法連接的情況下，db2pd 是最好的選擇。
DB2 數據庫堵塞怎么辦？首先是快速定位原因，使用 db2pd 將常見的堵塞現象分析一遍。如果定位到是曾經碰到的問題，那就比較好辦了，趕緊實行對應的解決方案。如果不是常見的問題，盡量收集足夠多的信息，例如 stack 等，然后重啟實例恢復數據庫，但是這樣可能堵塞問題還是會重現，不能根本解決問題。
Db2 數據庫常見堵塞問題
Db2 數據庫發生性能緩慢或者堵塞的最常見現象是數據庫活動會話激增，數據庫相關命令和語句運行緩慢。導致性能緩慢的原因有很多，最常見的可能是出現鎖問題。一個長 sql 堵塞其他相關 sql，導致短時間并發 sql 變多，系統變慢。也有可能是出現了大 sql，耗盡系統資源等。如下圖所示，我歸納列舉了一些常見的堵塞原因，整理了相關問題解決的方法。
圖 1. Db2 常見堵塞問題分析
圖中所列的這些問題都可以通過 db2pd 工具獲取信息來分析。我也在一鍵檢查分析工具里面包含了這些場景。
鎖鏈分析和處理
Db2 的鎖機制與其他數據庫差異很大，鎖問題也是在數據庫運維中重點關注的對象。鎖是用來控制事務的一致性和并發性的。Db2 的隔離級別和其他數據庫差不多，都是解決臟讀，幻讀，不可重復讀等問題。然而不同于其他數據庫，Db2 的鎖是存放在內存里的。數據庫的 locklist 參數控制這個內存的大小。如果出現某個實務需要加的鎖特別多，可能會導致這個內存里放不下，觸發鎖升級。鎖升級更容易引起堵塞。
發現鎖堵塞
一個正常運行的數據庫突然出現鎖問題通常有兩種情況： 一種是運行了不常運行的 SQL 事務，堵塞了正常的交易。一種是正常的交易事務突然性能有問題，例如查詢計劃改變。不管是哪種情況，最緊要的是將源頭找出來。db2top 工具有一個非常好用的功能，就是查看鎖鏈的信息。
清單 1. db2top 查看鎖鏈
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
[]16:01:41,refresh=0secs(0.008) Locks AIX,member=[4/4],DB2GDPC:CHGMDB[d=Y,a=N,e=N,p=ALL] [qp=off] +---------------------------------------------------------------------------------+ | | | Blocker->Blocked Agent Chain | | --------------------------------------------------------------------------- | | 1546->64481->1309 | | | | | | | | | | | | | | | | | | | | | | | | Press any key to resume... | +---------------------------------------------------------------------------------+Quit: q, Help: h Lock=49 (Entries=49), L: Lock Chain db2top 2 |
在這個輸出里面，1546 這個應用是鎖的持有者，其他都是等待者。下一步就是分析 1546 在執行什么語句，是否需要殺，是否需要優化。
然而對于已經堵塞的 Db2 數據庫，db2top 可能根本打不開。這個時候就需要 db2pd 工具來查看鎖等待的信息。
清單 2. db2pd 查看鎖等待
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -wlocksksh: There is not enough space in the file system. Database Member 0 -- Database CHGMDB -- Active -- Up 19 days 01:18:29 -- Date2018-02-27-16.52.48.487758 Locks being waited on :AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID 1546 [000-01546] 39 00030418000000000000000452 RowLock ..X G 176565 db2bp DB2GDPC *N0.db2gdpc.180430224639 1309 [000-01309] 40 00030418000000000000000452 RowLock ..X W 323302 db2bp DB2GDPC *N0.db2gdpc.180430224640 1546 [000-01546] 39 00030418000000000000000054 TableLock .IX G 176565db2bp DB2GDPC *N0.db2gdpc.180430224639 1309 [000-01309] 40 00030418000000000000000054 TableLock .IX G 323302 db2bp DB2GDPC *N0.db2gdpc.180430224640 64481 [000-64481] 3 00030418000000000000000054 TableLock ..S W 394879db2bp DB2GDPC *N0.db2gdpc.180430224637 |
在這個 db2pd 的輸出里面，第八列 Sts 就是持有者（G）和等待者（W）。第四列 lockname 是對應的鎖。需要綜合這兩個信息，才能知道應用的等待關系。這里分析鎖等待關系并不是非常直觀。所以我在開發的工具里結合 lockname 和鎖狀態信息組織出鎖鏈關系，然后展示出來。
分析鎖問題
基于上述信息，找到鎖的持有者源頭，現在還需要知道持有者在運行什么語句。這個可以通過 db2pd 的 application 選項和 dynamic 選項綜合分析出當前正在執行和上次執行的語句。
清單 3. db2pd 查看 application
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -application 1546 Database Member 0 -- Database CHGMDB -- Active -- Up 20 days 18:31:55 -- Date 2018-03-01-10.06.14.595025 Applications:Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID CollectActData CollectActPartitionCollectSectionActuals 0x0A00020042CA0080 1546 [000-1546] 1 147263 UOW-Waiting 0 0 341 2 *N0.db2gdpc.1805040253241 37352 N C N External Connection AttributesAddress AppHandl [nod-index] ClientIPAddress EncryptionLvl SystemAuthID 0x0A00020042CA0080 1546 [000-1546] n/a None DB2GDPC Trusted Connection AttributesAddress AppHandl [nod-index] TrustedContext ConnTrustType RoleInherited 0x0A00020042CA0080 1546 [000-1546] n/a non trusted n/a Autonomous Routine ConnectionsAddress AppHandl [nod-index] Status Autonomous Routine Handl [nod-index] Status Anonymous Block ConnectionsAddress AppHandl [nod-index] Status Anonymous Block Handl [nod-index] Status |
在 db2pd 工具的 application 輸出里面，C-AnchID 和 C-StmtUID 結合起來指向當前正在運行的語句。L-AnchID 和 L-StmtUID 結合起來指向上一次執行的語句。要獲得詳細的語句，需要從 dynamic cache 里找到。圖中 C-AnchID 和 C-StmtUID 都是 0，也就是當前應用沒有執行任何語句。而 L-AnchID 和 L-StmtUID 是 341 和 2，上一次執行的語句是可以獲取到的。
清單 4. db2pd 查看動態語句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -dynamic anch=341 Database Member 0 -- Database CHGMDB -- Active -- Up 20 days 19:16:16 -- Date 2018-03-01-10.50.35.125266 Dynamic Cache:Current Memory Used 1700359Total Heap Size 130191196Cache Overflow Flag 0Number of References 83506Number of Statement Inserts 74444Number of Statement Deletes 74408Number of Variation Inserts 48Number of Statements 36 Dynamic SQL Statements:Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text0x0A0005024E0EE9A0 341 2 1 1 3 3 select * from t with rr Dynamic SQL Environments:Address AnchID StmtUID EnvID Iso QOpt Blk0x0A0005024E0EE520 341 2 2 CS 5 B Dynamic SQL Variations:Address AnchID StmtUID EnvID VarID NumRef Typ Lockname Val Insert Time Sect Size Num Copies0x0A0005024E0BEE60 341 2 2 1 2 6 000000020000000200012AA0D6 Y 2018-03-01-09.06.10.891027 6056 0 |
基于 L-AnchID 為 341 去查 dynamic cache，可以看到 StmtUID 為 2 的 sql 語句是"select * from t with rr"。至此就得到了鎖的持有者正在運行的語句或者最后運行的語句是什么。這樣就可以和開發一起分析這個問題是什么原因導致的。
處理鎖問題
通常異常出現鎖問題的原因分兩種：
- 不常見的 SQL：當前 SQL 不是業務常用 SQL，例如新上線的功能，管理節點發起的維護 SQL，或者個人后臺發起的 SQL 等。因為測試不充分，沒有評估好對生產業務的影響。這種情況下一般選擇先殺掉，并且控制不要再次發起，等優化完再上線。
- 常見 SQL 突然變慢：例如執行計劃發生變化，導致 SQL 變慢，從而促發了鎖競爭的問題。這種情況僅僅殺 SQL 可能是不管用的，因為 SQL 還會被調用起來。這時需要立刻獲取 SQL 的查詢計劃，抓緊時間調優。例如運行 runstats，創建必要的索引等方式。
我在 Db2 堵塞一鍵檢查工具里面對上述操作進行了自動化分析和處理。
清單 5. 一鍵檢查工具分析鎖問題
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py chgmdb lock ################################################################################ Lock Analyze ################################################################################ #The lock chains are:['15412', '15657']['15412', '19008']#The root lock holders are: ['15412']#The stmt for applicaiton 15412 is:The current stmt is:NULL .The last stmt is: select * from t with rr .#You can force the holders by:db2 "force application (15412) " |
工具在分析鎖問題的時候，首先展示鎖鏈并排序，然后找到所有鎖鏈中鎖持有者執行的 SQL 語句，并將需要快速殺應用的語句打印出來，便于快速決策是否調用。
latch 鏈分析和處理
Db2 的 latch 是一個教科書里沒有詳細闡述也無法詳細枚舉所有 latch 種類的機制。Latch 簡單來說就是線程鎖。它和 Db2 的鎖不一樣但是堵塞時的現象差不多，都是一個線程獲取到了 latch，堵塞了其他需要這個 latch 的線程。Latch 促發的問題可能還要嚴重。Lock 通過殺掉持有者的 apphdl 還可以釋放，Latch 的持有者可能并不是應用，可能是 Db2 的其他內部線程，是沒有開放接口去殺的。這種情況下只有等待或者重啟實例。
latch 問題可能是數據庫管理員最頭疼的問題。因為通常這種問題牽涉的是 Db2 開發的內部機制，屬于未公開的信息。基本上這個時候能做的只是想辦法解開 latch，收集信息給 IBM 支持團隊分析原因。
查看 latch 堵塞
處理這類問題首先是監控是否發生了 latch 等待：
清單 6. db2pd 查看 latch 等待
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
AGDPCMB1:/home/db2gdpc$db2pd -latchesDatabase Member 0 -- Active -- Up 30 days 00:11:52 -- Date 2017-12-01-17.11.29.074912 Latches:Address Holder Waiter Filename LOC LatchType HoldCount 0x0780000004F00478 1553 0 ../include/sqle_workload_disp.h 1391 SQLO_LT_sqeWLDispatcher__m_tunerLatch 1 0x0A00050000069D20 33105 589675 sqlpgResSpace.C 542 SQLO_LT_SQLP_DBCB__add_logspace_sem 1 0x0A00050000069D20 33105 528805 sqlpgResSpace.C 542 SQLO_LT_SQLP_DBCB__add_logspace_sem 1 Latch Waiters With No Holders:Address Holder Waiter Filename LOC LatchType 0x0A0005059594A800 0 529319 /view/db2_v105fp7_aix64_s151221/vbs/engn/include/sqlpt_inlines.h 2186 SQLO_LT_SQLB_BPD__bpdLatch_SX0x0A00050225DAA938 0 415209 /view/db2_v105fp7_aix64_s151221/vbs/engn/include/sqlpt_inlines.h 2186 SQLO_LT_SQLB_BPD__bpdLatch_SX |
圖中的輸出信息分兩個主要部分。第一部分是有持有者的 latch 信息，包含有等待的和沒等待的。沒有等待者的持有者是不需要關心的。第二部分是找不到持有者但是有等待者的 latch 信息。相對第一部分，這個是因為持有者在內部開發的代碼里沒有顯示給監控，并不是真的沒有持有者。解讀下這個輸出里面的內容：
- Address：latch 地址，唯一定位一個 latch 對象。
- Holder：latch 的持有者。這是個 EDUID。
- Waiter：latch 的等待者。這是個 EDUID。
- Filename：獲取這個 latch 的源文件名。
- LOC：源文件里的代碼位置。
- LatchType：latch 名稱。
- HoldCount：持有數量。
上面這個例子包含三種場景：
- latch 地址為 0x0780000004F00478 的持有者是 1553，等待者是 0 也就是沒有等待者。這是一個正常的現象，不需要去關注。
- latch 地址為 0x0A00050000069D20 的持有者是 33105，等待者有 589675 和 528805。這是一個典型的堵塞現象。33105 堵塞了 589675 和 528805。這個 latch 的名稱是 SQLO_LT_SQLP_DBCB__add_logspace_sem。
- latch 地址為 0x0A0005059594A800 和 0x0A00050225DAA938 沒有顯示持有者（顯示持有者的代價太高，所以 Db2 內部屏蔽了），但是分別有等待者 529319 和 415209。這個 latch 的名稱是 SQLO_LT_SQLB_BPD__bpdLatch_SX。
Latch 的等待信息是瞬間抓取的，如果想要確定是否存在堵塞現象，需要多抓一次 latch 信息來確認。在確認了 latch 堵塞問題的情況下，需要抓取 stack 來獲取詳細信息給 IBM 的支持開 case。Latch 問題的處理里面 stack 是關鍵信息。發生競爭的 latch 持有者和等待者都需要抓取 stack。抓取 stack 的語句是：db2pd -stack <eduid> 。 這里的 eduid 輸入就是 latch 選項輸出里面的 Holder 和 Waiter。
分析 latch 堵塞對象
如果是有持有者的堵塞現象，可以檢查持有者是什么 EDU，是否對應到 application，然后確定能否通過解決持有者的方式釋放這個堵塞問題。
清單 7. db2pd 查看 edu 等待
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
AAGDPCMB1:/home/db2gdpc$db2pd -edus Database Member 0 -- Active -- Up 21 days 00:00:06 -- Date 2018-03-01-15.26.59.059962 List of all EDUs for database member 0 db2sysc PID: 17760262db2wdog PID: 34930696db2acd PID: 45875450 EDU ID TID Kernel TID EDU Name USR (s) SYS (s) ===================================================================================================================23561 23561 67373307 db2agnta (XTCUR2) 0 0.232340 0.039394577794 577794 130024209 db2agnta (CHGMDB) 0 0.475758 0.083151526009 526009 21563441 db2loggr (CMPDB) 0 28.628607 4.885121525752 525752 39125599 db2logmgr.0 (CMPDB) 0 10.656058 6.702469525495 525495 58590885 db2castructevent SA (CMPDB) 0 0.000232 0.000020…… |
通過 db2pd 工具能夠查看 EDUID 對應的 EDU Name 是什么。如果 EDU Name 是 db2agent，那么就能對應到一個 application。這個時候查看對應數據庫的 applications 輸出，就找到 CoorEDUID 對應的 AppHandl 了。
清單 8. db2pd 查看 application
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -applications Database Member 0 -- Database CHGMDB -- Active -- Up 20 days 23:56:31 -- Date 2018-03-01-15.30.50.066987 Applications:Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID CollectActData CollectActPartition CollectSectionActuals 0x0A00020021180080 3842 [000-03842] 1 82548 ConnectCompleted 0 0 0 0 *N0.DB2.180208083025 0 0 N C N 0x0780000008B00080 3822 [000-03822] 1 72268 ConnectCompleted 0 0 0 0 *N0.DB2.180208083005 0 0 N C N …… |
找到了 AppHandl，就可以查看到對應的 SQL 語句是什么，知道這個應用在做什么。方法分析鎖問題的時候找 SQL 一樣。最后嘗試"db2 force application (<AppHandl>)" ，運氣好的話這個堵塞問題可能就暫時解決了。
處理 latch 堵塞問題
獲取到 latch 名稱后，首先去 IBM 網站查找這個 latch 的關鍵詞，看看有沒有已知的問題現象一致，有沒有解決辦法。最后一定要開 CASE 找 IBM 官方支持，找到真正原因，避免再出現這樣的問題。我在一鍵檢查工具里面按照這個思路處理 latch 問題。
清單 9. 一鍵檢查工具分析 latch 問題
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py chgmdb latch ################################################################################ Latch Analyse ################################################################################ ############### Collect contentions on Address: ##############Address: 0x0A00050000069D20Holder: ['33105']Waiter: ['589675', '528805']LatchType: SQLO_LT_SQLP_DBCB__add_logspace_sem####Start analyse contentions:####Collect holder information: #Collect holder info: 33105The apphdl for tid 33105 is 0The last stmt is: No stmt found for 0.No edu found for eduid: 0 #You can force this holder by: ####Collect Waiter information: #Collect waiter info: 589675The apphdl for tid 589675 is 0The last stmt is: No stmt found for 0.No edu found for eduid: 0 #Collect waiter info: 528805The apphdl for tid 528805 is 0The last stmt is: No stmt found for 0.No edu found for eduid: 0 ############### Collect contentions on Address: ##############Address: 0x0A0005059594A800Holder: ['0']Waiter: ['529319']LatchType: SQLO_LT_SQLB_BPD__bpdLatch_SX####Start analyse contentions:####No holder on this address, collect stack and sanpshot for waiters: #Collect waiter info: 529319The apphdl for tid 529319 is 0The last stmt is: No stmt found for 0.No edu found for eduid: 0 ############### Collect contentions on Address: ##############Address: 0x0A00050225DAA938Holder: ['0']Waiter: ['415209']LatchType: SQLO_LT_SQLB_BPD__bpdLatch_SX####Start analyse contentions:####No holder on this address, collect stack and sanpshot for waiters: #Collect waiter info: 415209The apphdl for tid 415209 is 0The last stmt is: No stmt found for 0.No edu found for eduid: 0 |
這個工具會對每個出現堵塞的 latch 地址展開 latch 鏈，然后對相關 eduid 收集 stack，最后嘗試找到這些 eduid 對應的 apphandl 和 sql 語句。如果持有者對應到 apphandl，那么也把處理的 force 語句打印出來。
