DB2 效能最佳化引數

(轉)
1、Application Support Layer Heap Size (ASLHEAPSZ)
它是app和agent通訊的buffer，佔用例項共享記憶體空間。
監控:
get snapshot for all on | grep –i “Rejected Block Remote Cursor requests”
Rejected Block Remote Cursor requests = 2283
如果Rejected Block Remote Cursor requests值比較高，增大ASLHEAPSZ值，直到該值為0
配置：
update dbm cfg using aslheapsz 20

2、Maximum Requester I/O Block Size (RQRIOBLK)
它是client和通訊的buffer，佔用每個agent的私有記憶體空間。
監控:無法監控
配置：建議設定為最大值64K，預設32767bytes，（設到最大值不會影響其它效能）
update dbm cfg using rqrioblk 65536

3、Sort Heap Threshold (SHEAPTHRES)
私有模式排序空間最大閥值，值＝併發數×SORTHEAP
監控：
需要開啟sort監控開關－ update monitor switches using sort on
get snapshot for dbm | grep –i “sort”
如果Post threshold sorts值比較大，增加SORTHEAP 、SHEAPTHRES引數值
如果(Piped sorts accepted/Piped sorts requested)值比較低，增加SORTHEAP 、SHEAPTHRES引數值
配置：
update dbm cfg using sheapthres 80000

4、Enable Intra-Partition Parallelism (INTRA_PARALLEL)
在SMP環境中開啟該選項，提高表和索引掃描速度
監控：
list applications
看application對應的Agents（# of Agents）數目是否大於1
配置：
update dbm cfg using intra_parallel yes

5、Maximum Query Degree of Parallelism (MAX_QUERYDEGREE)
指定一個語句的最大subagent數目，當INTRA_PARALLEL值為yes時該引數起作用。如果該值為 ANY (-1)，那麼最佳化器將使用伺服器的最大cpu數目。
監控：
list applications
看application對應的Agents（# of Agents）數目是否大於1
配置：
update dbm cfg using MAX_QUERYDEGREE 4 IMMEDIATE

6、Query Heap Size (QUERY_HEAP_SZ)
佔 用agent的私有記憶體空間，儲存每個agent執行時所有的sql文，包括the input SQLDA，the output SQLDA，the statement text，the SQLCA，the package name，the package creator，the section number，a consistency token，the cursor control block for any blocking cursors。
監控：
無法監控
配置：
一般不需要修改，如果訪問大的LOB，可能需要增加該值
update dbm cfg using query_heap_sz 10000

7、Number of FCM Buffers (FCM_NUM_BUFFERS)
在multi-partitioned database（partition之間）和intra-partition parallelism enabled（subagent之間）環境中通訊快取。
在AIX上，如果DBM有充足的空間，每個partition依照FCM配置擁有獨立的空間，如果不夠，所有partition依照FCM配置共享空間；
在其它作業系統上，所有partition依照FCM配置共享空間；
如果DB2_FORCE_FCM_BP註冊變數設定為YES，所有partition將一直共享空間，但大小將受32bit的OS限制
監控：
get snapshot for FCM for all dbpartitionnums
配置：
update dbm cfg using fcm_num_buffers 4096 immediate

8、Connection、Agent配置
監控：
db2 get snapshot for dbm | grep -i agent
High water mark for agents registered = 2
High water mark for agents waiting for a token = 0
Agents registered = 2
Agents waiting for a token = 0
Idle agents = 1
Agents assigned from pool = 146
Agents created from empty pool = 3
Agents stolen from another application = 0
High water mark for coordinating agents = 2
Max agents verflow = 0
Gateway connection pool agents stolen = 0

9、Keep Fenced Process (KEEPFENCED)
UDF和SP按照執行模式分為兩種：fenced和unfenced，fenced模式是一種c/s的通訊方式，儲存過程為客戶端請求server的一個 agent為其執行業務邏輯。unfenced模式是一種直接呼叫db2程式並在程式的地址空間內執行，有不，但該模式可以讀取執行的PID，而 fenced模式做不到。
如果KEEPFENCED設定為YES，可以使UDF或SP所呼叫fenced程式或執行緒一直保持並被重複使用，一直到例項關閉才銷燬，但這將佔用一定資源（如記憶體）。例如，使用寫的sp，sp執行完成後不會結束JVM，下次執行sp將省去啟動JVM的時間。
配置：
update dbm cfg using keepfenced YES

10、Maximum Total of Files Open (MAXFILOP)
伺服器開啟檔案的最大數目，如果使用SMS容器，要求該值比較高，也需要檢查作業系統對該值的限制。
配置：
update db cfg using maxfilop 2000
監控：（需要bufferpool的monitor：db2 update monitor switches using bufferpool on）
db2 get snapshot for db on testdb |grep -i ‘close’

11、Default Buffer Pool Size (BUFFPAGE)
調整緩衝池的大小辦法：
1、 alter bufferpool IBMDEFAULTBP size -1，修改所有bufferpool大小為-1，然後依賴BUFFPAGE引數控制，緩衝池的數量：4（隱藏的）＋建立的緩衝池（含 IBMDEFAULTBP），每個建立的緩衝池大小＝pagesize×buffpage×（1＋5％）
2、直接修改bufferpool大小，建議使用該方法，可以控制pagesize大小不同緩衝池的大小。
配置：
update db cfg for using BUFFPAGE bigger_value
alter bufferpool IBMDEFAULTBP size -1
監控：
get snapshot for db on db_name

12、Log Buffer Size (LOGBUFSZ)
從logbuff寫到磁碟的啟用條件：
1）A transaction commits (or MINCOMMIT transactions commit). （最小提交事務數時flush）
2）The log buffer is full（日誌緩衝滿時flush）
3）One second has elapsed since the last log buffer flush.（間隔1秒時flush）
配置：
update database cfg for using LOGBUFSZ 256
監控：
get snapshot for database on | grep –i “Log space”
Log space available to the database (Bytes) = 4549916
Log space used by the database (Bytes) = 550084
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 550084
CLSA(current amount of log space available ) = Log space available to the database - Log space used by the database, CLSA就是LOGBUFSZ引數可以配置的最大值。
get snapshot for database on | grep –i “Log pages”
Log pages read = 0
Log pages written = 12644
日 志頁面讀（Log pages read）是日誌記錄器（logger）從磁碟讀取的日誌頁面的數目，而日誌頁面寫（Log pages written）是日誌記錄器（logger）寫入磁碟的日誌頁面的數目。理想狀態，Log pages read為0，如果該值比較大，考慮增加LOGBUFSZ值。

13、Application Heap Size (APPLHEAPSZ)
存放agent或subagent當前sql文處理的所需記憶體，大小決定於sql文的複雜度及宿主變數大小。如果是分割槽，這部分記憶體使用APP_CTL_HEAP_SZ堆，而不在應用程式堆。在執行時按需要分配記憶體，這個值僅是上限值。
配置：
update database cfg for using applheapsz 1024
監控：
無法監控，如果應用報錯，加倍該值，看應用錯誤是否消失

14、Sorting (SORTHEAP, SHEAPTHRES_SHR)
只有 INTRA_PARALLEL 資料庫器配置引數是 ON 或啟用集中器（concentrator）時（即當 MAX_CONNECTIONS 大於 MAX_COORDAGENTS 時），才可以使用共享排序。
對私有排序，SHEAPTHRES 在例項級是一個軟限制，如果超過這個值，系統將分配很小的排序堆，效能會下降
對共享排序，SHEAPTHRES_SHR在資料庫級是一個硬限制。
當併發使用者為10個，sortheap為100，如果是私有排序，SHEAPTHRES ＝10×sortheap，如果是共享排序，SHEAPTHRES_SHR＝5×sortheap

15、Locking (LOCKLIST, MAXLOCKS, LOCKTIMEOUT, DLCHKTIME)
LOCKLIST 表明分配給鎖列表的儲存容量。每個資料庫都有一個鎖列表，鎖列表包含了併發連線到該資料庫的所有應用程式所持有的鎖。鎖定是資料庫管理器用來控制多個應用 程式併發訪問資料庫中資料的機制。行和表都可以被鎖定。根據物件是否還持有其它鎖，每把鎖需要 32 個或 64 個位元組的鎖列表：
* 需要 64 個位元組來持有某個物件上的鎖，在這個物件上，沒有持有其它鎖。64bit的是112位元組
* 需要 32 個位元組來記錄某個物件上的鎖，在這個物件上，已經持有一個鎖。32bit的是56位元組
MAXLOCKS 定義了應用程式持有的鎖列表的百分比，當任何一個應用程式所持有的鎖數量達到整個鎖列表大小的這個百分比時，對該應用程式所持有的鎖進行鎖升級。如果鎖列 表用完了空間，那麼也會發生鎖升級。經驗：MAXLOCKS 引數乘以 MAXAPPLS 引數不能小於 100。
如果發生死鎖，將資料庫診斷級別改為4，資料庫日誌將記錄死鎖發生的原因（與誰發生了死鎖，鎖型別，是否行級鎖）
db2 update dbm cfg using diaglevel 3
0 為不記錄資訊
1 為僅記錄錯誤
2 記錄服務和非服務錯誤
預設是3，記錄db2的錯誤和警告
4 是記錄全部資訊，包括成功執行的資訊
監控：
get snapshot for database on | grep -i ‘Lock’
Locks held currently = 12
Lock waits = 0
Time database waited on locks (ms) = 0
Lock list memory in use (Bytes) = 2080
Deadlocks detected = 1
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Internal rollbacks due to deadlock = 1
如果診斷級別改為4，Lock Timeouts的詳細原因就可以記錄：
Request for lock “TAB: (2, 13)” in mode “.IX” timed out
Application caused the lock wait is “*LOCAL.DB2.007340152709″
Statement: 7570 6461 7465 2074 3120 7365 7420 6331 update t1 set c1
3d63 312b 3531 3231 30 =c1+51210
可以看到LOCAL.DB2.007340152709應用佔著t1表的鎖沒有釋放

16、Number of Asynchronous Page Cleaners (NUM_IOCLEANERS)
非同步清理緩衝池中髒頁的程式數.
清理程式啟動的條件：
1)髒頁閥值CHNGPGS_THRESH：表示髒頁佔緩衝池的百分數
2)達到SOFTMAX（soft checkpoint）值時，（如果softmax值是50，logfilesize值是10M，當寫入日誌檔案的資料量達到5M＝10M×50％時，啟動清理程式）
3）當發生Dirty page steals情況時，
配置：經驗值（A rule of thumb）－小於等於CPU數目
update db cfg for using NUM_IOCLEANERS 16
監控：
update monitor switches using bufferpool on
get snapshot for db on testdb|grep -i “writes”
Buffer pool data writes = 0
Asynchronous pool data page writes = 167660
Buffer pool index writes = 0
Asynchronous pool index page writes = 178944
PADW = (Asynchronous pool data page writes / Buffer pool data writes) * 100%
PAIX = (Asynchronous pool index page writes / Buffer pool index writes) * 100%
如果PADW 、PAIX 接近100％，應該減少NUM_IOCLEANERS
get snapshot for db on testdb|grep -i “cleaner trigger”
LSN Gap cleaner triggers = 142
Dirty page steal cleaner triggers = 2
Dirty page threshold cleaner triggers = 396
如果Dirty page steal cleaner triggers 值非常小，其它兩個大，說明配置恰當。
如果Dirty page steal cleaner triggers 值非常大，LSN Gap cleaner triggers比較小，說明softmax值比較高，需要調小。
計算每次cleaner啟動後，寫多少page？
APPAW = (167660 + 178944) / (142 + 2 + 396) ＝ 641 Pages
相當於每次cleaner寫641×4k＝2.5M資料到硬碟，對於1G的buffer來講，啟動次數太多，但對100M的buffer來講，是合適的。

17、Number of I/O Servers (NUM_IOSERVERS)
該引數值就是預取器的數量。一般該值等於資料庫資料所在的磁碟數目。

18、Number of Commits to Group (MINCOMMIT)
如果1秒內有6個事務，該引數最後調到6。
並且也要調logbufsize>=MINCOMMIT×平均每個事務消耗的空間
配置：
update db cfg for using MINCOMMIT 5
監控：
get snapshot for database on
Last reset timestamp = 09-12-2002 14:51:43.786876
Snapshot timestamp = 09-12-2002 14:56:27.787088
Commit statements attempted = 1011
Rollback statements attempted = 10
Log space used by the database (Bytes) = 3990
Log pages written = 23
每秒執行的事務數＝(Commit statements attempted + Rollback statements attempted) /
(Last reset timestamp - Snapshot timestamp)

19、Catalog Cache Size (CATALOGCACHE_SZ)
配置：
update db cfg for using CATALOGCACHE_SZ 32
監控：
db2 get snapshot for db on testdb|grep -i “catalog”
Catalog database partition number = 0
Catalog network node name =
Catalog cache lookups = 17
Catalog cache inserts = 7
Catalog cache verflows = 0
Catalog cache high water mark = 0
如果命中率(1 – (Catalog cache inserts / Catalog cache lookups)) * 100 < 95％,增加該引數值。
如果Catalog cache overflows 不為0， 也需要增加該引數值，一般同時會增加dbheap引數值。

20、Average Number of Active Applications (AVG_APPLS)
DB2最佳化器根據這個引數來評估資源的使用策略，特別是緩衝池空間。
配置：
update db cfg for using AVG_APPLS 16
監控：
db2 get snapshot for db on testdb|grep -i “appls”
Appls. executing in db manager currently = 0

修改註冊變數：
DB2_SKIPINSERTED=on,DB2_SKIPDELETED=on,DB2_EVALUNCOMMITTED=on(V.8.2以上)
可以提高資料庫併發量，但是查詢的結果不含正在插入或刪除的記錄。