DB2數(shù)據(jù)庫(kù)性能優(yōu)化的幾個(gè)小技巧
DB2數(shù)據(jù)庫(kù)提供了高層次的數(shù)據(jù)利用性、完整性、安全性、可恢復(fù)性，以及小規(guī)模到大規(guī)模的執(zhí)行能力，其性能是非常強(qiáng)大的，首先介紹一下最簡(jiǎn)單而最見(jiàn)成效的——Bufferpool
緩沖池是內(nèi)存中的一塊存儲(chǔ)區(qū)域，用于臨時(shí)讀入和更改數(shù)據(jù)庫(kù)頁(yè)(包含表行或索引項(xiàng))。緩沖池的用途是為了提高數(shù)據(jù)庫(kù)系統(tǒng)的性能。從內(nèi)存訪問(wèn)數(shù)據(jù)要比從磁盤訪問(wèn)數(shù)據(jù)快得多。因此，數(shù)據(jù)庫(kù)管理器需要從磁盤讀取或?qū)懭氪疟P的次數(shù)越少，性能就越好。對(duì)一個(gè)或多個(gè)緩沖池進(jìn)行配置之所以是調(diào)優(yōu)的最重要方面，是因?yàn)檫B接至數(shù)據(jù)庫(kù)的應(yīng)用程序的大多數(shù)數(shù)據(jù)(不包括大對(duì)象和長(zhǎng)字段數(shù)據(jù))操作都在緩沖池中進(jìn)行。
缺省情況下，應(yīng)用程序使用緩沖池 IBMDEFAULTBP，它是在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)創(chuàng)建的。當(dāng) SYSCAT.BUFFERPOOLS 目錄表中該緩沖池的 NPAGES 值為 -1 時(shí)，DB2 數(shù)據(jù)庫(kù)配置參數(shù) BUFFPAGE 控制著緩沖池的大小。否則會(huì)忽略 BUFFPAGE 參數(shù)，并且用 NPAGES 參數(shù)所指定的頁(yè)數(shù)創(chuàng)建緩沖池。
建議對(duì)于僅使用一個(gè)緩沖池的應(yīng)用程序，將 NPAGES 更改成 -1，這樣 BUFFPAGE 就可以控制該緩沖池的大小。這使得更新和報(bào)告緩沖池大小以及其它 DB2 數(shù)據(jù)庫(kù)配置參數(shù)變得更加方便。
確保可以使用數(shù)據(jù)庫(kù)配置中的 BUFFPAGE 參數(shù)來(lái)控制緩沖池大小之后，將該參數(shù)設(shè)置成合適的值。根據(jù)數(shù)據(jù)庫(kù)的大小和應(yīng)用程序的性質(zhì)將該參數(shù)設(shè)置成一個(gè)合理的大值，這種做法很安全。通常，該參數(shù)的缺省值非常小，可能滿足不了要求。
db2 "get snapshot for all bufferpools"
在數(shù)據(jù)庫(kù)快照或緩沖池快照的快照輸出中，查找下列"logical reads"和"physical reads"，這樣就可以計(jì)算出緩沖池命中率，它可以幫助調(diào)優(yōu)緩沖池:
緩沖池命中率表明數(shù)據(jù)庫(kù)管理器不需要從磁盤裝入頁(yè)(即該頁(yè)已經(jīng)在緩沖池中)就能處理頁(yè)請(qǐng)求的時(shí)間百分比。緩沖池的命中率越高，使用磁盤 I/O 的頻率就越低。按如下計(jì)算緩沖池命中率:
(1 - ((buffer pool data physical reads + buffer pool index physical reads) /
(buffer pool data logical reads + pool index logical reads))
) * 100%
這個(gè)計(jì)算考慮了緩沖池高速緩存的所有頁(yè)(索引和數(shù)據(jù))。理想情況下，該比率應(yīng)當(dāng)超過(guò) 95%，并盡可能接近 100%。要提高緩沖池命中率，請(qǐng)嘗試下面這些方法:
1、增加緩沖池大小。
2、考慮分配多個(gè)緩沖池，如果可能的話，為每個(gè)經(jīng)常被訪問(wèn)的大表所屬的表空間分配一個(gè)緩沖池，為一組小表分配一個(gè)緩沖池，然后嘗試一下使用不同大小的緩沖池以查看哪種組合會(huì)提供最佳性能。
3、如果已分配的內(nèi)存不能幫助提高性能，那么請(qǐng)避免給緩沖池分配過(guò)多的內(nèi)存。應(yīng)當(dāng)根據(jù)取自測(cè)試環(huán)境的快照信息來(lái)決定緩沖池的大小。
4、太小的緩沖池會(huì)產(chǎn)生過(guò)多的、不必要的物理 I/O。太大的緩沖池使系統(tǒng)處在操作系統(tǒng)頁(yè)面調(diào)度的風(fēng)險(xiǎn)中并消耗不必要的 CPU 周期來(lái)管理過(guò)度分配的內(nèi)存。正好合適的緩沖池大小就在"太小"和"太大"之間的某個(gè)平衡點(diǎn)上。適當(dāng)?shù)拇笮〈嬖谟诨貓?bào)將要開(kāi)始減少的點(diǎn)上。
獲得最佳性能的——SQL
一條糟糕的 SQL 語(yǔ)句會(huì)徹底破壞一切。一個(gè)相對(duì)簡(jiǎn)單的 SQL 語(yǔ)句也能夠搞糟一個(gè)調(diào)整得很好的數(shù)據(jù)庫(kù)和機(jī)器。對(duì)于很多這些語(yǔ)句，天底下(或在文件中)沒(méi)有 DB2 UDB 配置參數(shù)能夠糾正因錯(cuò)誤的 SQL 語(yǔ)句導(dǎo)致的高成本的情況。
更糟糕的是，DBA 常常受到種種束縛:不能更改 SQL(可能是因?yàn)樗菓?yīng)用程序供應(yīng)商提供的)。這給 DBA 只留下三條路可走:
1. 更改或添加索引
2. 更改群集
3. 更改目錄統(tǒng)計(jì)信息
健壯的應(yīng)用程序由成千上萬(wàn)條不同的 SQL 語(yǔ)句組成。這些語(yǔ)句執(zhí)行的頻率隨應(yīng)用程序的功能和日常的業(yè)務(wù)需要的不同而不同。SQL 語(yǔ)句的實(shí)際成本是它執(zhí)行一次的成本乘以它執(zhí)行的次數(shù)。
每個(gè) DBA 所面臨的重大的任務(wù)是，識(shí)別具有最高"實(shí)際成本"的語(yǔ)句的挑戰(zhàn)，并且減少這些語(yǔ)句的成本。
通過(guò)本機(jī) DB2 Explain 實(shí)用程序、一些第三方供應(yīng)商提供的工具或 DB2 UDB SQL Event Monitor 數(shù)據(jù)，可以計(jì)算出執(zhí)行一次 SQL 語(yǔ)句所用的資源成本。但是語(yǔ)句執(zhí)行頻率只能通過(guò)仔細(xì)和耗時(shí)地分析 DB2 UDB SQL Event Monitor 的數(shù)據(jù)來(lái)了解。
最佳性能不僅需要排除高成本 SQL 語(yǔ)句，而且需要確保相應(yīng)的物理基礎(chǔ)結(jié)構(gòu)是適當(dāng)?shù)?。?dāng)所有的調(diào)節(jié)旋鈕都設(shè)置得恰到好處、內(nèi)存被有效地分配到池和堆而且 I/O 均勻地分配到各個(gè)磁盤時(shí)，才可得到最佳性能。
不可遺漏的——Lock
這些與鎖相關(guān)的控制都是數(shù)據(jù)庫(kù)配置參數(shù):
LOCKLIST 表明分配給鎖列表的存儲(chǔ)容量。每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)鎖列表，鎖列表包含了并發(fā)連接到該數(shù)據(jù)庫(kù)的所有應(yīng)用程序所持有的鎖。鎖定是數(shù)據(jù)庫(kù)管理器用來(lái)控制多個(gè)應(yīng)用程序并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)中數(shù)據(jù)的機(jī)制。行和表都可以被鎖定。根據(jù)對(duì)象是否還持有其它鎖，每把鎖需要 32 個(gè)或 64 個(gè)字節(jié)的鎖列表:
1、需要 64 個(gè)字節(jié)來(lái)持有某個(gè)對(duì)象上的鎖，在這個(gè)對(duì)象上，沒(méi)有持有其它鎖。
2、需要 32 個(gè)字節(jié)來(lái)記錄某個(gè)對(duì)象上的鎖，在這個(gè)對(duì)象上，已經(jīng)持有一個(gè)鎖。
MAXLOCKS 定義了應(yīng)用程序持有的鎖列表的百分比，在數(shù)據(jù)庫(kù)管理器執(zhí)行鎖升級(jí)之前必須填充該鎖列表。當(dāng)一個(gè)應(yīng)用程序所使用的鎖列表百分比達(dá)到 MAXLOCKS 時(shí)，數(shù)據(jù)庫(kù)管理器會(huì)升級(jí)這些鎖，這意味著用表鎖代替行鎖，從而減少列表中鎖的數(shù)量。當(dāng)任何一個(gè)應(yīng)用程序所持有的鎖數(shù)量達(dá)到整個(gè)鎖列表大小的這個(gè)百分比時(shí)，對(duì)該應(yīng)用程序所持有的鎖進(jìn)行鎖升級(jí)。如果鎖列表用完了空間，那么也會(huì)發(fā)生鎖升級(jí)。數(shù)據(jù)庫(kù)管理器通過(guò)查看應(yīng)用程序的鎖列表并查找行鎖最多的表，來(lái)決定對(duì)哪些鎖進(jìn)行升級(jí)。如果用一個(gè)表鎖替換這些行鎖，將不再會(huì)超出 MAXLOCKS 值，那么鎖升級(jí)就會(huì)停止。否則，鎖升級(jí)就會(huì)一直進(jìn)行，直到所持有的鎖列表百分比低于 MAXLOCKS。MAXLOCKS 參數(shù)乘以 MAXAPPLS 參數(shù)不能小于 100。
雖然升級(jí)過(guò)程本身并不用花很多時(shí)間，但是鎖定整個(gè)表(相對(duì)于鎖定個(gè)別行)降低了并發(fā)性，而且數(shù)據(jù)庫(kù)的整體性能可能會(huì)由于對(duì)受鎖升級(jí)影響的表的后續(xù)訪問(wèn)而降低。
LOCKTIMEOUT 的缺省值是 -1，這意味著將沒(méi)有鎖超時(shí)(對(duì) OLTP 應(yīng)用程序，這種情況可能會(huì)是災(zāi)難性的)。許多 DB2 用戶用 LOCKTIMEOUT = -1。將 LOCKTIMEOUT 設(shè)置為很短的時(shí)間值，例如 10 或 15 秒。在鎖上等待過(guò)長(zhǎng)時(shí)間會(huì)在鎖上產(chǎn)生雪崩效應(yīng)。
首先，用以下命令檢查 LOCKTIMEOUT 的值:
db2 "get db cfg for DBNAME"
并查找包含以下文本的行:
Lock timeout (sec) (LOCKTIMEOUT) = -1
如果值是 -1，考慮使用以下命令將它更改為 15 秒(一定要首先詢問(wèn)應(yīng)用程序開(kāi)發(fā)者或供應(yīng)商以確保應(yīng)用程序能夠處理鎖超時(shí)):
db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"
同時(shí)應(yīng)該監(jiān)視鎖等待的數(shù)量、鎖等待時(shí)間和正在使用鎖列表內(nèi)存(lock list memory)的量。請(qǐng)發(fā)出以下命令:
db2 "get snapshot for database on DBNAME"
如果 Lock list memory in use (Bytes) 超過(guò)所定義 LOCKLIST 大小的 50%，那么在 LOCKLIST 數(shù)據(jù)庫(kù)配置中增加 4k 頁(yè)的數(shù)量。
掩蓋問(wèn)題的—SORTHEAP
SORTHEAP 是一個(gè)數(shù)據(jù)庫(kù)配置參數(shù)，它定義了私有排序所使用的私有內(nèi)存頁(yè)的最大數(shù)目，或共享排序所使用的共享內(nèi)存頁(yè)的最大數(shù)目。如果排序是私有排序，那么該參數(shù)影響代理程序私有內(nèi)存。如果排序是共享排序，那么該參數(shù)影響數(shù)據(jù)庫(kù)的共享內(nèi)存。
每個(gè)排序都有單獨(dú)的由數(shù)據(jù)庫(kù)管理器按需分配的排序堆。在排序堆中對(duì)數(shù)據(jù)進(jìn)行排序。如果由優(yōu)化器來(lái)指導(dǎo)排序堆大小的分配，那么用優(yōu)化器提供的信息來(lái)分配的排序堆的大小要小于由該參數(shù)所指定的排序堆大小。
SHEAPTHRES 是一個(gè)數(shù)據(jù)庫(kù)管理器配置參數(shù)。私有和共享排序所使用內(nèi)存的來(lái)源不一樣。共享排序內(nèi)存區(qū)的大小是在第一次連接到數(shù)據(jù)庫(kù)時(shí)根據(jù) SHEAPTHRES 值以靜態(tài)方式預(yù)先確定的。私有排序內(nèi)存區(qū)的大小是不受限制的。對(duì)于私有排序和共享排序，應(yīng)用 SHEAPTHRES 參數(shù)的方式不同:
對(duì)于私有排序，SHEAPTHRES 是對(duì)私有排序在任何給定的時(shí)間可以消耗的全部?jī)?nèi)存的實(shí)例級(jí)"軟"限制。當(dāng)實(shí)例的總私有排序內(nèi)存消耗量達(dá)到這一限制時(shí)，為其它進(jìn)入的私有排序請(qǐng)求而分配的內(nèi)存會(huì)大大減少。
對(duì)于共享排序，SHEAPTHRES 是對(duì)共享排序在任何給定的時(shí)間可以消耗的全部?jī)?nèi)存的數(shù)據(jù)庫(kù)級(jí)"硬"限制。當(dāng)達(dá)到這一限制時(shí)，不允許有其它共享排序內(nèi)存請(qǐng)求，直到總的共享內(nèi)存消耗量回落到 SHEAPTHRES 所指定的限制以下。
使用排序堆的操作示例包括內(nèi)存中表的散列連接和操作。閾值的顯式定義防止數(shù)據(jù)庫(kù)管理器將過(guò)多數(shù)量的內(nèi)存用于大量排序。
建議
使用數(shù)據(jù)庫(kù)系統(tǒng)監(jiān)視器來(lái)跟蹤排序活動(dòng)。
使用合適的索引使排序堆的使用降到最低。
當(dāng)需要頻繁進(jìn)行大型排序時(shí)，增加 SORTHEAP 的值。
如果增加 SORTHEAP，請(qǐng)確定是否還需要調(diào)整數(shù)據(jù)庫(kù)管理器配置文件中的 SHEAPTHRES 參數(shù)。
優(yōu)化器用排序堆大小來(lái)確定存取路徑。在更改該參數(shù)后請(qǐng)考慮重新綁定應(yīng)用程序(使用 REBIND PACKAGE 命令)。
理想情況下，應(yīng)當(dāng)將排序堆閾值(SHEAPTHRES)參數(shù)合理地設(shè)置為在數(shù)據(jù)庫(kù)管理器實(shí)例中設(shè)置的 SORTHEAP 參數(shù)最大值的倍數(shù)。該參數(shù)至少應(yīng)當(dāng)是實(shí)例中任何數(shù)據(jù)庫(kù)所定義的最大 SORTHEAP 的兩倍。
如何更改這些參數(shù)
要更改 SORTHEAP 和 SHEAPTHRES 的值，請(qǐng)運(yùn)行以下命令:
-- SORTHEAP should be changed for individual database --
db2 "update db cfg for DB_NAME using SORTHEAP a_value"
-- SHEAPTHRES is a database manager parameter --
db2 "update dbm cfg using SHEAPTHRES b_value"
研究步驟
OLTP 應(yīng)用程序不應(yīng)該執(zhí)行大型排序。大型排序在 CPU 和 I/O 資源方面的成本太高了。通常，SORTHEAP 大小的缺省值(256 個(gè) 4KB 頁(yè))就足夠了。事實(shí)上，對(duì)于高并發(fā)性 OLTP，可能希望降低這個(gè)缺省值。當(dāng)需要進(jìn)一步研究時(shí)，可以發(fā)出下面這條命令:
db2 "update monitor switches using sort on"
然后，讓應(yīng)用程序運(yùn)行一會(huì)，然后輸入:
db2 "get snapshot for database on DBNAME"
根據(jù)該輸出，可以計(jì)算每個(gè)事務(wù)的排序數(shù)目，并可以計(jì)算溢出了可用于排序的內(nèi)存的那部分排序的百分比。
SortsPerTransaction
= (Total Sorts) / (Commit statements attempted + Rollback statements attempted)
PercentSortOverflow
= (Sort overflows * 100 ) / (Total sorts)
經(jīng)驗(yàn):如果 SortsPerTransaction 大于 5，它可能表明每個(gè)事務(wù)的排序太多。如果 PercentSortOverflow 大于 3%，那么可能發(fā)生了嚴(yán)重的、未曾預(yù)料到的大型排序。發(fā)生這種情況時(shí)，增加 SORTHEAP 只會(huì)隱藏性能問(wèn)題 - 卻無(wú)法修正它。這個(gè)問(wèn)題的正確解決方案是通過(guò)添加正確的索引改進(jìn)有問(wèn)題的 SQL 語(yǔ)句的存取方案。
DB2數(shù)據(jù)庫(kù)性能優(yōu)化的這幾個(gè)小技巧都是很實(shí)用的，不相信的話，不妨對(duì)照著實(shí)際操作試試。
【編輯推薦】
