DB2培訓講義_DB2性能優(yōu)化入門(共31張)(PPT 31頁)

1、DB2培訓講義性能優(yōu)化入門第1頁，共31頁。有關的概念第2頁，共31頁。DB2 性能優(yōu)化的三個方面內(nèi)存CPUI/O3第3頁，共31頁。內(nèi)存因素在內(nèi)存方面，主要是考慮緩沖池 (BUFFERPOOL) 的使用。緩沖池是一片用來緩沖從磁盤上讀取的數(shù)據(jù)和索引的內(nèi)存區(qū)域，這些數(shù)據(jù)和索引信息在緩沖池中進行運算后最終還要寫回磁盤。緩沖池的頁面大小有四種 (4K,8K,16K,32K)，分別對應四種不同頁面大小的表空間。緩沖池的大小決定了能夠從磁盤上緩沖數(shù)據(jù)的容量大小。當然緩沖池也不是越大越好，緩沖池過大可能會導致連接數(shù)據(jù)庫的時間過長，因為在連接數(shù)據(jù)庫時要為數(shù)據(jù)庫的緩沖池分配內(nèi)存空間。可以通過計算緩沖池的命中

2、率來評估緩沖池的使用效率：緩沖池命中率 =(1-( 數(shù)據(jù)物理讀 + 索引物理讀 )/( 數(shù)據(jù)邏輯讀 + 索引邏輯讀 ) *100%，緩沖池命中率越大說明緩沖池的使用效率高。緩沖池命中率太小說明緩沖池太小應當調(diào)大。其中的數(shù)據(jù)物理讀，索引物理讀以及數(shù)據(jù)邏輯讀和索引邏輯讀都可以從緩沖池的快照中獲取。 4第4頁，共31頁。內(nèi)存因素在內(nèi)存方面要考慮的另外幾個重要因素是排序堆 (SORTHEAP)，鎖列表 (LOCKLIST), 日志緩沖區(qū) (LOGBUFSZ) 。排序堆在查詢結果帶有排序選項而沒有相關索引對應時將會被使用，排序堆太小會產(chǎn)生排序溢出 (Overflowed), 那些在排序堆中裝不下的排序數(shù)

3、據(jù)將會溢出到一個臨時表中，這會使性能下降。與 SORTHEAP 參數(shù)相關的是 SHEAPTHRES_SHR 和 SHEAPTHRES，SHEAPTHRES_SHR 限制了一個數(shù)據(jù)庫中共享排序的最大內(nèi)存，SHEAPTHRES 限制了私有排序的最大內(nèi)存。 LOCKLIST 指的是一個數(shù)據(jù)庫中用來存放鎖的內(nèi)存空間，當這個參數(shù)設得過小會導致在鎖用光這部分資源后導致鎖升級（即多個行鎖轉(zhuǎn)化為一個表鎖來釋放出更多的資源）。這會導致系統(tǒng)的并行性下降，很多應用連接出現(xiàn)掛起，使得系統(tǒng)的性能衰退。5第5頁，共31頁。內(nèi)存因素所以盡可能調(diào)大 LOCKLIST 參數(shù)，這里需要指出： LOCKLIST 指的并不是鎖的個數(shù)

4、，而是以數(shù)據(jù)庫頁為單位的一片內(nèi)存區(qū)域（在 32 位系統(tǒng)中每個鎖需要 96 個字節(jié)，在 64 位系統(tǒng)中每個鎖需要 128 個字節(jié)）。與 LOCKLIST 參數(shù)對應的是 MAXLOCKS 參數(shù)，MAXLOCKS 定義的是一個百分數(shù)，它指定了一個應用程序所能占用的最大的鎖空間占 LOCKLIST 的比例。日志緩沖區(qū) (LOGBUFSZ) 指的是日志在寫到磁盤以前用于緩沖的一片內(nèi)存空間，這樣可以減少寫日志帶來的過多的 I/O 。6第6頁，共31頁。內(nèi)存因素從版本 9 以后 DB2 推出了一個新特性自調(diào)節(jié)內(nèi)存管理器 (STMM: Self Tuning Memory Manager), 這個特性使得很多

5、內(nèi)存參數(shù)如前面所述的 SORTHEAP，LOCKLIST，LOGBUFSZ 等進行自動調(diào)節(jié)，當數(shù)據(jù)庫參數(shù) SELF_TUNING_MEM 設為 ON, 這些參數(shù)設為 AUTOMATIC 即可以進行自動調(diào)整。這樣可以節(jié)省很多人工調(diào)整的時間。7第7頁，共31頁。CPU 因素關于 CPU 因素首先是考慮 DB2 優(yōu)化器 (OPTIMIZER) 對訪問計劃 (ACCESS PLAN) 的分析與優(yōu)化。一般來說，一條 SQL 在執(zhí)行時首先會被解析，然后進行語義分析，進而重寫 SQL, 優(yōu)化器會對重寫過的 SQL 進行基于成本的分析最終選擇最有效的訪問計劃。最終生成可執(zhí)行代碼（執(zhí)行計劃）來執(zhí)行這條語句。查詢

6、訪問計劃的工具有很多，既有圖形化工具 Visual Explain，也有命令 db2exfmt 來格式化解釋表 (Explain tables) 中的數(shù)據(jù)生成 ACCESS PLAN 。還有命令 db2expln 查詢 ACCESS PLAN 。 8第8頁，共31頁。CPU 因素在 DB2 里的優(yōu)化級別分為九級，缺省是第五級，級別越高優(yōu)化器分析得程度越深。這個級別有數(shù)據(jù)庫配置參數(shù) DFT_QUERYOPT 決定。并不是級別設得越高性能越好，因為對于一些較為簡單的 SQL 語句，如果優(yōu)化級別過高那么花在優(yōu)化 SQL 上的時間就會過長，而執(zhí)行時間相對來說很短，有些得不償失。在選擇訪問計劃時，索引掃

7、描的效率往往會比表掃描要高，所以索引的優(yōu)化也是值得注意的。正確的建立索引會使查詢性能大幅度的提高。 9第9頁，共31頁。CPU 因素在 DB2 中連接 (JOIN) 分為三種：嵌套循環(huán)連接 (nest-loop join), 合并連接 (merge-join), 散列表連接 (hash-join) 。一般來說效率最低的是嵌套循環(huán)連接，這種連接采用的是笛卡兒集，進行多次循環(huán)遍歷得到結果。而合并連接和散列表連接只進行一次循環(huán)遍歷，相對來說效率較高。其中散列表連接可以采用多個等式做為條件而合并連接只能采用單個等式作為條件。但是在有索引掃描的情況下嵌套循環(huán)連接效率則更高。當優(yōu)化級別等于零時，連接只能采

8、用嵌套循環(huán)連接， 當優(yōu)化級別大于等于 1 時，連接可以采用合并連接。當優(yōu)化級別大于 5 時連接可以采用散列表連接。散列表連接要求 SORTHEAP 比較大，因為要為生成散列表準備空間。 10第10頁，共31頁。CPU 因素在考慮 CPU 因素時還要考慮 CPUSPEED 這個參數(shù)，這個參數(shù)標明了 CPU 的運行速度，它會幫助優(yōu)化器評估最好的訪問計劃。一般來說這個參數(shù)設為 -1，優(yōu)化器將自動計算 CPU 的速度。另外運用多分區(qū)的特性可以把一個數(shù)據(jù)庫分布到多臺機器上，這樣可以充分利用多臺機器的 CPU 的資源對應用程序的事務進行并行處理，從而提高數(shù)據(jù)庫的性能。11第11頁，共31頁。I/O 因素關

9、于 I/O 因素要考慮以下幾個方面：首先是磁盤的 I/O, 為了能夠最大化磁盤的 I/O 可以把數(shù)據(jù)，索引以及日志分別放在不同的硬盤上。因為在一個事務中數(shù)據(jù)和索引可能需要同時訪問，而在事務提交時，數(shù)據(jù)和日志要同時寫入磁盤，而且有可能索引也要同步維護，所以將它們放在不同的硬盤上可以使它們的讀寫并行運行，從而不致使磁盤成為瓶頸。同時選擇數(shù)據(jù)庫管理表空間 (DMS) 要比系統(tǒng)管理表空間 (SMS) 性能要好，因為讀寫 SMS 需要經(jīng)過操作系統(tǒng)的 cache 再到緩沖池，而可以采用裸設備的 DMS 則不需要。但是 DMS 相對 SMS 來說維護起來較麻煩。 12第12頁，共31頁。I/O 因素其次要考

10、慮的是日志文件的大小，當數(shù)據(jù)庫在寫事務日志時當一個日志文件寫滿后會轉(zhuǎn)向另外一個日志文件，這種日志文件的切換會造成操作系統(tǒng)上的開銷。所以應當盡量將日志文件大?。↙OGFILSIZ）設得大一些，這樣可以減少日志文件切換的次數(shù)。但是日志文件過大難免會造成一些空間的浪費。 13第13頁，共31頁。I/O 因素同時也要考慮到隔離級別的因素，在 DB2 中隔離級別分成 4 級：可重復的讀，讀穩(wěn)定性，游標穩(wěn)定性和未提交的讀。這四種級別逐個降低。越高的隔離級別越能保證數(shù)據(jù)完整性，但卻會降低并發(fā)性，所以應當綜合權衡后做出決定。隔離級別可以通過如下命令來改變：CHANGE ISOLATION TO=CS|RR|R

11、S|UR在連接方面還要考慮到代理和連接的關系，這也會影響到數(shù)據(jù)庫的并發(fā)性。 最后要考慮的還是關于多分區(qū)的特性。在多分區(qū)數(shù)據(jù)庫中，一個請求首先傳到協(xié)調(diào)分區(qū)，然后由協(xié)調(diào)分區(qū)將請求細分成多個部分發(fā)送到其他分區(qū)，這樣數(shù)據(jù)可以在各個分區(qū)進行并行讀寫，實現(xiàn) I/O 最大化。14第14頁，共31頁。性能優(yōu)化工具在 DB2 中有很多和性能優(yōu)化相關的工具和命令，下面簡單地介紹幾種：SNAPSHOTDB2PDRUNSTATSREORGDB2DARTDB2SUPPORT15第15頁，共31頁。性能優(yōu)化工具SNAPSHOT : 這是 DB2 獲取數(shù)據(jù)庫信息快照的一種方法。它能夠獲取在數(shù)據(jù)庫中關于緩沖池，鎖，排序以及

12、SQL 等等信息。 DBA 可以通過獲取這些信息來對數(shù)據(jù)庫中的各組件進行評估來分析問題的瓶頸。 DB2PD : 這個命令是用來分析數(shù)據(jù)庫的當前狀態(tài)，它帶有很多參數(shù)。可以用來分析應用程序，代理，內(nèi)存塊，緩沖池，日志及鎖狀態(tài)等信息。 RUNSTATS : 這個命令是用來收集數(shù)據(jù)庫中數(shù)據(jù)的最新統(tǒng)計信息，并更新到系統(tǒng)表中。更新統(tǒng)計信息將會促使優(yōu)化器選擇更加符合實際的高效的訪問計劃，從而提高工作效率。 REORG : 這個命令用來重新整理數(shù)據(jù)庫中數(shù)據(jù)和索引的碎片，使其在物理上可以得以按一定規(guī)則排列，這樣可以加快檢索的速度。16第16頁，共31頁。性能優(yōu)化工具DB2DART : 這個命令是一個數(shù)據(jù)庫的分析

13、和報告工具，它用來檢查表空間，索引以及數(shù)據(jù)庫結構的正確性，分析在性能問題上的一些原因。 DB2SUPPORT : 這個命令用來收集 DB2 和操作系統(tǒng)的所有相關信息并生成一個壓縮文件，可傳送給優(yōu)化人員進行分析。 17第17頁，共31頁。DB2 性能問題分類與分析思路 第18頁，共31頁。DML性能問題：查詢優(yōu)化DML(Data Manipulation Language) 包括了查詢，增加，刪除和更新紀錄等操作。首先看一下查詢的性能問題，在查詢一張表或多張表的聯(lián)合查詢時有時反應時間會比較長，這使得用戶難以忍受。針對這種問題，可以通過下述方法來分析：在查詢的連接或條件子句中的相關字段是否加了索引

14、。察看緩沖池的大小，緩沖池太小會造成很多數(shù)據(jù)不能讀到緩沖池而直接從硬盤上讀取，造成很大的瓶頸。另一方面關于緩沖池預取的設置，一般能將預取大小 (PREFETCHSIZE) 設定為區(qū)段大小與容器個數(shù)的積，這樣可以最大利用到預取的并行性。 在查詢中涉及到 order by 字句時，如果排序的字段沒有設置索引那么排序?qū)玫絻?nèi)存中的排序堆 (sortheap) 。如果排序堆過小會造成排序溢出到硬盤上 (Overflowed) 造成性能衰退。 19第19頁，共31頁。DML性能問題：查詢優(yōu)化同時還要考慮到 RUNSTATS/REORG 因素。 RUNSTATS 命令可以更新表中的統(tǒng)計信息。當表中的數(shù)據(jù)

15、經(jīng)過頻繁的增刪改后其相應的統(tǒng)計信息會發(fā)生變化，而優(yōu)化器選擇執(zhí)行計劃的時候是根據(jù)這種統(tǒng)計信息來計算的，所以運行 RUNSTATS 此時顯得尤為重要。 REORG 可以整理數(shù)據(jù)存儲的物理結構，也能減少數(shù)據(jù)掃描的時間，提高查詢的性能。從存儲方面應當注意的是選取裸設備的 DMS 要比 SMS 性能要好，因為它少了一層文件系統(tǒng)的緩沖而直接訪問緩沖池。 學會使用 optimize for n rows 子句，它可以提高前面 n 條記錄的顯示速度。這樣可以使用戶能夠先快速查看這 n 條記錄，然后再看其他紀錄。減少了用戶的等待時間。 20第20頁，共31頁。DML性能問題：查詢優(yōu)化針對復雜查詢時可以將數(shù)據(jù)庫配

16、置參數(shù) DFT_QUERYOPT( 缺省查詢優(yōu)化類 ) 的值設得高一些（7 或 9），針對簡單查詢可以將它設得低一些 (3 或 5)，因為設置越高優(yōu)化器所作的分析就越深入，耗費在生成計劃上的時間就越多。 針對 C/S 結構的查詢可以將查詢語句寫在服務器端生成存儲過程來減少數(shù)據(jù)的網(wǎng)絡傳輸以及客戶端的壓力。而經(jīng)過編譯的存儲過程執(zhí)行得更加高效。 21第21頁，共31頁。DML性能問題：查詢優(yōu)化還要考慮到隔離級別與鎖的因素，隔離級別越高越能保證數(shù)據(jù)的完整性，但同時會減弱并發(fā)性。這一點需要權衡需求而定。 網(wǎng)絡因素也不可忽視，將數(shù)據(jù)庫服務器參數(shù) RQRIOBLK 設為 65534 可以相應地提高網(wǎng)絡吞吐量

17、。（缺省值 32767） 最后需要考慮的是數(shù)據(jù)庫的結構，在某些情況下，在某些表中增加一些冗余字段雖然犧牲了一些空間和維護成本，但是在查詢時可以減少很多連接操作，這樣可以大大提高查詢性能。就是用空間換取時間。 22第22頁，共31頁。DML性能問題：增刪改優(yōu)化首先是索引因素，在做增刪改時數(shù)據(jù)庫會對表中的索引做相應的修改。這會消耗一定的資源，所以在保證數(shù)據(jù)完整性的前提下可以先將索引刪除，待到增刪改結束后再重建這些索引。這也會節(jié)省一些時間。將索引和數(shù)據(jù)放在不同的硬盤上也可以增加寫操作的并行性。 其次要考慮日志因素，在數(shù)據(jù)寫操作的同時，數(shù)據(jù)庫系統(tǒng)也在維護著事務日志，所以應盡量減少日志維護的代價。將 a

18、uto commit 設為 false，可以減少提交的次數(shù)（同時也減少了寫日志的次數(shù)）。增大 LOGBUFSZ,LOGFILSZ 可以減少刷新日志的次數(shù)以及日志文件切換的次數(shù)?；蛘邔⒈淼膶傩愿臑椤?ACTIVATE NOT LOGGED INITIALLY ” , 這樣可以屏蔽表的日志操作，以提高寫操作的性能，但是失去事務日志的表的數(shù)據(jù)很難修復，這一點需要權衡。23第23頁，共31頁。DML性能問題：增刪改優(yōu)化將日志和數(shù)據(jù)分別放在不同的硬盤上也可以增加寫操作的并行性。在插入記錄時采用 APPEND MODE 可以消除 DB2 尋找表中間的空余空間的時間而直接插到表尾，從而提高插入的性能。 還要

19、考慮到約束和觸發(fā)器的影響，在寫操作時應當盡量避免表中有約束和觸發(fā)器。在保證數(shù)據(jù)完整性的前提下可在頻繁大批量寫操作時先將約束或觸發(fā)器去除，完畢后重建。 和查詢一樣，寫操作同樣要考慮到隔離級別和鎖的因素。24第24頁，共31頁。DB2 實用程序的性能優(yōu)化 第25頁，共31頁。如何提高備份操作的性能？提高數(shù)據(jù)庫配置參數(shù) UTIL_HEAP_SZ 的大小，這個內(nèi)存區(qū)域用來為備份和恢復操作提供緩沖。 減少整庫備份，多采用表空間備份需要的表空間。 減少完全備份，多采用增量備份或 DELTA 備份。 增加備份命令中的 PARALLELISM 參數(shù)來增加備份的并行性（增加線程或進程）。 增加備份命令中的 BUFFER 參數(shù)值。 增加備份的目標目錄，最好能將多個目錄放在不同的硬盤上，這樣可以增加備份的并行程度。 26第26頁，共31頁。如何提高恢復操作的性能？和備份操作一樣，需要增大數(shù)據(jù)庫配置參數(shù) UTIL_H