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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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