經(jīng) DB2 Magazine 許可，以在此轉(zhuǎn)載。
每隔大約幾個星期，我們就會接到苦惱的 DBA 們的電話，抱怨有關(guān)性能的問題。“我們 Web 站點(diǎn)速度慢得像蝸牛一樣”，他們叫苦道，“我們正在失去客戶，情況嚴(yán)重。你能幫忙嗎？”為了回答這些問題，我為我的咨詢公司開發(fā)了一個分析流程，它能讓我們很快找到性能問題的原因，開發(fā)出補(bǔ)救措施并提出調(diào)整意見。這些打電話的人極少詢問費(fèi)用和成本 － 他們只關(guān)心制止損失。當(dāng) DB2 或電子商務(wù)應(yīng)用程序的運(yùn)行不能達(dá)到預(yù)期的性能時，組織和財務(wù)的收益將遭受極大的損失。
為了幫助 DB2 DBA 避免性能災(zāi)難并獲得高性能，我為我們的客戶、用戶和 DB2 專家同行總結(jié)了一套故障診斷流程。以下詳細(xì)說明在 Unix、Windows 和 OS/2 環(huán)境下使用 DB2 UDB 的電子商務(wù) OLTP 應(yīng)用程序的 10 條最重要的性能改善技巧 － 并在本文的結(jié)束部分作出 總結(jié)。
10. 監(jiān)視開關(guān)
確保已經(jīng)打開監(jiān)視開關(guān)。如果它們沒有打開，您將無法獲取您需要的性能信息。要打開該監(jiān)視開關(guān)，請發(fā)出以下命令：
db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON" |
9. 代理程序
確保有足夠的 DB2 代理程序來處理工作負(fù)載。要找出代理程序的信息，請發(fā)出命令：
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 overflow= 0 |
如果您發(fā)現(xiàn)Agents waiting for a token或Agents stolen from another application不為 0，那么請增加對數(shù)據(jù)庫管理器可用的代理程序數(shù)（MAXAGENTS 和／或 MAX_COORDAGENTS取適用者）。
8. 最大打開的文件數(shù)
DB2 在操作系統(tǒng)資源的約束下盡量做一個“優(yōu)秀公民”。它的一個“優(yōu)秀公民”的行動就是給在任何時刻打開文件的最大數(shù)設(shè)置一個上限。數(shù)據(jù)庫配置參數(shù)MAXFILOP約束 DB2 能夠同時打開的文件最大數(shù)量。當(dāng)打開的文件數(shù)達(dá)到此數(shù)量時，DB2 將開始不斷地關(guān)閉和打開它的表空間文件（包括裸設(shè)備）。不斷地打開和關(guān)閉文件減緩了 SQL 響應(yīng)時間并耗費(fèi)了 CPU 周期。要查明 DB2 是否正在關(guān)閉文件，請發(fā)出以下命令：
db2 "get snapshot for database on DBNAME" |
并查找以下的行：
Database files closed = 0 |
如果上述參數(shù)的值不為 0，那么增加MAXFILOP的值直到不斷打開和關(guān)閉文件的狀態(tài)停止。使用以下命令：
db2 "update db cfg for DBNAME using MAXFILOP N" |
7. 鎖
LOCKTIMEOUT的缺省值是 -1，這意味著將沒有鎖超時（對 OLTP 應(yīng)用程序，這種情況可能會是災(zāi)難性的）。盡管如此，我還是經(jīng)常發(fā)現(xiàn)許多 DB2 用戶用LOCKTIMEOUT= -1。將LOCKTIMEOUT設(shè)置為很短的時間值，例如 10 或 15 秒。在鎖上等待過長時間會在鎖上產(chǎn)生雪崩效應(yīng)。
首先，用以下命令檢查LOCKTIMEOUT的值：
db2 "get db cfg for DBNAME" |
并查找包含以下文本的行：
Lock timeout (sec) (LOCKTIMEOUT) = -1 |
如果值是 -1，考慮使用以下命令將它更改為 15 秒（一定要首先詢問應(yīng)用程序開發(fā)者或您的供應(yīng)商以確保應(yīng)用程序能夠處理鎖超時）：
db2 "update db cfg for DBNAME using LOCKTIMEOUT 15" |
您同時應(yīng)該監(jiān)視鎖等待的數(shù)量、鎖等待時間和正在使用鎖列表內(nèi)存（lock list memory）的量。請發(fā)出以下命令：
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數(shù)據(jù)庫配置中增加 4k 頁的數(shù)量。
6. 臨時表空間
為了改善 DB2 執(zhí)行并行 I/O 和提高使用TEMPSPACE的排序、散列連接（hash join）和其它數(shù)據(jù)庫操作的性能，臨時表空間至少應(yīng)該在三個不同的磁盤驅(qū)動器上擁有三個容器。
要想知道您的臨時表空間具有多少容器，請發(fā)出以下命令：
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的倍數(shù)。這個倍數(shù)應(yīng)該等于容器的個數(shù)。
要查找容器的定義，請發(fā)出以下命令：
db2 "list tablespace containers for 1 show detail" |
1 指的是tablespace ID #1，它是剛才所給出的示例中的TEMPSPACE1。
5. 內(nèi)存排序
OLTP 應(yīng)用程序不應(yīng)該執(zhí)行大的排序。它們在 CPU、I/O 和所用時間方面的成本極高，而且將使任何 OLTP 應(yīng)用程序慢下來。因此，256 個 4K 頁（1MB）的缺省SORTHEAP大?。?MB）應(yīng)該是足夠了。您也應(yīng)該知道排序溢出的數(shù)量和每個事務(wù)的排序數(shù)。
請發(fā)出以下命令：
Db2 "get snapshot for database on DBNAME" |
并查找以下行：
Total sort heap allocated= 0 Total sorts = 1 Total sort time (ms)= 8 Sort overflows = 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 個百分點(diǎn)，那么在應(yīng)用程序 SQL 中會出現(xiàn)嚴(yán)重的或意外的排序問題。因為正是溢出的存在表明發(fā)生了大的排序，所以理想的情況是發(fā)現(xiàn)沒有排序溢出或至少其百分比小于一個百分點(diǎn)。
如果出現(xiàn)過多的排序溢出，那么“應(yīng)急”解決方案是增加SORTHEAP的大小。然而，這樣做只是掩蓋了真實(shí)的性能問題。相反，您應(yīng)該確定引起排序的 SQL 并更改該 SQL、索引或群集來避免或減少排序開銷。
如果SortsPerTX大于 5 （作為一種經(jīng)驗之談），那么每個事務(wù)的排序數(shù)可能很大。雖然某些應(yīng)用程序事務(wù)執(zhí)行許多小的組合排序（它們不會溢出并且執(zhí)行時間很短），但是它消耗了過多的 CPU。當(dāng)SortsPerTX很大時，按我的經(jīng)驗，這些機(jī)器通常會受到 CPU 的限制。確定引起排序的 SQL 并改進(jìn)存取方案（通過索引、群集或更改 SQL）對提高事務(wù)吞吐率是極為重要的。
對于每個表，確定 DB2 為每個事務(wù)讀取的行數(shù)。您必須發(fā)出兩個命令：
db2 "get snapshot for database on DBNAME" |
db2 "get snapshot for tables on DBNAME" |
在發(fā)出第一個命令以后，確定發(fā)生了多少個事務(wù)（通過取Commit statements attempted和Rollback statements attempted之和 － 請參閱 技巧 3）。
在發(fā)出第二個命令以后，將讀取的行數(shù)除以事務(wù)數(shù)（RowsPerTX）。在每個事務(wù)中，OLTP 應(yīng)用程序通常應(yīng)該從每個表讀取 1 到 20 行。如果您發(fā)現(xiàn)對每個事務(wù)有成百上千的行正被讀取，那么發(fā)生了掃描操作，也許需要創(chuàng)建索引。（有時以分布和詳細(xì)的索引來運(yùn)行 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 Overflows= 0 Page Reorgs= 0 |
Overflows 的數(shù)量很大就可能意味著您需要重組表。當(dāng)由于更改了行的寬度從而 DB2 必須在一個不夠理想的頁上定位一個行時就會發(fā)生溢出。
表空間快照對理解訪問什么數(shù)據(jù)以及如何訪問是極其有價值的。要得到一個表空間快照，請發(fā)出以下命令：
db2 "get snapshot for tablespaces on DBNAME" |
對每個表空間，回答以下問題：
對于所有表空間，回答以下問題：
對每個表空間，確保預(yù)取大小等于數(shù)據(jù)塊大小乘以容器數(shù)。請發(fā)出以下命令：
db2 "list tablespaces show detail" |
如果需要，可以為一個給定表空間改變預(yù)取大小?？梢允褂靡韵旅顏頇z查容器定義：
db2 "list tablespace containers for N show detail" |
在此，N 是表空間標(biāo)識號。
2. 緩沖池優(yōu)化
我時常發(fā)現(xiàn)一些 DB2 UDB 站點(diǎn)，雖然機(jī)器具有 2、4 或 8GB 內(nèi)存，但是 DB2 數(shù)據(jù)庫卻只有一個緩沖池（IBMDEFAULTBP），其大小只有 16MB！
如果在您的站點(diǎn)上也是這種情況，請為 SYSCATSPACE 目錄表空間創(chuàng)建一個緩沖池、為TEMPSPACE表空間創(chuàng)建一個緩沖池以及另外創(chuàng)建至少兩個緩沖池：BP_RAND和BP_SEQ。隨機(jī)訪問的表空間應(yīng)該分配給用于隨機(jī)訪問的緩沖池（BP_RAND）。順序訪問（使用異步預(yù)取 I/O）的表空間應(yīng)該分配給用于順序訪問的緩沖池（BP_SEQ）。根據(jù)某些事務(wù)的性能目標(biāo)，您可以創(chuàng)建附加的緩沖池；例如，您可以使一個緩沖池足夠大以存儲整個“熱”（或者說訪問非常頻繁的）表。當(dāng)涉及到大的表時，某些 DB2 用戶將重要表的索引放入一個索引（BP_IX）緩沖池取得了很大成功。
太小的緩沖池會產(chǎn)生過多的、不必要的物理 I/O。太大的緩沖池使系統(tǒng)處在操作系統(tǒng)頁面調(diào)度的風(fēng)險中并消耗不必要的 CPU 周期來管理過度分配的內(nèi)存。正好合適的緩沖池大小就在“太小”和“太大”之間的某個平衡點(diǎn)上。適當(dāng)?shù)拇笮〈嬖谟诨貓髮⒁_始減少的點(diǎn)上。如果您沒有使用工具來自動進(jìn)行回報減少分析，那么您應(yīng)該在不斷增加緩沖池大小上科學(xué)地測試緩沖池性能（命中率、I/O 時間和物理 I/O 讀取率），直到達(dá)到最佳的緩沖池大小。因為業(yè)務(wù)一直在變動和增長，所以應(yīng)該定期重新評估“最佳大小”決策。
1. SQL 成本分析
一條糟糕的 SQL 語句會徹底破壞您的一整天。我不止一次地看到一個相對簡單的 SQL 語句搞糟了一個調(diào)整得很好的數(shù)據(jù)庫和機(jī)器。對于很多這些語句，天底下（或在文件中）沒有 DB2 UDB 配置參數(shù)能夠糾正因錯誤的 SQL 語句導(dǎo)致的高成本的情況。
更糟糕的是，DBA 常常受到種種束縛：不能更改 SQL（可能是因為它是應(yīng)用程序供應(yīng)商提供的，例如 SAP、 PeopleSoft或 Siebel）。這給 DBA 只留下三條路可走：
1. 更改或添加索引
2. 更改群集
3. 更改目錄統(tǒng)計信息
另外，如今健壯的應(yīng)用程序由成千上萬條不同的 SQL 語句組成。這些語句執(zhí)行的頻率隨應(yīng)用程序的功能和日常的業(yè)務(wù)需要的不同而不同。SQL 語句的實(shí)際成本是它執(zhí)行一次的成本乘以它執(zhí)行的次數(shù)。
每個 DBA 所面臨的重大的任務(wù)是，識別具有最高“實(shí)際成本”的語句的挑戰(zhàn)，并且減少這些語句的成本。
通過本機(jī) DB2 Explain 實(shí)用程序、一些第三方供應(yīng)商提供的工具或 DB2 UDB SQL Event Monitor 數(shù)據(jù)，您可以計算出執(zhí)行一次 SQL 語句所用的資源成本。但是語句執(zhí)行頻率只能通過仔細(xì)和耗時地分析 DB2 UDB SQL Event Monitor 的數(shù)據(jù)來了解。
在研究 SQL 語句問題時，DBA 使用的標(biāo)準(zhǔn)流程是：
1. 創(chuàng)建一個 SQL Event Monitor，寫入文件：
$> db2 "create event monitor SQLCOST for statements write to ..." |
2. 激活事件監(jiān)視器（確保有充足的可用磁盤空間）：
$> db2 "set event monitor SQLCOST state = 1" |
3. 讓應(yīng)用程序運(yùn)行。
4. 取消激活事件監(jiān)視器：
$> db2 "set event monitor SQLCOST state = 0" |
5. 使用 DB2 提供的 db2evmon 工具來格式化 SQL Event Monitor 原始數(shù)據(jù)（根據(jù) SQL 吞吐率可能需要數(shù)百兆字節(jié)的可用磁盤空間）：
$> db2evmon -db DBNAME -evm SQLCOST > sqltrace.txt |
6. 瀏覽整個已格式化的文件，尋找顯著大的成本數(shù)（一個耗時的過程）：
$> more sqltrace.txt |
7. 對已格式化的文件進(jìn)行更完整的分析，該文件試圖標(biāo)識唯一的語句（獨(dú)立于文字值）、每個唯一語句的頻率（它出現(xiàn)的次數(shù)）和其總 CPU、排序以及其它資源成本的總計。如此徹底的分析在 30 分鐘的應(yīng)用程序 SQL 活動樣本上可能要花一周或更多的時間。
要減少確定高成本 SQL 語句所花的時間，您可以考慮許多可用的信息來源：
繼續(xù)調(diào)節(jié)
最佳性能不僅需要排除高成本 SQL 語句，而且需要確保相應(yīng)的物理基礎(chǔ)結(jié)構(gòu)是適當(dāng)?shù)?。?dāng)所有的調(diào)節(jié)旋鈕都設(shè)置得恰到好處、內(nèi)存被有效地分配到池和堆而且 I/O 均勻地分配到各個磁盤時，才可得到最佳性能。雖然量度和調(diào)整需要時間，但是執(zhí)行這 10 個建議的 DBA 將非常成功地滿足內(nèi)部和外部的 DB2 客戶。因為電子商務(wù)的變化和增長，即使是管理得最好的數(shù)據(jù)庫也需要定期的微調(diào)。DBA 的工作永遠(yuǎn)都做不完！
