Informix 數(shù)據(jù)庫系統(tǒng)字典表簡介

nformix 數(shù)據(jù)庫服務器運行時的狀態(tài)信息是數(shù)據(jù)庫管理員 DBA 進行系統(tǒng)監(jiān)控和優(yōu)化的必需信息來源。Informix 的狀態(tài)信息在內(nèi)部以 2 種方式存在，如圖 1 所示，一部分是存在于 Informix 運行的共享內(nèi)存中，這部分信息在數(shù)據(jù)庫關(guān)閉后，其信息將自動消失，只是一個內(nèi)存信息，我們稱為內(nèi)存表，如：sysbufpool，sysvpprof，sysprofile 等。另外一部分是以 Informix 物理字典表的方式存儲，如：systables，sysindex。Informix 數(shù)據(jù)庫系統(tǒng)字典表是用來訪問這 2 個部分的內(nèi)部信息的一個接口，可以通過 SQL 語句查詢 Informix 系統(tǒng)運行的動態(tài)情況。

圖 1. Informix 系統(tǒng)表接口示意圖

從另外一個視角來理解 Informix 系統(tǒng)表，就是從系統(tǒng)的組成數(shù)據(jù)庫來看。如圖 2 所示，主要包括 3 個數(shù)據(jù)庫：sysmaster，sysadmin 和用戶數(shù)據(jù)庫。其中 sysmaster 是最重要的系統(tǒng)數(shù)據(jù)庫，該數(shù)據(jù)庫保存實例 (Instance) 級別的系統(tǒng)信息，如實例運行的總體信息，所有的表等。sysadmin 是一個管理系統(tǒng)數(shù)據(jù)庫，主要用來管理 Informix 系統(tǒng)管理相關(guān)的信息，如可以通過該數(shù)據(jù)庫可以定義 Informix 的任務調(diào)度器等。用戶數(shù)據(jù)庫，就是用戶定義用來存儲用戶數(shù)據(jù)的數(shù)據(jù)庫，每個用戶數(shù)據(jù)庫都包含有數(shù)據(jù)庫 (Database) 級別的系統(tǒng)表，如 systables 等。

圖 2. Informix 系統(tǒng)表數(shù)據(jù)庫組成示意圖

Informix 系統(tǒng)字典表的結(jié)構(gòu)及含義詳細解釋：也可以直接訪問 IBM Informix 在線文檔，URL 如下：
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=/com.ibm.adref.doc/ids_adr_0210.htm
https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.adref.doc/ids_adr_0210.htm
文檔中對每一個系統(tǒng)表的每一個字段的含義有詳盡的說明。

常用系統(tǒng)表監(jiān)控 SQL 及查詢結(jié)果的診斷與分析
本節(jié)以 Informix 數(shù)據(jù)庫監(jiān)控和優(yōu)化的方法和分析主題為單位，提供具體訪問 Informix 系統(tǒng)表來監(jiān)控數(shù)據(jù)庫運行狀態(tài)的 SQL 語句，對 SQL 返回的結(jié)果進行分析，提出數(shù)據(jù)庫優(yōu)化建議。DBA 可以根據(jù)本節(jié)內(nèi)容就可以掌握如何使用 Informix 系統(tǒng)表進行數(shù)據(jù)庫的監(jiān)控和性能優(yōu)化。

注意：本文中所演示用到的用戶定義數(shù)據(jù)庫名為 demodb，在應用本文提供的 SQL 語句時，需要將數(shù)據(jù)庫名 demodb 修改為實際的數(shù)據(jù)庫名

1. 數(shù)據(jù)庫實例基本運行狀況
了解數(shù)據(jù)庫實例的運行信息，如統(tǒng)計信息的起始時間，數(shù)據(jù)庫出現(xiàn)長事務的次數(shù)。

清單 1. 查詢數(shù)據(jù)庫實例基本運行情況的 SQL

dbaccess sysmasterselect dbinfo('UTC_TO_DATETIME',sh_boottime) start_time, current year to second - dbinfo('UTC_TO_DATETIME',sh_boottime) run_time, sh_maxchunks as maxchunks, sh_maxdbspaces maxdbspaces, sh_maxuserthreads maxuserthreads, sh_maxtrans maxtrans, sh_maxlocks locks, sh_nlrus buff_lrus, sh_longtx longtxs, dbinfo('UTC_TO_DATETIME',sh_pfclrtime) onstat_z_running_time from sysmaster:sysshmvals;

圖 3. 數(shù)據(jù)庫實例基本運行情況查詢結(jié)果

分析： 從如上 SQL 語句返回的結(jié)果可以得到 Informix 實例如下有用的信息：
上一次運行 onstat -z 清除統(tǒng)計信息的時間：onstat_z_running_time，該時間可以幫助 DBA 確認當前統(tǒng)計的信息的時間長度，而不需要重新啟動數(shù)據(jù)庫，可以通過 onstat -z 來清除統(tǒng)計信息從而確認時間間隔內(nèi)的數(shù)據(jù)庫運行情況。
數(shù)據(jù)庫出現(xiàn)長事務的次數(shù)：longtxs。

另外，我們可以得到實例所支持的最大 chunk 和 dbspace 數(shù)量，以及可以運行的線程數(shù)量。還包含有實例的配置參數(shù)值：鎖的個數(shù)，LRU 隊列數(shù)。

2. 數(shù)據(jù)庫實例概要信息
數(shù)據(jù)庫實例的概要信息稱為 Informix 數(shù)據(jù)庫運行的健康檢查的“血常規(guī)表”，可以從整體上掌握數(shù)據(jù)庫運行的狀況，評價數(shù)據(jù)庫是否存在性能問題。

清單 2. 查詢數(shù)據(jù)庫實例概要信息的 SQL
dbaccess sysmaster
select
name, value
from sysmaster:sysprofile;

圖 4. 數(shù)據(jù)庫實例概要信息查詢結(jié)果

分析： 系統(tǒng)表 sysprofile 是保存了 Informix 運行的概要信息，是 onstat -p 命令的基本信息來源，如上查詢結(jié)果可以看出，可以獲取類似的讀 / 寫緩存命中率、鎖溢出、鎖等待、死鎖、順序掃描次數(shù)、事務回滾次數(shù)及比例、磁盤排序、內(nèi)次排序情況、磁盤寫情況（onstat -F）等信息。
數(shù)據(jù)庫運行概要信息是整個實例自開機或者上一次運行 onstat -z 以來的統(tǒng)計信息，反應了數(shù)據(jù)庫實例的總體情況，從各個方面確定數(shù)據(jù)庫實例是否存在性能問題，在 DBA 進行數(shù)據(jù)庫優(yōu)化時，對 Informix 診斷要做的第一件事情就是查看該信息，如發(fā)現(xiàn) seqscans 值偏大，同時 diskread 也較大，則表明系統(tǒng)中有很多 SQL 語句對大表進行順序掃描方式，可以根據(jù)本文后續(xù)內(nèi)容以進一步找到問題原因。簡而言之，該信息是進行數(shù)據(jù)庫優(yōu)化的一個指南針，也是評價一個系統(tǒng)是否健康的一個“血常規(guī)表”。

3. Session 的連接情況

通過 Session 的連接信息，可以分析出數(shù)據(jù)庫系統(tǒng)業(yè)務負載情況，來自哪些客戶端的任務較多，并且根據(jù) Session 的空閑情況，判斷客戶端連接池是否存在過多的連接。

清單 3. 查詢 Session 的連接情況的 SQL

dbaccess sysmasterSELECT s.sid, s.username, s.hostname, q.odb_dbname database, dbinfo('UTC_TO_DATETIME',s.connected) conection_time, dbinfo('UTC_TO_DATETIME',t.last_run_time) last_run_time, current-dbinfo('UTC_TO_DATETIME',t.last_run_time) idle_time FROM syssessions s, systcblst t, sysrstcb r, sysopendb q WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid ORDER BY 7 DESC;

圖 5. 數(shù)據(jù)庫 Session 連接情況查詢結(jié)果

分析：在數(shù)據(jù)庫監(jiān)控過程中，我們經(jīng)常需要監(jiān)控 Session 的連接信息，如 Session 來自哪一個客戶端 ( 客戶端 IP 地址或者名稱 )，在客戶端的進程 ID（-1 標識長連接，一些來自 java 連接池的情況都顯示為 -1），連接到哪一個數(shù)據(jù)庫。連接時間，以及多長時間沒有執(zhí)行任務，通過該信息可以確定連接池開啟的連接個數(shù)是否過多或者過少。

4. Session 等待事件

Session 是監(jiān)控應用程序?qū)?shù)據(jù)庫訪問的窗口，通過分析 Session 的等待事件，可以快速的了解到應用程序客戶端數(shù)據(jù)庫請求是否存在性能問題，通過等待事件，我們可以找到性能慢的應用，并加以優(yōu)化。

清單 4. 查詢 Session 等待事件的 SQL

dbaccess sysmasterselect sid,pid, username, hostname is_wlatch, -- blocked waiting on a latch is_wlock, -- blocked waiting on a locked record or table is_wbuff, -- blocked waiting on a buffer is_wckpt, -- blocked waiting on a checkpoint is_incrit -- session is in a critical section of transaction from syssessions order by username;

圖 6. 數(shù)據(jù)庫 Session 等待事件查詢結(jié)果

分析：可以通過 where 條件過濾滿足特定條件的 session，確定是否有鎖等待、buff 等待的情況。

5. 監(jiān)控正在執(zhí)行的 SQL 語句

數(shù)據(jù)庫此時到底在忙什么，我們可以通過數(shù)據(jù)庫當前正在執(zhí)行的 SQL 語句進行判斷，找到哪些出現(xiàn)頻繁的 SQL 語句，哪些運行慢的 SQL 語句。同時，可以用來監(jiān)控訪問特定表的 SQL。

清單 5. 查詢 Informix 正在執(zhí)行的 SQL 語句的 SQL

dbaccess sysmasterselect username,sqx_sessionid, sqx_sqlstatement from sysmaster:syssqexplain, sysmaster:sysscblst where sqx_sessionid = sid --and sqx_sqlstatement like '%tabname%';

圖 7. 監(jiān)控正在執(zhí)行的 SQL 查詢結(jié)果

分析：當需要監(jiān)控找到符合某一條件的 SQL 語句時，該方法提供了直接的信息，如要找到正在訪問表名為 customer 的 SQL 語句有那些，哪只需要通過條件 and sqx_sqlstatement like ‘%customer%’過濾即可。

6. 找到運行最慢的 SQL 語句

系統(tǒng)中 20% 的 SQL 語句占用了 80% 的系統(tǒng)資源，所以 DBA 在優(yōu)化數(shù)據(jù)庫時，找出和優(yōu)化運行慢的 SQL 語句至關(guān)重要，如何捕獲到系統(tǒng)中運行慢的 SQL 語句對很多 DBA 來說非常困難，這里介紹兩個有效的方法：當前運行慢的 SQL 和一段時間內(nèi)運行慢的 SQL 語句。

清單 6. 查詢數(shù)據(jù)庫當前運行最慢 SQL 語句的 SQL

dbaccess sysmasterselect first 25 sqx_estcost, sqx_estrows, sqx_sqlstatement from sysmaster:syssqexplain where 1=1 order by sqx_estcost desc;

圖 8. 監(jiān)控數(shù)據(jù)庫當前運行最慢 SQL 語句的查詢結(jié)果

分析：通過查詢當前正在執(zhí)行的 SQL 語句的開銷來監(jiān)控運行慢的 SQL 語句。當你的數(shù)據(jù)庫處于非常繁忙的時刻，多次運行該語句，就可以找到那些慢的 SQL 語句。

如果要找到數(shù)據(jù)庫一段時間以內(nèi)（比如早上 8 點到 12 點）運行慢的 SQL 語句，那么我們需要利用到 Informix11 的 SQLTRACE 功能。SQLTRACE 功能的使用如下：

打開 SQLTRACE 跟蹤 SQL:

echo 'execute function task ("set sql tracing on",100000, "1k", "low","demodb");' | dbaccess sysadmin

說明：

• demodb 為跟蹤的數(shù)據(jù)庫名；
• 100000 為最多跟蹤的 SQL 語句個數(shù)，超過這個數(shù)字時，將最早跟蹤的 SQL 刪除
• 1k 為每個SQL 占用的內(nèi)存，對于有特別大的 SQL 語句，需要設(shè)置更大的值，如 2k,4k

關(guān)閉 SQLTRACE 功能 :

echo ' execute function sysadmin:task("SET SQL TRACING OFF"); ' | dbaccess sysadmin

說明：跟蹤分析完成后，一定要關(guān)閉。SQL-Tracing 開啟下將對系統(tǒng)有 2%－5% 的性能消耗。另外，關(guān)閉后，跟蹤的信息（內(nèi)存）將字典釋放，故一定要分析完成后，再關(guān)閉，或者定期把捕獲的信息轉(zhuǎn)存到自定義的表 ( 創(chuàng)建三個和 sql－tracing 字典表一致的表即可 ) 中，供進一步分析使用。

結(jié)果分析 :

我們可以對 SQL-Tracing 捕獲的結(jié)果進行分析

順序掃描的 SQL

select distinct sql_statement from sysmaster:Syssqltrace t inner join sysmaster:syssqltrace_iter i on t.sql_id = i.sql_id where i.sql_itr_info='Seq Scan';

查詢速度慢 SQL

可以通過不同的指標進行排名

echo "select first 20 * from sysmaster:syssqltrace order by sql_totaltime"| dbaccess demodb

7. 哪些表使用了最多的鎖

鎖是數(shù)據(jù)庫中的常見問題，我們通過 2. 節(jié)了解到數(shù)據(jù)庫系統(tǒng)整體上是否存在鎖等待、死鎖的問題。我們可以通過監(jiān)控表的鎖使用情況，以進一步確認出現(xiàn)鎖問題的原因。

清單 7. 監(jiān)控表使用鎖的情況的 SQL

dbaccess sysmasterselect dbsname databanse, tabname, sum(pf_rqlock) as locks,sum(pf_wtlock) as lockwaits, sum(pf_deadlk) as deadlocks from sysactptnhdr,systabnames where systabnames.partnum = sysactptnhdr.partnum --and pf_wtlock >=0 and pf_rqlock >=0 group by dbsname,tabname order by lockwaits desc;

圖 9. 表使用鎖情況的查詢結(jié)果

分析：當數(shù)據(jù)庫出現(xiàn)鎖問題時，首先我們需要找到哪些表消耗了最多的鎖資源，哪些表出現(xiàn)了鎖等待和死鎖情況。從而我們可以進一步確定需要監(jiān)控的對象和有針對性的優(yōu)化，可以分析表的鎖模式：頁級鎖還是行級鎖，還需要監(jiān)控訪問表的 SQL 語句是否發(fā)生了順序掃描和采用的隔離級別。

8. 鎖等待監(jiān)控

當出現(xiàn)鎖沖突時，如何找到鎖的占用者以及導致了哪些 Session 等待，是進行鎖優(yōu)化的關(guān)鍵。

清單 8. 監(jiān)控鎖等待情況的 SQL

dbaccess sysmasterselect dbsname databanse, tabname, sum(pf_rqlock) as locks,sum(pf_wtlock) as lockwaits, sum(pf_deadlk) as deadlocks from sysactptnhdr,systabnames where systabnames.partnum = sysactptnhdr.partnum --and pf_wtlock >=0 and pf_rqlock >=0 group by dbsname,tabname order by lockwaits desc;

圖 10. 數(shù)據(jù)庫鎖等待查詢結(jié)果

分析：當發(fā)現(xiàn)數(shù)據(jù)庫中有鎖等待的情況，即使用本文 2.2 節(jié)查詢的結(jié)果 lockwts 值比較大時，或者通過 2.4 發(fā)現(xiàn) Session 有鎖等待情況，或者我們發(fā)現(xiàn)表被鎖的情況，我們可以通過該 SQL 去找到鎖的使用情況，及該鎖是否造成了其他使用者的等待。

9. DBSpace 監(jiān)控

我們可以通過 onstat -d 了解到 Informix 的 DBSpace 的使用情況，剩余空間情況等。但是輸出格式不是很友好，通過該 SQL 可以得到 dbspace 的全面、友好的信息。

清單 9. 監(jiān)控 DBSpace 空間使用情況的 SQL

dbaccess sysmasterSELECT A.dbsnum as No, trim(B.name) as name, CASE WHEN (bitval(B.flags,'0x10')>0 AND bitval(B.flags,'0x2')>0) THEN 'MirroredBlobspace' WHEN bitval(B.flags,'0x10')>0 THEN 'Blobspace' WHEN bitval(B.flags,'0x2000')>0 AND bitval(B.flags,'0x8000')>0 THEN 'TempSbspace' WHEN bitval(B.flags,'0x2000')>0 THEN 'TempDbspace' WHEN (bitval(B.flags,'0x8000')>0 AND bitval(B.flags,'0x2')>0) THEN 'MirroredSbspace' WHEN bitval(B.flags,'0x8000')>0 THEN 'SmartBlobspace' WHEN bitval(B.flags,'0x2')>0 THEN 'MirroredDbspace' ELSE 'Dbspace' END as dbstype, CASE WHEN bitval(B.flags,'0x4')>0 THEN 'Disabled' WHEN bitand(B.flags,3584)>0 THEN 'Recovering' ELSE 'Operational' END as dbsstatus, format_units(sum(chksize),max(A.pagesize)) as DBS_SIZE , format_units(sum(decode(mdsize,-1,nfree,udfree)),max(A.pagesize)) as free_size, TRUNC(100-sum(decode(mdsize,-1,nfree,udfree))*100/sum(chksize),2)||'%' as used, TRUNC(MAX(A.pagesize/1024)) as pgsize, MAX(B.nchunks) as nchunks FROM syschktab A, sysdbstab B WHERE A.dbsnum = B.dbsnum GROUP BY A.dbsnum,name, 3, 4 ORDER BY A.dbsnum;

圖 11. 數(shù)據(jù)庫 DBspace 空間查詢結(jié)果

分析：Dbspace 的 chunk 數(shù)量、類型、狀態(tài)（Operational 為正常狀態(tài)）, 空間的大小、已用空間及已用空間的百分比。及時發(fā)現(xiàn)空間即將使用完的情況，提前增加空間。

10. Chunks I/O 監(jiān)控

Chunk 的 I/O 是否均衡，是從 Chunk 角度判斷數(shù)據(jù)庫存儲規(guī)劃是否存在問題的出發(fā)點。

清單 10. 監(jiān)控 Chunk I/O 情況的 SQL

dbaccess sysmasterselect d.name dbspace, fname[1,125] chunk_name, reads read_count, writes write_count, reads+writes total_count, pagesread, pageswritten, pagesread+pageswritten total_pg from sysmaster:syschkio c, sysmaster:syschunks k, sysmaster:sysdbspaces d where d.dbsnum = k.dbsnum and k.chknum = c.chunknum --# c.chknum order by 8 desc;

圖 12. Chunks 讀寫情況查詢結(jié)果

分析：通過查看 Chunk 的 I/O 情況，可以判定數(shù)據(jù)庫系統(tǒng)的 I/O 是否均衡，如果出現(xiàn)不均衡的情況容易出現(xiàn) I/O 沖突，性能下降。為了充分利用所有的磁盤設(shè)備，我們需要盡量均衡 I/O 到不同的設(shè)備。對于 I/O 比較集中的 Chunk，需要根據(jù)本文后面的內(nèi)容找到相應的表及索引，通過把表存儲在不同的 DBSpace 上，及分片方式進行均衡 I/O。

11. 臨時表空間監(jiān)控

臨時表是否使用正確，是否存在磁盤排序？可以通過臨時表空間的使用情況得到答案。以及是否存在大量的磁盤排序情況。

清單 11. 監(jiān)控臨時表空間使用情況況的 SQL

dbaccess sysmasterselect trim(n.dbsname) tab_type, trim(n.owner) users,trim(n.tabname) tab_name, dbinfo('UTC_TO_DATETIME',i.ti_created) index_createtime, trim(dbinfo('DBSPACE', i.ti_partnum)) dbspace, format_units(i.ti_nptotal,i.ti_pagesize) total_size,i.ti_nrows FROM sysmaster:systabnames n, sysmaster:systabinfo i WHERE (sysmaster:bitval(i.ti_flags, 32) = 1 OR sysmaster:bitval(i.ti_flags, 64) = 1 OR sysmaster:bitval(i.ti_flags, 128) = 1) AND i.ti_partnum = n.partnum order by 1,3;

圖 13. 臨時表空間使用情況查詢結(jié)果

分析：SortTEMP 是用來排序用的臨時空間，合理調(diào)整參數(shù) : DS_NONPDQ_QUERY_MEM，減少磁盤排序 onmode -wf DS_NONPDQ_QUERY_MEM=2048 。 確定是否有臨時表存儲的 dbspace 不是臨時表空間的情況，那可能由于沒有正確配置好臨時表空間，或者沒有在創(chuàng)建臨時表時使用 with no log 選項。Informix11 及以上版本可以通過該參數(shù) TEMPTAB_NOLOG 讓應用程序中遺忘使用 with no log 的情況正常使用臨時表空間和不記日志方式。可以提高臨時表的性能。修改方式，可以在線修改。onmode -wf TEMPTAB_NOLOG=1

12. Table Space 監(jiān)控

數(shù)據(jù)庫中哪些表占用了 80% 的空間，哪些表的記錄數(shù)最多，哪些表存在過多的 extent ？這些大表往往決定了系統(tǒng)的性能。那么快速得到數(shù)據(jù)庫中大數(shù)據(jù)量表的情況非常重要。

清單 12. 查詢表使用空間情況的 SQL

dbaccess sysmaster--A 含分片select st.dbsname databasename,st.tabname,sd.name dbs_name, ti_nextns extents, sin.ti_nrows,sin.ti_pagesize, sin.ti_rowsize, sin.ti_nptotal nptotal, format_units(sin.ti_nptotal,sd.pagesize) total_size, sin.ti_npused npused, format_units(sin.ti_npused,sd.pagesize) used_size, sin.ti_nextsiz nextsize from sysmaster:systabnames st, sysmaster:sysdbspaces sd, sysmaster:systabinfo sin,demodb:systables dt where sd.dbsnum = trunc(st.partnum/1048576) and dt.tabid>99 and dt.tabname=st.tabname and st.partnum=sin.ti_partnum and st.dbsname='demodb' --and sd.name= ’ demodbs ’order by 10 desc; --B 總和select st.dbsname databasename,st.tabname, sum(ti_nextns) extents, sum(sin.ti_nrows) nrows,max(sin.ti_pagesize) pagesize, sum(sin.ti_nptotal) nptotal, format_units(sum(sin.ti_nptotal),max(sd.pagesize)) total_size, sum(sin.ti_npused) npused, format_units(sum(sin.ti_npused),max(sd.pagesize)) used_size from sysmaster:systabnames st, sysmaster:sysdbspaces sd, sysmaster:systabinfo sin,demodb:systables dt where sd.dbsnum = trunc(st.partnum/1048576) and dt.tabid>99 and dt.tabname=st.tabname and st.partnum=sin.ti_partnum and st.dbsname='demodb' group by 1,2 order by 8 desc;

圖 14. 表使用空間情況查詢結(jié)果—按分片統(tǒng)計

圖 15. 表使用空間情況查詢結(jié)果—按總和統(tǒng)計

分析：通過該查詢可以得到數(shù)據(jù)庫中哪些大表的情況，如最大記錄數(shù)的表，使用空間最大的表，分配空間，使用空間的情況。同時需要關(guān)注 extent 數(shù)量超過 200 的表，需要重建表，對于數(shù)據(jù)量特別大的表需要進行分片等來提高性能。另外，可以通過分析占用空間最多的表的建表語句，是否存在錯誤使用 char(n) 的情況，比如用 char(255)，但是數(shù)據(jù)是變長的，平均長度只有 100，那么建議采用 varchar(255) 替代 char(255)。

13. Table I/O 監(jiān)控

I/O 是系統(tǒng)性能的關(guān)鍵，減少無效的 I/O 是數(shù)據(jù)庫設(shè)計和優(yōu)化的關(guān)鍵，了解 80% 的 I/O 發(fā)生在哪些 20% 的表上成為 DBA 進行 I/O 優(yōu)化的出發(fā)點。

清單 13. 查詢表 I/O 情況的 SQL

dbaccess sysmasterSELECT p.tabname, sum(sin.ti_nrows) nrows, format_units(sum(sin.ti_nptotal),max(sd.pagesize)) total_size, format_units(sum(sin.ti_npused),max(sd.pagesize)) used_size, sum(seqscans) as seqscans , sum( pagreads) diskreads, sum(bufreads) bufreads, sum( bufwrites) bufwrites, sum( pagwrites) diskwrites,sum( pagreads)+ sum( pagwrites) disk_rsws , trunc(decode(sum(bufreads),0,0, (100-((sum(pagreads)*100)/sum(bufreads+pagreads)))),2) rbufhits , trunc(decode(sum(bufwrites),0,0, (100-((sum(pagwrites)*100)/sum(bufwrites+pagwrites)))),2) wbufhits from demodb:systables s , sysmaster:sysptprof p , sysmaster:systabinfo sin, sysmaster:sysdbspaces sd,sysmaster:systabnames st where s.tabid>99 and s.tabname = p.tabname and p.dbsname=st.dbsname and sd.dbsnum = trunc(st.partnum/1048576) and p.partnum=st.partnum and s.tabname=st.tabname and st.partnum=sin.ti_partnum and st.dbsname='demodb' group by 1 order by 10 desc;

圖 16. 表讀寫情況查詢結(jié)果

分析：通過該查詢可以得到數(shù)據(jù)庫中哪些大表的 I/O 情況，通過找到 I/O 量最大的表，查看是否有順序掃描情況，一般情況如果記錄數(shù)較大情況，并且有順序掃描出現(xiàn)，會非常嚴重的影響系統(tǒng)的性能。數(shù)據(jù)庫系統(tǒng)優(yōu)化最難的就是 I/O 部分，往往由于不良設(shè)計和不正確使用索引所導致，對于有大量順序掃描的情況的大表一定要找到相應的 SQL，并創(chuàng)建對于的索引。只有不斷的優(yōu)化，提高有效的 I/O，消除不必要的 I/O 才能提高系統(tǒng)的處理能力。

14. Index 創(chuàng)建時間

找到表的創(chuàng)建時間比較容易，但是索引的創(chuàng)建時間比較復雜。

清單 14. 查詢索引創(chuàng)建時間的 SQL

dbaccess sysmasterselect i.owner,st.dbsname,t.tabname,i.idxname, dbinfo('UTC_TO_DATETIME',ti.ti_created) index_createtime from demodb:systables t, demodb:sysindexes i , sysmaster:systabinfo ti,sysmaster:systabnames st where t.tabid=i.tabid and t.tabid>99 and st.partnum = ti.ti_partnum and i.idxname = st.tabname -- and t.tabid=102 -- and t.tabname='tabname'--and dbinfo('UTC_TO_DATETIME',ti.ti_created)>='2010-11-03 08:00:00'and st.dbsname='demodb'order by t.tabname;

圖 17. 查詢索引創(chuàng)建時間查詢結(jié)果

分析：通過查詢索引的創(chuàng)建時間，可以監(jiān)控到系統(tǒng)中某段時間內(nèi)創(chuàng)建的新索引。在很多實際生成系統(tǒng)中，由于管理的混亂，人人都可以創(chuàng)建索引，通過查詢索引的創(chuàng)建時間，可以找到數(shù)據(jù)庫創(chuàng)建以來新增的索引。
注意，這里查詢結(jié)果對于分片索引會有多個結(jié)果。

15. Index Space

索引采用 B+ 樹結(jié)構(gòu)存儲表的部分字段，索引需要占用空間，不合理的索引會占用非常大的空間，或者大表需要占用大的索引空間。找到大的索引，進行優(yōu)化一般就能解決很多性能問題。

清單 15. 查詢索引空間使用情況的 SQL

dbaccess sysmaster--A 含分片select st.dbsname databasename,dt.tabname,di.idxname,sd.name dbs_name, di.levels,sin.ti_nextns extents, sin.ti_nptotal nptotal, format_units(sin.ti_nptotal,sd.pagesize) total_size, sin.ti_npused npused, format_units(sin.ti_npused,sd.pagesize) used_size from sysmaster:systabnames st, sysmaster:sysdbspaces sd,sysmaster:systabinfo sin, demodb:sysindexes di,demodb:systables dt where sd.dbsnum = trunc(st.partnum/1048576) and dt.tabid>99 and di.idxname = st.tabname and dt.tabid=di.tabid and st.partnum=sin.ti_partnum and st.dbsname='demodb' order by 2,1,3; --B 總和select st.dbsname databasename,dt.tabname,di.idxname , max(di.levels) levels,max(sin.ti_nextns) extents, sum(sin.ti_nptotal) nptotal, format_units(sum(sin.ti_nptotal), max(sd.pagesize)) total_size, sum(sin.ti_npused) npused, format_units(sum(sin.ti_npused), max(sd.pagesize)) used_size from sysmaster:systabnames st, sysmaster:sysdbspaces sd,sysmaster:systabinfo sin, demodb:sysindexes di,demodb:systables dt where sd.dbsnum = trunc(st.partnum/1048576) and dt.tabid>99 and di.idxname = st.tabname and dt.tabid=di.tabid and st.partnum=sin.ti_partnum and st.dbsname='demodb'group by 1,2,3 order by 8 desc;

圖 18. 索引空間使用情況查詢結(jié)果—按分片統(tǒng)計

圖 19. 索引空間使用情況查詢結(jié)果—按總和統(tǒng)計

分析：通過分析索引所占用的空間情況，找大空間索引，以確定索引的合理性，有些情況由于在一個大字段（如 char(30)）上創(chuàng)建了一個索引，還有一些情況由于創(chuàng)建了一個包含過多字段的復合索引，導致索引非常大，其效率就較低。還有找到層次超過 5 層的索引，對于大索引，我們需要去再一次分析其合理性，另外可以采用分片的方式來降低索引的層次。

16. Index I/O 監(jiān)控

某個索引是否被經(jīng)常使用？某個索引從來沒有被使用過？如下 SQL 語句回答了該問題。

清單 16. 查詢索引 I/O 情況的 SQL

dbaccess sysmasterselect st.dbsname databasename,dt.tabname,di.idxname,sd.name dbs_name, di.levels,sin.ti_nextns extents, sin.ti_nptotal nptotal, format_units(sin.ti_nptotal,sd.pagesize) total_size, sin.ti_npused npused, format_units(sin.ti_npused,sd.pagesize) used_size, pagreads diskreads, bufreads bufreads, bufwrites bufwrites, pagwrites diskwrites,pagreads + pagwrites disk_rsws from sysmaster:systabnames st, sysmaster:sysdbspaces sd,sysmaster:systabinfo sin, demodb:sysindexes di,demodb:systables dt,sysmaster:sysptprof p where sd.dbsnum = trunc(st.partnum/1048576) and dt.tabid>99 and di.idxname = st.tabname and dt.tabid=di.tabid and st.partnum=sin.ti_partnum and st.dbsname='demodb' and p.partnum=st.partnum order by 2,1,3;

圖 20. 索引 I/O 情況的查詢結(jié)果

分析：我們不但可以通過該方法找到 I/O 較大的索引，還可以找到 I/O 小或者甚至無 I/O 的索引。如果一個索引沒有被使用到，則沒有 I/O，那么這個索引是一個沒有用的索引，可以進一步確認是否屬于垃圾索引。如果 dirk read 和 disk write 差不多，那邊表明對 Index 的讀都是由于需要對 Index 寫產(chǎn)生的，這種情況，可以判讀為該 INDEX 沒有被查詢 SQL 使用到。如果一個索引確實沒有使用到，從而可以確定地將該索引 drop 掉。可以通過增大 Buffer Pool，避免由于內(nèi)存不足把索引交換出內(nèi)存，可以減少不必要的索引 I/O。對應 I/O 大的索引，可以根據(jù)索引的空間使用情況，確定索引是否合理。 注意：索引的 I/O 讀寫數(shù)據(jù)在數(shù)據(jù)重啟后重新計數(shù)，或者通過 onstat -z 重新計數(shù)磁盤 I/O 部分的信息。

我們不僅可以通過 onstat 命令監(jiān)控 Informix 數(shù)據(jù)庫運行情況，也可以通過訪問 system tables 的方式監(jiān)控 Informix 運行情況，這種方式更易于 DBA 進行數(shù)據(jù)庫管理工作，可以將本文所提供的 SQL 語句集成到管理工具中，可以快速對數(shù)據(jù)庫進行周期性的監(jiān)控和分析。可以大大簡化數(shù)據(jù)庫監(jiān)控工作，提高 DBA 的工作效率。

Informix 系統(tǒng)表提供了非常多的信息，本文只是通過 16 個常用的數(shù)據(jù)庫監(jiān)控場景下如何使用 Informix 系統(tǒng)表來進行數(shù)據(jù)庫運行狀況的監(jiān)控和優(yōu)化。

總結(jié)

以上是生活随笔為你收集整理的Informix数据库优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。