讓數據庫飛起來 十大DB2優化技巧
為了幫助DB2 DBA 避免性能災難并獲得高性能，我為我們的客戶、用戶和 DB2 專家同行總結了一套故障診斷流程。以下詳細說明在 Unix、Windows 和 OS/2 環境下使用 DB2 UDB 的電子商務OLTP 應用程序的10 條最重要的性能改善技巧，希望下文中涉及到的內容對大家能夠有所幫助。
一、 監視開關
確保已經打開監視開關。如果它們沒有打開，您將無法獲取您需要的性能信息。要打開該監視開關，請發出以下命令：
1 db2 "update monitor switches using
2 lock ON sort ON bufferpool ON uow ON
3 table ON statement ON"
二、代理程序
確保有足夠的 DB2 代理程序來處理工作負載。要找出代理程序的信息，請發出命令：
db2 "get snapshot for database manager"
并查找以下行：
1 High water mark for agents registered = 7
2 High water mark for agents waiting for a token = 0
3 Agents registered= 7
4 Agents waiting for a token= 0
5 Idle agents= 5
6 Agents assigned from pool= 158
7 Agents created from empty Pool = 7
8 Agents stolen from another application= 0
9 High water mark for coordinating agents= 7
10 Max agents overflow= 0
如果您發現Agents waiting for a token或Agents stolen from another application不為 0，那么請增加對數據庫管理器可用的代理程序數(MAXAGENTS 和/或 MAX_COORDAGENTS取適用者)。
三、最大打開的文件數
DB2 在操作系統資源的約束下盡量做一個“優秀公民”。它的一個“優秀公民”的行動就是給在任何時刻打開文件的最大數設置一個上限。數據庫配置參數 MAXFILOP約束 DB2 能夠同時打開的文件最大數量。當打開的文件數達到此數量時，DB2 將開始不斷地關閉和打開它的表空間文件(包括裸設備)。不斷地打開和關閉文件減緩了 SQL 響應時間并耗費了 CPU 周期。要查明 DB2 是否正在關閉文件，請發出以下命令：
db2 "get snapshot for database on DBNAME"
并查找以下的行：
Database files closed = 0
如果上述參數的值不為 0，那么增加MAXFILOP的值直到不斷打開和關閉文件的狀態停埂。
db2 "update db cfg for DBNAME using MAXFILOP N"
四、鎖
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"
查找以下行：
1 Locks held currently= 0
2 Lock waits= 0
3 Time database waited on locks (ms)= 0
4 Lock list memory in use (Bytes)= 576
5 Deadlocks detected= 0
6 Lock escalations= 0
7 Exclusive lock escalations= 0
8 Agents currently waiting on locks= 0
9 Lock Timeouts= 0
如果Lock list memory in use (Bytes)超過所定義LOCKLIST大小的 50%，那么在LOCKLIST數據庫配置中增加 4k 頁的數量。
五、臨時表空間
為了改善 DB2 執行并行 I/O 和提高使用TEMPSPACE的排序、散列連接(hash join)和其它數據庫操作的性能，臨時表空間至少應該在三個不同的磁盤驅動器上擁有三個容器。
要想知道您的臨時表空間具有多少容器，請發出以下命令：
db2 "list tablespaces show detail"
查找與以下示例類似的TEMPSPACE表空間定義：
1 Tablespace ID= 1
2 Name= TEMPSPACE1
3 Type= System managed space
4 Contents= Temporary data
5 State= 0x0000
6 Detailed explanation: Normal
7 Total pages= 1
8 Useable pages= 1
9 Used pages= 1
10 Free pages= Not applicable
11 High water mark (pages)= Not applicable
12 Page size (bytes)= 4096
13 Extent size (pages)= 32
14 Prefetch size (pages)= 96
15 Number of containers= 3
注意Number of containers的值是 3，而且Prefetch size是Extent size的三倍。為了得到最佳的并行 I/O 性能，重要的是Prefetch size為Extent size的倍數。這個倍數應該等于容器的個數。
要查找容器的定義，請發出以下命令：
db2 "list tablespace containers for 1 show detail"
指的是tablespace ID #1，它是剛才所給出的示例中的TEMPSPACE1。
六、內存排序
OLTP 應用程序不應該執行大的排序。它們在 CPU、I/O 和所用時間方面的成本極高，而且將使任何 OLTP 應用程序慢下來。因此，256 個 4K 頁(1MB)的缺省SORTHEAP大小(1MB)應該是足夠了。您也應該知道排序溢出的數量和每個事務的排序數。
請發出以下命令：
Db2 "get snapshot for database on DBNAME"
并查找以下行：
1 Total sort heap allocated= 0
2 Total sorts = 1
3 Total sort time (ms)= 8
4 Sort overflows = 0
5 Active sorts = 0
6 Commit statements attempted = 3
7 Rollback statements attempted = 0
8 Let transactions = Commit statements attempted + Rollback
9 statements attempted
10 Let SortsPerTX= Total sorts / transactions
11 Let PercentSortOverflows = Sort overflows * 100 / Total sorts
如果PercentSortOverflows ((Sort overflows * 100) / Total sorts )大于 3 個百分點，那么在應用程序 SQL 中會出現嚴重的或意外的排序問題。因為正是溢出的存在表明發生了大的排序，所以理想的情況是發現沒有排序溢出或至少其百分比小于一個百分點。
如果出現過多的排序溢出，那么“應急”解決方案是增加SORTHEAP的大小。然而，這樣做只是掩蓋了真實的性能問題。相反，您應該確定引起排序的 SQL 并更改該 SQL、索引或群集來避免或減少排序開銷。
如果SortsPerTX大于 5 (作為一種經驗之談)，那么每個事務的排序數可能很大。雖然某些應用程序事務執行許多小的組合排序(它們不會溢出并且執行時間很短)，但是它消耗了過多的 CPU。當SortsPerTX很大時，按我的經驗，這些機器通常會受到 CPU 的限制。確定引起排序的 SQL 并改進存取方案(通過索引、群集或更改 SQL)對提高事務吞吐率是極為重要的。
七、表訪問
對于每個表，確定 DB2 為每個事務讀取的行數。您必須發出兩個命令：
1 db2 "get snapshot for database on DBNAME"
2 db2 "get snapshot for tables on DBNAME"
在發出第一個命令以后，確定發生了多少個事務(通過取Commit statements attempted和Rollback statements attempted之和 - 請參閱 技巧 3)。
在發出第二個命令以后，將讀取的行數除以事務數(RowsPerTX)。在每個事務中，OLTP 應用程序通常應該從每個表讀取 1 到 20 行。如果您發現對每個事務有成百上千的行正被讀取，那么發生了掃描操作，也許需要創建索引。(有時以分布和詳細的索引來運行 runstats 也可提供了一個解決的辦法。)
“get snapshot for tables on DBNAME”的樣本輸出如下：
1 Snapshot timestamp = 09-25-2000
2 4:47:09.970811
3 Database name= DGIDB
4 Database path= /fs/inst1/inst1/NODE0000/SQL00001/
5 Input database alias= DGIDB
6 Number of accessed tables= 8
7 Table List
8 Table Schema= INST1
9 Table Name= DGI_
10 SALES_ LOGS_TB
11 Table Type= User
12 Rows Written= 0
13 Rows Read= 98857
14 Overflows= 0
15 Page Reorgs= 0
Overflows 的數量很大就可能意味著您需要重組表。當由于更改了行的寬度從而 DB2 必須在一個不夠理想的頁上定位一個行時就會發生溢出。
八、表空間分析
表空間快照對理解訪問什么數據以及如何訪問是極其有價值的。要得到一個表空間快照，請發出以下命令：
db2 "get snapshot for tablespaces on DBNAME"
對每個表空間，回答以下問題：
平均讀取時間(ms)是多少?
平均寫入時間(ms)是多少?
異步(預取)相對于同步(隨機)所占的物理 I/O 的百分比是多少?
每個表空間的緩沖池命中率是多少?
每分鐘讀取多少物理頁面?
對于每個事務要讀取多少物理和邏輯頁面?
對于所有表空間，回答以下問題：
哪個表空間的讀取和寫入的時間最慢?為什么?是因為其容器在慢速的磁盤上嗎?容器大小是否相等?對比異步訪問和同步訪問，訪問屬性是否和期望的一致?隨機讀取的表應該有隨機讀取的表空間，這是為了得到高的同步讀取百分比、通常較高的緩沖池命中率和更低的物理 I/O 率。
對每個表空間，確保預取大小等于數據塊大小乘以容器數。請發出以下命令：
db2 "list tablespaces show detail"
如果需要，可以為一個給定表空間改變預取大小。可以使用以下命令來檢查容器定義：
db2 "list tablespace containers for N show detail"
在此，N 是表空間標識號。
九、緩沖池優化
我時常發現一些 DB2 UDB 站點，雖然機器具有 2、4 或 8GB 內存，但是 DB2 數據庫卻只有一個緩沖池(IBMDEFAULTBP)，其大小只有 16MB!
如果在您的站點上也是這種情況，請為 SYSCATSPACE 目錄表空間創建一個緩沖池、為TEMPSPACE表空間創建一個緩沖池以及另外創建至少兩個緩沖池：BP_RAND和BP_SEQ。隨機訪問的表空間應該分配給用于隨機訪問的緩沖池(BP_RAND)。順序訪問(使用異步預取 I/O)的表空間應該分配給用于順序訪問的緩沖池(BP_SEQ)。根據某些事務的性能目標，您可以創建附加的緩沖池;例如，您可以使一個緩沖池足夠大以存儲整個“熱”(或者說訪問非常頻繁的)表。當涉及到大的表時，某些 DB2 用戶將重要表的索引放入一個索引(BP_IX)緩沖池取得了很大成功。
太小的緩沖池會產生過多的、不必要的物理 I/O。太大的緩沖池使系統處在操作系統頁面調度的風險中并消耗不必要的 CPU 周期來管理過度分配的內存。正好合適的緩沖池大小就在“太小”和“太大”之間的某個平衡點上。適當的大小存在于回報將要開始減少的點上。如果您沒有使用工具來自動進行回報減少分析，那么您應該在不斷增加緩沖池大小上科學地測試緩沖池性能(命中率、I/O 時間和物理 I/O 讀取率)，直到達到最佳的緩沖池大小。因為業務一直在變動和增長，所以應該定期重新評估“最佳大小”決策。
十、SQL 成本分析
一條糟糕的 SQL 語句會徹底破壞您的一整天。我不止一次地看到一個相對簡單的 SQL 語句搞糟了一個調整得很好的數據庫和機器。對于很多這些語句，天底下(或在文件中)沒有 DB2 UDB 配置參數能夠糾正因錯誤的 SQL 語句導致的高成本的情況。
更糟糕的是，DBA 常常受到種種束縛：不能更改 SQL(可能是因為它是應用程序供應商提供的，例如 SAP、 PeopleSoft或 Siebel)。這給 DBA 只留下三條路可走：
1. 更改或添加索引
2. 更改群集
3. 更改目錄統計信息
另外，如今健壯的應用程序由成千上萬條不同的 SQL 語句組成。這些語句執行的頻率隨應用程序的功能和日常的業務需要的不同而不同。SQL 語句的實際成本是它執行一次的成本乘以它執行的次數。
每個 DBA 所面臨的重大的任務是，識別具有最高“實際成本”的語句的挑戰，并且減少這些語句的成本。
通過本機 DB2 Explain 實用程序、一些第三方供應商提供的工具或 DB2 UDB SQL Event Monitor 數據，您可以計算出執行一次 SQL 語句所用的資源成本。但是語句執行頻率只能通過仔細和耗時地分析 DB2 UDB SQL Event Monitor 的數據來了解。
在研究 SQL 語句問題時，DBA 使用的標準流程是：
1. 創建一個 SQL Event Monitor，寫入文件：
$> db2 "create event monitor SQLCOST for statements write to ..."
2. 激活事件監視器(確保有充足的可用磁盤空間)：
$> db2 "set event monitor SQLCOST state = 1"
3. 讓應用程序運行。
4. 取消激活事件監視器：
$> db2 "set event monitor SQLCOST state = 0"
5. 使用 DB2 提供的 db2evmon 工具來格式化 SQL Event Monitor 原始數據(根據 SQL 吞吐率可能需要數百兆字節的可用磁盤空間)：
$> db2evmon -db DBNAME -evm SQLCOST
> sqltrace.txt
6. 瀏覽整個已格式化的文件，尋找顯著大的成本數(一個耗時的過程)：
$> more sqltrace.txt
7. 對已格式化的文件進行更完整的分析，該文件試圖標識唯一的語句(獨立于文字值)、每個唯一語句的頻率(它出現的次數)和其總 CPU、排序以及其它資源成本的總計。如此徹底的分析在 30 分鐘的應用程序 SQL 活動樣本上可能要花一周或更多的時間。
要減少確定高成本 SQL 語句所花的時間，您可以考慮許多可用的信息來源：
從技巧 4，務必要計算在每個事務中從每個表中讀取的行數。如果產生的數字看上去很大，那么 DBA 可以在 SQL Event Monitor 格式化輸出中搜索有關的表名稱(這將縮小搜索范圍而且節省一些時間)，這樣也許能夠找出有問題的語句。 從 技巧 3，務必計算每個表空間的異步讀取百分比和物理 I/O 讀取率。如果一個表空間的異步讀取百分比很高并遠遠超過平均的物理 I/O 讀取率，那么在此表空間中的一個或更多的表正在被掃描。查詢目錄并找出哪些表被分配到可疑的表空間(每個表空間分配一個表提供最佳性能檢測)，然后在 SQL Event Monitor 格式化輸出中搜索這些表。這些也可能有助于縮小對高成本 SQL 語句的搜索范圍。 嘗試觀察應用程序執行的每條 SQL 語句的 DB2 Explain 信息。然而，我發現高頻率、低成本語句經常爭用機器容量和能力來提供期望的性能。如果分析時間很短而且最大性能是關鍵的，那么請考慮使用供應商提供的工具(它們能夠快速自動化識別資源密集的 SQL 語句的過程)。 Database-GUYS Inc.的 SQL-GUY 工具提供精確、實時且均衡的 SQL 語句的成本等級分析。
繼續調節
最佳性能不僅需要排除高成本 SQL 語句，而且需要確保相應的物理基礎結構是適當的。當所有的調節旋鈕都設置得恰到好處、內存被有效地分配到池和堆而且 I/O 均勻地分配到各個磁盤時，才可得到最佳性能。雖然量度和調整需要時間，但是執行這 10 個建議的 DBA 將非常成功地滿足內部和外部的 DB2 客戶。因為電子商務的變化和增長，即使是管理得最好的數據庫也需要定期的微調。DBA 的工作永遠都做不完!
快速回顧最棒的10 個技巧
* 對工作負載使用足夠的代理程序。
* 不允許 DB2 不必要地關閉和打開文件。
* 不允許長期的鎖等待。
* 確保數據庫的 TEMPSPACE 表空間的并行 I/O 能力。
* 保守地管理 DB2 排序內存并不要以大的 SORTHEAP 來掩蓋排序問題。
* 分析表的訪問活動并確定具有特別高的每個事務讀取行數或溢出數的表。
* 分析每個表空間的性能特性，并尋求改善讀取時間最慢、等待時間最長、物理 I/O 讀取率最高、命中率最差的表空間性能以及與所期望的不一致的訪問屬性。
* 創建多個緩沖池，有目的地將表空間分配到緩沖池以便于共享訪問屬性。
* 檢查 DB2 UDB SQL Event Monitor 信息以找到哪個 SQL 語句消耗計算資源最多并采取正確的措施。
一旦排除了高成本 SQL，馬上重新評估配置和物理設計設置。關于DB2優化技巧九為大家介紹到這里，希望大家都能夠從中有所收獲。
【編輯推薦】
