http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1203sungr/index.html
最近公司数据库常出问题，发现IBM官网的informix
通过 Informix 系统表监控和优化数据库
简介： 大多数 Informix 使用者都会使用 onstat,oncheck 等命令的方式来监控 Informix 数据库的运行情况，从而确定数据库优化方案。但这种方法存在一些难度，需要多个命令输出综合考虑，才能得出结论。 本文从另外一个角度－－SQL 语句，通过 SQL 语句查询系统表的方式，返回综合的、有组织性的、有实际数据根据的信息。DBA 根据本文可以直接找到系统性能问题，从而进行快速有效的优化，同时可以根据这些有效的数据完成数据库优化过程的报告文档。
Informix 数据库服务器运行时的状态信息是数据库管理员 DBA 进行系统监控和优化的必需信息来源。Informix 的状态信息在内部以 2 种方式存在，如图 1 所示，一部分是存在于 Informix 运行的共享内存中，这部分信息在数据库关闭后，其信息将自动消失，只是一个内存信息，我们称为内存表，如：sysbufpool，sysvpprof，sysprofile 等。另外一部分是以 Informix 物理字典表的方式存储，如：systables，sysindex。Informix 数据库系统字典表是用来访问这 2 个部分的内部信息的一个接口，可以通过 SQL 语句查询 Informix 系统运行的动态情况。
图 1. Informix 系统表接口示意图
从另外一个视角来理解 Informix 系统表，就是从系统的组成数据库来看。如图 2 所示，主要包括 3 个数据库：sysmaster，sysadmin 和用户数据库。其中 sysmaster 是最重要的系统数据库，该数据库保存实例 (Instance) 级别的系统信息，如实例运行的总体信息，所有的表等。sysadmin 是一个管理系统数据库，主要用来管理 Informix 系统管理相关的信息，如可以通过该数据库可以定义 Informix 的任务调度器等。用户数据库，就是用户定义用来存储用户数据的数据库，每个用户数据库都包含有数据库 (Database) 级别的系统表，如 systables 等。
图 2. Informix 系统表数据库组成示意图
Informix 系统字典表的结构及含义详细解释：也可以直接访问 IBM Informix 在线文档，URL 如下：
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=/com.ibm.adref.doc/ids_adr_0210.htm
文档中对每一个系统表的每一个字段的含义有详尽的说明。
本节以 Informix 数据库监控和优化的方法和分析主题为单位，提供具体访问 Informix 系统表来监控数据库运行状态的 SQL 语句，对 SQL 返回的结果进行分析，提出数据库优化建议。DBA 可以根据本节内容就可以掌握如何使用 Informix 系统表进行数据库的监控和性能优化。
注意：本文中所演示用到的用户定义数据库名为 demodb，在应用本文提供的 SQL 语句时，需要将数据库名 demodb 修改为实际的数据库名。
了解数据库实例的运行信息，如统计信息的起始时间，数据库出现长事务的次数。
清单 1. 查询数据库实例基本运行情况的 SQL
dbaccess sysmaster
select
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. 数据库实例基本运行情况查询结果
分析： 从如上 SQL 语句返回的结果可以得到 Informix 实例如下有用的信息：
上一次运行 onstat -z 清除统计信息的时间：onstat_z_running_time，该时间可以帮助 DBA 确认当前统计的信息的时间长度，而不需要重新启动数据库，可以通过 onstat -z 来清除统计信息从而确认时间间隔内的数据库运行情况。
数据库出现长事务的次数：longtxs。
另外，我们可以得到实例所支持的最大 chunk 和 dbspace 数量，以及可以运行的线程数量。还包含有实例的配置参数值：锁的个数，LRU 队列数。
数据库实例的概要信息称为 Informix 数据库运行的健康检查的“血常规表”，可以从整体上掌握数据库运行的状况，评价数据库是否存在性能问题。
清单 2. 查询数据库实例概要信息的 SQL
dbaccess sysmaster select name, value from sysmaster:sysprofile; |
图 4. 数据库实例概要信息查询结果
分析： 系统表 sysprofile 是保存了 Informix 运行的概要信息，是 onstat -p 命令的基本信息来源，如上查询结果可以看出，可以获取类似的读 / 写缓存命中率、锁溢出、锁等待、死锁、顺序扫描次数、事务回滚次数及比例、磁盘排序、内次排序情况、磁盘写情况（onstat -F）等信息。
数据库运行概要信息是整个实例自开机或者上一次运行 onstat -z 以来的统计信息，反应了数据库实例的总体情况，从各个方面确定数据库实例是否存在性能问题，在 DBA 进行数据库优化时，对 Informix 诊断要做的第一件事情就是查看该信息，如发现 seqscans 值偏大，同时 diskread 也较大，则表明系统中有很多 SQL 语句对大表进行顺序扫描方式，可以根据本文后续内容以进一步找到问题原因。简而言之，该信息是进行数据库优化的一个指南针，也是评价一个系统是否健康的一个“血常规表”。
通过 Session 的连接信息，可以分析出数据库系统业务负载情况，来自哪些客户端的任务较多，并且根据 Session 的空闲情况，判断客户端连接池是否存在过多的连接。
清单 3. 查询 Session 的连接情况的 SQL
dbaccess sysmaster
SELECT 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. 数据库 Session 连接情况查询结果
分析：在数据库监控过程中，我们经常需要监控 Session 的连接信息，如 Session 来自哪一个客户端 ( 客户端 IP 地址或者名称 )，在客户端的进程 ID（-1 标识长连接，一些来自 java 连接池的情况都显示为 -1），连接到哪一个数据库。连接时间，以及多长时间没有执行任务，通过该信息可以确定连接池开启的连接个数是否过多或者过少。
Session 是监控应用程序对数据库访问的窗口，通过分析 Session 的等待事件，可以快速的了解到应用程序客户端数据库请求是否存在性能问题，通过等待事件，我们可以找到性能慢的应用，并加以优化。
清单 4. 查询 Session 等待事件的 SQL
dbaccess sysmaster select 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. 数据库 Session 等待事件查询结果
分析：可以通过 where 条件过滤满足特定条件的 session，确定是否有锁等待、buff 等待的情况。
数据库此时到底在忙什么，我们可以通过数据库当前正在执行的 SQL 语句进行判断，找到哪些出现频繁的 SQL 语句，哪些运行慢的 SQL 语句。同时，可以用来监控访问特定表的 SQL。
清单 5. 查询 Informix 正在执行的 SQL 语句的 SQL
dbaccess sysmaster select username,sqx_sessionid, sqx_sqlstatement from sysmaster:syssqexplain, sysmaster:sysscblst where sqx_sessionid = sid --and sqx_sqlstatement like '%tabname%'; |
图 7. 监控正在执行的 SQL 查询结果
分析：当需要监控找到符合某一条件的 SQL 语句时，该方法提供了直接的信息，如要找到正在访问表名为 customer 的 SQL 语句有那些，哪只需要通过条件 and sqx_sqlstatement like '%customer%'过滤即可。
系统中 20% 的 SQL 语句占用了 80% 的系统资源，所以 DBA 在优化数据库时，找出和优化运行慢的 SQL 语句至关重要，如何捕获到系统中运行慢的 SQL 语句对很多 DBA 来说非常困难，这里介绍两个有效的方法：当前运行慢的 SQL 和一段时间内运行慢的 SQL 语句。
清单 6. 查询数据库当前运行最慢 SQL 语句的 SQL
dbaccess sysmaster select first 25 sqx_estcost, sqx_estrows, sqx_sqlstatement from sysmaster:syssqexplain where 1=1 order by sqx_estcost desc; |
图 8. 监控数据库当前运行最慢 SQL 语句的查询结果
分析：通过查询当前正在执行的 SQL 语句的开销来监控运行慢的 SQL 语句。当你的数据库处于非常繁忙的时刻，多次运行该语句，就可以找到那些慢的 SQL 语句。
如果要找到数据库一段时间以内（比如早上 8 点到 12 点）运行慢的 SQL 语句，那么我们需要利用到 Informix11 的 SQLTRACE 功能。SQLTRACE 功能的使用如下：
打开 SQLTRACE 跟踪 SQL:
echo 'execute function
task ("set sql tracing on",100000, "1k", "low","demodb");' | dbaccess sysadmin |
说明：
- demodb 为跟踪的数据库名；
- 100000 为最多跟踪的 SQL 语句个数，超过这个数字时，将最早跟踪的 SQL 删除
- 1k 为每个 SQL 占用的内存，对于有特别大的 SQL 语句，需要设置更大的值，如 2k,4k
关闭 SQLTRACE 功能 :
echo ' execute function sysadmin:task("SET SQL TRACING OFF"); ' | dbaccess sysadmin |
说明：跟踪分析完成后，一定要关闭。SQL-Tracing 开启下将对系统有 2%－5% 的性能消耗。另外，关闭后，跟踪的信息（内存）将字典释放，故一定要分析完成后，再关闭，或者定期把捕获的信息转存到自定义的表 ( 创建三个和 sql－tracing 字典表一致的表即可 ) 中，供进一步分析使用。
结果分析 :
我们可以对 SQL-Tracing 捕获的结果进行分析，
顺序扫描的 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
锁是数据库中的常见问题，我们通过 2. 节了解到数据库系统整体上是否存在锁等待、死锁的问题。我们可以通过监控表的锁使用情况，以进一步确认出现锁问题的原因。
清单 7. 监控表使用锁的情况的 SQL
dbaccess sysmaster select 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. 表使用锁情况的查询结果
分析：当数据库出现锁问题时，首先我们需要找到哪些表消耗了最多的锁资源，哪些表出现了锁等待和死锁情况。从而我们可以进一步确定需要监控的对象和有针对性的优化，可以分析表的锁模式：页级锁还是行级锁，还需要监控访问表的 SQL 语句是否发生了顺序扫描和采用的隔离级别。
当出现锁冲突时，如何找到锁的占用者以及导致了哪些 Session 等待，是进行锁优化的关键。
清单 8. 监控锁等待情况的 SQL
dbaccess sysmaster select 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. 数据库锁等待查询结果
分析：当发现数据库中有锁等待的情况，即使用本文 2.2 节查询的结果 lockwts 值比较大时，或者通过 2.4 发现 Session 有锁等待情况，或者我们发现表被锁的情况，我们可以通过该 SQL 去找到锁的使用情况，及该锁是否造成了其他使用者的等待。
我们可以通过 onstat -d 了解到 Informix 的 DBSpace 的使用情况，剩余空间情况等。但是输出格式不是很友好，通过该 SQL 可以得到 dbspace 的全面、友好的信息。
清单 9. 监控 DBSpace 空间使用情况的 SQL
dbaccess sysmaster SELECT 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. 数据库 DBspace 空间查询结果
分析：Dbspace 的 chunk 数量、类型、状态（Operational 为正常状态）, 空间的大小、已用空间及已用空间的百分比。及时发现空间即将使用完的情况，提前增加空间。
Chunk 的 I/O 是否均衡，是从 Chunk 角度判断数据库存储规划是否存在问题的出发点。
清单 10. 监控 Chunk I/O 情况的 SQL
dbaccess sysmaster select 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 读写情况查询结果
分析：通过查看 Chunk 的 I/O 情况，可以判定数据库系统的 I/O 是否均衡，如果出现不均衡的情况容易出现 I/O 冲突，性能下降。为了充分利用所有的磁盘设备，我们需要尽量均衡 I/O 到不同的设备。对于 I/O 比较集中的 Chunk，需要根据本文后面的内容找到相应的表及索引，通过把表存储在不同的 DBSpace 上，及分片方式进行均衡 I/O。
临时表是否使用正确，是否存在磁盘排序？可以通过临时表空间的使用情况得到答案。以及是否存在大量的磁盘排序情况。
清单 11. 监控临时表空间使用情况况的 SQL
dbaccess sysmaster
select 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. 临时表空间使用情况查询结果
分析：SortTEMP 是用来排序用的临时空间，合理调整参数 : DS_NONPDQ_QUERY_MEM，减少磁盘排序 onmode -wf DS_NONPDQ_QUERY_MEM=2048 。 确定是否有临时表存储的 dbspace 不是临时表空间的情况，那可能由于没有正确配置好临时表空间，或者没有在创建临时表时使用 with no log 选项。Informix11 及以上版本可以通过该参数 TEMPTAB_NOLOG 让应用程序中遗忘使用 with no log 的情况正常使用临时表空间和不记日志方式。可以提高临时表的性能。修改方式，可以在线修改。onmode -wf TEMPTAB_NOLOG=1
数据库中哪些表占用了 80% 的空间，哪些表的记录数最多，哪些表存在过多的 extent ？这些大表往往决定了系统的性能。那么快速得到数据库中大数据量表的情况非常重要。
清单 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. 表使用空间情况查询结果—按分片统计
图 15. 表使用空间情况查询结果—按总和统计
分析：通过该查询可以得到数据库中哪些大表的情况，如最大记录数的表，使用空间最大的表，分配空间，使用空间的情况。同时需要关注 extent 数量超过 200 的表，需要重建表，对于数据量特别大的表需要进行分片等来提高性能。另外，可以通过分析占用空间最多的表的建表语句，是否存在错误使用 char(n) 的情况，比如用 char(255)，但是数据是变长的，平均长度只有 100，那么建议采用 varchar(255) 替代 char(255)。
I/O 是系统性能的关键，减少无效的 I/O 是数据库设计和优化的关键，了解 80% 的 I/O 发生在哪些 20% 的表上成为 DBA 进行 I/O 优化的出发点。
清单 13. 查询表 I/O 情况的 SQL
dbaccess sysmaster
SELECT 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. 表读写情况查询结果
分析：通过该查询可以得到数据库中哪些大表的 I/O 情况，通过找到 I/O 量最大的表，查看是否有顺序扫描情况，一般情况如果记录数较大情况，并且有顺序扫描出现，会非常严重的影响系统的性能。数据库系统优化最难的就是 I/O 部分，往往由于不良设计和不正确使用索引所导致，对于有大量顺序扫描的情况的大表一定要找到相应的 SQL，并创建对于的索引。只有不断的优化，提高有效的 I/O，消除不必要的 I/O 才能提高系统的处理能力。
找到表的创建时间比较容易，但是索引的创建时间比较复杂。
清单 14. 查询索引创建时间的 SQL
dbaccess sysmaster
select
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. 查询索引创建时间查询结果
分析：通过查询索引的创建时间，可以监控到系统中某段时间内创建的新索引。在很多实际生成系统中，由于管理的混乱，人人都可以创建索引，通过查询索引的创建时间，可以找到数据库创建以来新增的索引。
注意，这里查询结果对于分片索引会有多个结果。
索引采用 B+ 树结构存储表的部分字段，索引需要占用空间，不合理的索引会占用非常大的空间，或者大表需要占用大的索引空间。找到大的索引，进行优化一般就能解决很多性能问题。
清单 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. 索引空间使用情况查询结果—按分片统计
图 19. 索引空间使用情况查询结果—按总和统计
分析：通过分析索引所占用的空间情况，找大空间索引，以确定索引的合理性，有些情况由于在一个大字段（如 char(30)）上创建了一个索引，还有一些情况由于创建了一个包含过多字段的复合索引，导致索引非常大，其效率就较低。还有找到层次超过 5 层的索引，对于大索引，我们需要去再一次分析其合理性，另外可以采用分片的方式来降低索引的层次。
某个索引是否被经常使用？某个索引从来没有被使用过？如下 SQL 语句回答了该问题。
清单 16. 查询索引 I/O 情况的 SQL
dbaccess sysmaster 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, 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 情况的查询结果
分析：我们不但可以通过该方法找到 I/O 较大的索引，还可以找到 I/O 小或者甚至无 I/O 的索引。如果一个索引没有被使用到，则没有 I/O，那么这个索引是一个没有用的索引，可以进一步确认是否属于垃圾索引。如果 dirk read 和 disk write 差不多，那边表明对 Index 的读都是由于需要对 Index 写产生的，这种情况，可以判读为该 INDEX 没有被查询 SQL 使用到。如果一个索引确实没有使用到，从而可以确定地将该索引 drop 掉。可以通过增大 Buffer Pool，避免由于内存不足把索引交换出内存，可以减少不必要的索引 I/O。对应 I/O 大的索引，可以根据索引的空间使用情况，确定索引是否合理。 注意：索引的 I/O 读写数据在数据重启后重新计数，或者通过 onstat -z 重新计数磁盘 I/O 部分的信息。
我们不仅可以通过 onstat 命令监控 Informix 数据库运行情况，也可以通过访问 system tables 的方式监控 Informix 运行情况，这种方式更易于 DBA 进行数据库管理工作，可以将本文所提供的 SQL 语句集成到管理工具中，可以快速对数据库进行周期性的监控和分析。可以大大简化数据库监控工作，提高 DBA 的工作效率。
Informix 系统表提供了非常多的信息，本文只是通过 16 个常用的数据库监控场景下如何使用 Informix 系统表来进行数据库运行状况的监控和优化。
扫一扫
3607
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
