IBM DB2 基本性能調(diào)整3
2024-09-06 23:58:02
供稿：網(wǎng)友
基本性能調(diào)整 3
roger sanders 著
笑熬漿糊 譯
原文出處：《db2 magazine》 quarter 1, 2004 · vol. 9, issue 1
英文原文（由于文章翻譯未經(jīng)授權(quán)，請(qǐng)?jiān)谵D(zhuǎn)載時(shí)保留原文鏈接）
為了尋求最佳的性能，將數(shù)據(jù)庫(kù)對(duì)象作為你關(guān)注的目標(biāo)。
在我早先的專欄中，我展示了怎么樣去明確那些影響db2 udb for linux、unix，和windows性能的注冊(cè)變量和配置參數(shù)。在這個(gè)專欄中，我將圍繞可能有對(duì)數(shù)據(jù)庫(kù)性能的重大影響的另外一個(gè)因素：數(shù)據(jù)庫(kù)設(shè)計(jì)來(lái)進(jìn)行說(shuō)明。就像你想象中的那樣，db2 udb 數(shù)據(jù)庫(kù)是由一些對(duì)象組成的，這些對(duì)象是怎么被創(chuàng)建和他們?cè)趺摧^好的在一起工作會(huì)對(duì)一個(gè)數(shù)據(jù)庫(kù)執(zhí)行活動(dòng)的優(yōu)劣產(chǎn)生重大的影響。 能對(duì)性能產(chǎn)生重大影響的對(duì)象包括緩沖池、表空間和索引；我將帶你們?nèi)ケ闅v每個(gè)需要考慮的因素。
緩沖池
如果你有使用db2 udb的經(jīng)驗(yàn)，你應(yīng)該知道, 緩沖池是內(nèi)存中的一些單獨(dú)分配給db2 數(shù)據(jù)庫(kù)管理器的空間，讓它去做為數(shù)據(jù)庫(kù)添加新數(shù)據(jù)或者去響應(yīng)一個(gè)查詢從磁盤返回一些數(shù)據(jù)數(shù)據(jù)頁(yè)的緩沖區(qū)。由于從內(nèi)存中訪問(wèn)數(shù)據(jù)勢(shì)必比從磁盤上讀取要快的多,因此通過(guò)減少磁盤的i/o操作緩沖池改善了數(shù)據(jù)庫(kù)的整體性能。 實(shí)際上，研究緩沖池是怎樣被創(chuàng)建和被使用在調(diào)整優(yōu)化的數(shù)據(jù)庫(kù)性能是最重要的一步。
由于緩沖區(qū)的重要性地位，每個(gè)dba 都應(yīng)該了解db2 udb怎么使用他們。這里是關(guān)于它的快速回顧課程。當(dāng)新數(shù)據(jù)增加到數(shù)據(jù)庫(kù)時(shí)，它首先在緩沖區(qū)中增加新頁(yè)。最終這個(gè)頁(yè)將被具體化到數(shù)據(jù)庫(kù)存儲(chǔ)空間中。另一方面，為了響應(yīng)查詢當(dāng)數(shù)據(jù)從數(shù)據(jù)庫(kù)中被檢索出來(lái)的時(shí)候，db2數(shù)據(jù)庫(kù)管理器首先將會(huì)將這些包含數(shù)據(jù)的頁(yè)存放在緩沖池中，然后才會(huì)把它傳遞給需要它的應(yīng)用程序或者用戶。每次執(zhí)行新的查詢時(shí)，將會(huì)在每個(gè)可以利用的緩沖池中搜尋是否已經(jīng)有所需要數(shù)據(jù)的頁(yè)駐留在內(nèi)存中。如果那樣，就會(huì)立刻將它傳遞給對(duì)應(yīng)的的應(yīng)用或用戶。但是，如果不能在這些緩沖池找到需要的數(shù)據(jù)的話，db2 數(shù)據(jù)庫(kù)管理器將會(huì)講這些數(shù)據(jù)從存儲(chǔ)器中檢索出來(lái)并且在傳遞數(shù)據(jù)之前將它復(fù)制到緩沖區(qū)中。一旦頁(yè)被復(fù)制到緩沖池，那么這個(gè)頁(yè)將會(huì)一直駐留在緩沖區(qū)中直到數(shù)據(jù)庫(kù)被關(guān)閉或直到它所在的空間需要存儲(chǔ)其他頁(yè)為止。(由于所有數(shù)據(jù)的加載和修改首先發(fā)生在緩沖區(qū)——修改過(guò)的頁(yè)最終會(huì)去刷新磁盤存儲(chǔ)——因此存放在緩沖池中的數(shù)據(jù)總是最新的)當(dāng)緩沖池滿了之后，db2 數(shù)據(jù)庫(kù)管理器將通過(guò)檢測(cè)頁(yè)的最后引用時(shí)間，頁(yè)類型，或者頁(yè)的修改不會(huì)影響磁盤內(nèi)容的改變來(lái)選擇去除哪些頁(yè)，這些頁(yè)可能再次被引用。例如，在30 分鐘前被檢索以響應(yīng)查詢的頁(yè)會(huì)比包含更新操作而沒(méi)有落實(shí)更新的頁(yè)更容易被覆蓋。
db2 udb缺省創(chuàng)建了一個(gè)緩沖池（ibmdefaultbp）作為數(shù)據(jù)庫(kù)創(chuàng)建過(guò)程的一部分。在linux和unix平臺(tái)，該緩沖池從內(nèi)存中被分配了1,000個(gè)4kb頁(yè)；在windows平臺(tái)，該緩沖池從內(nèi)存中被分配了250個(gè)4kb頁(yè)。 你可以通過(guò)在控制中心找到緩沖池菜單并且選擇適當(dāng)?shù)牟僮骰蛘邎?zhí)行alter bufferpool語(yǔ)句來(lái)增加或減少這個(gè)緩沖池的4kb 頁(yè)的數(shù)量。你也可以通過(guò)在控制中心同樣的方法或者執(zhí)行create bufferpool語(yǔ)句來(lái)創(chuàng)建另外的緩沖區(qū)。
由于緩沖區(qū)的重要性，你應(yīng)該仔細(xì)考慮使用多少個(gè)緩沖池來(lái)適應(yīng)你的實(shí)施需要；每一個(gè)究竟需要多大；以及每個(gè)緩沖池怎么樣能被充分利用。 在多數(shù)環(huán)境里，能被有效使用的緩沖區(qū)個(gè)數(shù)取決于可利用的系統(tǒng)內(nèi)存的大小。 如果可利用的內(nèi)存可以保留10,000個(gè)4k 頁(yè)(或更少)，那么通常的使用單獨(dú)的大緩沖池比去使用多個(gè)小緩沖池要好。 使用多個(gè)小緩沖區(qū)將導(dǎo)致頻繁地訪問(wèn)頁(yè)來(lái)經(jīng)常與內(nèi)存進(jìn)行進(jìn)出交換，反過(guò)來(lái)會(huì)導(dǎo)致為存儲(chǔ)對(duì)象比如編目表的i/o競(jìng)爭(zhēng)或者重復(fù)的訪問(wèn)用戶表和索引。但是，如果有比較多的內(nèi)存，應(yīng)該考慮創(chuàng)兼各自的緩沖區(qū)為以下：
· 每一種臨時(shí)表空間被定義
· 包含著被一些短期的更新事務(wù)一直或者重復(fù)訪問(wèn)表的表空間
· 包含著表和索引頻繁地被更新的表空間
· 包含著表和索引頻繁地被查詢但很少被更新的表空間
· 包含著表頻繁地被使用于隨意的查詢的表空間
· 包含著很少被應(yīng)用程序訪問(wèn)的數(shù)據(jù)的表空間
· 包含著一些你想要使用的數(shù)據(jù)和索引的表空間。
在許多情況下，大一點(diǎn)的緩沖池要優(yōu)于較小的緩沖池。但是，考慮到可以使用的內(nèi)存總額以及緩沖池將怎么被使用。 如果你擁有一個(gè)要從一個(gè)非常大的表中執(zhí)行許多隨機(jī)存取操作的應(yīng)用，那么你應(yīng)該為這個(gè)特殊表創(chuàng)造和使用一個(gè)小緩沖池。 在這種情況下，沒(méi)有必要在緩沖池內(nèi)存中保留數(shù)據(jù)頁(yè)一旦他們被用于去執(zhí)行一次單獨(dú)的查詢。 另一方面，如果你擁有一個(gè)要從幾個(gè)看似很小的表中頻繁地檢索數(shù)據(jù)的應(yīng)用，你應(yīng)該考慮創(chuàng)建一個(gè)足夠大緩沖池來(lái)存放所有在這些表里免得數(shù)據(jù)。 采用這個(gè)設(shè)計(jì)方案，數(shù)據(jù)能一次裝入內(nèi)存，并且允許它反復(fù)的被獲取而沒(méi)有必要額外的磁盤i/o 。
表空間
數(shù)據(jù)庫(kù)管理的一個(gè)重要部份包括通過(guò)使用表空間來(lái)完成邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)到物理存儲(chǔ)的映射。 db2 udb 使用二種類型表空間: 系統(tǒng)管理表空間(sms) 和數(shù)據(jù)庫(kù)管理表空間(dms)。使用sms 表空間，操作系統(tǒng)的文件管理器負(fù)責(zé)分配和處理表空間使用存儲(chǔ)空間。 使用dms表空間，表空間創(chuàng)建器(或者，在某些情況下是db2 數(shù)據(jù)庫(kù)管理器) 負(fù)責(zé)分配空間， db2 數(shù)據(jù)庫(kù)管理器負(fù)責(zé)管理。性能通常是以dms 表空間來(lái)獲得快速的相應(yīng)。 但是，sms 表空間沒(méi)有大小限制(16,777,215 頁(yè)) ，在這點(diǎn)上不同于dms 表空間。 sms 表空間還更加容易管理，在許多情況下，因?yàn)樵谛枰獣r(shí)系統(tǒng)可以自動(dòng)地獲取額外的存儲(chǔ)空間。通常dms 表空間被用于那些頻繁地的表，但增長(zhǎng)很慢。sms 表空間一般被用于那些連續(xù)增長(zhǎng)的表。
早些時(shí)候，我提及過(guò)數(shù)據(jù)是在表空間存儲(chǔ)容器之間傳送（譬如文件系統(tǒng)目錄、文件和裸設(shè)備）并且緩沖池是被稱之為頁(yè)的一些分離塊組成。db2 udb 提供四不同頁(yè)面大小(4kb 、8kb 、16kb ，和32kb) 。缺省情況下在數(shù)據(jù)庫(kù)創(chuàng)建過(guò)程期間產(chǎn)生的三個(gè)表空間(syscatspace, userspace1和 tempspace1) 被分配4kb 頁(yè)面大小。各個(gè)表空間必須與緩沖區(qū)結(jié)合；一個(gè)特殊表空間所使用頁(yè)面大小必須與它關(guān)聯(lián)的緩沖區(qū)的頁(yè)面大小相匹配。 另外，如果你創(chuàng)建可一個(gè)頁(yè)面大小是4kb之外的一個(gè)表空間，你應(yīng)該創(chuàng)建一個(gè)使用同樣頁(yè)面大小的系統(tǒng)臨時(shí)表空間。否則，在執(zhí)行一個(gè)需要臨時(shí)表空間的操作時(shí)性能可能會(huì)降低（譬如排序和表重組）。
當(dāng)表空間橫跨多個(gè)容器時(shí)，數(shù)據(jù)將會(huì)用round-robin方式寫入每個(gè)容器。所謂擴(kuò)展長(zhǎng)度的屬性是控制當(dāng)數(shù)據(jù)要寫入列表中下一個(gè)容器之前在一個(gè)容器中寫多少頁(yè)數(shù)據(jù)。這種方法有助于對(duì)屬于所給定表空間的所有容器之間的數(shù)據(jù)平衡。
為了減少查詢的相應(yīng)時(shí)間，db2數(shù)據(jù)庫(kù)管理器使用了一種被稱之為prefetching的技術(shù)去檢索(或取得)那些數(shù)據(jù)庫(kù)管理器確定用戶可能需要在實(shí)際執(zhí)行之前的數(shù)據(jù)。(數(shù)據(jù)與需要的實(shí)際頁(yè)一起被復(fù)制到緩沖區(qū)；表空間的prefetch 大小將決定在響應(yīng)一個(gè)查詢時(shí)有多少額外的數(shù)據(jù)頁(yè)被復(fù)制到緩沖池里。)
缺省的，所有表空間被創(chuàng)建成extent和prefetch均為32頁(yè)的大小。你通?？梢酝ㄟ^(guò)超過(guò)這個(gè)缺省的extent和prefetch大小來(lái)改進(jìn)整體性能。以下二個(gè)算式將確定適當(dāng)?shù)膃xtent大小:
min extent size = [number of physical disks used by the tablespace * 4096 (bytes)] / tablespace page size (in bytes)
max extent size = 524288 (bytes) / tablespace page size (in bytes)
（如果你感覺(jué)象你以前從未看了這些等式或者是我提供的prefetch的大小，這都是正常的現(xiàn)象。因?yàn)槲覜](méi)有在任何ibm 指南或文獻(xiàn)中發(fā)現(xiàn)它們。相反的，這些是我作為db2 udb 性能組的成員在多倫多ibm 實(shí)驗(yàn)室工作期間研究出來(lái)的結(jié)果。）
最合適的extent大小應(yīng)該是計(jì)算出來(lái)的最小值與最大值中間某處的值。切記，對(duì)于extent的大小，更多不一定意味著更好。在理想狀態(tài)下，你在從最小值向最大值過(guò)渡的過(guò)程中會(huì)發(fā)現(xiàn)一個(gè)合適的extent大小，運(yùn)行性能測(cè)試和評(píng)估每次設(shè)置的結(jié)果。注意，extent大小在表空間創(chuàng)建以后無(wú)法修改， 所以，在每次測(cè)試的時(shí)候表空間將必須刪除，再創(chuàng)建并且重新計(jì)算。
一旦你決定了extent的大小，你就可以通過(guò)下面的方程來(lái)得到prefetch 大?。?br>
min prefetch size = (extent size * number of containers used) * factor
factor是一個(gè)常量，一般為3。
對(duì)于prefetch大小，與extent不同的是它在通常狀況下比較好。
分離數(shù)據(jù)
仔細(xì)查看在一個(gè)db2udb數(shù)據(jù)庫(kù)里面大多數(shù)數(shù)據(jù)是如何存放你就會(huì)發(fā)現(xiàn)這里有三種不同的對(duì)象：常規(guī)用戶數(shù)據(jù)存儲(chǔ)為數(shù)據(jù)對(duì)象；索引數(shù)據(jù)聯(lián)系了在表中定義了的索引信息存儲(chǔ)為索引對(duì)象；長(zhǎng)字段數(shù)據(jù)被存儲(chǔ)成一個(gè)長(zhǎng)字段對(duì)象 (長(zhǎng)字段對(duì)象只存在于表包含一個(gè)或多個(gè)長(zhǎng)數(shù)據(jù)列中——long varchar, long vargraphic, blob, clob, dbclob) 。如果采用dms類型的標(biāo)空間，這些對(duì)象分開(kāi)地被存放并且每個(gè)都被存放在它自己的單獨(dú)表空間里面。在缺省情況下，這三個(gè)對(duì)象都被存放在同一個(gè)表空間里； 但是，性能可能通過(guò)將數(shù)據(jù)分別存放在上述三種類型的表空間中存放時(shí)常得到改善。
關(guān)于db2empfa
在sms 表空間，文件系統(tǒng)（而不是db2 數(shù)據(jù)庫(kù)管理器）負(fù)責(zé)在需要時(shí)分配額外的存儲(chǔ)空間。并且在缺省情況下，sms 表空間每次擴(kuò)展一頁(yè)。但是，在某些工作負(fù)荷下(例如，當(dāng)進(jìn)行一次大批量的插入操作)它也許傾向于使用在extent中分配的存儲(chǔ)空間而不是頁(yè)。這就是db2empfa工具起了作用。 當(dāng)db2empfa運(yùn)行的時(shí)候， 數(shù)據(jù)庫(kù)配置參數(shù)multipage_alloc被設(shè)置位yes(雖然它是一個(gè)只讀配置參數(shù))，它會(huì)導(dǎo)致 db2 udb 每次擴(kuò)展sms 表空間一個(gè)extent而不是一頁(yè)。db2empfa工具在db2安裝路徑下的sqllib/bin目錄里面。
索引與性能
索引的主要目的就是幫助db2 數(shù)據(jù)庫(kù)管理器快速的從表中查出記錄。為表中經(jīng)常被使用的列創(chuàng)建索引通常有助于數(shù)據(jù)存取和更新操作性能的改善。此外，索引還考慮到當(dāng)多重事務(wù)處理在同一時(shí)間里訪問(wèn)同一個(gè)表時(shí)候的更好的并發(fā)性；這樣，行檢索更加快速并且鎖迅速被獲取而且不必?fù)?dān)心它長(zhǎng)期的掛起。但是這些優(yōu)勢(shì)需要成本。索引會(huì)占用數(shù)據(jù)庫(kù)空間，并且它們可能導(dǎo)致在插入和更新操作執(zhí)行過(guò)程的輕微型能降低。 (所有插入操作和部分更新操作必須發(fā)生在表和它對(duì)應(yīng)的索引中。)
那么怎么才能告訴你是否創(chuàng)建索引將改進(jìn)性能？db2 udb 8.1封裝了一個(gè)工具包來(lái)協(xié)助你，它可通過(guò)控制中心訪問(wèn)。它被稱為設(shè)計(jì)顧問(wèn)，它會(huì)捕獲關(guān)于數(shù)據(jù)庫(kù)的典型工作負(fù)荷以及推薦修改的特定信息，譬如根據(jù)提供的信息可以去創(chuàng)建新索引或刪除未使用的索引。
runstats工具與性能
每當(dāng)sql語(yǔ)句被發(fā)送到到db2 數(shù)據(jù)庫(kù)管理器中處理時(shí)，sql 優(yōu)化器會(huì)去讀取系統(tǒng)編目表來(lái)確定被引用的列的特性以及在被引用的表中時(shí)候已經(jīng)定義了索引，同時(shí)被語(yǔ)句引用的每個(gè)表的大小也包括在內(nèi)。根據(jù)這些得到的信息，優(yōu)化器可以估算出能滿足sql語(yǔ)句需要的每一種數(shù)據(jù)存取路徑的成本，然后推薦最佳的一個(gè)。 優(yōu)化器用于做決策的數(shù)據(jù)庫(kù)統(tǒng)計(jì)集合數(shù)據(jù)在系統(tǒng)編目表中是一個(gè)關(guān)鍵性的元素。所以，統(tǒng)計(jì)的變化可能導(dǎo)致選擇存取路徑的變化；如果信息丟失或過(guò)時(shí)，優(yōu)化器也許選擇出來(lái)的存取計(jì)劃將導(dǎo)致sql語(yǔ)句執(zhí)行時(shí)間比正常的要長(zhǎng)。
擁有合法的信息在sql語(yǔ)句的復(fù)雜性增加的時(shí)候變得更加關(guān)鍵。當(dāng)只引用一張表(沒(méi)有定義索引)時(shí)，優(yōu)化器選擇的數(shù)量是有限的。但是，當(dāng)多個(gè)表被引用時(shí)(每個(gè)表都有一個(gè)或多個(gè)索引) ，那么可供優(yōu)化器選擇的數(shù)量會(huì)大大加大。但不幸的是，優(yōu)化器所使用的統(tǒng)計(jì)信息是不會(huì)自動(dòng)得保持更新。反而必須階段性地通過(guò)使用運(yùn)行統(tǒng)計(jì)工具(runstats)重新生成。可以通過(guò)控制中心和命令行兩種方式執(zhí)行runstats工具。語(yǔ)法如下：
runstats on table [tablename] < with distribution | with distribution and < detailed > indexes all | with distribution and < detailed > index [indexname] > < shrlevel [change | reference] >
或者
runstats on table [tablename] < [and | for] < detailed > indexes all | [and | for] < detailed > index [indexname] > < shrlevel [change | reference] >
tablename 是需要收集(或者更新)統(tǒng)計(jì)信息的表的名稱。indexname 是需要收集或者更新統(tǒng)計(jì)信息的索引的名稱。
注:被顯示在角括號(hào)里(< > )的參數(shù)是可選的；方括號(hào)([ ])中的參數(shù)是必須的。
例如，更新存儲(chǔ)在系統(tǒng)編目表中的關(guān)于表default.employee的統(tǒng)計(jì)信息。你可以執(zhí)行以下命令：
runstats on table default.employee with distribution and indexes all shrlevel change
運(yùn)行統(tǒng)計(jì)工具不會(huì)輸出信息。但是，你能通過(guò)查詢系統(tǒng)編目視圖syscat.tables的card, overflow, npages, fpages列來(lái)觀看它的結(jié)果。(如果這些列的值是21，就意味著統(tǒng)計(jì)信息尚未對(duì)該行所代表的對(duì)象起作用。)
那么應(yīng)該多久去收集表的統(tǒng)計(jì)信息呢?理想狀況下，你應(yīng)該在下面一些事件之后去使用運(yùn)行統(tǒng)計(jì)工具：
· 大量的插入、更新或刪除操作
· 導(dǎo)入操作
· 裝載操作
· 在現(xiàn)有表中插入一個(gè)新的字段
· 創(chuàng)建新索引
· 表重組
每當(dāng)表的統(tǒng)計(jì)信息被收集或更新的時(shí)候，所有引用它的程序包都要被重新與它綁定這樣優(yōu)化器就可以利用新統(tǒng)計(jì)信息并且在可能的時(shí)候，會(huì)指出它們所包含的sql語(yǔ)句的更好的訪問(wèn)計(jì)劃。 如果重新綁定失敗或者忘記重新綁定這些程序包可能導(dǎo)致動(dòng)態(tài)sql操作執(zhí)行起來(lái)會(huì)比靜態(tài)sql操作要快（譯者注：我對(duì)此不太明白，詢問(wèn)別人后得到的解釋是：靜態(tài)可能采取的一個(gè)費(fèi)時(shí)間的路線，數(shù)據(jù)變了但訪問(wèn)的策略沒(méi)有變；重新綁定就意味著重新改變?cè)L問(wèn)策略），相反也適用。
最后，將它們放到一起
對(duì)db2 udb 系統(tǒng)或任一復(fù)雜rdbms的調(diào)優(yōu)，為了得到最佳的性能將會(huì)是一個(gè)長(zhǎng)的過(guò)程。在這一系列專欄我通過(guò)對(duì)數(shù)據(jù)庫(kù)的分析，解釋了性能問(wèn)題是如何典型地出現(xiàn)從一個(gè)或更多的下列：
粗劣的系統(tǒng)(環(huán)境) 配置 粗劣的實(shí)例配置 粗劣的數(shù)據(jù)庫(kù)配置 粗劣的數(shù)據(jù)庫(kù)設(shè)計(jì) 粗劣的應(yīng)用設(shè)計(jì)。
系統(tǒng)調(diào)優(yōu)應(yīng)該從db2udb注冊(cè)變量，db2 數(shù)據(jù)庫(kù)管理器實(shí)例配置參量以及可能有對(duì)性能產(chǎn)生巨大影響的數(shù)據(jù)庫(kù)配置參量開(kāi)始。接下來(lái)再考慮緩沖池如何使用并且確定是否使用附加的緩沖池或不同的緩沖池大小會(huì)有所幫助。選擇適當(dāng)?shù)谋砜臻g類型，extent大小和prefetch 大小，并且保持系統(tǒng)目錄統(tǒng)計(jì)最新，最終完成基本性能調(diào)整。
《全文完》
