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

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 系统字典表的结构及含义详细解释：也可以直接访问 IBM Informix 在线文档，URL 如下：/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. 查询数据库实例基本运行情况的 SQLdbaccess 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. 查询数据库实例概要信息的 SQLdbaccess 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 的连接情况的 SQLdbaccess 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 等待事件的 SQLdbaccess 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 语句的 SQLdbaccess 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 语句的 SQLdbaccess 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% 的性能消耗。另外，关闭后，跟踪的信息（内存）将字典释放，故一定要分析完成后，再关闭，或者定期把捕获的信息转存到自定义的表 ( 创建三个和 sqltracing 字典表一致的表即可 ) 中，供进一步分析使用。结果分析 :我们可以对 SQL-Tracing 捕获的结果进行分析，顺序扫描的 SQLselect 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 demodb7. 哪些表使用了最多的锁锁是数据库中的常见问题，我们通过 2. 节了解到数据库系统整体上是否存在锁等待、死锁的问题。我们可以通过监控表的锁使用情况，以进一步确认出现锁问题的原因。清单 7. 监控表使用锁的情况的 SQLdbaccess 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. 监控锁等待情况的 SQLdbaccess 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 空间使用情况的 SQLdbaccess 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 情况的 SQLdbaccess sysmaster select dbspace, fname1,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. 监控临时表空间使用情况况的 SQLdbaccess 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=112. Table Space 监控数据库中哪些表占用了 80% 的空间，哪些表的记录数最多，哪些表存在过多的 extent ？这些大表往往决定了系统的性能。那么快速得到数据库中大数据量表的情况非常重要。清单 12. 查询表使用空间情况的 SQLdbaccess sysmaster -A 含分片 select st.dbsname databasename,st.tabname, 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.tabid99 and dt.tabname=st.tabname and st.partnum=sin.ti_partnum and st.dbsname=demodb -and = 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.tabid99 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 情况的 SQLdbaccess 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.tabid99 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. 查询索引创建时间的 SQLdbaccess 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.tabid99 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. 查询索引空间使用情况的 SQLdbaccess sysmaster -A 含分片 select st.dbsname databasename,dt.tabname,di.idxname, 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.tabid99 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.tabid99 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. 索引空间使用情况查询结果按总和统计分析：通过分析索引所占用的空间情况，找大空间索引，