Db2 數(shù)據(jù)庫堵塞怎么辦
作為一個數(shù)據(jù)庫管理員，工作中經(jīng)常會遇到的一個問題：當數(shù)據(jù)庫出現(xiàn)故障的情況下，如何快速定位問題和找到解決方案。尤其是在運維非常重要系統(tǒng)的時候，解決問題恢復服務是分秒必爭。Db2 作為廣泛使用的商業(yè)數(shù)據(jù)庫，內(nèi)部提供了眾多方法論和診斷工具等來協(xié)助分析問題。然而當問題真正發(fā)生的時候，數(shù)據(jù)庫管理員還是會手忙腳亂，不知道從何處下手。如果著手分析的方向發(fā)生了錯誤，時間更是浪費嚴重，問題得不到及時解決，甚至有可能采取了錯誤的措施，導致更嚴重的后果。
導致數(shù)據(jù)庫堵塞原因有很多，即便是現(xiàn)在總結(jié)，也僅僅是總結(jié)曾經(jīng)遇到過的情況。即便是曾經(jīng)遇到的問題重復發(fā)生的時候，快速找到源頭并處理也是很大的挑戰(zhàn)。這個時候腦子里想著方法論，手上敲著各種診斷工具的命令，從輸出的結(jié)果再繼續(xù)分析處理。整個過程即便是非常有經(jīng)驗的數(shù)據(jù)庫管理員也需要很多操作時間。如果可以針對常見的堵塞問題，開發(fā)出一個自動分析的工具，直接展示堵塞原因和處理語句，就能夠大大加快處理的速度。這也是一直以來數(shù)據(jù)庫管理員亟需的工具。然而因為導致數(shù)據(jù)庫堵塞原因的多樣性和未知性，寫這樣一個工具并不容易，所以市場上并沒有這樣的成熟工具。
退而求其次，僅僅針對常見的堵塞問題，是可以開發(fā)出這樣的一鍵檢查處理工具的。所以我開發(fā)了一個簡單的 python 腳本，幫助分析日常工作中的遇到的數(shù)據(jù)庫問題。后續(xù)也需要慢慢加強和改進。最重要的是，寫這個文章是為了總結(jié)幾種 Db2 數(shù)據(jù)庫常見的堵塞問題并提供解決方案。
開發(fā)這個工具的時候，我聯(lián)想到在以前遇到過數(shù)據(jù)庫堵塞問題的時候，數(shù)據(jù)庫甚至都沒有辦法連接，新請求也會被堵塞住。db2top 等命令完全出不來結(jié)果。只有 db2pd 這樣的工具能夠使用。db2pd 工具是從內(nèi)存直接獲取信息，不需要連接數(shù)據(jù)庫，屬于輕量級的診斷工具。所以在數(shù)據(jù)庫發(fā)生堵塞，數(shù)據(jù)庫無法連接的情況下，db2pd 是最好的選擇。
DB2 數(shù)據(jù)庫堵塞怎么辦？首先是快速定位原因，使用 db2pd 將常見的堵塞現(xiàn)象分析一遍。如果定位到是曾經(jīng)碰到的問題，那就比較好辦了，趕緊實行對應的解決方案。如果不是常見的問題，盡量收集足夠多的信息，例如 stack 等，然后重啟實例恢復數(shù)據(jù)庫，但是這樣可能堵塞問題還是會重現(xiàn)，不能根本解決問題。
Db2 數(shù)據(jù)庫常見堵塞問題
Db2 數(shù)據(jù)庫發(fā)生性能緩慢或者堵塞的最常見現(xiàn)象是數(shù)據(jù)庫活動會話激增，數(shù)據(jù)庫相關(guān)命令和語句運行緩慢。導致性能緩慢的原因有很多，最常見的可能是出現(xiàn)鎖問題。一個長 sql 堵塞其他相關(guān) sql，導致短時間并發(fā) sql 變多，系統(tǒng)變慢。也有可能是出現(xiàn)了大 sql，耗盡系統(tǒng)資源等。如下圖所示，我歸納列舉了一些常見的堵塞原因，整理了相關(guān)問題解決的方法。
圖 1. Db2 常見堵塞問題分析
圖中所列的這些問題都可以通過 db2pd 工具獲取信息來分析。我也在一鍵檢查分析工具里面包含了這些場景。
鎖鏈分析和處理
Db2 的鎖機制與其他數(shù)據(jù)庫差異很大，鎖問題也是在數(shù)據(jù)庫運維中重點關(guān)注的對象。鎖是用來控制事務的一致性和并發(fā)性的。Db2 的隔離級別和其他數(shù)據(jù)庫差不多，都是解決臟讀，幻讀，不可重復讀等問題。然而不同于其他數(shù)據(jù)庫，Db2 的鎖是存放在內(nèi)存里的。數(shù)據(jù)庫的 locklist 參數(shù)控制這個內(nèi)存的大小。如果出現(xiàn)某個實務需要加的鎖特別多，可能會導致這個內(nèi)存里放不下，觸發(fā)鎖升級。鎖升級更容易引起堵塞。
發(fā)現(xiàn)鎖堵塞
一個正常運行的數(shù)據(jù)庫突然出現(xiàn)鎖問題通常有兩種情況： 一種是運行了不常運行的 SQL 事務，堵塞了正常的交易。一種是正常的交易事務突然性能有問題，例如查詢計劃改變。不管是哪種情況，最緊要的是將源頭找出來。db2top 工具有一個非常好用的功能，就是查看鎖鏈的信息。
清單 1. db2top 查看鎖鏈
[\]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 在執(zhí)行什么語句，是否需要殺，是否需要優(yōu)化。
然而對于已經(jīng)堵塞的 Db2 數(shù)據(jù)庫，db2top 可能根本打不開。這個時候就需要 db2pd 工具來查看鎖等待的信息。
清單 2. db2pd 查看鎖等待
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -wlocks
ksh: 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 176565
db2bp 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 394879
db2bp DB2GDPC *N0.db2gdpc.180430224637
在這個 db2pd 的輸出里面，第八列 Sts 就是持有者（G）和等待者（W）。第四列 lockname 是對應的鎖。需要綜合這兩個信息，才能知道應用的等待關(guān)系。這里分析鎖等待關(guān)系并不是非常直觀。所以我在開發(fā)的工具里結(jié)合 lockname 和鎖狀態(tài)信息組織出鎖鏈關(guān)系，然后展示出來。
分析鎖問題
基于上述信息，找到鎖的持有者源頭，現(xiàn)在還需要知道持有者在運行什么語句。這個可以通過 db2pd 的 application 選項和 dynamic 選項綜合分析出當前正在執(zhí)行和上次執(zhí)行的語句。
清單 3. db2pd 查看 application
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 CollectActPartition
CollectSectionActuals
0x0A00020042CA0080 1546 [000-1546] 1 147263 UOW-Waiting 0
0 341 2 *N0.db2gdpc.180504025324
1 37352 N C N
External Connection Attributes
Address AppHandl [nod-index] ClientIPAddress
EncryptionLvl SystemAuthID
0x0A00020042CA0080 1546 [000-1546] n/a None
DB2GDPC
Trusted Connection Attributes
Address AppHandl [nod-index] TrustedContext
ConnTrustType RoleInherited
0x0A00020042CA0080 1546 [000-1546] n/a
non trusted n/a
Autonomous Routine Connections
Address AppHandl [nod-index] Status Autonomous Routine Handl [nod-
index] Status
Anonymous Block Connections
Address AppHandl [nod-index] Status Anonymous Block Handl [nod-index]
Status
在 db2pd 工具的 application 輸出里面，C-AnchID 和 C-StmtUID 結(jié)合起來指向當前正在運行的語句。L-AnchID 和 L-StmtUID 結(jié)合起來指向上一次執(zhí)行的語句。要獲得詳細的語句，需要從 dynamic cache 里找到。圖中 C-AnchID 和 C-StmtUID 都是 0，也就是當前應用沒有執(zhí)行任何語句。而 L-AnchID 和 L-StmtUID 是 341 和 2，上一次執(zhí)行的語句是可以獲取到的。
清單 4. db2pd 查看動態(tài)語句
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 1700359
Total Heap Size 130191196
Cache Overflow Flag 0
Number of References 83506
Number of Statement Inserts 74444
Number of Statement Deletes 74408
Number of Variation Inserts 48
Number of Statements 36
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x0A0005024E0EE9A0 341 2 1 1 3 3 select *
from t with rr
Dynamic SQL Environments:
Address AnchID StmtUID EnvID Iso QOpt Blk
0x0A0005024E0EE520 341 2 2 CS 5 B
Dynamic SQL Variations:
Address AnchID StmtUID EnvID VarID NumRef Typ Lockname
Val Insert Time Sect Size Num Copies
0x0A0005024E0BEE60 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"。至此就得到了鎖的持有者正在運行的語句或者最后運行的語句是什么。這樣就可以和開發(fā)一起分析這個問題是什么原因?qū)е碌摹?br />
處理鎖問題
通常異常出現(xiàn)鎖問題的原因分兩種：
- 不常見的 SQL：當前 SQL 不是業(yè)務常用 SQL，例如新上線的功能，管理節(jié)點發(fā)起的維護 SQL，或者個人后臺發(fā)起的 SQL 等。因為測試不充分，沒有評估好對生產(chǎn)業(yè)務的影響。這種情況下一般選擇先殺掉，并且控制不要再次發(fā)起，等優(yōu)化完再上線。
- 常見 SQL 突然變慢：例如執(zhí)行計劃發(fā)生變化，導致 SQL 變慢，從而促發(fā)了鎖競爭的問題。這種情況僅僅殺 SQL 可能是不管用的，因為 SQL 還會被調(diào)用起來。這時需要立刻獲取 SQL 的查詢計劃，抓緊時間調(diào)優(yōu)。例如運行 runstats，創(chuàng)建必要的索引等方式。
我在 Db2 堵塞一鍵檢查工具里面對上述操作進行了自動化分析和處理。
清單 5. 一鍵檢查工具分析鎖問題
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) "
工具在分析鎖問題的時候，首先展示鎖鏈并排序，然后找到所有鎖鏈中鎖持有者執(zhí)行的 SQL 語句，并將需要快速殺應用的語句打印出來，便于快速決策是否調(diào)用。
latch 鏈分析和處理
Db2 的 latch 是一個教科書里沒有詳細闡述也無法詳細枚舉所有 latch 種類的機制。Latch 簡單來說就是線程鎖。它和 Db2 的鎖不一樣但是堵塞時的現(xiàn)象差不多，都是一個線程獲取到了 latch，堵塞了其他需要這個 latch 的線程。Latch 促發(fā)的問題可能還要嚴重。Lock 通過殺掉持有者的 apphdl 還可以釋放，Latch 的持有者可能并不是應用，可能是 Db2 的其他內(nèi)部線程，是沒有開放接口去殺的。這種情況下只有等待或者重啟實例。
latch 問題可能是數(shù)據(jù)庫管理員最頭疼的問題。因為通常這種問題牽涉的是 Db2 開發(fā)的內(nèi)部機制，屬于未公開的信息。基本上這個時候能做的只是想辦法解開 latch，收集信息給 IBM 支持團隊分析原因。
查看 latch 堵塞
處理這類問題首先是監(jiān)控是否發(fā)生了 latch 等待：
清單 6. db2pd 查看 latch 等待
AGDPCMB1:/home/db2gdpc$db2pd -latches
Database 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_SX
0x0A00050225DAA938 0 415209 /view/db2_v105fp7_aix64_s151221/vbs/engn/include/sqlpt_inlines.h 2186
SQLO_LT_SQLB_BPD__bpdLatch_SX
圖中的輸出信息分兩個主要部分。第一部分是有持有者的 latch 信息，包含有等待的和沒等待的。沒有等待者的持有者是不需要關(guān)心的。第二部分是找不到持有者但是有等待者的 latch 信息。相對第一部分，這個是因為持有者在內(nèi)部開發(fā)的代碼里沒有顯示給監(jiān)控，并不是真的沒有持有者。解讀下這個輸出里面的內(nèi)容：
- Address：latch 地址，唯一定位一個 latch 對象。
- Holder：latch 的持有者。這是個 EDUID。
- Waiter：latch 的等待者。這是個 EDUID。
- Filename：獲取這個 latch 的源文件名。
- LOC：源文件里的代碼位置。
- LatchType：latch 名稱。
- HoldCount：持有數(shù)量。
上面這個例子包含三種場景：
- latch 地址為 0x0780000004F00478 的持有者是 1553，等待者是 0 也就是沒有等待者。這是一個正常的現(xiàn)象，不需要去關(guān)注。
- latch 地址為 0x0A00050000069D20 的持有者是 33105，等待者有 589675 和 528805。這是一個典型的堵塞現(xiàn)象。33105 堵塞了 589675 和 528805。這個 latch 的名稱是 SQLO_LT_SQLP_DBCB__add_logspace_sem。
- latch 地址為 0x0A0005059594A800 和 0x0A00050225DAA938 沒有顯示持有者（顯示持有者的代價太高，所以 Db2 內(nèi)部屏蔽了），但是分別有等待者 529319 和 415209。這個 latch 的名稱是 SQLO_LT_SQLB_BPD__bpdLatch_SX。
Latch 的等待信息是瞬間抓取的，如果想要確定是否存在堵塞現(xiàn)象，需要多抓一次 latch 信息來確認。在確認了 latch 堵塞問題的情況下，需要抓取 stack 來獲取詳細信息給 IBM 的支持開 case。Latch 問題的處理里面 stack 是關(guān)鍵信息。發(fā)生競爭的 latch 持有者和等待者都需要抓取 stack。抓取 stack 的語句是：db2pd -stack eduid> 。 這里的 eduid 輸入就是 latch 選項輸出里面的 Holder 和 Waiter。
分析 latch 堵塞對象
如果是有持有者的堵塞現(xiàn)象，可以檢查持有者是什么 EDU，是否對應到 application，然后確定能否通過解決持有者的方式釋放這個堵塞問題。
清單 7. db2pd 查看 edu 等待
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: 17760262
db2wdog PID: 34930696
db2acd PID: 45875450
EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
===================================================================================================================
23561 23561 67373307 db2agnta (XTCUR2) 0 0.232340 0.039394
577794 577794 130024209 db2agnta (CHGMDB) 0 0.475758 0.083151
526009 526009 21563441 db2loggr (CMPDB) 0 28.628607 4.885121
525752 525752 39125599 db2logmgr.0 (CMPDB) 0 10.656058 6.702469
525495 525495 58590885 db2castructevent SA (CMPDB) 0 0.000232 0.000020
……
通過 db2pd 工具能夠查看 EDUID 對應的 EDU Name 是什么。如果 EDU Name 是 db2agent，那么就能對應到一個 application。這個時候查看對應數(shù)據(jù)庫的 applications 輸出，就找到 CoorEDUID 對應的 AppHandl 了。
清單 8. db2pd 查看 application
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 網(wǎng)站查找這個 latch 的關(guān)鍵詞，看看有沒有已知的問題現(xiàn)象一致，有沒有解決辦法。最后一定要開 CASE 找 IBM 官方支持，找到真正原因，避免再出現(xiàn)這樣的問題。我在一鍵檢查工具里面按照這個思路處理 latch 問題。
清單 9. 一鍵檢查工具分析 latch 問題
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py chgmdb latch
###############################################################################
# Latch Analyse #
###############################################################################
############### Collect contentions on Address: ##############
Address: 0x0A00050000069D20
Holder: ['33105']
Waiter: ['589675', '528805']
LatchType: SQLO_LT_SQLP_DBCB__add_logspace_sem
####Start analyse contentions:
####Collect holder information:
#Collect holder info: 33105
The apphdl for tid 33105 is 0
The 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: 589675
The apphdl for tid 589675 is 0
The last stmt is: No stmt found for 0.
No edu found for eduid: 0
#Collect waiter info: 528805
The apphdl for tid 528805 is 0
The last stmt is: No stmt found for 0.
No edu found for eduid: 0
############### Collect contentions on Address: ##############
Address: 0x0A0005059594A800
Holder: ['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: 529319
The apphdl for tid 529319 is 0
The last stmt is: No stmt found for 0.
No edu found for eduid: 0
############### Collect contentions on Address: ##############
Address: 0x0A00050225DAA938
Holder: ['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: 415209
The apphdl for tid 415209 is 0
The last stmt is: No stmt found for 0.
No edu found for eduid: 0
這個工具會對每個出現(xiàn)堵塞的 latch 地址展開 latch 鏈，然后對相關(guān) eduid 收集 stack，最后嘗試找到這些 eduid 對應的 apphandl 和 sql 語句。如果持有者對應到 apphandl，那么也把處理的 force 語句打印出來。
查看當前運行時間長的 SQL 語句
Db2 出現(xiàn)運行緩慢如果不是因為鎖或者 latch 的等待問題。這時就需要看看當前哪些 SQL 運行的時間比較長。我會挑選 10 條運行時間最長的 SQL 來分析。
清單 10. 查看 activestatements
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -activestatements
Database Member 0 -- Database CHGMDB -- Active -- Up 21 days 00:37:29 -- Date 2018-03-01-
16.11.48.180193
Active Statement List:
Address AppHandl [nod-index] UOW-ID StmtID AnchID StmtUID EffISO
EffLockTOut EffDegree EntryTime StartTime LastRefTime
0x0A0005024E322860 15657 [000-15657] 5 1 548 1 1
3000 0 Thu Mar 1 16:11:38 Thu Mar 1 16:11:38 Thu Mar 1 16:11:38
0x0A0005024DF5CE60 14933 [000-14933] 2 1 317 1 1
3000 0 Thu Mar 1 16:00:33 Thu Mar 1 16:00:33 Thu Mar 1 16:00:33
0x0A0005024E147CC0 19008 [000-19008] 6 1 365 2 1
3000 0 Thu Mar 1 16:11:42 Thu Mar 1 16:11:42 Thu Mar 1 16:11:42
這個輸出里面需要關(guān)注的是 StartTime，按照這個排序就可以找到運行時間最長的語句了。和分析鎖堵塞問題里的方法一樣。這里的 AnchID 和 StmtUID 可以在 dynamic cache 里面定位到唯一的 SQL 語句。這個在一鍵檢查工具里面是自動收集展示的。
清單 11. 一鍵檢查工具查看 TOP SQL
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py chgmdb stmt
###############################################################################
# Show top 10 running stmt #
###############################################################################
#Check active statements for: CHGMDB
The apphdl is: 14933 , started at : Thu Mar 1 16:00:33
SELECT ID,substr(HOME_HOST,:L0 ,:L1 ) as HOME_HOST,substr(CURRENT_HOST,:L2 ,:L3 ) as
CURRENT_HOST,STATE,ALERT FROM SYSIBMADM.DB2_MEMBER
The apphdl is: 15657 , started at : Thu Mar 1 16:11:38
update t set c1 =:L0
The apphdl is: 19008 , started at : Thu Mar 1 16:11:42
delete from t
這個工具基于執(zhí)行時間排序，只抓取前 10 的 SQL 語句。獲得這些信息后就可以分析有沒有異常。
查看熱表和相關(guān) SQL 語句
Db2 運行緩慢不可忽視的誘因之一就是存在熱點數(shù)據(jù)。通常熱點數(shù)據(jù)會伴隨鎖等待和 latch 等待等現(xiàn)象，但不是完全堵塞的狀態(tài)。現(xiàn)象就是熱點表相關(guān)的 SQL 會比正常情況下慢很多，從而導致整個數(shù)據(jù)庫運行緩慢。
獲取熱點表
當數(shù)據(jù)庫出現(xiàn)緩慢的時候，如果想要從熱點數(shù)據(jù)的角度去分析問題，找到對應的表，然后再找到對應的熱點語句，就可以分析是否存在問題，是否需要優(yōu)化。db2top 首頁鍵入 T 可以進入 Tables 的監(jiān)控界面。在這個界面里能看到 Delta RowsRead 和 Delta RowsWritten 等信息，從而獲取當前熱點表信息。
清單 12. db2top 查看熱點表
[/]15:52:03,refresh=2secs(0.003) Tables AIX,member=[4/4],DB2GDPC:CHGMDB
[d=Y,a=N,e=N,p=ALL] [qp=off]
Table Delta Delta
Name RowsRead/s RowsWritten/s
---------------------------------------- -------------- --------------
DB2GDPC.TEST 0 0
SYSIBM.SYSCOLUMNS 0 0
SYSIBM.SYSCONTEXTATTRIBUTES 0 0
SYSIBM.SYSCONTEXTS 0 0
SYSIBM.SYSDBAUTH 0 0
SYSIBM.SYSEVENTMONITORS 0 0
SYSIBM.SYSEVENTTABLES 0 0
SYSIBM.SYSHISTOGRAMTEMPLATEBINS 0 0
SYSIBM.SYSHISTOGRAMTEMPLATES 0 0
SYSIBM.SYSHISTOGRAMTEMPLATEUSE 0 0
SYSIBM.SYSINDEXES 0 0
SYSIBM.SYSNODEGROUPS 0 0
SYSIBM.SYSPLAN 0 0
SYSIBM.SYSROLEAUTH 0 0
SYSIBM.SYSROUTINES 0 0
SYSIBM.SYSSERVICECLASSES 0 0
SYSIBM.SYSSTOGROUPS 0 0
Quit: q, Help: h L: top temp storage consumers db2top 2.
db2top 最強的地方就是能夠自動獲取兩次捕獲信息之間的差異并計算出 Delta 值展示出來。其他監(jiān)控工具只能獲取當前累計值，需要手工計算和排序。然而就像之前所擔心的那樣，db2top 在數(shù)據(jù)庫緩慢的情況下不一定能工作。這個時候只有 db2pd 工具能夠正常使用。db2pd 的 tcbstats 選項可以展示表和索引的累計訪問信息。
清單 13. db2pd 查看表信息
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -tcbstats nocatalog
Database Member 0 -- Database CHGMDB -- Active -- Up 0 days 01:27:49 -- Date 2018-03-07-
15.58.13.184798
TCB Table Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName
SchemaNm ObjClass DataSize LfSize LobSize XMLSize IxReqRebld
0x0A0005024DDDDAB0 2 -1 n/a 2 -1 INTERNAL SYSIBM
Perm 1 0 0 0 No
0x0A0005024DCF9430 3 1540 n/a 3 1540 LOCKS DB2GDPC
Perm 1787 0 64 0 No
0x0A0005024DCF6EB0 3 -1 n/a 3 -1 INTERNAL SYSIBM
Perm 7 0 0 0 No
0x0A0005024DDDE8B0 2 5 n/a 2 5 TEST DB2GDPC
Perm 8013 0 0 0 No
TCB Table Stats:
Address TableName SchemaNm Scans UDI RTSUDI
PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads
OvFlCrtes PgDictsCrt CCLogReads StoreBytes BytesSaved
0x0A0005024DDDDAB0 INTERNAL SYSIBM 0 0 0
0 0 4 0 0 0 0 0 0
0 0 - -
0x0A0005024DCF9430 LOCKS DB2GDPC 0 147 147
0 0 0 0 0 0 0 0 0
0 0 - -
0x0A0005024DCF6EB0 INTERNAL SYSIBM 0 0 0
0 0 7 0 0 0 0 0 0
0 0 - -
0x0A0005024DDDE8B0 TEST DB2GDPC 1 0 0
0 0 592865 0 0 0 0 0 0
0 0 - -
db2pd 的這個輸出里面關(guān)注 Scans，Reads，Inserts，Updates 和 Deletes。其中 Scans 表示發(fā)生了表掃描的次數(shù)。Reads，Inserts，Updates 和 Deletes 分別是讀增改刪的次數(shù)。這些值都是累計值。如果需要當前實際的訪問數(shù)量，需要通過抓取多次取差值排序才能知道。這個是非常不直觀的。我在一鍵分析工具里面將個思路實現(xiàn)，最終通過計算出 Reads，Inserts，Updates 和 Deletes 的差值總和來排序獲取到熱點表。
獲取相關(guān)應用和 SQL
獲取到熱點表之后的下一步就是找到當前訪問這個熱點表的應用 AppHDL 和對應的 SQL 語句。Db2 的默認隔離級別是 RS。即便是查詢語句，也會在表上加共享鎖。所以通過查看當前的數(shù)據(jù)庫鎖信息，找到在熱點表上加了鎖的應用就好了。
清單 14. db2pd 查看表鎖信息
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -lock showlocks|more
Database Member 0 -- Database CHGMDB -- Active -- Up 0 days 02:00:29 -- Date 2018-03-07-
16.30.53.779832
Locks:
Address TranHdl Lockname Type Mode Sts Owner
Dur HoldCount Att Re
leaseFlg rrIID TableNm SchemaNm
0x0A0005000761CD00 40 414141414166416415C78BFEC1 PlanLock ..S G 40
1 0 0x00000000 0x
40000000 0 N/A N/A 414141414166416415C78BFEC1 SQLP_PLAN
({41414141 41664164 15C78BFE}, load
ing=0)
0x0A000500075BD600 13 00030604000000000000000054 TableLock .IX G 13
1 1 0x00202000 0x
40000000 0 LOCKS DB2GDPC 00030604000000000000000054 SQLP_TABLE
(obj={3;1540})
0x0A000500075C2F80 14 00030604000000000000000054 TableLock .IX G 14
1 1 0x00202000 0x
40000000 0 LOCKS DB2GDPC 00030604000000000000000054 SQLP_TABLE
(obj={3;1540})
0x0A000500075C6380 15 00030604000000000000000054 TableLock .IX G 15
1 1 0x00202000 0x
40000000 0 LOCKS DB2GDPC 00030604000000000000000054 SQLP_TABLE
(obj={3;1540})
0x0A0005000761D400 40 00020005000000000000000054 TableLock .IS G 40
1 0 0x00003000 0x
40000000 0 TEST DB2GDPC 00020005000000000000000054 SQLP_TABLE
(obj={2;5})
通過 TableNm 和 SchemaNm 匹配到熱點表，獲取到 TranHdl，然后通過 db2pd 的 transactions 選項找到對應的 AppHandl。例如在這個案例里面 TEST 是一張熱點表。從鎖信息來看 TranHdl 為 40 的事務占用了鎖。下一步通過 TranHdl 找 AppHandl：
清單 15. db2pd 查看事務信息
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -transactions 40
Database Member 0 -- Database CHGMDB -- Active -- Up 0 days 02:04:26 -- Date 2018-03-07-
16.34.50.447672
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2
Firstlsn Lastlsn Firstlso Lastlso LogSpace
SpaceReserved TID AxRegCnt GXID ClientUserID
ClientWrkstnName ClientApplName ClientAccntng
0x0A00050001064480 19451 [000-19451] 40 3 READ 0x00000000
0x00000000 0x0000000000000000 0x0000000000000000 0 0
0 0 0x0000081DB04F 1 0 n/a
n/a n/a n/a
Total application commits : 806
Total application rollbacks : 25
最后通過應用的 AppHandl 找到對應的 SQL，過程和前面幾個案例一樣。
一鍵分析熱點表問題
我在一鍵檢查工具里將上述分析過程自動化處理，間隔 10 秒抓取兩次表訪問數(shù)據(jù)，計算差值，然后獲取到熱點表。基于每個熱點表的當前加鎖信息找到對應的事務和應用，展示出當前正在執(zhí)行的 SQL。
清單 16. db2pd 查看事務信息
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py chgmdb hottable
###############################################################################
# Show hot tables and its statements #
###############################################################################
#DB2GDPC.TEST is hot.
#Reads: 12266 Inserts: 0 Updates: 0 Deletes: 0 Scans: 0
#The apphdl on this table are: ['19451', '19452', '19453']
************statements 1 ***********
The current stmt is:NULL .
The last stmt is: select * from test .
************statements 2 ***********
The current stmt is:NULL .
The last stmt is: select * from test .
************statements 3 ***********
The current stmt is:NULL .
The last stmt is: select * from test .
這個輸出里面的語句是同一個，執(zhí)行時間應該都超過了 10 秒，所以 Scans 差值為 0。但事實上這個 SQL 是走的表掃描。通過這個工具可以立刻看到當前的熱表，對應的 apphdl 和 SQL。而 apphdl 可以用來殺 SQL。
查看占用臨時表的 SQL 語句
Db2 數(shù)據(jù)庫的 SQL 排序是在內(nèi)存里進行的。SHEAPTHRES_SHR 參數(shù)是限制總的排序內(nèi)存大小。SORTHEAP 參數(shù)是限制單個排序能占用的內(nèi)存大小。當 SQL 排序的時候超出任意一個限制，那么數(shù)據(jù)需要放到系統(tǒng)臨時表里面來排序。相對于內(nèi)存里排序，這個開銷就非常大，SQL 也會變得慢。如果系統(tǒng)臨時表對應的磁盤出現(xiàn)瓶頸，那整個數(shù)據(jù)庫也會運行緩慢。
誰在占用臨時表
系統(tǒng)臨時表是存儲在系統(tǒng)臨時表空間的一種數(shù)據(jù)庫自動創(chuàng)建和刪除的臨時表。通過查看 db2pd 的 tcbstats 選項能夠找到正在使用的臨時表。
清單 17. db2pd 查看臨時表
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -tcbstats nocatalog
Database Member 0 -- Database CHGMDB -- Active -- Up 0 days 19:13:27 -- Date 2018-03-08-
09.43.51.707946
TCB Table Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName
SchemaNm ObjClass DataSize LfSize LobSize XMLSize IxReqRebld
0x0A0005024DDDDAB0 2 -1 n/a 2 -1 INTERNAL SYSIBM
Perm 1 0 0 0 No
0x0A0005024DCF9430 3 1540 n/a 3 1540 LOCKS DB2GDPC
Perm 1787 0 64 0 No
0x0A0005024DCF6EB0 3 -1 n/a 3 -1 INTERNAL SYSIBM
Perm 7 0 0 0 No
0x0A0005024E1132B0 1 2 n/a 1 2 TEMP (00001,00002)
54365> Temp 8045 0 0 0 No
0x0A0005024DDDE8B0 2 5 n/a 2 5 TEST DB2GDPC
Perm 8013 0 0 0 No
TCB Table Stats:
Address TableName SchemaNm Scans UDI RTSUDI
PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads
OvFlCrtes PgDictsCrt CCLogReads StoreBytes BytesSaved
0x0A0005024DDDDAB0 INTERNAL SYSIBM 0 0 0
0 0 10 0 0 0 0 0 0
0 0 - -
0x0A0005024DCF9430 LOCKS DB2GDPC 0 147 147
0 0 0 0 0 0 0 0 0
0 0 - -
0x0A0005024DCF6EB0 INTERNAL SYSIBM 0 0 0
0 0 7 0 0 0 0 0 0
0 0 - -
0x0A0005024E1132B0 TEMP (00001,00002) 54365> 0 0 0
0 0 60386 0 592865 0 0 0 0
0 0 126720902 0
0x0A0005024DDDE8B0 TEST DB2GDPC 5 0 0
0 0 2964325 0 0 0 0 0 0
0 0 - -
查找表名是 TEMP 的記錄，案例里面是"TEMP (00001,00002)" ，對應的 SchemaNm 是"54365>DB2GDPC >"（案例里的命令加上 full 選項就能看到全部內(nèi)容：db2pd -d chgmdb -tcbstats nocatalog -full ） 。這里的 54365 就是應用的鏈接句柄 AppHdl。DB2GDPC 是連接用戶也就是 schema。下面基于 AppHdl 就可以找到正在運行的 SQL 是什么了。
我在一鍵檢查工具里面通過 db2pd 獲取到所有占用了臨時表的應用鏈接句柄 AppHDL，然后將 SQL 都展示出來。
清單 18. 一鍵檢查工具查看臨時表
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py chgmdb temptable
###############################################################################
# Show applications using temptable #
###############################################################################
************Statements for application: 54365 ***********
The current stmt is:NULL .
The last stmt is: select * from test order by c5 .
獲取到了 SQL 就可以分析是否有異常，如果有異常，判斷是否基于 apphdl 來殺 SQL。
查看當前運行的管理操作
Db2 的一些管理類操作也可能影響數(shù)據(jù)庫的性能。所以當數(shù)據(jù)庫緩慢的時候，我們還需要查看一下當前數(shù)據(jù)庫內(nèi)有哪些管理性的操作。
是否存在統(tǒng)計信息收集
統(tǒng)計信息收集（runstats）的對象是表和索引。Db2 在做 runstats 的時候需要掃描大量數(shù)據(jù)并計算，因此是一類開銷比較大的操作。db2pd 的 runstats 選項可以查看當前正在執(zhí)行的 runstats。
清單 19. db2pd 查看 runstats
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -runstats
Database Member 0 -- Database CHGMDB -- Active -- Up 12 days 20:23:45 -- Date 2017-12-18-
11.02.56.265437
Table Runstats Information:
Retrieval Time: 12/18/2017 11:02:56
TbspaceID: -6 TableID: -32768
Schema: CHGMDB TableName: SERVICE_LOG
Status: In Progress Access: Allow write
Sampling: No Sampling Rate: -
Start Time: 12/18/2017 11:02:43 End Time: -
Total Duration: -
Cur Count: 61797 Max Count: 500841
Retrieval Time: 12/18/2017 11:02:56
TbspaceID: 2 TableID: 5
Schema: DB2GDPC TableName: TEST
Status: Completed Access: Allow write
Sampling: No Sampling Rate: -
Start Time: 12/18/2017 11:01:48 End Time: 12/18/2017 11:01:48
Total Duration: 00:00:01
Cur Count: 0 Max Count: 0
Index Runstats Information:
Retrieval Time: 12/18/2017 11:02:56
TbspaceID: 2 TableID: 5
Schema: DB2GDPC TableName: TEST
Status: Completed Access: Allow write
Start Time: 12/18/2017 11:01:48 End Time: 12/18/2017 11:01:49
Total Duration: 00:00:01
Prev Index Duration [1]: 00:00:01
Prev Index Duration [2]: -
Prev Index Duration [3]: -
Cur Index Start: 12/18/2017 11:01:48
Cur Index: 2 Max Index: 2 Index ID: 2
Cur Count: 0 Max Count: 0
其中 End Time 為空的記錄就是當前正在做的 runstats。這里能看到具體是表還是索引正在做 runstats。結(jié)合當前的熱點表，長時間運行的 SQL 等信息一起分析數(shù)據(jù)庫變慢的原因。
是否存在表重組
數(shù)據(jù)庫的表和索引重組需要將磁盤上的數(shù)據(jù)重新整理一遍。這也是一個比較漫長和耗資源的操作。db2pd 的 reorgs 選項能找到當前正在執(zhí)行的重組操作。
清單 20. db2pd 查看 reorgs
AGDPCMB1:/home/db2gdpc$db2pd -d chgmdb -reorgs
Database Member 0 -- Database CHGMDB -- Active -- Up 21 days 01:26:55 -- Date 2017-12-26-
16.06.06.495099
Table Reorg Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName Type
IndexID TempSpaceID
0x0A0006024E14FB00 2 5 n/a n/a n/a TEST Offline
0 2
Table Reorg Stats:
Address TableName Start End PhaseStart
MaxPhase Phase CurCount MaxCount Status Completion
0x0A0006024E14FB00 TEST 12/26/2017 16:05:54 n/a 12/26/2017
16:05:55 3 Build 3007 8012 Started 0
找到了正在重組的表，再結(jié)合當前的熱點表，長時間運行的 SQL 等信息一起分析數(shù)據(jù)庫變慢的原因。
是否存在 load 和 backup
Db2 內(nèi)部有一個內(nèi)存塊叫做 Utilities heap，用來做一些管理類的操作。這個內(nèi)存塊的大小由數(shù)據(jù)庫參數(shù) UTIL_HEAP_SZ 來控制。例如 load 和 backup 這兩種操作就需要使用這塊內(nèi)存。這個內(nèi)存不足會導致 load 和 backup 變慢或者失敗。而 load 和 backup 也是開銷比較大的操作。db2pd 工具提供了 utilities 選項查看實例級別的此類操作。
清單 21. db2pd 查看 utilities
AGDPCMB1:/home/db2gdpc$db2pd -utilities
Database Member 0 -- Active -- Up 0 days 20:11:37 -- Date 2018-03-08-10.40.23.994613
Utilities:
Address ID Type State Invoker Priority
StartTime DBName NumPhases CurPhase Description
Progress:
Address ID PhaseNum CompletedWork TotalWork
StartTime Description
數(shù)據(jù)庫緩慢的時候第一時間發(fā)現(xiàn)是否存在管理類的操作很有必要。這對于分析堵塞問題的方向很有幫助。這類管理性的操作不能隨便處理。需要具體分析它的影響。例如 load 操作如果殺掉，會導致當前表不可用，需要 load 重置?？赡軐е赂鼔牡慕Y(jié)果。但是基于表的大小，load 的數(shù)據(jù)量可以估算還需要多長時間這個操作會完成，期間是否可以有辦法加速等。
一鍵檢查分析工具介紹
根據(jù)上述各種導致數(shù)據(jù)庫堵塞的場景和分析方法，我編寫了一個 python 腳本的一鍵檢查分析工具，用來快速定位和分析數(shù)據(jù)庫堵塞問題。這個腳本完全基于 db2pd 命令，可以在數(shù)據(jù)庫堵塞的情況下，避免連接數(shù)據(jù)庫失敗，從內(nèi)存直接獲取診斷信息。這個腳本是基于 Db2 10.5 版本編寫的，不適用與其他版本。
清單 22. 一鍵檢查工具使用方法
AGDPCMB1:/home/db2gdpc$python db2_check_hang_105.py
usage ./db2_check_hang.py dbname> option>
#Valid options> are:
all :collect all information, which is default.
lock : show lock chains and statements of holders, and print killcmd.
latch : show latch chains and get snapshot, stack for holders. print killcmd.
stmt : show top 10 running statements and its apolication handler.
hottable : show top tables(siud > 1000 in 10 seconds), get running stmt and apphdl.
util : show runstats, reorgs, loads, backup.
temptable: show applications using temtable, and show the sql statement.
這是個 python 腳本，需要安裝 python 來調(diào)用。執(zhí)行用戶為數(shù)據(jù)庫實例用戶。dbname 是數(shù)據(jù)庫名。option 選項可以選擇案例里的內(nèi)容。如果不輸入 option，默認是 all，收集全部內(nèi)容。如果輸入單項，例如 lock，那么只收集鎖等待相關(guān)的信息。
總結(jié)
導致數(shù)據(jù)庫堵塞的問題根源可能性非常多。處理緊急問題最忌慌亂，找錯方向浪費時間，選擇錯誤的處理步驟，還可能導致問題更嚴重。我經(jīng)歷過一個反面案例：某個分區(qū)數(shù)據(jù)庫發(fā)生了堵塞問題，管理員分析定位到是一個大事務造成的。這個事務查詢了大量數(shù)據(jù)并在做插入操作。數(shù)據(jù)庫管理員一著急殺掉了這個事務，導致事務回滾。結(jié)果這個事務回滾非常慢，整整花了兩天才釋放。期間業(yè)務完全受影響。其實如果當時評估下實際完成的數(shù)據(jù)量是不是已經(jīng)很多，是不是快要完成了，然后耐心等待事務完成可能會更快。當然這方面的判斷需要依賴數(shù)據(jù)庫管理員的處理經(jīng)驗。
這個文章里面將一些常見的原因做了分析和處理。借助一鍵檢查工具，快速分析問題和找到解決方案。
參考資源
- Db2 for Linux UNIX and Windows：獲得 DB2 家族產(chǎn)品和特性的描述。
- 參考 IBM DB2 database and SAP software，了解更多 DB2 SAP 相關(guān)內(nèi)容。
- 通過訪問 developerWorks 中國 Information Management 專區(qū) 的 Information Management 技術(shù)資源中心獲得更多的文章、教程和多媒體課件等學習資源。
好了，以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- Spring Data Jpa 自動生成表結(jié)構(gòu)的方法示例
- Spring Data JPA 建立表的聯(lián)合主鍵
- Spring Data Jpa 復合主鍵的實現(xiàn)
- Spring Boot中使用Spring-data-jpa的配置方法詳解
- 詳解Spring Data Jpa當屬性為Null也更新的完美解決方案
- 詳解易語言鏈接DB2 OLEDB實例方法
- 實例講解使用Spring通過JPA連接到Db2
