Informix 內(nèi)存討論之一: SQL語句高速緩存

大夫

環(huán)境:informix 9.3/9.4
需求(通過討論,期望可得到如statspack一樣的一個性能測試報告工具),本部分僅先討論由內(nèi)存觸發(fā)的優(yōu)化操作:
1,從整體優(yōu)化Informix數(shù)據(jù)庫內(nèi)存設(shè)置.
2,內(nèi)存優(yōu)化測試方法,算法及優(yōu)化說明。

SQL語句高速緩存
需求:
1,監(jiān)控SQL語句執(zhí)行,減少SQL解析及內(nèi)存管理時間.
2,收集SQL語句執(zhí)行的物理讀寫,邏輯讀寫,執(zhí)行時間,執(zhí)行次數(shù)等性能數(shù)據(jù)。。



SQL語句高速緩存存儲分析和優(yōu)化過的SQL語句，使執(zhí)行相同的SQL語句用戶得到如下性能改進：
1，縮短響應(yīng)時間。因繞過分析和優(yōu)化步驟。
2，減少占用的內(nèi)存量。因用戶間共享了查詢結(jié)構(gòu)。

下圖顯示SQL語句高速緩存如何優(yōu)化SQL執(zhí)行及其控制參數(shù)在執(zhí)行過程中的作用：


啟用SQL語句高速緩存：
SET STATEMENT CACHE的優(yōu)先級高于STMT_CACHE設(shè)置，但前提是必須先用STMT_CACHE參數(shù)或onmode命令啟用了SQL語句高速緩存。
        ONCONFIG配置參數(shù)啟用
1）如果STMT_CACHE=0（缺省值）時，數(shù)據(jù)庫不緩存。
2）如果STMT_CACHE=1，則數(shù)據(jù)庫會在單個用戶設(shè)置STMT_CACHE環(huán)境變量為1時或應(yīng)用程序中執(zhí)行SET STATEMENT CACHE ON時為該用戶使用的SQL語句提供高速緩存。
3）如果STMT_CACHE=2，則數(shù)據(jù)庫將所有用戶的SQL語句緩存，除非單個用戶使用STMT_CACHE環(huán)境變量或SET STATEMENT CACHE OFF關(guān)閉該功能。
        onmode -e命令動態(tài)覆蓋STMT_CACHE配置參數(shù)啟用
1）如果執(zhí)行onmode -e enable，則數(shù)據(jù)庫會在單個用戶設(shè)置STMT_CACHE環(huán)境變量為1時或應(yīng)用程序中執(zhí)行SET STATEMENT CACHE ON時為該用戶使用的SQL語句提供高速緩存。
2）如果執(zhí)行onmode -e on,則數(shù)據(jù)庫將所有用戶的SQL語句緩存，除非單個用戶使用STMT_CACHE環(huán)境變量或SET STATEMENT CACHE OFF關(guān)閉該功能。

SQL語句符合高速緩存匹配條件：
符合以下內(nèi)容的SQL語句，認為是相等的：
字母大小寫，注釋，空格，優(yōu)化設(shè)置，并行性設(shè)置，查詢文本字符串，文字。

語句具備緩存的條件：
比如符合一些規(guī)則，如滿足以下條件才可以：
1）必須是SELECT，INSERT，UPDATE或DELETE語句。
2）必須僅包含內(nèi)置的數(shù)據(jù)類型，且不包括如BLOB，BYTE等一些大類型。
3）不能包含用戶定義的SPL。
4）不能包含臨時表或遠程表。
等等條件。

以下參數(shù)影響數(shù)據(jù)庫是否將SQL語句放入高速緩存：
1.STMT_CACHE_HITS
指定使用了高速緩存的內(nèi)容來執(zhí)行該SQL語句的次數(shù)。即命中數(shù)。
1）如果STMT_CACHE_HITS=0，則SQL語句直接插入高速緩存，其中包含SQL語句的文本結(jié)構(gòu)加內(nèi)存結(jié)構(gòu)。
2）如果STMT_CACHE_HITS !=0，且該SQL語句不存在于高速緩存中，則插入一個包含SQL語句文本的唯一關(guān)鍵條目到高速緩存中，該SQL語句的后續(xù)執(zhí)行，會增加這個關(guān)鍵條目的命中數(shù)。
3）如果STMT_CACHE_HITS=唯一關(guān)鍵字條目的命中數(shù)，則把該SQL語句的內(nèi)存結(jié)構(gòu)加入到高速緩存中，變?yōu)橥耆玈QL語句高速緩存。
2.STMT_CACHE_SIZE，STMT_CACHE_NOLIMIT
STMT_CACHE_SIZ指定SQL語句高速緩存的大小。
STMT_CACHE_NOLIMIT指定是否將“QL語句高速緩存的大小”限制為STMT_CACHE_SIZE值。
缺省STMT_CACHE_SIZE=512*1024個字節(jié)。
1）如果STMT_CACHE_NOLIMIT=1（缺�。�，表示不限制“SQL語句高速緩存的大小”，即使將超過STMT_CACHE_SIZE指定的大小；
2）如果STMT_CACHE_NOLIMIT=0，表示受到限制，不會超過STMT_CACHE_SIZE指定的大小。

優(yōu)化說明：
onstat -g ssc 顯示完全共享了條目的SQL語句信息
onstat -g ssc all 顯示完全共享了條目的SQL語句信息和沒完全共享但已包含唯一關(guān)鍵字條目的信息，顯示的是整個“SQL語句高速緩存內(nèi)存里的信息”

如果他們之間的輸出差別很大，應(yīng)考慮放寬STMT_CACHE_HITS的條件，讓更多的條目得到完全緩存，減少管理開銷。STMT_CACHE_HITS除了可以修改ONCONFIG參數(shù)文件外，可以使用onmode -W 命令在線動態(tài)修改，能改大不能改小。



更新STMT_CACHE_HITS參數(shù)方法：
1，更新ONCONFIG文件。
2，onmode -W命令動態(tài)修改，不保存到ONCONFIG文件。

如果SQL語句高速緩存太小，則可能發(fā)生：
1，頻繁執(zhí)行的SQL語句不在高速緩存中。
2，數(shù)據(jù)庫花費大量時間清除SQL語句高速緩存。數(shù)據(jù)庫使用閥值(STMT_CACHE_SIZE的70%)來確定何時從高速緩存中清除條目，防止分配大量內(nèi)存。

更改SQL語句高速緩存大�。�
onstat -g ssc all

Statement Cache Summary的#hits，為STMT_CACHE_HITS的配置值。
Statement Cache Summary的Maxsize，為STMT_CACHE_SIZE的配置值。
Statement Cache Summary的nolimit，為STMT_CACHE_NOLIMIT的配置值。
Statement Cache Summary的currsize，為SQL語句高速緩存當(dāng)前使用的值。
Statement Cache Summary的#lrus   LRU隊列數(shù)目。
Statement Cache Summary的poolsize  所有POOL的累積大小。
Statement Cache Entries的lur   LRU標(biāo)識符。
Statement Cache Entries的hash        散列存儲區(qū)標(biāo)識符。
Statement Cache Entries的ref_cnt         當(dāng)前使用此語句的會話數(shù)。
Statement Cache Entries的hits        執(zhí)行次數(shù)（不包括第一次）
Statement Cache Entries的flag列，第一個位置為D表示該語句已被刪除，“-”表示繼續(xù)有效；第二個位置的值為F，表示已完全高速緩存（即在onstat –g ssc中會出現(xiàn)），值為“-”，表示僅緩存關(guān)鍵字和文本條目。
Statement Cache Entries的hits列，顯示SQL語句執(zhí)行次數(shù)（不包括第一次執(zhí)行）。
Statement Cache Entries的heap_ptr         該語句的堆指針。
Statement Cache Entries的database        執(zhí)行SQL語句的數(shù)據(jù)庫
Statement Cache Entries的user        執(zhí)行SQL語句的用戶
Statement Cache Entries的statement         測試是否匹配時使用的語句文本



STMT_CACHE_NOLIMIT
指定當(dāng)插入SQL語句到高速緩存時，由于當(dāng)前正在使用而不能清除SQL語句，無法存儲更多的語句，此時要插入更多的SQL語句，是否可擴展的比STMT_CACHE_SIZE大。
1，STMT_CACHE_NOLIMIT=1，缺省值。表示即使高速緩存可能大雨STMT_CACHE_SIZE大小，也插入新語句。
2，STMT_CACHE_NOLIMIT=0，表示不會擴展插入。

多個SQL語句高速緩存池
啟用SQL語句高速緩存后，數(shù)據(jù)庫以下情況從一個池中為查詢結(jié)構(gòu)分配內(nèi)存：
1，在高速緩存中沒找到匹配條目時。
2，當(dāng)找到匹配的唯一關(guān)鍵字條目且命中數(shù)達到STMT_CACHE_HITS配置的參數(shù)值時。

STMT_CACHE_NUMPOOL（缺省=1）可配置多個sscpools，應(yīng)確定：
sscpools數(shù)的工作負載。
SQL語句高速緩存的大小沒有引起過多的內(nèi)存管理。

onstat -g spi輸出sscpool上的Num Waits和Num Loops，輸出至少等待一次的資源。如果Num Loops太大，增加STMT_CACHE_NUMPOOL可以提高性能。

onstat -g ssc pool可以監(jiān)視每個pool的使用情況。所有STMT的內(nèi)存都從POOL里分配。通常pool的大小不會超過STMT_CACHE_SIZE的值。



name sscpool名稱
class，始終是V，表示虛擬部分
addr   SSC池共享內(nèi)存地址
totalsize   此SSC池的總大小
freesize    此SSC池的可用字節(jié)數(shù)
#allocfrag  在此SSC池中已分配的連續(xù)內(nèi)存區(qū)域數(shù)
#freefrag   在此SSC池中未使用的連續(xù)內(nèi)存區(qū)域數(shù)

大夫

現(xiàn)在的問題:
1.配置過后,可通過onstat -g ssc的方法找到緩存的SQL語句,但找不到相應(yīng)的SMI接口表.
2.找不到記錄如每個SQL語句的執(zhí)行時間,發(fā)生的物理讀寫,邏輯讀寫,執(zhí)行的用戶,數(shù)據(jù)庫,執(zhí)行的時長等等信息。
3.這部分的性能測試標(biāo)準(zhǔn),比如ORACLE的User calls,Recursive Call,Parse CPU to Parse Elapsd,Hard parses, Execute to Parse等等測試方法。

請知道的達官貴人,或有興趣的好人,補充,完善一下,大家萬歲


xib_jk:
這樣的問題我們要頂！
我也正在困惑于這樣的問題，難點在于找出sql執(zhí)行慢的語句，如果找到可以用set explain 看他的執(zhí)行計劃，還有一個問題很困惑，SQL語句高速緩存，那么我們該從哪里入手呢？比方說7版本的informix該從哪里來統(tǒng)計？

sam_8286

幫忙頂一下

IFMXDBA

nice one, marked.  Thanks !

大夫

有人能補充么?

zzjijun

支持!