【51CTO綜述】在上一篇文章中我們看了DB2實(shí)用程序的性能優(yōu)化，這次我們來關(guān)注一下DB2并發(fā)連接時(shí)要做的性能考慮。

一般來說在連接數(shù)較少情況下，db2 的性能會(huì)比較穩(wěn)定。因?yàn)檫@時(shí)連接的應(yīng)用所產(chǎn)生的請(qǐng)求比 db2 代理池中所能產(chǎn)生的協(xié)調(diào)代理少，這時(shí)基本上能夠滿足每一個(gè)請(qǐng)求都能夠被及時(shí)的協(xié)調(diào)代理所響應(yīng)處理。 在連接集中器激活(MAX_CONNECTIONS > MAX_COORDAGENTS)的情況下，如果連接數(shù)超過了協(xié)調(diào)代理，這時(shí)連接所過來的請(qǐng)求就會(huì)進(jìn)入隊(duì)列等候協(xié)調(diào)代理服務(wù)，并發(fā)的連接數(shù)提高了，但是某些連接的性能就會(huì)顯著下降。此時(shí)應(yīng)當(dāng)考慮激活分區(qū)間并行 (SMP) 或多分區(qū)(MPP)特性來增加 I/O 的并行性以及多個(gè) CPU 的并行運(yùn)算。

案例分析

查詢優(yōu)化案例

接下來這里從一個(gè)試驗(yàn)來看一下 DML 操作過程中優(yōu)化的詳細(xì)步驟和具體數(shù)據(jù)。首先看一個(gè)查詢優(yōu)化的例子，下面是試驗(yàn)中的建表語句：

CREATE TABLE MCLAIM.T1_DMS (  
C11 VARCHAR (10) NOT NULL ,  
C12 VARCHAR (15) NOT NULL ,  
C13 VARCHAR (20) NOT NULL ,  
CONSTRAINT C11_PK PRIMARY KEY ( C11) ) IN DMS_Space;  
CREATE TABLE MCLAIM.T2_DMS (  
C21 VARCHAR (15) NOT NULL ,  
C22 VARCHAR (25) NOT NULL ,  
C23 VARCHAR (30) NOT NULL ,  
CONSTRAINT C21_PK PRIMARY KEY ( C21) ) IN DMS_Space;  
CREATE TABLE MCLAIM.T3_DMS (  
C31 VARCHAR (10) NOT NULL ,  
C32 VARCHAR (25) NOT NULL ,  
C33 VARCHAR (35) NOT NULL ,  
CONSTRAINT C31_PK PRIMARY KEY ( C31) ) IN DMS_Space; 

最初的環(huán)境沒有優(yōu)化，表空間類型 SMS 表空間，查詢的表中沒有索引，sortheap 過小等等。在這種情況下執(zhí)行下列查詢語句：

select C12 from TESTOPT.T1_SMS,%SCHEMA%.T2_SMS,%SCHEMA%.T3_SMS  
where substr(C12,1,10)=substr(C21,1,10) and C22=C32  
order by C12 asc 

在沒有優(yōu)化的情況下得到的總的執(zhí)行時(shí)間是 653 秒，而經(jīng)過優(yōu)化后得到總的執(zhí)行時(shí)間是大概是 15 秒左右。在優(yōu)化中采用了如下優(yōu)化步驟：

選擇 DMS 表空間。

添加索引：

CREATE UNIQUE INDEX INDEX_C12 on T1_DMS (C12 ASC);  
CREATE UNIQUE INDEX INDEX_C22 on T2_DMS (C22 ASC);  
CREATE UNIQUE INDEX INDEX_C32 on T2 _DMS (C32 ASC); 

增大 sortheap 的大小

執(zhí)行 runstats

選擇適當(dāng)?shù)膬?yōu)化級(jí)別

改進(jìn)表結(jié)構(gòu)，增加冗余字段。以空間換時(shí)間：

ALTER TABLE T1 ADD C12_Red VARCHAR(10);  
ALTER TABLE T2 ADD C21_Red VARCHAR(10);  
UPDATE T1 SET C12_Red=SUBSTR(C12,1,10);  
UPDATE T2 SET C21_Red=SUBSTR(C21,1,10); 

查詢語句變成：

select C12 from TESTOPT.T1_DMS, TESTOPT.T2_DMS, TESTOPT.T3_DMS  
where C12_Red=C21_Red and C22=C32 order by C12 asc 

圖 1. 查詢操作優(yōu)化示意圖

從圖中可以看出選擇好的表空間類型 ( 數(shù)據(jù)庫(kù)管理表空間 ) 和添加索引會(huì)對(duì)性能有很大的改善作用。而添加冗余字段對(duì)性能的改進(jìn)作用最大。當(dāng)然這會(huì)涉及表結(jié)構(gòu)的變化，是需要在數(shù)據(jù)庫(kù)設(shè)計(jì)階段考慮的因素。同時(shí)代價(jià)是增加磁盤的占用空間。

寫入操作優(yōu)化

接下來是一個(gè)寫操作的例子(插入)。下面是試驗(yàn)的腳本：

CONNECT TO FFTEST;  
CREATE SCHEMA TESTOPT;  
DROP TABLE TESTOPT.T3;  
CREATE TABLE TESTOPT.T3 (  
C31 VARCHAR (10) NOT NULL ,  
C32 VARCHAR (15) NOT NULL ,  
CONSTRAINT C31_A CHECK ( C31 LIKE 'A%' or C31 LIKE 'a%'));  
CREATE INDEX TESTOPT.INDEX_C31 on TESTOPT.T3 (C31 ASC);  
ALTER TABLE TESTOPT.T3 ADD CONSTRAINT C31_A CHECK (substr(C31,1,1)= ’ a ’  
or substr(C31,1,1)= ’ A ’ )  
ALTER TABLE TESTOPT.T3 APPEND OFF;  
CONNECT RESET; 

最初的表沒有優(yōu)化，含有索引，約束等因素，插入 4 萬條記錄大約花了 68 秒鐘，而最終優(yōu)化后插入 4 萬條記錄只需 6 秒鐘。如下是優(yōu)化步驟：

1. 去除索引。
2. 去除約束。
3. 在 insert 語句中包括多行。
4. 采用 Append 模式
5. 屏蔽表的日志操作。
6. 采用并行寫操作。
7. 采用嚴(yán)格的隔離級(jí)別。

圖 2. 插入操作優(yōu)化示意圖

從圖中可以看出減少索引和約束可以大幅度提高插入性能，而將多條插入語句合并成一行產(chǎn)生的效果更加明顯。

性能調(diào)優(yōu)注意事項(xiàng)

為了得到高性能將緩沖池調(diào)得過大，導(dǎo)致數(shù)據(jù)庫(kù)連不上。這對(duì)沒有經(jīng)驗(yàn)的用戶來說可能是個(gè)災(zāi)難，這意味著數(shù)據(jù)庫(kù)可能要重建。最初我們?cè)?jīng)犯過這樣的錯(cuò)誤。現(xiàn)在可以通過調(diào)節(jié) DB2 注冊(cè)參數(shù) DB2_OVERRIDE_BPF 來設(shè)置緩沖池的大小，從而能夠再次連接數(shù)據(jù)庫(kù)。當(dāng)然最好將 STMM 激活，使內(nèi)存能夠自動(dòng)調(diào)整。

往往忽視 runstats 和 reorg 的作用，我們發(fā)現(xiàn)不止一個(gè)的性能問題，都是由于優(yōu)化器選擇了錯(cuò)誤的 access plan 導(dǎo)致系統(tǒng)整體性能下降。而對(duì)外顯示的則不光是 SQL 執(zhí)行慢，同時(shí)也能會(huì)表現(xiàn)出 I/O 瓶頸或系統(tǒng)響應(yīng)時(shí)間長(zhǎng)。這往往會(huì)誤導(dǎo)我們?nèi)シ治銎渌胤?。但究其根源，很多時(shí)間是由于優(yōu)化器的錯(cuò)誤。這些問題往往在重新執(zhí)行 runstats 和 reorg 之后就解決了。所以這兩個(gè)命令也要特別注意。

在進(jìn)行數(shù)據(jù)加載的時(shí)候往往忽略了索引因素，導(dǎo)致性能加載性能下降。我們遇到過這樣的一個(gè)例子，一張表導(dǎo)入 1000 條記錄花了 5 分鐘，檢查了很多配置找不到原因，最后發(fā)現(xiàn)這張表上有 1 個(gè)主鍵，還有 4 個(gè)外鍵。將他們刪除后重新導(dǎo)入只花了幾秒鐘。所以在進(jìn)行 load 或者是 insert 的時(shí)候盡量將主外鍵或相關(guān)索引刪除，加載完成后重建相關(guān)索引。主外鍵盡量通過加載程序來保證它的數(shù)據(jù)完整性。這一點(diǎn)往往會(huì)被忽略，所以在加載數(shù)據(jù)前先檢查一下所有表的索引狀態(tài)及引用關(guān)系。

在修改 db2 參數(shù)的時(shí)候，一次最好修改一個(gè)參數(shù)，然后看看效果，在調(diào)節(jié)其他參數(shù)。否則一次多個(gè)參數(shù)，調(diào)好了也沒弄清楚是哪個(gè)參數(shù)起的作用。下次還得全部來一遍。還要注意，并非所有參數(shù)都是越大越好，有時(shí)可能會(huì)適得其反。

注意索引的試用，優(yōu)化好的索引對(duì)查詢語句性能的提高往往會(huì)產(chǎn)生數(shù)十倍的性能改進(jìn)。所以，調(diào)優(yōu)前可以先察看一下相關(guān)語句的索引利用情況。這可以通過察看 SQL 語句和執(zhí)行計(jì)劃，看一下已有索引是否被利用起來了或是否需要建立新的索引。這往往比 DB2 系統(tǒng)調(diào)優(yōu)更重要。但切記考慮插入操作，索引也會(huì)降低插入的性能。這一點(diǎn)要綜合考慮。

由于 XML 數(shù)據(jù)可以跨頁(yè)存儲(chǔ)，在設(shè)計(jì) XML 數(shù)據(jù)庫(kù)時(shí)要盡可能的使用較大的數(shù)據(jù)頁(yè)，這樣可以避免 XML 數(shù)據(jù)跨頁(yè)查詢，以提高查詢性能。

采用表分區(qū)：有這樣一個(gè)例子：客戶有一張表的數(shù)據(jù)量非常大，每天都會(huì)產(chǎn)生大約 30 萬條記錄，同時(shí)每天都會(huì)刪除五天前的記錄，所以此表大概有 150 萬條記錄，現(xiàn)在客戶在每天的第一次查詢時(shí)要重新對(duì)表進(jìn)行索引(因?yàn)橥砩蠒?huì)產(chǎn)生很多數(shù)據(jù)，所以新增加的數(shù)據(jù)都沒有建索引)，導(dǎo)致響應(yīng)非常慢!對(duì)于這種問題，后來采用了表分區(qū)，用 6 個(gè)分區(qū)表來分別裝載原來 6 天的數(shù)據(jù)。所以查詢和插入都只涉及一張表，所以響應(yīng)速度得到大幅度提高。

了解 CHNGPGS_THRESH 參數(shù)，是緩沖池寫日志的閥值。有一個(gè)例子，在創(chuàng)建索引時(shí)比較慢，經(jīng)過檢查發(fā)現(xiàn) CHNGPGS_THRESH 參數(shù)過大，造成每次寫日志的時(shí)候數(shù)據(jù)量過大，造成 I/O 瓶頸，適當(dāng)減小這個(gè)參數(shù)值，可以增加寫日志的次數(shù)，但數(shù)減少每次寫日志的數(shù)據(jù)量，這對(duì)于大緩沖池里的大表上創(chuàng)建索引時(shí)很有效的。

在導(dǎo)入數(shù)據(jù)時(shí)盡量采用 load, 少用 import, 我們做過統(tǒng)計(jì)，用 import 花費(fèi) 10 分鐘的數(shù)據(jù)，用 load 大概只需要 1 分鐘，這大大提高了工作效率。

注意 db2diag.log 的大小，當(dāng)這個(gè)文件很大的時(shí)候，數(shù)據(jù)庫(kù)的所有操作，包括停啟 db2 都會(huì)特別的慢，有時(shí)甚至掛起。所以要經(jīng)?？纯催@個(gè)文件的大小，過大時(shí)最好刪掉，重啟 db2 。當(dāng)然 DIAGLEVEL 不要設(shè)得太高，除非為了診斷某個(gè)問題獲得更多信息，一般默認(rèn)的 3 足夠了。