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