DB2數據庫性能優化技巧詳解
最簡單而最見成效的——Bufferpool
緩沖池是內存中的一塊存儲區域，用于臨時讀入和更改數據庫頁(包含表行或索引項)。緩沖池的用途是為了提高數據庫系統的性能。從內存訪問數據要比從磁盤訪問數據快得多。因此，數據庫管理器需要從磁盤讀取或寫入磁盤的次數越少，性能就越好。對一個或多個緩沖池進行配置之所以是調優的最重要方面，是因為連接至數據庫的應用程序的大多數數據(不包括大對象和長字段數據)操作都在緩沖池中進行。
缺省情況下，應用程序使用緩沖池 IBMDEFAULTBP，它是在創建數據庫時創建的。當 SYSCAT.BUFFERPOOLS 目錄表中該緩沖池的 NPAGES 值為 -1 時，DB2 數據庫配置參數 BUFFPAGE 控制著緩沖池的大小。否則會忽略 BUFFPAGE 參數，并且用 NPAGES 參數所指定的頁數創建緩沖池。
建議對于僅使用一個緩沖池的應用程序，將 NPAGES 更改成 -1，這樣 BUFFPAGE 就可以控制該緩沖池的大小。這使得更新和報告緩沖池大小以及其它 DB2 數據庫配置參數變得更加方便。
確保可以使用數據庫配置中的 BUFFPAGE 參數來控制緩沖池大小之后，將該參數設置成合適的值。根據數據庫的大小和應用程序的性質將該參數設置成一個合理的大值，這種做法很安全。通常，該參數的缺省值非常小，可能滿足不了要求。
db2 "get snapshot for all bufferpools"
在數據庫快照或緩沖池快照的快照輸出中，查找下列"logical reads"和"physical reads"，這樣就可以計算出緩沖池命中率，它可以幫助調優緩沖池:
緩沖池命中率表明數據庫管理器不需要從磁盤裝入頁(即該頁已經在緩沖池中)就能處理頁請求的時間百分比。緩沖池的命中率越高，使用磁盤 I/O 的頻率就越低。按如下計算緩沖池命中率:
(1 - ((buffer pool data physical reads + buffer pool index physical reads) / (buffer pool data logical reads + pool index logical reads)) ) * 100% |
這個計算考慮了緩沖池高速緩存的所有頁(索引和數據)。理想情況下，該比率應當超過 95%，并盡可能接近 100%。要提高緩沖池命中率，請嘗試下面這些方法:
1. 增加緩沖池大小。
2. 考慮分配多個緩沖池，如果可能的話，為每個經常被訪問的大表所屬的表空間分配一個緩沖池，為一組小表分配一個緩沖池，然后嘗試一下使用不同大小的緩沖池以查看哪種組合會提供***性能。
3. 如果已分配的內存不能幫助提高性能，那么請避免給緩沖池分配過多的內存。應當根據取自測試環境的快照信息來決定緩沖池的大小。
4. 太小的緩沖池會產生過多的、不必要的物理 I/O。太大的緩沖池使系統處在操作系統頁面調度的風險中并消耗不必要的 CPU 周期來管理過度分配的內存。正好合適的緩沖池大小就在"太小"和"太大"之間的某個平衡點上。適當的大小存在于回報將要開始減少的點上。
獲得***性能的——SQL
一條糟糕的 SQL 語句會徹底破壞一切。一個相對簡單的 SQL 語句也能夠搞糟一個調整得很好的數據庫和機器。對于很多這些語句，天底下(或在文件中)沒有 DB2 UDB 配置參數能夠糾正因錯誤的 SQL 語句導致的高成本的情況。
更糟糕的是，DBA 常常受到種種束縛:不能更改 SQL(可能是因為它是應用程序供應商提供的)。這給 DBA 只留下三條路可走：
1. 更改或添加索引
2. 更改群集
3. 更改目錄統計信息
健壯的應用程序由成千上萬條不同的 SQL 語句組成。這些語句執行的頻率隨應用程序的功能和日常的業務需要的不同而不同。SQL 語句的實際成本是它執行一次的成本乘以它執行的次數。
每個DBA 所面臨的重大的任務是，識別具有***"實際成本"的語句的挑戰，并且減少這些語句的成本。
通過本機 DB2 Explain 實用程序、一些第三方供應商提供的工具或 DB2 UDB SQL Event Monitor 數據，可以計算出執行一次 SQL 語句所用的資源成本。但是語句執行頻率只能通過仔細和耗時地分析 DB2 UDB SQL Event Monitor 的數據來了解。
***性能不僅需要排除高成本 SQL 語句，而且需要確保相應的物理基礎結構是適當的。當所有的調節旋鈕都設置得恰到好處、內存被有效地分配到池和堆而且 I/O 均勻地分配到各個磁盤時，才可得到***性能。
不可遺漏的——Lock
這些與鎖相關的控制都是數據庫配置參數:
LOCKLIST 表明分配給鎖列表的存儲容量。每個數據庫都有一個鎖列表，鎖列表包含了并發連接到該數據庫的所有應用程序所持有的鎖。鎖定是數據庫管理器用來控制多個應用程序并發訪問數據庫中數據的機制。行和表都可以被鎖定。根據對象是否還持有其它鎖，每把鎖需要 32 個或 64 個字節的鎖列表：
1. 需要 64 個字節來持有某個對象上的鎖，在這個對象上，沒有持有其它鎖。
2. 需要 32 個字節來記錄某個對象上的鎖，在這個對象上，已經持有一個鎖。
MAXLOCKS 定義了應用程序持有的鎖列表的百分比，在數據庫管理器執行鎖升級之前必須填充該鎖列表。當一個應用程序所使用的鎖列表百分比達到 MAXLOCKS 時，數據庫管理器會升級這些鎖，這意味著用表鎖代替行鎖，從而減少列表中鎖的數量。當任何一個應用程序所持有的鎖數量達到整個鎖列表大小的這個百分比時，對該應用程序所持有的鎖進行鎖升級。如果鎖列表用完了空間，那么也會發生鎖升級。數據庫管理器通過查看應用程序的鎖列表并查找行鎖最多的表，來決定對哪些鎖進行升級。如果用一個表鎖替換這些行鎖，將不再會超出 MAXLOCKS 值，那么鎖升級就會停止。否則，鎖升級就會一直進行，直到所持有的鎖列表百分比低于 MAXLOCKS。MAXLOCKS 參數乘以 MAXAPPLS 參數不能小于 100。
雖然升級過程本身并不用花很多時間，但是鎖定整個表(相對于鎖定個別行)降低了并發性，而且數據庫的整體性能可能會由于對受鎖升級影響的表的后續訪問而降低。
LOCKTIMEOUT 的缺省值是 -1，這意味著將沒有鎖超時(對 OLTP 應用程序，這種情況可能會是災難性的)。許多 DB2 用戶用 LOCKTIMEOUT = -1。將 LOCKTIMEOUT 設置為很短的時間值，例如 10 或 15 秒。在鎖上等待過長時間會在鎖上產生雪崩效應。
首先，用以下命令檢查 LOCKTIMEOUT 的值：
db2 "get db cfg for DBNAME" |
并查找包含以下文本的行：
Lock timeout (sec) (LOCKTIMEOUT) = -1 |
如果值是 -1，考慮使用以下命令將它更改為 15 秒(一定要首先詢問應用程序開發者或供應商以確保應用程序能夠處理鎖超時)：
db2 "update db cfg for DBNAME using LOCKTIMEOUT 15" |
同時應該監視鎖等待的數量、鎖等待時間和正在使用鎖列表內存(lock list memory)的量。請發出以下命令：
db2 "get snapshot for database on DBNAME" |
如果 Lock list memory in use (Bytes) 超過所定義 LOCKLIST 大小的 50%，那么在 LOCKLIST 數據庫配置中增加 4k 頁的數量。
#p#
掩蓋問題的—SORTHEAP
SORTHEAP 是一個數據庫配置參數，它定義了私有排序所使用的私有內存頁的***數目，或共享排序所使用的共享內存頁的***數目。如果排序是私有排序，那么該參數影響代理程序私有內存。如果排序是共享排序，那么該參數影響數據庫的共享內存。
每個排序都有單獨的由數據庫管理器按需分配的排序堆。在排序堆中對數據進行排序。如果由優化器來指導排序堆大小的分配，那么用優化器提供的信息來分配的排序堆的大小要小于由該參數所指定的排序堆大小。
SHEAPTHRES 是一個數據庫管理器配置參數。私有和共享排序所使用內存的來源不一樣。共享排序內存區的大小是在***次連接到數據庫時根據 SHEAPTHRES 值以靜態方式預先確定的。私有排序內存區的大小是不受限制的。對于私有排序和共享排序，應用 SHEAPTHRES 參數的方式不同:
對于私有排序，SHEAPTHRES 是對私有排序在任何給定的時間可以消耗的全部內存的實例級"軟"限制。當實例的總私有排序內存消耗量達到這一限制時，為其它進入的私有排序請求而分配的內存會大大減少。
對于共享排序，SHEAPTHRES 是對共享排序在任何給定的時間可以消耗的全部內存的數據庫級"硬"限制。當達到這一限制時，不允許有其它共享排序內存請求，直到總的共享內存消耗量回落到 SHEAPTHRES 所指定的限制以下。
使用排序堆的操作示例包括內存中表的散列連接和操作。閾值的顯式定義防止數據庫管理器將過多數量的內存用于大量排序。
建議
◆ 使用數據庫系統監視器來跟蹤排序活動。
◆ 使用合適的索引使排序堆的使用降到***。
◆ 當需要頻繁進行大型排序時，增加 SORTHEAP 的值。
◆ 如果增加 SORTHEAP，請確定是否還需要調整數據庫管理器配置文件中的 SHEAPTHRES 參數。
◆ 優化器用排序堆大小來確定存取路徑。在更改該參數后請考慮重新綁定應用程序(使用 REBIND PACKAGE 命令)。
◆ 理想情況下，應當將排序堆閾值(SHEAPTHRES)參數合理地設置為在數據庫管理器實例中設置的 SORTHEAP 參數***值的倍數。該參數至少應當是實例中任何數據庫所定義的*** SORTHEAP 的兩倍。
如何更改這些參數
要更改 SORTHEAP 和 SHEAPTHRES 的值，請運行以下命令:
-- SORTHEAP should be changed for individual database -- db2 "update db cfg for DB_NAME using SORTHEAP a_value" -- SHEAPTHRES is a database manager parameter -- db2 "update dbm cfg using SHEAPTHRES b_value" |
研究步驟
OLTP 應用程序不應該執行大型排序。大型排序在 CPU 和 I/O 資源方面的成本太高了。通常，SORTHEAP 大小的缺省值(256 個 4KB 頁)就足夠了。事實上，對于高并發性 OLTP，可能希望降低這個缺省值。當需要進一步研究時，可以發出下面這條命令:
db2 "update monitor switches using sort on" |
然后，讓應用程序運行一會，然后輸入:
db2 "get snapshot for database on DBNAME" |
根據該輸出，可以計算每個事務的排序數目，并可以計算溢出了可用于排序的內存的那部分排序的百分比。
SortsPerTransaction = (Total Sorts) / (Commit statements attempted + Rollback statements attempted) PercentSortOverflow = (Sort overflows * 100 ) / (Total sorts) |
經驗:如果 SortsPerTransaction 大于 5，它可能表明每個事務的排序太多。如果 PercentSortOverflow 大于 3%，那么可能發生了嚴重的、未曾預料到的大型排序。發生這種情況時，增加 SORTHEAP 只會隱藏性能問題 - 卻無法修正它。這個問題的正確解決方案是通過添加正確的索引改進有問題的 SQL 語句的存取方案。
【編輯推薦】
