DB2的效能問題

基本效能調整 Roger Sanders 著 笑熬漿糊 譯 原文出處：《DB2 Magazine》 Quarter 3, 2003 · Vol. 8, Issue 3 英文原文（由於文章翻譯未經授權，請在轉載時保留原文連結） 你的平台--明確的指明從DB2 UDB中可以取得很好的效能 DB2 UDB 8。1 FOR LINUX、 UNIX、WINDOWS 的版本可以存在與簡單的單一的系統上，也可以在存在與在各種平台上啟動並執行、複雜的客戶-伺服器環境裡。但是不論是什麼環境，使用者總是傾向於關心這樣一個問題：資料庫應用程式的效能。那麼什麼是效能，怎麼樣才能改善它？ 簡單地說，效能就是反映電腦系統執行一項指定的任務時候的具體表現。它主要用於測量系統回應時間、計算能力和可用性。 每一個度量都可能受幾個因素影響，包括硬體、系統(和資料庫) 配置、類型和同時工作的使用者數量，以及每一個使用者應用程式的負載。 如果系統執行效率低，你通常可以有幾個調整的方法可供選擇。由於有不同的選擇，你總是傾向於選擇一種被組織化的，簡明的方式，在心裡有一個具體的目標；並且那個目標應該是現實的，定量的，而且還是可以測量的; 否則，效能調整將會變成為一個hit-or-miss exercise.（註：原文如此，我不知道該怎麼解釋所以保留原文） 那麼， DBA 應該從哪裡開始呢? 通過對一個資料庫的觀察，效能問題比較典型的出現從在下列的一個或更多因素的不足上: 系統(環境) 配置 執行個體配置 資料庫配置 資料庫設計 應用程式設計。 關注你上述的每個要素的最初的調整效果，讓他們在每一個因素下逐步的運轉，直到得到你所期望的那些效能。 在這一章節中，我將描述怎麼檢查Linux 、Unix ，和windows平台系統內容配置。 在後續章節將覆蓋到剩餘的部分。 在具體介紹之前，雖然，我將介紹一些適合於所有平台的相關的調整指南。 一般性的調整規則 如果在你開始之前你能夠關注一下這些指南，那麼你將要調整的項目將會是更加容易和更可能會成功。 1. 檢查已知的硬體和軟體問題。 一些效能問題可能通過簡單地更新軟體補丁包或者升級硬體來改正。 既然有可能通過一個簡單的service pack來解決問題那為何要浪費時間和精力去檢查調整系統中的其它部分呢？也就是說，在你決定升級硬體之前請確定你已經瞭解問題的所在。在你發現系統實際上需要更多記憶體之前去盲目的增加其它網路介面卡將會付出昂貴的代價，它實際上是不會對改進效能有任何作用的。 2. 基於整個系統考慮。 通常，如果沒有在這個系統的至少一個組件上起作用的話,你就不能調整這個系統的任何一方面。 例如，如果你為DB2 資料庫管理員的後台進程預留出很大一塊記憶體空間，那麼你不會有足夠的剩餘空間去執行你的預存程序。所以，在你做改變之前，應該整體去考慮這些改變將會對系統造成什麼樣的影響。 3. 根據不同的層級去做測量和重新設定。不要在一次調整當中改變一個系統層級以上。 即使你確定你的計劃是有益的，你將不得不評估每一個改變會對效能改善的結果產生多大的貢獻。 如果你做錯了，效能則有降無升，這樣你就無法知道是哪項變動使它產生了負面的影響。在資料庫伺服器環境裡，以下是可以作為獨立考慮的層級： 硬體、作業系統、通訊軟體、資料庫、SQL 陳述式以及應用程式。 4. 每次改變一件事。 因為同樣原因你應該每次只調整一個系統層級，當你調整每一個系統層級你應該每次只改變一個要素(註冊表變數、執行個體組態變數、資料庫組態變數、等等)。 5. 在開始之前請將你的跟蹤和反饋程式放置就位。效能的調整不是一門具體的學科。 你做的一些變動將會損害而不是有助於效能。 如果這樣情況發生，如果你有辦法撤銷每一次所作的變動你就可以花費較少時間設法使系統回到修改之前的狀態。 我喜歡使用shell 指令碼程式或批次檔去作變動。 那樣，我將能存放一條命令（能返回一個等同與原始狀態的配置值），並把它作為注釋行直接放在賦予配置參量新值的命令之上。 然後，如果我需要取消變動，我把這行明令取消注釋，而把引起變動的命令注釋掉並且重新運行這個指令碼或批次檔，這樣就可以了。如果你被一些改變強行退出,請準備好向每一個 必要的改變重新應用。 6. 不要因為覺得調整的好處而刻意去做調整。所執行的調整必須能解決一個明確的問題 。如果你的調整策略與讓你試圖解決問題的根源沒有直接的關係的話，你將收穫甚微或者一無所獲直到問題的根源被最終解決。從某個角度而言,這樣的行為確實為後來的調整工作帶來更多的麻煩。 7. 謹記回報遞減規律。 記住, 最高效的效能調整結果的收益通常來自你最初的努力。 隨後調整將會導致逐步減小的收益和需要付出更多的努力。 調整的DB2 UDB 系統配置 當DB2 UDB安裝後DB2 UDB會使用一套註冊變數來配置系統。 其中一些變數對於效能起著關鍵性的作用; 而其他一些的影響則是微乎其微,甚至毫無作用。 接下來我將說明哪些註冊變數能在每一個作業系統的平台上產生重大的影響。 切記，對這些變數的改動將會影響整個系統，因此在改變註冊變數的時候要特別當心。 對於所有平台 以下變數的推薦適用於Linux 、Unix 和Windows平台。 DB2_APM_PERFORMANCE  OFF是該註冊變數的預設值。這個參數指定是否能夠在存取計劃管理器（APM）中作出相應的調整，這樣做就可以對SQL高速緩衝儲存空間的動作產生影響。它還闡述了全域性的SQL高速緩衝儲存空間是否能在沒有使用任何包鎖定的情況下工作，這是由阻止高速緩衝包從不被注意的與之無關的地方進入的內部系統鎖機制所決定。 在nonproduction 環境裡，這變數只能被設定為ON。 當設定成ON的時候，你可以看到Out of package cache的錯誤資訊，並且記憶體使用量率將會增加。 先行編譯、綁定和重新綁定（PRECOMPILE, BIND, and REBIND） 操作無法進行，也不能將這些包無效或者無法執行。 DB2_AVOID_PREFETCH  該變數指定在災難性恢複期間是否執行預存取（prefetching） 。 預設值是OFF ; 如果設定成ON，prefetching將不執行。 DB2BPVARS  支援DB2BPVARS的參數明確指出了在調整緩衝池（buffer pools）時使用的包含參數值的那個檔案的位置，參數包括: NO_NT_SCATTER NT_SCATTER_DMSFILE NT_SCATTER_DMSDEVICE NT_SCATTER_SMS NUMPREFETCHQUEUES PREFETCHQUEUESIZE 對於每個帶_SCATTER的參數，預設值是 0 (或OFF )， 允許的取值是：0 (或OFF) 和 1 (ON)。 對於NUMPREFETCHQUEUES參數，預設值是1； 參數值的範圍是 1 到NUM_IOSERVERS。 對於 PREFETCHQUEUESIZE參數，預設值是都是最大值： 100 或 2 * NUM_IOSERVERS。範圍是 1 到32,767。 每一個_SCATTER參數都用於開啟或關閉各自的資料表空間容器的scatter read（或者關閉所有容器的scatter read）。其他的參數則可用於提高緩衝池資料的預存取（prefetching）。 注: 當使用Windows作業系統並且DB2NTNOCACHE參數被設定成ON，那麼帶_SCATTER的參數只能被設定成ON。 DB2CHKPTR  該變數指定是否執行輸入指標檢查； 預設值是OFF。 DB2_ENABLE_BUFPD  預設值是OFF，它指明是否DB2 將使用中間緩衝去改進查詢的效能。 DB2_EXTENDED_OPTIMIZATION  該變數指定查詢最佳化工具是否使用最佳化擴充去改進查詢效能；預設值是OFF。 DB2MAXFSCRSEARCH  該變數可能被設定成-1，或者是從 1 到33554的任何一個，為了在當增加一條紀錄到一個表中的時候指定搜尋可用空間控制紀錄的數量。他允許你去平衡空間再使用的插入速度（小的數值使插入速度最佳化,大的數值使空間再使用成為最佳化）。 如果被設定成 -1，DB2 資料庫管理員將搜尋所有可用空間控制紀錄。預設值是5。 DB2MEMMAXFREE  該變數指定每個DB2 代理將保留可以用記憶體的數量; 取值範圍從0 到2.0e+32 位元組。 預設是8,388,608 個位元組。 DB2_OVERRIDE_BPF  該變數可以被設定為整數個4K頁，它指定將被建立在資料庫北啟用時或首次建立一個串連時的緩衝池的大小（在頁面裡）。當由於記憶體限制的發生導致出現故障時，DB2_OVERRIDE_BPF這個參數是非常有用的。這樣記憶體限制可能出現在由於真正的記憶體不足（很少發生）或由於嘗試用DB2 資料庫管理員對緩衝池分配過大或者使用不恰當的配置的時候。預設值是空。 DB2PRIORITIES  該變數的取值是與平台相關的。 DB2PRIORITIES 控制著DB2 進程和線程優先權。 DB2_SORT_AFTER_TQ  DB2_SORT_AFTER_TQ 指定了在分區資料庫環境下當接收端要求資料排序並且接收的節點數與發送的相等時DB2 最佳化器與引導表隊列怎樣協同工作。當設定成NO (預設值) ，DB2 最佳化器傾向於在傳送端進行排序並且在接收端進行合并行。當設定為YES，當所有行接收完成後，最佳化器在接收端傳輸未排序的行並將其排序。 DB2_STPROC_LOOKUP_FIRST 該變數指定DB2 UDB 伺服器是否在察看sqllib/function目錄 和 sqllib/function/unfenced目錄之前執行對所有DARI（資料庫應用程式遠程介面，預存程序的過時術語）和預存程序的編目尋找。預設值是OFF。 DB2_HASH_JOIN  YES 或者 NO 的取值指明是否在編譯一個存取計劃（access plan）的時候可以使用hash join。預設值是NO。 DB2_PARALLEL_IO  可能的取值包括 * 和 NULL（預設值），指定是否DB2在從資料表空間容器中讀寫資料時候使用並行I/O。 However, because of the one-page container tag, the extents will not line up with the RAID stripes. It may be necessary to access more physical disks than would be optimal during an I/O request unless this registry variable is set to ON. DB2_STRIPED_CONTAINERS  該變數設定成ON或者NULL（預設）指定資料表空間容器ID 標籤是否採用部份或全部的RAID 磁碟資料條。 當使用RAID 裝置，資料表空間建立的時候採用與RAID 資料區塊大小相等的或者是數倍的大小的資料條的擴充尺寸。然而，由於頁容器標記的存在，擴充部分不能按照RAID資料條進行排列。訪問更多物理硬碟比在I/O請求的過程中進行最佳化顯得更有必要，除非該變數被設定為ON。 對於UNIX平台 以下變數適用於Unix 平台（AIX、HP-UX或者其他一些被提到的）。 DB2MEMDISCLAIM  該變數指明AIX作業系統是否應該停止頁面記憶體使之不再佔據任何的實際的儲存空間。設定DB2MEMDISCLAIM為YES（預設）指明在程式關閉的時候DB2 UDB釋放部分或者全部記憶體，這取決於由DB2MEMMAXFREE註冊變數所決定的值。如果DB2MEMMAXFREE為空白，程式關閉的時候所有記憶體將被釋放。如果DB2MEMMAXFREE被賦值，僅有部分的（最高到DB2MEMMAXFREE所設定得值）記憶體釋放。程式終結時，釋放保證了記憶體能被其他的進程所使用。 DB2_MMAP_READ  它與DB2_MMAP_WRITE一起使用，預設值 YES 允許DB2 for AIX 使用mmap 作為I/O 一個交錯法。 在多數環境中，mmap在使用的時候應該避免當多個進程對同一檔案的不同扇區執行寫入操作造成的作業系統鎖定的情況。 DB2_MMAP_WRITE  它與DB2_MMAP_READ一起使用，預設值是YES； 在這個時候他允許DB2 for AIX使用mmap 作為一個I/O的預備的方法。 DB2_PINNED_BP  這個AIX 和HP-UX平台上的變數指定是否資料庫使用的全域記憶體 (包括緩衝池的記憶體) 將被保留在系統主儲存空間以獲得更加穩定的資料庫效能。 對於Windows平台 以下變數適用於Windows NT 作業環境。 DB2NTMEMSIZE  Windows NT 要求, 所有共用儲存空間片段在初始化時都被儲存在動態串連程式庫，以保證程式啟動並執行整個過程中均有匹配地址。必要的話，在Windows NT下可不考慮DB2的預設值；但是，在多數情況下，預設值應當足夠使用。預設值如下： DBMS: 16,777,216; FCM: 22,020,096; DBAT: 33,554,432; APLD: 16,777,216. DB2NTNOCACHE  指定檔案系統高速緩衝儲存空間是否執行。該變數適用於除了LONG或者LOB類型資料之外的任何類型的資料。除去系統高速緩衝儲存空間允許資料庫使用更多可用記憶體以便增加更多的緩衝池（buffer pool）或排序堆（sortheap）。 預設是OFF。 DB2NTPRICLASS  用於關聯各個獨立線程的優先權(設定使用 DB2PRIORITIES) ，該變數為DB2 執行個體程式(DB2SYSCS.EXE)設定優先組同時確定DB2線程在系統中相對於其他線程的絕對優先權。以下三個優先組是可用的： NORMAL_PRIORITY_CLASS (預設) REALTIME_PRIORITY_CLASS (R) HIGH_PRIORITY_CLASS (H). 取值可以是R、H或者其他值，預設是NULL。 DB2NTWORKSET  該變數指明DB2可以利用到的最小和最大工作區大小。預設時，當Windows NT沒有處在頁面調度的狀態下，一個過程的工作區可能隨心所欲的增大。 但是，一旦處於頁面調度，最大工作區只能是大約1MB。 該變數允許你忽略這個預設狀態。 可能的取值包括 PositiveNumber, PositiveNumber0. (預設是1,1 ) 修改DB2註冊變數 你怎樣去確定是否這些變數已經被設定並且他們被設定成了什嗎？ DB2 UDB V8.1 給你兩種方式察看和改變變數： 由使用DB2 註冊管理工具(從Configuration Assistant可以找到) 或執行系統命令 db2set。 Listing 1 顯示那個命令的文法。 註：顯示在角括弧裡的參數(< >) 是可選擇的；顯示在方括弧裡的參量或選項([ ])  是必需的。 Listing 1: 察看和改變變數值的文法

Table 1 描述了這個命令的其他可利用的選項.

Table 1: db2set 命令選項：

代碼:

如果執行不加選項的 db2set 命令，將會返回一個包含了已經被設定的變數以及他們的當前值的列表， 繼續調節 DB2 UDB 的效能調整可能是一個漫長的過程因為許多因素可能影響效能。 這個專欄報道了一般效能調整和改進DB2 UDB 系統配置的方法。在下個專欄，我將闡述調整執行個體和資料庫配置為最佳效能的方法。 關於作者 Roger E. Sanders 是 Network Appliance Inc.的資料庫維護工程師。他也是 All-In-One DB2 Administration Exam Guide (McGraw-Hill/Osborne Media, 2002)一書的作者。如果你想與他取得聯絡，請致函roger.sanders@netapp.com。