Informix 系统表监控和优化数据库.docx

Informix 系统表监控和优化数据库.docx

《Informix 系统表监控和优化数据库.docx》由会员分享，可在线阅读，更多相关《Informix 系统表监控和优化数据库.docx（18页珍藏版）》请在冰豆网上搜索。

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

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

简介：

大多数Informix使用者都会使用onstat,oncheck等命令的方式来监控Informix数据库的运行情况，从而确定数据库优化方案。

但这种方法存在一些难度，需要多个命令输出综合考虑，才能得出结论。

本文从另外一个角度－－SQL语句，通过SQL语句查询系统表的方式，返回综合的、有组织性的、有实际数据根据的信息。

DBA根据本文可以直接找到系统性能问题，从而进行快速有效的优化，同时可以根据这些有效的数据完成数据库优化过程的报告文档。

Informix数据库系统字典表简介

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系统字典表的结构及含义详细解释：

也可以直接访问IBMInformix在线文档，URL如下：

文档中对每一个系统表的每一个字段的含义有详尽的说明。

回页首

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

本节以Informix数据库监控和优化的方法和分析主题为单位，提供具体访问Informix系统表来监控数据库运行状态的SQL语句，对SQL返回的结果进行分析，提出数据库优化建议。

DBA可以根据本节内容就可以掌握如何使用Informix系统表进行数据库的监控和性能优化。

注意：

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

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

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

清单1.查询数据库实例基本运行情况的SQL

dbaccesssysmasterselectdbinfo（'UTC_TO_DATETIME',sh_boottime）start_time,currentyeartosecond-dbinfo（'UTC_TO_DATETIME',sh_boottime）run_time,sh_maxchunksasmaxchunks,sh_maxdbspacesmaxdbspaces,sh_maxuserthreadsmaxuserthreads,sh_maxtransmaxtrans,sh_maxlockslocks,sh_nlrusbuff_lrus,sh_longtxlongtxs,dbinfo（'UTC_TO_DATETIME',sh_pfclrtime）onstat_z_running_timefromsysmaster:

sysshmvals;

图3.数据库实例基本运行情况查询结果

分析：

从如上SQL语句返回的结果可以得到Informix实例如下有用的信息：

上一次运行onstat-z清除统计信息的时间：

onstat_z_running_time，该时间可以帮助DBA确认当前统计的信息的时间长度，而不需要重新启动数据库，可以通过onstat-z来清除统计信息从而确认时间间隔内的数据库运行情况。

数据库出现长事务的次数：

longtxs。

另外，我们可以得到实例所支持的最大chunk和dbspace数量，以及可以运行的线程数量。

还包含有实例的配置参数值：

锁的个数，LRU队列数。

2.数据库实例概要信息

数据库实例的概要信息称为Informix数据库运行的健康检查的“血常规表”，可以从整体上掌握数据库运行的状况，评价数据库是否存在性能问题。

清单2.查询数据库实例概要信息的SQL

dbaccesssysmasterselectname,valuefromsysmaster:

sysprofile;

图4.数据库实例概要信息查询结果

分析：

系统表sysprofile是保存了Informix运行的概要信息，是onstat-p命令的基本信息来源，如上查询结果可以看出，可以获取类似的读/写缓存命中率、锁溢出、锁等待、死锁、顺序扫描次数、事务回滚次数及比例、磁盘排序、内次排序情况、磁盘写情况（onstat-F）等信息。

数据库运行概要信息是整个实例自开机或者上一次运行onstat-z以来的统计信息，反应了数据库实例的总体情况，从各个方面确定数据库实例是否存在性能问题，在DBA进行数据库优化时，对Informix诊断要做的第一件事情就是查看该信息，如发现seqscans值偏大，同时diskread也较大，则表明系统中有很多SQL语句对大表进行顺序扫描方式，可以根据本文后续内容以进一步找到问题原因。

简而言之，该信息是进行数据库优化的一个指南针，也是评价一个系统是否健康的一个“血常规表”。

3.Session的连接情况

通过Session的连接信息，可以分析出数据库系统业务负载情况，来自哪些客户端的任务较多，并且根据Session的空闲情况，判断客户端连接池是否存在过多的连接。

清单3.查询Session的连接情况的SQL

dbaccesssysmasterSELECTs.sid,s.username,s.hostname,q.odb_dbnamedatabase,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_timeFROMsyssessionss,systcblstt,sysrstcbr,sysopendbqWHEREt.tid=r.tidANDs.sid=r.sidANDs.sid=q.odb_sessionidORDERBY7DESC;

图5.数据库Session连接情况查询结果

分析：

在数据库监控过程中，我们经常需要监控Session的连接信息，如Session来自哪一个客户端（客户端IP地址或者名称），在客户端的进程ID（-1标识长连接，一些来自java连接池的情况都显示为-1），连接到哪一个数据库。

连接时间，以及多长时间没有执行任务，通过该信息可以确定连接池开启的连接个数是否过多或者过少。

4.Session等待事件

Session是监控应用程序对数据库访问的窗口，通过分析Session的等待事件，可以快速的了解到应用程序客户端数据库请求是否存在性能问题，通过等待事件，我们可以找到性能慢的应用，并加以优化。

清单4.查询Session等待事件的SQL

dbaccesssysmasterselectsid,pid,username,hostnameis_wlatch,--blockedwaitingonalatchis_wlock,--blockedwaitingonalockedrecordortableis_wbuff,--blockedwaitingonabufferis_wckpt,--blockedwaitingonacheckpointis_incrit--sessionisinacriticalsectionoftransactionfromsyssessionsorderbyusername;

图6.数据库Session等待事件查询结果

分析：

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

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

数据库此时到底在忙什么，我们可以通过数据库当前正在执行的SQL语句进行判断，找到哪些出现频繁的SQL语句，哪些运行慢的SQL语句。

同时，可以用来监控访问特定表的SQL。

清单5.查询Informix正在执行的SQL语句的SQL

dbaccesssysmasterselectusername,sqx_sessionid,sqx_sqlstatementfromsysmaster:

syssqexplain,sysmaster:

sysscblstwheresqx_sessionid=sid--andsqx_sqlstatementlike'%tabname%';

图7.监控正在执行的SQL查询结果

分析：

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

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

系统中20%的SQL语句占用了80%的系统资源，所以DBA在优化数据库时，找出和优化运行慢的SQL语句至关重要，如何捕获到系统中运行慢的SQL语句对很多DBA来说非常困难，这里介绍两个有效的方法：

当前运行慢的SQL和一段时间内运行慢的SQL语句。

清单6.查询数据库当前运行最慢SQL语句的SQL

dbaccesssysmasterselectfirst25sqx_estcost,sqx_estrows,sqx_sqlstatementfromsysmaster:

syssqexplainwhere1=1orderbysqx_estcostdesc;

图8.监控数据库当前运行最慢SQL语句的查询结果

分析：

通过查询当前正在执行的SQL语句的开销来监控运行慢的SQL语句。

当你的数据库处于非常繁忙的时刻，多次运行该语句，就可以找到那些慢的SQL语句。

如果要找到数据库一段时间以内（比如早上8点到12点）运行慢的SQL语句，那么我们需要利用到Informix11的SQLTRACE功能。

SQLTRACE功能的使用如下：

打开SQLTRACE跟踪SQL:

echo'executefunctiontask（"setsqltracingon",100000,"1k","low","demodb"）;'|dbaccesssysadmin

说明：

∙demodb为跟踪的数据库名；

∙100000为最多跟踪的SQL语句个数，超过这个数字时，将最早跟踪的SQL删除

∙1k为每个SQL占用的内存，对于有特别大的SQL语句，需要设置更大的值，如2k,4k

关闭SQLTRACE功能:

echo'executefunctionsysadmin:

task（"SETSQLTRACINGOFF"）;'|dbaccesssysadmin

说明：

跟踪分析完成后，一定要关闭。

SQL-Tracing开启下将对系统有2%－5%的性能消耗。

另外，关闭后，跟踪的信息（内存）将字典释放，故一定要分析完成后，再关闭，或者定期把捕获的信息转存到自定义的表（创建三个和sql－tracing字典表一致的表即可）中，供进一步分析使用。

结果分析:

我们可以对SQL-Tracing捕获的结果进行分析，

顺序扫描的SQL

selectdistinctsql_statementfromsysmaster:

Syssqltracetinnerjoinsysmaster:

syssqltrace_iteriont.sql_id=i.sql_idwherei.sql_itr_info='SeqScan';

查询速度慢SQL

可以通过不同的指标进行排名

echo"selectfirst20*fromsysmaster:

syssqltraceorderbysql_totaltime"|dbaccessdemodb

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

锁是数据库中的常见问题，我们通过2.节了解到数据库系统整体上是否存在锁等待、死锁的问题。

我们可以通过监控表的锁使用情况，以进一步确认出现锁问题的原因。

清单7.监控表使用锁的情况的SQL

dbaccesssysmasterselectdbsnamedatabanse,tabname,sum（pf_rqlock）aslocks,sum（pf_wtlock）aslockwaits,sum（pf_deadlk）asdeadlocksfromsysactptnhdr,systabnameswheresystabnames.partnum=sysactptnhdr.partnum--andpf_wtlock>=0andpf_rqlock>=0groupbydbsname,tabnameorderbylockwaitsdesc;

图9.表使用锁情况的查询结果

分析：

当数据库出现锁问题时，首先我们需要找到哪些表消耗了最多的锁资源，哪些表出现了锁等待和死锁情况。

从而我们可以进一步确定需要监控的对象和有针对性的优化，可以分析表的锁模式：

页级锁还是行级锁，还需要监控访问表的SQL语句是否发生了顺序扫描和采用的隔离级别。

8.锁等待监控

当出现锁冲突时，如何找到锁的占用者以及导致了哪些Session等待，是进行锁优化的关键。

清单8.监控锁等待情况的SQL

dbaccesssysmasterselectdbsnamedatabanse,tabname,sum（pf_rqlock）aslocks,sum（pf_wtlock）aslockwaits,sum（pf_deadlk）asdeadlocksfromsysactptnhdr,systabnameswheresystabnames.partnum=sysactptnhdr.partnum--andpf_wtlock>=0andpf_rqlock>=0groupbydbsname,tabnameorderbylockwaitsdesc;

图10.数据库锁等待查询结果

分析：

当发现数据库中有锁等待的情况，即使用本文2.2节查询的结果lockwts值比较大时，或者通过2.4发现Session有锁等待情况，或者我们发现表被锁的情况，我们可以通过该SQL去找到锁的使用情况，及该锁是否造成了其他使用者的等待。

9.DBSpace监控

我们可以通过onstat-d了解到Informix的DBSpace的使用情况，剩余空间情况等。

但是输出格式不是很友好，通过该SQL可以得到dbspace的全面、友好的信息。

清单9.监控DBSpace空间使用情况的SQL

dbaccesssysmasterSELECTA.dbsnumasNo,trim（B.name）asname,CASEWHEN（bitval（B.flags,'0x10'）>0ANDbitval（B.flags,'0x2'）>0）THEN'MirroredBlobspace'WHENbitval（B.flags,'0x10'）>0THEN'Blobspace'WHENbitval（B.flags,'0x2000'）>0ANDbitval（B.flags,'0x8000'）>0THEN'TempSbspace'WHENbitval（B.flags,'0x2000'）>0THEN'TempDbspace'WHEN（bitval（B.flags,'0x8000'）>0ANDbitval（B.flags,'0x2'）>0）THEN'MirroredSbspace'WHENbitval（B.flags,'0x8000'）>0THEN'SmartBlobspace'WHENbitval（B.flags,'0x2'）>0THEN'MirroredDbspace'ELSE'Dbspace'ENDasdbstype,CASEWHENbitval（B.flags,'0x4'）>0THEN'Disabled'WHENbitand（B.flags,3584）>0THEN'Recovering'ELSE'Operational'ENDasdbsstatus,format_units（sum（chksize）,max（A.pagesize））asDBS_SIZE,format_units（sum（decode（mdsize,-1,nfree,udfree））,max（A.pagesize））asfree_size,TRUNC（100-sum（decode（mdsize,-1,nfree,udfree））*100/sum（chksize）,2）||'%'asused,TRUNC（MAX（A.pagesize/1024））aspgsize,MAX（B.nchunks）asnchunksFROMsyschktabA,sysdbstabBWHEREA.dbsnum=B.dbsnumGROUPBYA.dbsnum,name,3,4ORDERBYA.dbsnum;

图11.数据库DBspace空间查询结果

分析：

Dbspace的chunk数量、类型、状态（Operational为正常状态）,空间的大小、已用空间及已用空间的百分比。

及时发现空间即将使用完的情况，提前增加空间。

10.ChunksI/O监控

Chunk的I/O是否均衡，是从Chunk角度判断数据库存储规划是否存在问题的出发点。

清单10.监控ChunkI/O情况的SQL

dbaccesssysmasterselectd.namedbspace,fname[1,125]chunk_name,readsread_count,writeswrite_count,reads+writestotal_count,pagesread,pageswritten,pagesread+pageswrittentotal_pgfromsysmaster:

syschkioc,sysmaster:

syschunksk,sysmaster:

sysdbspacesdwhered.dbsnum=k.dbsnumandk.chknum=c.chunknum--#c.chknumorderby8desc;

图12.Chunks读写情况查询结果

分析：

通过查看Chunk的I/O情况，可以判定数据库系统的I/O是否均衡，如果出现不均衡的情况容易出现I/O冲突，性能下降。

为了充分利用所有的磁盘设备，我们需要尽量均衡I/O到不同的设备。

对于I/O比较集中的Chunk，需要根据本文后面的内容找到相应的表及索引，通过把表存储在不同的DBSpace上，及分片方式进行均衡I/O。

11.临时表空间监控

临时表是否使用正确，是否存在磁盘排序？

可以通过临时表空间的使用情况得到答案。

以及是否存在大量的磁盘排序情况。

清单11.监控临时表空间使用情况况的SQL

dbaccesssysmasterselecttrim（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_nrowsFROMsysmaster:

systabnamesn,sysmaster:

systabinfoiWHERE（sysmaster:

bitval（i.ti_flags,32）=1ORsysmaster:

bitval（i.ti_flags,64）=1ORsysmaster:

bitval（i.ti_flags,128）=1）ANDi.ti_partnum=n.partnumorderby1,3;

图13.临时表空间使用情况查询结果

分析：

SortTEMP是用来排序用的临时空间，合理调整参数:

DS_NONPDQ_QUERY_MEM，减少磁盘排序onmode-wfDS_NONPDQ_QUERY_MEM=2048。

确定是否有临时表存储的dbspace不是临时表空间的情况，那可能由于没有正确配置好临时表空间，或者没有在创建临时表时使用withnolog选项。

Informix11及以上版本可以通过该参数TEMPTAB_NOLOG让应用程序中遗忘使用withnolog的情况正