db2 性能調優

1. 監視開關

　　確定已經打開監視開關。如果它們沒有打開，您将無法擷取您需要的性能資訊。要打開該監視開關，請發出以下指令：

  db2 "update monitor switches using

  lock ON sort ON bufferpool ON uow ON

  table ON statement ON"

2. 代理程式

　　確定有足夠的 DB2 代理程式來處理工作負載。要找出代理程式的資訊，請發出指令：

  db2 "get snapshot for database manager"

　　并查找以下行：

  High water mark for agents registered = 7

  High water mark for agents waiting for a token = 0

  Agents registered= 7

  Agents waiting for a token= 0

  Idle agents= 5

  Agents assigned from pool= 158

  Agents created from empty Pool = 7

  Agents stolen from another application= 0

  High water mark for coordinating agents= 7

  Max agents verflow= 0

　　如果您發現Agents waiting for a token或Agents stolen from another application不為 0，那麼請增加對資料庫管理器可用的代理程式數（MAXAGENTS 和／或 MAX_COORDAGENTS取适用者）。

3. 最大打開的檔案數

　　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"

4. 鎖

　　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"

　　查找以下行：

  Locks held currently= 0

  Lock waits= 0

  Time database waited on locks (ms)= 0

  Lock list memory in use (Bytes)= 576

  Deadlocks detected= 0

  Lock escalations= 0

  Exclusive lock escalations= 0

  Agents currently waiting on locks= 0

  Lock Timeouts= 0

　　如果Lock list memory in use (Bytes)超過所定義LOCKLIST大小的 50%，那麼在LOCKLIST資料庫配置中增加 4k 頁的數量。

5. 臨時表空間

　　為了改善 DB2 執行并行 I/O 和提高使用TEMPSPACE的排序、散列連接配接（hash join）和其它資料庫操作的性能，臨時表空間至少應該在三個不同的磁盤驅動器上擁有三個容器。

　　要想知道您的臨時表空間具有多少容器，請發出以下指令：

  db2 "list tablespaces show detail"

　　查找與以下示例類似的TEMPSPACE表空間定義：

  Tablespace ID= 1

  Name= TEMPSPACE1

  Type= System managed space

  Contents= Temporary data

  State= 0x0000

  Detailed explanation: Normal

  Total pages= 1

  Useable pages= 1

  Used pages= 1

  Free pages= Not applicable

  High water mark (pages)= Not applicable

  Page size (bytes)= 4096

  Extent size (pages)= 32

  Prefetch size (pages)= 96

  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"

1 指的是tablespace ID #1，它是剛才所給出的示例中的TEMPSPACE1。

6. 記憶體排序

　　OLTP 應用程式不應該執行大的排序。它們在 CPU、I/O 和所用時間方面的成本極高，而且将使任何 OLTP 應用程式慢下來。是以，256 個 4K 頁（1MB）的預設SORTHEAP大小（1MB）應該是足夠了。您也應該知道排序溢出的數量和每個事務的排序數。

　　請發出以下指令：

  Db2 "get snapshot for database on DBNAME"

　　并查找以下行：

  Total sort heap allocated= 0

  Total sorts = 1

  Total sort time (ms)= 8

  Sort verflows = 0

  Active sorts = 0

  Commit statements attempted = 3

  Rollback statements attempted = 0

  Let transactions = Commit statements attempted + Rollback

  statements attempted

  Let SortsPerTX= Total sorts / transactions

  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）對提高事務吞吐率是極為重要的。

7. 表通路 

　　對于每個表，确定 DB2 為每個事務讀取的行數。您必須發出兩個指令：

  db2 "get snapshot for database on DBNAME"

  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”的樣本輸出如下：

  Snapshot timestamp = 09-25-2000  4:47:09.970811

  Database name= DGIDB

  Database path= /fs/inst1/inst1/NODE0000/SQL00001/

  Input database alias= DGIDB

  Number of accessed tables= 8

  Table List

  Table Schema= INST1

  Table Name= DGI_

  SALES_ LOGS_TB

  Table Type= User

  Rows Written= 0

  Rows Read= 98857

  verflows= 0

  Page Reorgs= 0

　　Overflows 的數量很大就可能意味着您需要重組表。當由于更改了行的寬度進而 DB2 必須在一個不夠理想的頁上定位一個行時就會發生溢出。

8. 表空間分析 

　　表空間快照對了解通路什麼資料以及如何通路是極其有價值的。要得到一個表空間快照，請發出以下指令：

  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 是表空間辨別号。

9. 緩沖池優化 

　　我時常發現一些 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 讀取率），直到達到最佳的緩沖池大小。因為業務一直在變動和增長，是以應該定期重新評估“最佳大小”決策。

10. 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，馬上重新評估配置和實體設計設定。