ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过 Informix 系统表监控和优化数据库

通过 Informix 系统表监控和优化数据库

原创 Linux操作系统 作者:lhl1212 时间:2012-05-17 11:05:30 0 删除 编辑

 

简介: 大多数 Informix 使用者都会使用 onstat,oncheck 等命令的方式来监控 Informix 数据库的运行情况,从而确定数据库优化方案。但这种方法存在一些难度,需要多个命令输出综合考虑,才能得出结论。本文从另外一个角度--SQL 语句,通过 SQL 语句查询系统表的方式,返回综合的、有组织性的、有实际数据根据的信息。DBA 根据本文可以直接找到系统性能问题,从而进行快速有效的优化,同时可以根据这些有效的数据完成数据库优化过程的报告文档。

<!--

-->

免费下载:IBM® Informix® 11.7 试用版(包括 Ultimate Edition、Developer Edition 和 Innovator-C Edition)
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

Informix 数据库系统字典表简介

Informix 数据库服务器运行时的状态信息是数据库管理员 DBA 进行系统监控和优化的必需信息来源。Informix 的状态信息在内部以 2 种方式存在,如图 1 所示,一部分是存在于 Informix 运行的共享内存中,这部分信息在数据库关闭后,其信息将自动消失,只是一个内存信息,我们称为内存表,如:sysbufpool,sysvpprof,sysprofile 等。另外一部分是以 Informix 物理字典表的方式存储,如:systables,sysindex。Informix 数据库系统字典表是用来访问这 2 个部分的内部信息的一个接口,可以通过 SQL 语句查询 Informix 系统运行的动态情况。


图 1. Informix 系统表接口示意图
Informix 系统表接口示意图

从另外一个视角来理解 Informix 系统表,就是从系统的组成数据库来看。如图 2 所示,主要包括 3 个数据库:sysmaster,sysadmin 和用户数据库。其中 sysmaster 是最重要的系统数据库,该数据库保存实例 (Instance) 级别的系统信息,如实例运行的总体信息,所有的表等。sysadmin 是一个管理系统数据库,主要用来管理 Informix 系统管理相关的信息,如可以通过该数据库可以定义 Informix 的任务调度器等。用户数据库,就是用户定义用来存储用户数据的数据库,每个用户数据库都包含有数据库 (Database) 级别的系统表,如 systables 等。


图 2. Informix 系统表数据库组成示意图
Informix 系统表数据库组成示意图

Informix 系统字典表的结构及含义详细解释:也可以直接访问 IBM Informix 在线文档,URL 如下:
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=/com.ibm.adref.doc/ids_adr_0210.htm
文档中对每一个系统表的每一个字段的含义有详尽的说明。


常用系统表监控 SQL 及查询结果的诊断与分析

本节以 Informix 数据库监控和优化的方法和分析主题为单位,提供具体访问 Informix 系统表来监控数据库运行状态的 SQL 语句,对 SQL 返回的结果进行分析,提出数据库优化建议。DBA 可以根据本节内容就可以掌握如何使用 Informix 系统表进行数据库的监控和性能优化。

注意:本文中所演示用到的用户定义数据库名为 demodb,在应用本文提供的 SQL 语句时,需要将数据库名 demodb 修改为实际的数据库名。

1. 数据库实例基本运行状况

了解数据库实例的运行信息,如统计信息的起始时间,数据库出现长事务的次数。


清单 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 队列数。

2. 数据库实例概要信息

数据库实例的概要信息称为 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 语句对大表进行顺序扫描方式,可以根据本文后续内容以进一步找到问题原因。简而言之,该信息是进行数据库优化的一个指南针,也是评价一个系统是否健康的一个“血常规表”。

3. Session 的连接情况

通过 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 的连接信息,如 Session 来自哪一个客户端 ( 客户端 IP 地址或者名称 ),在客户端的进程 ID(-1 标识长连接,一些来自 java 连接池的情况都显示为 -1),连接到哪一个数据库。连接时间,以及多长时间没有执行任务,通过该信息可以确定连接池开启的连接个数是否过多或者过少。

4. Session 等待事件

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 等待事件查询结果
数据库 Session 等待事件查询结果

分析:可以通过 where 条件过滤满足特定条件的 session,确定是否有锁等待、buff 等待的情况。

5. 监控正在执行的 SQL 语句

数据库此时到底在忙什么,我们可以通过数据库当前正在执行的 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 查询结果

分析:当需要监控找到符合某一条件的 SQL 语句时,该方法提供了直接的信息,如要找到正在访问表名为 customer 的 SQL 语句有那些,哪只需要通过条件 and sqx_sqlstatement like '%customer%'过滤即可。

6. 找到运行最慢的 SQL 语句

系统中 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 语句。当你的数据库处于非常繁忙的时刻,多次运行该语句,就可以找到那些慢的 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

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

锁是数据库中的常见问题,我们通过 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 语句是否发生了顺序扫描和采用的隔离级别。

8. 锁等待监控

当出现锁冲突时,如何找到锁的占用者以及导致了哪些 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 去找到锁的使用情况,及该锁是否造成了其他使用者的等待。

9. DBSpace 监控

我们可以通过 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 空间查询结果

分析:Dbspace 的 chunk 数量、类型、状态(Operational 为正常状态), 空间的大小、已用空间及已用空间的百分比。及时发现空间即将使用完的情况,提前增加空间。

10. Chunks I/O 监控

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 读写情况查询结果
Chunks 读写情况查询结果

分析:通过查看 Chunk 的 I/O 情况,可以判定数据库系统的 I/O 是否均衡,如果出现不均衡的情况容易出现 I/O 冲突,性能下降。为了充分利用所有的磁盘设备,我们需要尽量均衡 I/O 到不同的设备。对于 I/O 比较集中的 Chunk,需要根据本文后面的内容找到相应的表及索引,通过把表存储在不同的 DBSpace 上,及分片方式进行均衡 I/O。

11. 临时表空间监控

临时表是否使用正确,是否存在磁盘排序?可以通过临时表空间的使用情况得到答案。以及是否存在大量的磁盘排序情况。


清单 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

12. Table Space 监控

数据库中哪些表占用了 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)。

13. Table I/O 监控

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. Index 创建时间

找到表的创建时间比较容易,但是索引的创建时间比较复杂。


清单 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. 查询索引创建时间查询结果
查询索引创建时间查询结果

分析:通过查询索引的创建时间,可以监控到系统中某段时间内创建的新索引。在很多实际生成系统中,由于管理的混乱,人人都可以创建索引,通过查询索引的创建时间,可以找到数据库创建以来新增的索引。
注意,这里查询结果对于分片索引会有多个结果。

15. Index Space

索引采用 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 层的索引,对于大索引,我们需要去再一次分析其合理性,另外可以采用分片的方式来降低索引的层次。

16. Index I/O 监控

某个索引是否被经常使用?某个索引从来没有被使用过?如下 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 系统表来进行数据库运行状况的监控和优化。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/611609/viewspace-729996/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Sar 监控命令小览
请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    252
  • 访问量
    494012