Informix数据库优化

Informix 数据库系统字典表简介

nformix 数据库服务器运行时的状态信息是数据库管理员 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
https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/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 来自哪一个客户端 ( 客户端 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 等待事件查询结果
这里写图片描述

分析:可以通过 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 语句时,该方法提供了直接的信息,如要找到正在访问表名为 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 语句。

如果要找到数据库一段时间以内(比如早上 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 的 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 读写情况查询结果
这里写图片描述

分析:通过查看 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 系统表来进行数据库运行状况的监控和优化。

  • 0
    点赞
  • 3
    收藏
  • 打赏
    打赏
  • 0
    评论
Informix 11 SQL 性能调整 1 前言 SQL 语言是关系型数据库与外界交互的重要途径,书写高效率的 SQL 是每一个 DBA 和开发人员必备的技能。众所周知,不同的数据库各有所长,SQL 也有不同,为了帮助应用开发者在最新版的 INFORMIX 数据库上书写高效的 SQL,利用 INFORMIX 的最新功能对 SQL 语句进行 SQL 调优、追踪,本文以调整 SQL 执行效率为最终目标,给大家介绍如何查看 INFORMIX 的 SQL 执行计划,如何通过统计信息,SQLDirectives 调整执行计划,如何通过 SQL Drill-down 监控 SQL 的执行效率,并且总结了书写 SQL 语句时若干需要注意的地方,可以作为一个实用的 SQL Cheklist 使用。本文尽量减少理论的论述而注重实例分析和实际操作,相信对大家一定会有所帮助。 2 实验环境 2 × Intel(R) Core(TM)2 Duo CPU T7700 @ 2.40GHz 2G Memory 100G Disk OS: REH 64bits IDS Version: 11.50.FC3 3 SQL 执行计划 关于执行计划,我们可以把它简单的理解成 SQL 语句执行时访问数据的方式。执行计划的优劣是影响 SQL 执行效率的重要因素。它包括:查询优化器认为最优的数据访问路径,返回记录数的估计值,SQL 的相对开销值,以及其他附属信息。 3.1 获取执行计划 获取执行计划有多种方式: 1. 在服务器上直接执行 SQL 语句 set explain on 得到当前 session 中 SQL 的执行计划描述文件,默认名称为:sqexplain.out,

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
评论

打赏作者

PickJerry

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值