讓DB2跑得更快——DB2內部解析與效能最佳化

讓DB2跑得更快——DB2內部解析與效能最佳化

（DB2資料庫領域的精彩強音，DB2技巧精髓的熱心分享，資深資料庫專家牛新莊、幹毅民、成孜論、唐志剛聯袂推薦！）

 洪燁著

2013年10月出版

定價：79.00元

 

編輯推薦

    本書作者在DB2China資料庫論壇擔任熱點討論版塊版主，主持多次熱點討論以及專家現場診斷，擅長DB2資料庫及相關產品的效能調優及故障分析，對 DB2技能及實踐經驗有多年積累，並且近年來多位業界專家一直在積極推動DB2領域的技術交流，真正理解DB2技術人員真正的需求與痛楚，是DB2系統知 識及技巧精髓的熱心分享者及貢獻者。

    作者本人出於對DB2的狂熱與追求，透過長期的凝練與匯聚，將DB2知識系統化，把DB2資料庫調優技巧的精髓熱心地分享給廣大讀者，並且憑藉深厚而紮實的理論及經驗，對DB2資料庫的內部進行了深入解析，這是對資料庫領域所做出的重要貢獻與精彩強音！

    單看“內部解析”四個字，就已經能體現本書的寶貴价值，在“內部解析”的基礎上進行“效能最佳化”，定會讓您的DB2“跑得更快”！

內容提要

    本書以最佳化為主題，根據資料庫內部原理將DB2資料庫對SQL語句及其他操作的內部機制進行詳細剖析，並將RDS、DMS、IXM、BPS等DB2內部組 件不為人知的一面展現給大家，以期做到對資料庫的調優過程知其然並知其所以然。同時本書結合響應時間與資源瓶頸兩種效能問題的現象，對資料庫調優的整體思 路進行詳細講解，對原來老式的調優思路進行整理和改動，結合了DB2 V10.1版本的一些新的監控工具及特性，以一種全新的方式闡述DB2資料庫效能調優的基本思路及實踐方法。

    本書適合DB2資料庫管理員、資料庫相關應用程式開發人員、系統管理員、系統架構師及有一定資料庫基礎的使用者自學和參考，也可作為DB2培訓的參考用書。

目錄

第1篇  效能定義及整體架構

第1章  DB2效能最佳化概述.... 2

1.1  效能目標.... 2

1.1.1  響應時間... 4

1.1.2  吞吐量... 5

1.2  工作負載型別.... 6

1.2.1  聯機事務處理（OLTP）... 6

1.2.2  聯機分析處理（OLAP）... 7

1.2.3  決策支援系統（DSS）... 8

1.2.4  企業資源規劃（ERP）... 9

1.3  影響效能的因素.... 10

1.3.1  軟體程式碼編寫對效能的影響... 12

1.3.2  應用程式架構設計對效能的影響... 14

1.3.3  資料庫設計對效能的影響... 14

1.3.4  系統設計對效能的影響... 27

1.4  本章小結.... 34

第2章  DB2架構介紹.... 36

2.1  DB2整體概況.... 36

2.1.1  DB2程式/執行緒體系簡介... 37

2.1.2  DB2記憶體體系簡介... 41

2.1.3  DB2相關檔案簡介... 42

2.2  DB2元件介紹.... 51

2.2.1  作業系統服務... 52

2.2.2  基本系統排程... 53

2.2.3  關係資料服務... 58

2.2.4  資料管理服務... 59

2.2.5  緩衝池服務... 60

2.2.6  資料保護服務... 63

2.3  SQL語句處理過程.... 65

2.3.1  資料查詢語言（DQL）... 66

2.3.2  資料操作語言（DML）... 68

2.3.3  事務處理語言（TPL）... 69

2.4  本章小結.... 70

第2篇  效能監控工具及監控技巧

第3章  效能監控工具.... 72

3.1  實時監控工具.... 72

3.1.1  db2trc. 72

3.1.2  db2top. 75

3.1.3  db2pd. 77

3.2  歷史監控工具.... 81

3.2.1  快照... 81

3.2.2  快照檢視及快照函式... 83

3.2.3  事件監視器... 86

3.3  DB2工作負載管理（DB2 Workload Manager）.... 87

3.3.1  標識階段（Identification Stage）... 88

3.3.2  管理階段（Management Stage）... 89

3.3.3  監控階段（Monitoring Stage）... 90

3.4  語句解釋說明工具.... 94

3.4.1  db2exfmt 94

3.4.2  db2expln. 102

3.4.3  語句解釋說明工具對比... 105

3.5  監控技巧.... 105

3.5.1  查詢資料庫中耗時最長的語句... 107

3.5.2  分析特定語句的時間分佈... 108

3.5.3  捕獲所有的SQL語句... 111

3.6  本章小結.... 113

第3篇  效能分析及內部原理剖析

第4章  深入探討最佳化器.... 116

4.1  語法語義分析.... 117

4.1.1  查詢解析... 117

4.1.2  語義檢查... 117

4.2  SQL語句重寫.... 118

4.2.1  謂詞簡介... 119

4.2.2  掃描方式... 122

4.2.3  連線運算... 125

4.2.4  查詢重寫... 130

4.3  最佳化器編譯.... 135

4.3.1  生成備選執行計劃... 135

4.3.2  基數評估... 137

4.3.3  成本計算公式... 156

4.3.4  生成可執行的程式碼... 157

4.4  基數評估檢查.... 157

4.4.1  透過COUNT語句檢查基數評估... 157

4.4.2  使用Section Actuals分析執行計劃... 162

4.5  本章小結.... 164

第5章  SQL語句效能最佳化之7種武器.... 165

5.1  長生劍——基本統計資訊.... 165

5.1.1  統計資訊收集方法... 170

5.1.2  統計資訊收集策略... 171

5.2  碧玉刀——分佈統計資訊.... 172

5.3  孔雀翎——列組統計資訊.... 177

5.4  離別鉤——REOPT.. 182

5.4.1  REOPT處理機制... 184

5.4.2  REOPT的啟用方式及監控... 187

5.5  多情環——靜態檢視.... 188

5.6  霸王槍——最佳化概要檔案.... 193

5.6.1  最佳化概要檔案的使用方法... 194

5.6.2  最佳化概要檔案規則... 196

5.7  拳頭——語句最佳化.... 199

5.8  本章小結.... 202

第6章  資料物件儲存設計.... 203

6.1  表型別及設計方法.... 204

6.1.1  常規表... 204

6.1.2  MDC表... 205

6.1.3  分割槽表... 212

6.1.4  MQT. 214

6.1.5  表設計原則... 217

6.2  索引型別及設計方法.... 220

6.2.1  索引的作用... 220

6.2.2  索引建立原則... 224

6.2.3  索引鍵順序的選擇... 226

6.2.4  索引設計效能考慮... 227

6.3  DB2設計顧問程式.... 228

6.3.1  戰略性的索引建立... 229

6.3.2  戰略性的表型別選擇... 230

6.4  本章小結.... 232

第7章  DB2物理結構深入解析.... 233

7.1  表空間結構剖析.... 233

7.1.1  SMS（系統管理表空間）結構剖析... 238

7.1.2  DMS（資料庫管理表空間）結構剖析... 240

7.1.3  高水位對於效能的影響... 243

7.1.4  對容器進行重新平衡對效能的影響.... 246

7.2  資料頁詳解.... 247

7.2.1  資料頁結構剖析... 247

7.2.2  欄位型別與行遷移... 251

7.2.3  頁重組... 259

7.3  索引頁詳解.... 263

7.3.1  索引內部結構剖析... 263

7.3.2  索引的分裂... 266

7.3.3  索引維護和清除... 269

7.4  日誌檔案結構剖析.... 273

7.5  本章小結.... 275

第8章  I/O管理及最佳化.... 276

8.1  資料I/O管理.... 277

8.1.1  緩衝池I/O原理... 278

8.1.2  緩衝池邏輯讀取... 282

8.1.3  緩衝池物理讀取... 285

8.1.4  緩衝池寫入操作... 292

8.1.5  基於塊的緩衝池I/O.. 297

8.1.6  緩衝池I/O監控... 298

8.1.7  直接I/O管理... 305

8.2  日誌I/O管理.... 307

8.2.1  日誌讀取... 308

8.2.2  日誌寫入... 309

8.2.3  日誌I/O原理... 310

8.2.4  日誌檔案I/O相關調優引數... 314

8.2.5  歸檔日誌對I/O的影響... 315

8.3  本章小結.... 319

第9章  記憶體管理.... 320

9.1  記憶體模型.... 320

9.1.1  例項共享記憶體... 326

9.1.2  資料庫共享記憶體... 328

9.1.3  應用程式全域性記憶體... 335

9.1.4  代理程式私有記憶體... 338

9.1.5  排序堆... 339

9.1.6  其他記憶體區域... 341

9.2  STMM... 345

9.2.1  STMM執行機制... 345

9.2.2  STMM監控... 347

9.3  如何定位及修復記憶體洩漏.... 348

9.3.1  記憶體洩漏診斷方法... 348

9.3.2  記憶體洩漏的處理方法... 350

9.4  本章小結.... 352

第10章  DB2等待事件.... 353

10.1  鎖物件及相容性.... 354

10.1.1  鎖物件及鎖模式... 355

10.1.2  鎖相容性及鎖轉換... 360

10.2  鎖問題的監控與解決.... 361

10.2.1  鎖事件監控... 365

10.2.2  鎖問題解決方法... 370

10.2.3  鎖案例分享... 377

10.3  latch事件.... 381

10.3.1  latch監控... 382

10.3.2  案例分析... 383

10.4  本章小結.... 384

第4篇  實用工具調優及作業系統最佳化

第11章  實用工具調優.... 386

11.1  備份恢復工具.... 386

11.1.1  backup. 386

11.1.2  restore. 391

11.2  資料移動.... 392

11.2.1  export 392

11.2.2  import 394

11.2.3  load. 396

11.3  其他管理工具.... 401

11.3.1  reorg. 401

11.3.2  runstats. 407

11.4  本章小結.... 409

第12章  作業系統相關問題.... 410

12.1  AIX.. 411

12.1.1  虛擬記憶體管理... 411

12.1.2  磁碟及檔案系統管理... 417

12.1.3  網路調優引數... 422

12.1.4  作業系統相關引數... 423

12.1.5  系統監控工具... 425

12.2  Windows. 429

12.2.1  記憶體管理... 429

12.2.2  磁碟及檔案系統相關引數... 432

12.2.3  系統監控工具... 432

12.3  本章小結.... 435

第5篇  效能分析思路及最佳化總結

第13章  效能問題分析思路.... 438

13.1  響應時間問題.... 439

13.1.1  響應時間總結... 439

13.1.2  透過快照進行分析... 442

13.1.3  透過快照函式進行分析... 444

13.2  資源佔用問題.... 447

13.2.1  磁碟瓶頸... 447

13.2.2  CPU瓶頸... 450

13.2.3  記憶體瓶頸... 455

13.3  本章小結.... 457

 

精彩節摘

9.3  如何定位及修復記憶體洩漏

在懷疑遇到記憶體洩漏的問題之前，必須謹慎地判斷當前資料庫中是否真實存在記憶體洩漏。判斷資料庫中存在記憶體洩漏時需要注意以下幾點：

   系統中的記憶體在逐漸減少，甚至導致換頁（paging）；

   SQL語句越來越慢（經常是換頁導致）；

   隨著時間增長，由DB2分配的記憶體在不斷增加。

9.3.1  記憶體洩漏診斷方法

    正如之前介紹的，從DB2 V9.5版本開始，可以很輕鬆地檢查當前各個記憶體集的使用情況。每個資料庫分割槽都會保留當前分割槽中記憶體使用資訊的實時狀態。其中包括所有的私有記憶體、應用 記憶體、資料庫共享記憶體、資料庫管理器共享記憶體及分割槽間共享的FCM記憶體。我們可以透過db2pd -dbptnmem命令觀察當前資料庫中各部分記憶體的使用情況。輸出結果如下：

>>>> Database Partition 0 Memory Statistics <<<<

Controller Enabled:   Y

Controller Automatic: Y

Memory Limit:         28609436 KB  記憶體限制（INSTANCE_MEMORY的值）

Current usage:        898240 KB       當前記憶體的使用量

HWM usage:            898240 KB 記憶體最高使用量

Cached memory:        271424 KB   總共的快取大小

 

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Mem Cached (KB)

============================================================

APPL-LIAM               160000        160000          159616

DBMS-lfinnie             36608         36608            3648

FMP_RESOURCES            22528         22528               0

PRIVATE                  12032         12032             256

FCM_RESOURCES            10048         10048               0

LCL-p1130882               128           128               0

DB-LIAM                 656896        656896          107904

    可以看到當前分割槽中INSTANCE_MEMORY的限制值大約為28GB，當前記憶體總共使用了898MB大小。其中271MB作為申請後並未使用的內 存，意味著當前已經提交的記憶體大約為627MB。如果將這些記憶體按照記憶體集分開觀察，可以分為以下幾個部分：Mem Used代表向作業系統中已經申請的記憶體部分，DB2會提前向作業系統申請部分多餘記憶體；Mem Used區域是目前可以被DB2分配給各個記憶體池的總共大小；CACHED部分是還沒有被任何記憶體池佔用的記憶體數量，同時也代表當作業系統中記憶體不足時， 可以釋放的最大記憶體數量。

    對於單次呼叫db2pd -dbptnmem命令所得到的輸出結果，很難從中發現當前是否存在潛在的記憶體洩漏問題。還需要了解以下情況來證實我們的猜測：

   如果沒有任何啟用的應用程式，實際提交的應用共享記憶體應該非常小（Used -Cached）；

   如果沒有任何監控開關開啟，則例項共享記憶體的值應該非常小；

   私有記憶體的大小應該根據執行緒數量浮動。如果發現每個執行緒需要消耗1～2MB的記憶體，代表可能會發生記憶體洩漏（除非啟用私有排序，如果啟用私有排序，則每個執行緒消耗的記憶體應該不大於SORTHEAP定義的值）；

   對於單分割槽例項，FCM_RESOURCES應該是不變的。對於多分割槽例項，如果存在大量跨節點連線的操作，則FCM_RESOURCES會佔用相當大一部分記憶體；

   對於資料庫共享記憶體，其記憶體集大小應該等於內部所有記憶體池之和（緩衝池、鎖列表、Package cache等）。

如果有緩慢的記憶體洩露發生，僅僅祈禱是不起作用的，需要定期檢查當前正在使用的記憶體量。在開發環境中，以下幾個關鍵點在對記憶體洩露檢查時非常有用。例如：

   資料庫啟用之後，還沒有任何應用程式連線時；

   應用程式準備執行前；

   應用程式執行過程中；

   應用程式執行後。

    判斷記憶體洩漏需要定期地對比當前資料庫中的記憶體情況。在應用程式數量、執行語句型別及資料量變化不大的情況下，記憶體的使用應該也會保持恆定。如果發現記憶體 在緩慢增長，則可以認為是記憶體出現洩漏。但是有一點例外，當啟用STMM功能且資料庫引數DATABASE_MEMORY是自動調整的情況下，可能會出現 資料庫共享記憶體會基於資料庫中的空閒記憶體數量增加或減少，但是其他記憶體集不會受到任何影響。

作者簡介

    洪燁，畢業於湖南大學，曾在IBM擔任資料庫現場技術支援，為北京移動、北京聯通、中國銀聯、中國銀行、中信銀行、聯想集團、國家氣象局、中華聯合保險、 唐山商業銀行等多家企業進行DB2故障診斷、技術支援以及技術培訓工作，擁有DB2開發、高階管理以及AIX管理等多項國際認證。

    並在DB2China資料庫論壇擔任熱點討論版塊版主，主持多次熱點討論以及專家現場診斷，擅長DB2資料庫及相關產品的效能調優及故障分析，對DB2技能及實踐經驗有多年積累。

    近年來與多位業界專家一直在積極推動DB2領域的技術交流，真正理解DB2技術人員真正的需求與痛楚，是DB2系統知識及技巧精髓的熱心分享者及貢獻者。

 

前言

寫作背景

    作為最早出現的關係型資料庫，DB2提供了高層次的資料利用性、完整性、安全性、可恢復性，以及小規模到大規模應用程式的執行能力。並且DB2以擁有一個 非常完備的查詢最佳化器而著稱，其外部連線改善了查詢效能，並支援多工並行查詢。近幾年來，為了應對爆炸式增長的資料量，DB2也在進行推陳出新，不斷推 出完美應對大資料的技術，並持續增強DB2的效能以及可用性。到本書出版之時，DB2已經推出了V10.5版本，並在該版本中提供了列式儲存功能及更加強 大的效能提升。

    DB2隨著一代代版本的發展，已經成為一個龐然大物，其內部元件眾多，對於SQL語句的處理有時甚至需要調動數十個內部元件進行相互協同。當遇到效能問題 時更需要對內部原理有詳細的瞭解才能進行有效的處理以及應對，否則就會讓人感到無從下手。與Oracle資料庫相比，DB2資料庫的資料顯得相對匱乏，其 中鮮有對其內部執行機制進行詳細剖析的資料，這也給DB2的傳播發展帶來了一定的弊端。因此對於這種情況，儘可能地將自己所知分享出來的想法從我腦中湧 現，儘可能地對DB2處理過程在流程上進行闡述的念頭一直支援著我完成本書的寫作。

    在常見的資料庫問題中，效能問題不僅出現的頻率較高並且很多生產系統中並不存在一個對效能問題進行隔離的高可用機制，正因為如此，在很多關鍵行業的系統 中，效能問題往往成為影響生產系統正常執行的最大因素。而效能問題的影響時間有時長達數小時，這樣不僅給生產系統帶來了極大的負面影響，也使業務很難正常 進行。

    由於效能問題的特殊性，很多情況下問題發生過後並沒有留下任何有效的故障資訊，這又給之後的分析處理帶來了一定的難度和困擾，往往並無相關線索可以對其進 行求證。而對於效能問題的資訊收集及調優方法，目前關於DB2效能調優的資料過於分散，並沒有提供一個很完善的效能解決體系，甚至有些資料中僅僅在套用各 個KPI指標對效能問題進行調節，雖然這種方式有時能夠解決一些問題，但在更多時候會顯得無能為力。

    在本書中，透過對DB2資料庫的內部機制進行探討，希望能夠讓讀者更好地理解資料庫的執行機制，當效能問題發生時能夠在腦海中對資料庫的整體運作機制有個清晰的認識，這樣才能夠直接有效地對資料庫進行分析與最佳化。

    感謝讀者選擇本書，筆者水平有限，書中錯漏在所難免，敬請讀者朋友諒解，並期望讀者朋友進行指正。

本書結構

    全書分為5大篇共13章。第1篇主要對效能問題的定義、影響效能問題的因素、DB2的整體元件結構，以及對於各種型別語句的處理機制進行詳細的探討；第2 篇主要針對DB2提供的各個監控工具進行闡述，並提供了一些監控建議；第3篇主要闡述DB2的內部執行機制及各個元件的原理；第4篇包含DB2中內部工具 的最佳化與執行機制，以及DB2在各個平臺中需要注意的效能引數；第5篇對效能最佳化思路進行了概括性的總結。

第1篇  效能定義及整體架構

第 1 章主要對效能問題的目標進行了闡述和定義，並描述了可能影響各個工作負載的特徵，以及可能對其產生效能影響的因素。

第2章對DB2的體系結構進行了基本介紹，並描述了DB2各個元件處理SQL語句的基本原理與機制。

第2篇  效能監控工具及監控技巧

第3章按照監控特性對DB2提供的監控工具進行了基本介紹，並介紹了一些基本的監控技巧。

第3篇  效能分析及內部原理剖析

第4章對最佳化器的原理進行了探討，闡述了最佳化器的重寫機制、最佳化原理及編譯原理，並介紹瞭如何檢查最佳化器的估算結果的兩種方法。

第5章介紹瞭解決最佳化器編譯問題的的7種效能最佳化武器，以及何時且如何才能有效地使用這些武器解決實際問題。

第6章描述了為了避免效能問題應該如何對資料庫表及索引進行有效設計，針對合適的場景使用適合的技術才能更有效地避免效能問題的發生。

第7章詳細描述了DB2資料庫的I/O原理，I/O效能通常是資料庫執行過程中最為耗時的一環，本章詳細介紹了DB2相關I/O情景，以及如何有效地提高I/O效能。

第8章詳細介紹了DB2中各個記憶體池的分配以及作用，並講述了怎樣定位及修復記憶體洩露的方法。

第9章對資料庫的物理結構進行了詳細剖析，並講解了各種情況下物理結構對於資料庫效能的負面影響及避免方法。

第10章對DB2中鎖及latch等待事件進行了描述與分析，並分享部分等待事件解決案例及心得。

第4篇  實用工具調優及作業系統最佳化

第11章講述了backup、restore、export、import、load、reorg、runstats等DB2提供的多種實用工具的執行原理以及效能調優方法。

第12章介紹了AIX及Windows平臺上CPU、記憶體、磁碟I/O及網路等方面的相關最佳化引數。

第5篇  效能分析思路及最佳化總結

第13章對效能分析思路進行了歸納與總結，並按照資源佔用問題及響應時間緩慢的問題對資料庫效能問題提供了整體分析的思路與解決方案。