讓數(shù)據(jù)庫飛起來 十大DB2優(yōu)化技巧
為了幫助DB2 DBA 避免性能災(zāi)難并獲得高性能，我為我們的客戶、用戶和 DB2 專家同行總結(jié)了一套故障診斷流程。以下詳細(xì)說明在 Unix、Windows 和 OS/2 環(huán)境下使用 DB2 UDB 的電子商務(wù)OLTP 應(yīng)用程序的10 條最重要的性能改善技巧，希望下文中涉及到的內(nèi)容對大家能夠有所幫助。
一、 監(jiān)視開關(guān)
確保已經(jīng)打開監(jiān)視開關(guān)。如果它們沒有打開，您將無法獲取您需要的性能信息。要打開該監(jiān)視開關(guān)，請發(fā)出以下命令：
1 db2 "update monitor switches using
2 lock ON sort ON bufferpool ON uow ON
3 table ON statement ON"
二、代理程序
確保有足夠的 DB2 代理程序來處理工作負(fù)載。要找出代理程序的信息，請發(fā)出命令：
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
如果您發(fā)現(xiàn)Agents waiting for a token或Agents stolen from another application不為 0，那么請增加對數(shù)據(jù)庫管理器可用的代理程序數(shù)(MAXAGENTS 和/或 MAX_COORDAGENTS取適用者)。
三、最大打開的文件數(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)時間并耗費了 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"
四、鎖
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"
查找以下行：
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數(shù)據(jù)庫配置中增加 4k 頁的數(shù)量。
五、臨時表空間
為了改善 DB2 執(zhí)行并行 I/O 和提高使用TEMPSPACE的排序、散列連接(hash join)和其它數(shù)據(jù)庫操作的性能，臨時表空間至少應(yīng)該在三個不同的磁盤驅(qū)動器上擁有三個容器。
要想知道您的臨時表空間具有多少容器，請發(fā)出以下命令：
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的倍數(shù)。這個倍數(shù)應(yīng)該等于容器的個數(shù)。
要查找容器的定義，請發(fā)出以下命令：
db2 "list tablespace containers for 1 show detail"
指的是tablespace ID #1，它是剛才所給出的示例中的TEMPSPACE1。
六、內(nèi)存排序
OLTP 應(yīng)用程序不應(yīng)該執(zhí)行大的排序。它們在 CPU、I/O 和所用時間方面的成本極高，而且將使任何 OLTP 應(yīng)用程序慢下來。因此，256 個 4K 頁(1MB)的缺省SORTHEAP大小(1MB)應(yīng)該是足夠了。您也應(yīng)該知道排序溢出的數(shù)量和每個事務(wù)的排序數(shù)。
請發(fā)出以下命令：
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 個百分點，那么在應(yīng)用程序 SQL 中會出現(xiàn)嚴(yán)重的或意外的排序問題。因為正是溢出的存在表明發(fā)生了大的排序，所以理想的情況是發(fā)現(xiàn)沒有排序溢出或至少其百分比小于一個百分點。
如果出現(xiàn)過多的排序溢出，那么“應(yīng)急”解決方案是增加SORTHEAP的大小。然而，這樣做只是掩蓋了真實的性能問題。相反，您應(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ā)出兩個命令：
1 db2 "get snapshot for database on DBNAME"
2 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ì)的索引來運行 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 的數(shù)量很大就可能意味著您需要重組表。當(dāng)由于更改了行的寬度從而 DB2 必須在一個不夠理想的頁上定位一個行時就會發(fā)生溢出。
八、表空間分析
表空間快照對理解訪問什么數(shù)據(jù)以及如何訪問是極其有價值的。要得到一個表空間快照，請發(fā)出以下命令：
db2 "get snapshot for tablespaces on DBNAME"
對每個表空間，回答以下問題：
平均讀取時間(ms)是多少?
平均寫入時間(ms)是多少?
異步(預(yù)取)相對于同步(隨機(jī))所占的物理 I/O 的百分比是多少?
每個表空間的緩沖池命中率是多少?
每分鐘讀取多少物理頁面?
對于每個事務(wù)要讀取多少物理和邏輯頁面?
對于所有表空間，回答以下問題：
哪個表空間的讀取和寫入的時間最慢?為什么?是因為其容器在慢速的磁盤上嗎?容器大小是否相等?對比異步訪問和同步訪問，訪問屬性是否和期望的一致?隨機(jī)讀取的表應(yīng)該有隨機(jī)讀取的表空間，這是為了得到高的同步讀取百分比、通常較高的緩沖池命中率和更低的物理 I/O 率。
對每個表空間，確保預(yù)取大小等于數(shù)據(jù)塊大小乘以容器數(shù)。請發(fā)出以下命令：
db2 "list tablespaces show detail"
如果需要，可以為一個給定表空間改變預(yù)取大小。可以使用以下命令來檢查容器定義：
db2 "list tablespace containers for N show detail"
在此，N 是表空間標(biāo)識號。
九、緩沖池優(yōu)化
我時常發(fā)現(xiàn)一些 DB2 UDB 站點，雖然機(jī)器具有 2、4 或 8GB 內(nèi)存，但是 DB2 數(shù)據(jù)庫卻只有一個緩沖池(IBMDEFAULTBP)，其大小只有 16MB!
如果在您的站點上也是這種情況，請為 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)存。正好合適的緩沖池大小就在“太小”和“太大”之間的某個平衡點上。適當(dāng)?shù)拇笮〈嬖谟诨貓髮⒁_始減少的點上。如果您沒有使用工具來自動進(jìn)行回報減少分析，那么您應(yīng)該在不斷增加緩沖池大小上科學(xué)地測試緩沖池性能(命中率、I/O 時間和物理 I/O 讀取率)，直到達(dá)到最佳的緩沖池大小。因為業(yè)務(wù)一直在變動和增長，所以應(yīng)該定期重新評估“最佳大小”決策。
十、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 語句的實際成本是它執(zhí)行一次的成本乘以它執(zhí)行的次數(shù)。
每個 DBA 所面臨的重大的任務(wù)是，識別具有最高“實際成本”的語句的挑戰(zhàn)，并且減少這些語句的成本。
通過本機(jī) DB2 Explain 實用程序、一些第三方供應(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)用程序運行。
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)識唯一的語句(獨立于文字值)、每個唯一語句的頻率(它出現(xiàn)的次數(shù))和其總 CPU、排序以及其它資源成本的總計。如此徹底的分析在 30 分鐘的應(yīng)用程序 SQL 活動樣本上可能要花一周或更多的時間。
要減少確定高成本 SQL 語句所花的時間，您可以考慮許多可用的信息來源：
從技巧 4，務(wù)必要計算在每個事務(wù)中從每個表中讀取的行數(shù)。如果產(chǎn)生的數(shù)字看上去很大，那么 DBA 可以在 SQL Event Monitor 格式化輸出中搜索有關(guān)的表名稱(這將縮小搜索范圍而且節(jié)省一些時間)，這樣也許能夠找出有問題的語句。 從 技巧 3，務(wù)必計算每個表空間的異步讀取百分比和物理 I/O 讀取率。如果一個表空間的異步讀取百分比很高并遠(yuǎn)遠(yuǎn)超過平均的物理 I/O 讀取率，那么在此表空間中的一個或更多的表正在被掃描。查詢目錄并找出哪些表被分配到可疑的表空間(每個表空間分配一個表提供最佳性能檢測)，然后在 SQL Event Monitor 格式化輸出中搜索這些表。這些也可能有助于縮小對高成本 SQL 語句的搜索范圍。 嘗試觀察應(yīng)用程序執(zhí)行的每條 SQL 語句的 DB2 Explain 信息。然而，我發(fā)現(xiàn)高頻率、低成本語句經(jīng)常爭用機(jī)器容量和能力來提供期望的性能。如果分析時間很短而且最大性能是關(guān)鍵的，那么請考慮使用供應(yīng)商提供的工具(它們能夠快速自動化識別資源密集的 SQL 語句的過程)。 Database-GUYS Inc.的 SQL-GUY 工具提供精確、實時且均衡的 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)都做不完!
快速回顧最棒的10 個技巧
* 對工作負(fù)載使用足夠的代理程序。
* 不允許 DB2 不必要地關(guān)閉和打開文件。
* 不允許長期的鎖等待。
* 確保數(shù)據(jù)庫的 TEMPSPACE 表空間的并行 I/O 能力。
* 保守地管理 DB2 排序內(nèi)存并不要以大的 SORTHEAP 來掩蓋排序問題。
* 分析表的訪問活動并確定具有特別高的每個事務(wù)讀取行數(shù)或溢出數(shù)的表。
* 分析每個表空間的性能特性，并尋求改善讀取時間最慢、等待時間最長、物理 I/O 讀取率最高、命中率最差的表空間性能以及與所期望的不一致的訪問屬性。
* 創(chuàng)建多個緩沖池，有目的地將表空間分配到緩沖池以便于共享訪問屬性。
* 檢查 DB2 UDB SQL Event Monitor 信息以找到哪個 SQL 語句消耗計算資源最多并采取正確的措施。
一旦排除了高成本 SQL，馬上重新評估配置和物理設(shè)計設(shè)置。關(guān)于DB2優(yōu)化技巧九為大家介紹到這里，希望大家都能夠從中有所收獲。
【編輯推薦】
