【51CTO綜述】IBM 為社區(qū)提供了DB2 免費(fèi)版本 DB2 Express-C，它提供了與 DB2 Express Edition 相同的核心數(shù)據(jù)特性，為構(gòu)建和部署應(yīng)用程序奠定了堅(jiān)實(shí)的基礎(chǔ)。

DB2 性能優(yōu)化是一件較為復(fù)雜的綜合性的工作 , 需要對(duì)問(wèn)題的根源作全方位的探索和思考。同時(shí)也需要較深厚的數(shù)據(jù)庫(kù)管理經(jīng)驗(yàn)與優(yōu)化知識(shí)。這對(duì)于初學(xué)者來(lái)說(shuō)可能有些勉為其難。但是在很多情況下，隨著 DB2 數(shù)據(jù)庫(kù)中的數(shù)據(jù)量的不斷增長(zhǎng)或者用戶數(shù)的激增，數(shù)據(jù)庫(kù)系統(tǒng)的性能會(huì)顯著下降，而此時(shí)快速定位性能上的瓶頸則至關(guān)重要。下面簡(jiǎn)要地介紹一下 DB2 的調(diào)優(yōu)的一些因素和工具，以及一些原理，使初學(xué)者對(duì)性能優(yōu)化能夠有一個(gè)大致的了解。

DB2 的性能優(yōu)化可以從三個(gè)方面分析：內(nèi)存，CPU 和 I/O 。

內(nèi)存因素

在內(nèi)存方面，主要是考慮緩沖池 (BUFFERPOOL) 的使用。緩沖池是一片用來(lái)緩沖從磁盤上讀取的數(shù)據(jù)和索引的內(nèi)存區(qū)域，這些數(shù)據(jù)和索引信息在緩沖池中進(jìn)行運(yùn)算后最終還要寫回磁盤。緩沖池的頁(yè)面大小有四種 (4K,8K,16K,32K)，分別對(duì)應(yīng)四種不同頁(yè)面大小的表空間。緩沖池的大小決定了能夠從磁盤上緩沖數(shù)據(jù)的容量大小。當(dāng)然緩沖池也不是越大越好，緩沖池過(guò)大可能會(huì)導(dǎo)致連接數(shù)據(jù)庫(kù)的時(shí)間過(guò)長(zhǎng)，因?yàn)樵谶B接數(shù)據(jù)庫(kù)時(shí)要為數(shù)據(jù)庫(kù)的緩沖池分配內(nèi)存空間?？梢酝ㄟ^(guò)計(jì)算緩沖池的命中率來(lái)評(píng)估緩沖池的使用效率：緩沖池命中率 =(1-(( 數(shù)據(jù)物理讀 + 索引物理讀 )/( 數(shù)據(jù)邏輯讀 + 索引邏輯讀 ))) *100%，緩沖池命中率越大說(shuō)明緩沖池的使用效率高。緩沖池命中率太小說(shuō)明緩沖池太小應(yīng)當(dāng)調(diào)大。其中的數(shù)據(jù)物理讀，索引物理讀以及數(shù)據(jù)邏輯讀和索引邏輯讀都可以從緩沖池的快照中獲取。

在內(nèi)存方面要考慮的另外幾個(gè)重要因素是排序堆 (SORTHEAP)，鎖列表 (LOCKLIST), 日志緩沖區(qū) (LOGBUFSZ) 。排序堆在查詢結(jié)果帶有排序選項(xiàng)而沒(méi)有相關(guān)索引對(duì)應(yīng)時(shí)將會(huì)被使用，排序堆太小會(huì)產(chǎn)生排序溢出 (Overflowed), 那些在排序堆中裝不下的排序數(shù)據(jù)將會(huì)溢出到一個(gè)臨時(shí)表中，這會(huì)使性能下降。與 SORTHEAP 參數(shù)相關(guān)的是 SHEAPTHRES_SHR 和 SHEAPTHRES，SHEAPTHRES_SHR 限制了一個(gè)數(shù)據(jù)庫(kù)中共享排序的***內(nèi)存，SHEAPTHRES 限制了私有排序的***內(nèi)存。 LOCKLIST 指的是一個(gè)數(shù)據(jù)庫(kù)中用來(lái)存放鎖的內(nèi)存空間，當(dāng)這個(gè)參數(shù)設(shè)得過(guò)小會(huì)導(dǎo)致在鎖用光這部分資源后導(dǎo)致鎖升級(jí)(即多個(gè)行鎖轉(zhuǎn)化為一個(gè)表鎖來(lái)釋放出更多的資源)。這會(huì)導(dǎo)致系統(tǒng)的并行性下降，很多應(yīng)用連接出現(xiàn)掛起，使得系統(tǒng)的性能衰退。所以盡可能調(diào)大 LOCKLIST 參數(shù)，這里需要指出 LOCKLIST 指的并不是鎖的個(gè)數(shù)，而是以數(shù)據(jù)庫(kù)頁(yè)為單位的一片內(nèi)存區(qū)域(在 32 位系統(tǒng)中每個(gè)鎖需要 96 個(gè)字節(jié)，鎖上加鎖的話每個(gè)鎖則需 48 個(gè)字節(jié)。在 64 位系統(tǒng)中每個(gè)鎖需要 128 個(gè)字節(jié)，鎖上加鎖的話每個(gè)鎖則需 64 個(gè)字節(jié))。與 LOCKLIST 參數(shù)對(duì)應(yīng)的是 MAXLOCKS 參數(shù)，MAXLOCKS 定義的是一個(gè)百分?jǐn)?shù)，它指定了一個(gè)應(yīng)用程序所能占用的***的鎖空間占 LOCKLIST 的比例。日志緩沖區(qū) (LOGBUFSZ) 指的是日志在寫到磁盤以前用于緩沖的一片內(nèi)存空間，這樣可以減少寫日志帶來(lái)的過(guò)多的 I/O 。

從版本 9 以后 DB2 推出了一個(gè)新特性自調(diào)節(jié)內(nèi)存管理器 (STMM: Self Tuning Memory Manager), 這個(gè)特性使得很多內(nèi)存參數(shù)如前面所述的 SORTHEAP，LOCKLIST，LOGBUFSZ 等進(jìn)行自動(dòng)調(diào)節(jié)，當(dāng)數(shù)據(jù)庫(kù)參數(shù) SELF_TUNING_MEM 設(shè)為 ON, 這些參數(shù)設(shè)為 AUTOMATIC 即可以進(jìn)行自動(dòng)調(diào)整。這樣可以節(jié)省很多人工調(diào)整的時(shí)間。

CPU 因素

關(guān)于 CPU 因素首先是考慮 DB2 優(yōu)化器 (OPTIMIZER) 對(duì)訪問(wèn)計(jì)劃 (ACCESS PLAN) 的分析與優(yōu)化。一般來(lái)說(shuō)，一條 SQL 在執(zhí)行時(shí)首先會(huì)被解析，然后進(jìn)行語(yǔ)義分析，進(jìn)而重寫 SQL, 優(yōu)化器會(huì)對(duì)重寫過(guò)的 SQL 進(jìn)行基于成本的分析最終選擇最有效的訪問(wèn)計(jì)劃。最終生成可執(zhí)行代碼(執(zhí)行計(jì)劃)來(lái)執(zhí)行這條語(yǔ)句。查詢?cè)L問(wèn)計(jì)劃的工具有很多，既有圖形化工具 Visual Explain，也有命令 db2exfmt 來(lái)格式化解釋表 (Explain tables) 中的數(shù)據(jù)生成 ACCESS PLAN 。還有命令 db2expln 查詢 ACCESS PLAN 。

在 DB2 里的優(yōu)化級(jí)別分為九級(jí)，缺省是第五級(jí)，級(jí)別越高優(yōu)化器分析得程度越深。這個(gè)級(jí)別有數(shù)據(jù)庫(kù)配置參數(shù) DFT_QUERYOPT 決定。并不是級(jí)別設(shè)得越高性能越好，因?yàn)閷?duì)于一些較為簡(jiǎn)單的 SQL 語(yǔ)句，如果優(yōu)化級(jí)別過(guò)高那么花在優(yōu)化 SQL 上的時(shí)間就會(huì)過(guò)長(zhǎng)，而執(zhí)行時(shí)間相對(duì)來(lái)說(shuō)很短，有些得不償失。在選擇訪問(wèn)計(jì)劃時(shí)，索引掃描的效率往往會(huì)比表掃描要高，所以索引的優(yōu)化也是值得注意的。正確的建立索引會(huì)使查詢性能大幅度的提高。

在 DB2 中連接 (JOIN) 分為三種：嵌套循環(huán)連接 (nest-loop join), 合并連接 (merge-join), 散列表連接 (hash-join) 。一般來(lái)說(shuō)效率***的是嵌套循環(huán)連接，這種連接采用的是笛卡兒集，進(jìn)行多次循環(huán)遍歷得到結(jié)果。而合并連接和散列表連接只進(jìn)行一次循環(huán)遍歷，相對(duì)來(lái)說(shuō)效率較高。其中散列表連接可以采用多個(gè)等式做為條件而合并連接只能采用單個(gè)等式作為條件。但是在有索引掃描的情況下嵌套循環(huán)連接效率則更高。當(dāng)優(yōu)化級(jí)別等于零時(shí)，連接只能采用嵌套循環(huán)連接， 當(dāng)優(yōu)化級(jí)別大于等于 1 時(shí)，連接可以采用合并連接。當(dāng)優(yōu)化級(jí)別大于 5 時(shí)連接可以采用散列表連接。散列表連接要求 SORTHEAP 比較大，因?yàn)橐獮樯缮⒘斜頊?zhǔn)備空間。

在考慮 CPU 因素時(shí)還要考慮 CPUSPEED 這個(gè)參數(shù)，這個(gè)參數(shù)標(biāo)明了 CPU 的運(yùn)行速度，它會(huì)幫助優(yōu)化器評(píng)估***的訪問(wèn)計(jì)劃。一般來(lái)說(shuō)這個(gè)參數(shù)設(shè)為 -1，優(yōu)化器將自動(dòng)計(jì)算 CPU 的速度。另外運(yùn)用多分區(qū)的特性可以把一個(gè)數(shù)據(jù)庫(kù)分布到多臺(tái)機(jī)器上，這樣可以充分利用多臺(tái)機(jī)器的 CPU 的資源對(duì)應(yīng)用程序的事務(wù)進(jìn)行并行處理，從而提高數(shù)據(jù)庫(kù)的性能。

I/O 因素

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

其次要考慮的是日志文件的大小，當(dāng)數(shù)據(jù)庫(kù)在寫事務(wù)日志時(shí)當(dāng)一個(gè)日志文件寫滿后會(huì)轉(zhuǎn)向另外一個(gè)日志文件，這種日志文件的切換會(huì)造成操作系統(tǒng)上的開(kāi)銷。所以應(yīng)當(dāng)盡量將日志文件大小(LOGFILSIZ)設(shè)得大一些，這樣可以減少日志文件切換的次數(shù)。但是日志文件過(guò)大難免會(huì)造成一些空間的浪費(fèi)。

同時(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í)別可以通過(guò)如下命令來(lái)改變：

CHANGE ISOLATION TO=CS|RR|RS|UR

在連接方面還要考慮到代理和連接的關(guān)系，這也會(huì)影響到數(shù)據(jù)庫(kù)的并發(fā)性，具體信息可以參考資源部分。

***要考慮的還是關(guān)于多分區(qū)的特性。在多分區(qū)數(shù)據(jù)庫(kù)中，一個(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 ***化。

性能優(yōu)化相關(guān)工具

在 DB2 中有很多和性能優(yōu)化相關(guān)的工具和命令，下面簡(jiǎn)單地介紹幾種：

• SNAPSHOT : 這是 DB2 獲取數(shù)據(jù)庫(kù)信息快照的一種方法。它能夠獲取在數(shù)據(jù)庫(kù)中關(guān)于緩沖池，鎖，排序以及 SQL 等等信息。 DBA 可以通過(guò)獲取這些信息來(lái)對(duì)數(shù)據(jù)庫(kù)中的各組件進(jìn)行評(píng)估來(lái)分析問(wèn)題的瓶頸。
• DB2PD : 這個(gè)命令是用來(lái)分析數(shù)據(jù)庫(kù)的當(dāng)前狀態(tài)，它帶有很多參數(shù)?？梢杂脕?lái)分析應(yīng)用程序，代理，內(nèi)存塊，緩沖池，日志及鎖狀態(tài)等信息。
• RUNSTATS : 這個(gè)命令是用來(lái)收集數(shù)據(jù)庫(kù)中數(shù)據(jù)的***統(tǒng)計(jì)信息，并更新到系統(tǒng)表中。更新統(tǒng)計(jì)信息將會(huì)促使優(yōu)化器選擇更加符合實(shí)際的高效的訪問(wèn)計(jì)劃，從而提高工作效率。
• REORG : 這個(gè)命令用來(lái)重新整理數(shù)據(jù)庫(kù)中數(shù)據(jù)和索引的碎片，使其在物理上可以得以按一定規(guī)則排列，這樣可以加快檢索的速度。
• DB2DART : 這個(gè)命令是一個(gè)數(shù)據(jù)庫(kù)的分析和報(bào)告工具，它用來(lái)檢查表空間，索引以及數(shù)據(jù)庫(kù)結(jié)構(gòu)的正確性，分析在性能問(wèn)題上的一些原因。
• DB2SUPPORT : 這個(gè)命令用來(lái)收集 DB2 和操作系統(tǒng)的所有相關(guān)信息并生成一個(gè)壓縮文件，可傳送給優(yōu)化人員進(jìn)行分析。

還有一些 DB2 中其他的文件可以用來(lái)分析性能問(wèn)題，比如說(shuō)診斷日志，追蹤文件等。一些第三方的工具也可供參考，如“ tivoli monitor for db2 ”， QUEST 等等。

其他性能因素

XML 的優(yōu)化： 在 DB2 V9 以后引入了純 XML 的數(shù)據(jù)類型，這是一種層次型數(shù)據(jù)類型。這和傳統(tǒng)的關(guān)系型數(shù)據(jù)類型不一樣，在 V9 以前 DB2 存儲(chǔ) XML 數(shù)據(jù)使用 CLOB 數(shù)據(jù)類型，應(yīng)用程序在存取 XML 數(shù)據(jù)的時(shí)候必須先要解析 XML 再使用其數(shù)據(jù)。而在純 XML 類型中，可以直接讀取其中的元素，這樣性能會(huì)有較大的提高。另外針對(duì)純 XML 還有 XML 的索引，也會(huì)增大存取的性能。

操作系統(tǒng)： 數(shù)據(jù)庫(kù)存在于操作系統(tǒng)之上，操作系統(tǒng)的性能將直接影響到數(shù)據(jù)庫(kù)的運(yùn)行效率，因此優(yōu)化操作系統(tǒng)也是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要過(guò)程。在操作系統(tǒng)級(jí)別上可以對(duì)內(nèi)存進(jìn)行優(yōu)化，比如說(shuō)對(duì)系統(tǒng)共享內(nèi)存，信號(hào)量以及虛擬內(nèi)存的設(shè)置等等都可以影響到數(shù)據(jù)庫(kù)的性能。同時(shí)在磁盤的分布上也會(huì)影響到數(shù)據(jù)庫(kù) I/O 效率。

網(wǎng)絡(luò)： 網(wǎng)絡(luò)將會(huì)影響到數(shù)據(jù)庫(kù)的 I/O 性能，當(dāng)數(shù)據(jù)通過(guò)網(wǎng)絡(luò)在客戶端和服務(wù)器端進(jìn)行傳送時(shí)，網(wǎng)絡(luò)上出現(xiàn)瓶頸會(huì)導(dǎo)致數(shù)據(jù)庫(kù) I/O 性能顯著下降。所以選擇優(yōu)良的網(wǎng)絡(luò)設(shè)備以及配置良好的網(wǎng)絡(luò)環(huán)境對(duì)數(shù)據(jù)庫(kù)性能相當(dāng)重要。同時(shí)也要考慮到防火墻的因素，有時(shí)防火墻會(huì)阻擋來(lái)自某些 IP 的數(shù)據(jù)包。

編者介紹

李越 (liyyue@cn.ibm.com), 軟件工程師, IBM

李越 IBM 中國(guó)軟件開(kāi)發(fā)中心 WebSphere Federation Server 測(cè)試部門軟件工程師。曾在 developerWorks 中國(guó)發(fā)表過(guò)有關(guān)優(yōu)化 DB2 的代理和連接的文章。

王飛鵬, 軟件工程師, WSO2 Inc

王飛鵬來(lái)自 IBM 中國(guó) Avalanche 團(tuán)隊(duì)，目前從事 Oracle/Teradata 數(shù)據(jù)庫(kù)遷移到 DB2 數(shù)據(jù)庫(kù)的售前咨詢和客戶支持工作；有為電信、地鐵、中央部委實(shí)施數(shù)據(jù)庫(kù)、數(shù)據(jù)倉(cāng)庫(kù)的成功經(jīng)驗(yàn)；是 DB2 性能優(yōu)化、Oracle 遷移到 DB2、DB2 9.7訓(xùn)練營(yíng)、IBM Information Server 訓(xùn)練營(yíng)、DB2 大學(xué)生訓(xùn)練營(yíng)的培訓(xùn)講師；擁有軟件專利3項(xiàng)，著有《DB2設(shè)計(jì)與性能優(yōu)化-原理、方法和實(shí)踐》一書。

狄浩, 軟件工程師, WSO2 Inc

狄浩，IBM 中國(guó)軟件開(kāi)發(fā)中心軟件工程師，主要從事 IBM CM 內(nèi)容管理產(chǎn)品的相關(guān)工作，最近對(duì) DB2 的性能調(diào)優(yōu)比較感興趣。

張蓉蓉 (rrzhang@cn.ibm.com), 軟件工程師, WSO2 Inc