informix常用知识.ppt

INFORMIX 常用知识,软件中心软件开发二科,INFORMIX原理和结构 环境变量 连接类型和通信协议 数据库状态及启停 日常中常用的命令工具 数据库物理存储关系 怎么增加dbspace? Extent 分片 监控 优化 复制,INFORMIX原理和结构,INFORMIX-OnLine动态服务器是INFORMIX的强大的多线程数据库服务器，其设计目的在于发挥对称多处理器（SMP）和单处理器结构的能力，以实现在数据库可伸缩性、可管理性和性能等方面的突破。OnLine动态服务提供最有效的并行数据库结构，以在管理日益增大和复杂化的数据库的同时帮助改善整个系统的性能和可伸缩性。 OnLine动态服务器提供高级事务处理，通过新的并行数据查询（PDQ）技术而达到最优的决策支持、高可用性、数据完整性、大型主机能力的系统管理、图形化的监控工具、以及多媒体的能力，所有这些都集成在一个器的核心技术基于INFORMIX的动态可伸缩结构，其目的在于客户/服务器就绪的软件包中。OnLine动态服务器支持INFORMIX所有基于SQL的应用开发工具以及许多第三方工具，并可以在许多基于UNIX的单机或网络环境下的计算机系统上运行。,系统结构,INFORMIX动态服务器采用的是一种多线程体系结构 动态服务器系统由三个主要组件构成：共享内存、进程和磁盘 共享内存：缓存、内存池、会话（session）、消息缓冲区 进程：被称为虚处理器(VP，Virtual Processors),每一个VP隶属于某一虚处理器类。 磁盘：由多个原始磁盘空间组成，称为chunk。Chunk以页（page）为基本单位。多个chunk集合逻辑上构成了数据空间（dbspace）。数据空间用于存储数据库、表、根以及物理日志和逻辑日志。一个数据空间至少包括一个chunk。,环境变量,export INFORMIXDIR=/informix #设置 IBM Informix 产品的安装目录 export INFORMIXSERVER=online10 #设置 DBSERVERDBNAME 或 DBSERVERALIASES 配置参数的值 export PATH=$INFORMIXDIR/bin:$PATH export ONCONFIG=onconfig #配置文件，在$INFORMIXDIR/etc/onconfig,连接类型和通信协议,Vi $INFORMIXDIR/etc/sqlhosts dbservername nettype hostname servicename online10 onipcshm 9 6664 online10 ontlitcp 9 6664 online10 onsoctcp 9 6664 Nettype说明 on - Dynamic Server se - Standard Engine ipc - IPC connection tli - TLI connection soc - socket connection shm - Shared memory str - Stream pipes tcp - TCP/IP protocol spx - IPX/SPX protocol,配置,数据库状态及启停,一、数据库状态查看 onstat - 启动状态: IBM Informix Dynamic Server Version 11.50.FC6 - On-Line - Up 17 days 02:24:38 - 3700976 Kbytes 停用状态: shared memory not initialized for INFORMIXSERVER online10,二、启动数据库 oninit oninit 命令只用于启动实例 oninit 命令只能在实例处于停止（offline）状态时使用。如果实例处于任何其他状态，那么执行 oninit 命令完全没作用。,三、停止数据库 onmode -ky onmode 命令用于停止实例和其他许多工作 onmode 命令可以改变实例的状态、动态地修改 onconfig 文件中的一些参数、增加和释放内存、配置 B-tree 扫描器、配置 HDR 和 Mach11 特性、实施检查点等。,日常中常用的工具,一、数据的备份恢复（ ontape 、 dbexport 、 dbimport ） 这两种方法的区别是 1). ontape 产生的是二进制流的数据，只能用于本系统的恢复或是二进制兼容的系统上的恢复；dbexport 产生的是 ASCII 数据，可以用于非二进制兼容的系统上的恢复 ； 2).ontape 含有IDS 的系统信息，而 dbexport 不含有IDS 的系统信息，只含有数据库,表及数据信息; 3).在数据量较大的情况下，ontape 比 dbimport 恢复较快； 4).dbexport 出来的文件的大小受到 OS 文件的大小的限制 ontape -s : 对数据的备份 按提示输入本次备份的级数(0级， 1级，2级) 0 级备份是整个ONLINE的备份 1 级备份是在0级基础上所有修改部分的内容的备份 2 级备份是在0级或1级的基础上所有修改部分的内容的备份 ontape -a : 对逻辑日志的备份 (自动方式) ontape -c : 对逻辑日志的备份 (连续方式) ontape -r : 对备份的恢复 按提示依次恢复数据备份(0级，1级，2级)和逻辑日志备份,Dbexport 卸载出ascii文件数据库 例子: dbexport cbsbg (做这个之前确保没有别的用户在使用数据库。) dbimport 用于将一ascii文件装入一个数据库 例子: dbimport d datadbs cbsbg (做这个之前先要删除原来的库。) 注意带 d datadbs ，不然默认导入到 rootdbs,二、dbschema 获取表模式 例子: dbschema d cbsbg t aapf10 ss aapf10.sql 三 、导出和导入表数据 SQL 语句 UNLOAD 和 LOAD 例如，要从数据库 db1 的表 t1 导出数据，可用使用以下命令： unload to my.dat select * from t1; 要将文件中的数据装载到数据库 db1 的表 t1 中，仅需在 db1 中运行以下命令： load from my.dat insert into t1; unload to my.dat delimiter $ select * from t1; load from my.dat delimiter $ insert into t1;,数据库物理存储关系,dbspace:是一组chunk的逻辑集合 chunk: 一个单位的物理磁盘空间。一个chunk由path,offset,size三部分组成。因此我们在使用onspaces命令来加chunk的时候这三个参数是必不可少的。 page: 是一最基本的I/O单位。一个 page的大小是由OS来决定的，有2K/4K/8K/16K extent: 是磁盘上连续page的一组 集合,onstat -d,怎么增加dbspace?,1、lsvg datavg #查看PP大小 2、smit lvm #增加lvm 3、cd /dev chown informix:informix *rdatadbs01* #更改属组 3、su informix cd data ln s /dev/rdatadbs01 datadbs01 #链接dbs 4、onmonitor进去增加或者执行命令 onspaces -c -d datadbs -p /informix/data/datadbs01 -o 0 -s 100000,extent,extend size 指定的数据大小的大小为创建该表的初始空间的大小（单位为kbyte) next size 指定的数据大小的大小为当该表的初始空间使用完毕后数据库表需要向数据空间申请空间大小的初始值 当创建表没有具体指定 extend size 和next sixe的值时，extend size 和next size的缺省值都为16kbyte，即初始空间和初始新增空间的大小为16k。 申请空间的大小= 初始新增空间的大小*2（申请空间的次数 / 16 ）,create table “cbsbg“.aapf30 ( aa30dpnoa char(11), aa30dpnok char(11), aa30acid integer, aa30date char(8), aa30seq integer, aa30brief char(3), aa30abst char(52), aa30crdcls char(4), aa30crdno char(20), aa30dc char(1), aa30amt decimal(16,2), aa30bal decimal(16,2), aa30acno char(32), aa30dpno char(11), aa30stan integer, aa30trcod char(4), aa30oprr char(10), aa30oprc char(10), aa30attr char(5), aa30enseq integer, aa30page integer ) fragment by expression (MONTH (aa30date ) = 1 ) in datadbs01, (MONTH (aa30date ) = 2 ) in datadbs02, (MONTH (aa30date ) = 3 ) in datadbs03, (MONTH (aa30date ) = 4 ) in datadbs04, (MONTH (aa30date ) = 5 ) in datadbs05, (MONTH (aa30date ) = 6 ) in datadbs06, (MONTH (aa30date ) = 7 ) in datadbs07, (MONTH (aa30date ) = 8 ) in datadbs08, (MONTH (aa30date ) = 9 ) in datadbs09, (MONTH (aa30date ) = 10 ) in datadbs10, (MONTH (aa30date ) = 11 ) in datadbs11, (MONTH (aa30date ) = 12 ) in datadbs12 extent size 500000 next size 500000 lock mode row;,分片,“数据分片”允许在表一级对数据存储进行控制。“表分片”是INFORMIX数据库的一个特征。用户可以对表中的记录或索引进行分组，并且存储在不同的位置，这样可以将数据存储到多个磁盘上，从而减少对磁盘I/O的竞争。 数据分片有两种基本类型：基于轮转分片“和 ”基于表达式分片”，对于“基于轮转法分片”，正如其名字一样，数据是根据轮转法方式存入表中的。“基于表达式分片”则根据表中的一个或多个字段对分片的规则进行定义。一般在预知查询条件时采用这种方式，从而避免查询中对某些分片的扫描。,轮转法（PR)所使用的分片规则 CREATE TABLE xyz FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2dbspaceN 基于范围表达式分片方案 CREATE TABLE xyz (aa integer) FRAGMENT BY EXPRESSION aa;1000 AND aa2000 IN dbspace2 REMAINDER IN dbspace3,create table “cbsbg“.aapf30 ( aa30dpnoa char(11), aa30dpnok char(11), aa30acid integer, aa30date char(8), aa30seq integer, aa30brief char(3), aa30abst char(52), aa30crdcls char(4), aa30crdno char(20), aa30dc char(1), aa30amt decimal(16,2), aa30bal decimal(16,2), aa30acno char(32), aa30dpno char(11), aa30stan integer, aa30trcod char(4), aa30oprr char(10), aa30oprc char(10), aa30attr char(5), aa30enseq integer, aa30page integer ) fragment by expression (MONTH (aa30date ) = 1 ) in datadbs01, (MONTH (aa30date ) = 2 ) in datadbs02, (MONTH (aa30date ) = 3 ) in datadbs03, (MONTH (aa30date ) = 4 ) in datadbs04, (MONTH (aa30date ) = 5 ) in datadbs05, (MONTH (aa30date ) = 6 ) in datadbs06, (MONTH (aa30date ) = 7 ) in datadbs07, (MONTH (aa30date ) = 8 ) in datadbs08, (MONTH (aa30date ) = 9 ) in datadbs09, (MONTH (aa30date ) = 10 ) in datadbs10, (MONTH (aa30date ) = 11 ) in datadbs11, (MONTH (aa30date ) = 12 ) in datadbs12 extent size 500000 next size 500000 lock mode row;,核心系统目前有两个表是分片的 aapf30 abpf20 恢复数据的时候需要删除分片的内容，测试环境无分片,监控,监控,onstat 系统监控接口（SMI） -库sysmaster,onstat,onstat g 常用监控命令,onstat -d,onstat -l,系统跟踪,1.打开系统跟踪 Dbaccess sysadmin - EXECUTE FUNCTION task(“set sql tracing on“, 10000, 1,“high“,“global“); 2.跟踪执行时间长语句 dbaccess sysmaster select sql_statement from syssqltrace where sql_stmtname=SELECT and sql_totaltime 4; sql_statement:监控到的语句 sql_stmtname:语句类型，如INSERT/SELECT/UPDATE/DELETE等 sql_totaltime:语句执行时间 3.关闭系统跟踪 Dbaccess sysadmin - EXECUTE FUNCTION task(set sql tracing off); 注：虽然INFORMIX的数据库的全局监控对于整个系统的影响只有5%，比其他数据库都要小的多，但是还是一定要记得，每次打开监控之后，务必进行关闭操作。,表空间,dbaccess sysmaster; select name dbspace, sum(chksize) allocated, sum(nfree) free, round(sum(chksize) - sum(nfree)/sum(chksize)*100,2) pcused from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum = c.dbsnum group by name order by 4 desc,name; 找出超过90%的表空间,表占用空间情况,dbaccess sysmaster; select rpad(tabname, (select max(length(tabname) from systables), ), npused * 2/1024 npused from systables where tabid 99 and tabname not like tmp% order by 2 desc,超过50 extents 的表,select t.tabname tabname, count(*) extent_num, max(t.nrows) rows from sysmaster:sysextents e, systables t where e.tabname=t.tabname and t.tabname not like “sys%“ group by 1 having count( * ) 50 order by 2 desc; 如果除了大型分段表以外，表的扩展块超过了50 个，那么您应该考 虑重新构建这些表以合并扩展块。通过指定表的extent size 和next size调整重建表来减少extent数量。同时我们还需要根据表的记录数 来判断表的extent设置的问题。 通过如下SQL 获取每个表占用的extent 数及空间大小，以便根据表 数据量进一步判断extent是否合理： select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size from sysmaster:systabnames, sysmaster:sysptnext where partnum = pe_partnum and dbsname=“demodb“ and tabname not like sys% group by 1, 2,索引层超过4层的表,select t.tabname,i.idxname, i.levels from sysindexes i,systables t where i.tabid = t.tabid and i.levels=4 order by 3 desc 超过4层的索引需要重建，如果索引超过4层，请分析表是否已经分片 另外可以通过8k或者16K pagesize来减少,顺序扫描的表及扫描次数,语句1 select p.dbsname , t.tabname , sum(p.seqscans) seqscans , max(t.nrows) nrows from sysmaster:sysptprof p , systables t where p.tabname = t.tabname and t.nrows 100 and p.seqscans0 and p.dbsname not like “sys%“ and p.tabname not like “sys%“ group by 1,2 order by 3 desc 从输出结果找出那些表的顺序扫描数很高。如果它是一个具有几千甚 至几百万行的大表，那么您可能需要考虑向该表添加一些索引，或者 考虑使用程序伪指令来强制内部查询优化器为访问该表中的数据选 择索引而不是顺序扫描。,语句2 select first 5 t.tabname,dbsname, nrows*rowsize*p.seqscans costs, p.seqscans,nrows from sysmaster:sysptprof p,systables t,sysmaster:sysprofile s where p.tabname=t.tabname and p.seqscanss.value/50 and =seqscans and nrows2000 order by 3 desc,DBSpace I/O,select dbspace, fname1,125 chunk_name, sum(pagesread) diskreads,sum(pageswritten) diskwrites, sum(pagesread)+sum(pageswritten) disk_rwes from sysmaster:syschkio c, sysmaster:syschunks k, sysmaster:sysdbspaces d where d.dbsnum = k.dbsnum and k.chknum = c.chunknum -# c.chknum group by 1, 2 order by 5 desc; onstat -D onstat -g iof 我们的目标是要使所有的dbspace 都有平衡的磁盘读写操作。在大多数情况下，这是不现实的，但上面的输出至少让您对dbspace I/O 的分配方式有了一个概念，可以帮助您标识“最热门的”dbspace 那些磁盘读写最多的dbspace。如果有些dbspace 的磁盘读写操作 相当繁忙而另外一些的读写操作则相当空闲，那么您可能需要为Informix 引擎调整甚至重新安排物理磁盘布局。我们可以使用onstat -D 和onstat -g ioq 获得类似的信息，前者显示各个块的磁盘读和写，而后者显示磁盘I/O 等待队列信息。（重启后从新计数） onstat -g iof打印异步I/O 块(chunk)或文件的统计数据。例如，我们可能会认为demodbchk得到了太多的活动。 如果一个繁忙的数据库占用demodbchk 块，我们应考虑迁移此数据库中某些表，分散部分表到其他的活动块。 如果一个繁忙的表占用demodbchk 块，我们应考虑分裂表(Fragmentation)和分散到其他的活动块,I/O最多的表,select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites) diskwrites, (isreads + pagreads)+ (iswrites + pagwrites) disk_rsws from sysmaster:sysptprof where tabname not like sys% and dbsname not like sys% order by 5 desc 可以根据输出得出哪些表有较大的I/O，也是我们需要重点优化的表。 根据从这个查询获得的输出，您可能需要在dbspace 间移动一些表 以使磁盘I/O 平衡得更好。（重启后从新计数）,Most CPU SQL,捕获消耗CPU 最多的SQL语句 select first 10 sqx_estcost,sqx_estrows,sqx_sqlstatement from sysmaster:syssqexplain order by sqx_estcost desc 技巧： 由于该方法只能捕获到当前活动SQL的CPU消耗情况。我们需要多次捕获不同 时间段的SQL。,查找表锁语句,select dbsname,tabname, sum(pf_rqlock) as locks, sum(pf_wtlock) as lockwaits, sum(pf_deadlk) as deadlocks from sysactptnhdr,systabnames where pf_wtlock 0 and systabnames.partnum = sysactptnhdr.partnum -and dbsname = “dbname“ group by dbsname,tabname order by lockwaits desc;,Informix数据库锁表处理,综合业务系统柜员在日终或平时出现锁表导致业务不能进行，(比如表外处理时死锁，表名:blankvouchuse),则操作如下： 1以informix用户登陆服务器，dbaccess,选择数据库bias_dbs, Select * from systables where tabname =锁表的表名,记录下字段partnum的值，通过计算器，把该值从10进制变成16进制。 2.退出dbaccess到$下，输入命令：onstat k,查找显示的tblsnum的值与partnum相符的记录，记下owner的值。 3.onstat u ,找到usethread与owner的值相符的记录，找到ssessid. 4.onmode z ssessid ，杀掉死锁的进程。,优化,性能规划：深入了解应用与数据库的交互特征，确立良好的设计、开发、测试迭代过程，上线前消除模型上的性能瓶颈。 实例调优：建立性能基准，对比调节数据库、操作系统、存储、网络等的配置，主动监控、消除瓶颈。 SQL 调优：书写高效 SQL，优化相关数据库对象，充分借助优化器，确定最佳执行计划。,性能优化流程 首先执行下面的初始检查： 获取直接用户的使用反馈，确定性能目标和范围。 获取性能表现好与坏时的操作系统、数据库、应用统计信息。 对数据库做一次全面健康检查。 根据收集的信息，以及对应用特性的了解，构建性能概念模型，明确性能瓶颈所在，以及导致性能的根本原因。 首先应该排除操作系统、硬件资源造成的瓶颈。 然后针对数据库系统性能进行分析 必要时，还需要检查应用日志，因为系统性能问题也可能由于应用非 SQL 部分造成瓶颈。 提出一系列针对的优化措施，并根据它们对性能改善的重要程度排序，然后逐一加以实施。不要一次执行所有的优化措施，必须逐条尝试，逐步对比。 通过获取直接用户的反馈验证调节是否已经产生预期的效果，否则，需要重新提炼性能概念模型，直到对应用特性了解进一步准确。 重复上述，直到性能达到目标或由于客观约束无法进一步优化。,典型性能问题案例,案例 1：数据库应用突然变慢 问题特征 数据库应用突然变慢，查看系统信息，发现 CPU 空闲突然很低，IO 性能没有明显恶化。 处理步骤 首先，需要排除操作系统上其他应用程序的问题。通过 top(HP)/topas(AIX/Linux) 命令可以看到当前占用 CPU 资源最多的进程，确认是 oninit 进程。Solaris 上默认没有 top 命令，可以通过 /usr/ucb/ps aux | more 的方式来查看，该输出是根据 CPU 占用情况来排序的。 数据库进程占用了大量 CPU 资源时，往往是在对大表在做全表扫描。通过 4.1 中的办法初步确认问题 SQL 后，如果是条件查询 SQL，如带 WHERE 条件的 SELECT /UPDATE /DELETE，还可以通过得到具体的 SQL 查询计划来确认是在进行全表扫描。此时需要对比 dbschema 得到的建表脚本，看是否建立了相应的索引，如果没有合适的索引，应该创建；如果应用没有合理应用已有索引，应该考虑修改应用 SQL。如果表上有合适的索引，应用 SQL 也没有问题，那么就有可能是由于表中数据已经变化较大而长时间未对表收集统计信息，造成数据库引擎选择了错误的查询计划。此时应该对该表收集统计信息后，通常可以收到良好的效果。 有时候问题 SQL 还会是 INSERT 语句，此时通常需要查看表的建表脚本，看看表上是否有过多的索引，是否该表上有不适当的外键指向另一个大表，也可以通过适当删除表中的记录来实现优化。,案例 2：检查点持续时间突然显著增加 问题特征 数据库应用突然变慢，查看系统信息，发现 CPU 空闲突然很低，IO 性能明显恶化。和问题 1 的显著不同在于，此时 IO 恶化现象非常明显。 Vmstat 显示 b(block) 很大，有很多等待 IO 的进程， sar 显示 wio 明显超过平时值。观察数据库日志，发现数据库检查点持续时间 (checkpoint duration time) 显著增加，平时在 3 秒以内就能完成，此时需要 10 秒甚至更长时间才能完成。 处理步骤 首先还是查看数据库日志和操作系统日志，排除数据库内部错误和操作系统 IO 错误。如果用的是阵列 (RAID)，最好再查看一下阵列的日志，出现这种情况最常见的原因是阵列出了问题，比如电池没电，cache 没有打开等等。 排除了操作系统和数据库内部错误，就需要了解一下是否有新的应用在进行大批量的数据操作，如 INSERT/DELETE/UPDATE，是否能将这些操作放在系统相对空闲的时候进行。对于大批量的数据导入操作，在 INFORMIX9.4 中提供了 RAW 类型的表，由于不记录逻辑日志，插入速度会快很多，导入完成后，再将表修改为正常模式；对普通表应该先导入数据，再创建索引，注意主键、外键默认都会创建索引，应该在数据导入后在创建。 不恰当的应用 SQL 也会导致 IO 量非常大，可以用案例 1 中的办法来找到问题 SQL，根据实际情况进行处理。,案例 3：检查点持续时间逐渐缓慢增加 问题特征 数据库稳定运行一段时间后，性能开始下降，检查点持续时间 (checkpoint duration time) 开始逐渐增加，系统 CPU 空闲降低，WIO 有所增加。这些情况往往出现在新的应用上线后一段时间，由于在开发和测试环境中数据量小，性能问题不会暴露，当生产环境数据量增长到一定程度后，性能问题就会出现。 针对这种情况，需要确认定期在对数据库，尤其是对数据库中的大表，在定期做收集统计数据的工作 (update statistics)，避免数据量的增大造成系统性能急剧下降。 找到被顺序扫描多次的大表及其上的问题 SQL，进行分析，采取相应办法尝试减少其上的顺序扫描： 创建相应索引； 修改应用 SQL； 及时删除表中不必要的数据。,常见调优技巧,找到 CPU 占用最高的 SQL 1. 在 sysmaster 库中执行 select sqx_estcost, sqx_sqlstatement from syssqexplain order by sqx_estcost desc 注意：此时看到的仅仅是当前正在执行的 SQL 需要多看几次 2. onstat 命令 onstat -g act 得到当前正在执行的 SQL Running threads: tid tcb rstcb prty status vp-class name 75 a327318 a14d6b4 2 cond wait(sm_read) 1cpu sqlexec 76 a327b40 a14d280 2 yield lockwait 1cpu sqlexec 根据 rstcb 列（不要包括前面的 C0000 等，仅要后面部分） onstat -u | grep a14d6b4 从第三列 sessid 得到 session id onstat -g sql 即可得到当时正在执行的 SQL 一般多找几个 threads 后，就基本可以确定问题 SQL,3. 示例 onstat g act Threads: tid tcb rstcb prty status vp-class name 141904 84176538 8030eab8 2 running 1cpu sqlexec onstat u | grep 8030eab8 Userthreads address flags sessid user tty wait tout locks nreads nwrites 8030eab8 Y-P- 131047 informix - 84022480 0 1 11671 14722 onstat g sql 131047 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 131047 DELETE (all) testdb DR Wait 10 0 0 9.03 Current SQL statement : delete from my_tab Last parsed SQL statement : delete from my_tab 得到 SQL 后，利用 set explain on 分析其查询路径， 看是否未利用索引，在对大表进行全表扫描 根据需要创建相应索引,找到全表扫描较多的表及其 SQL 1. 得到全表扫描较多的表 cat check.sql - 系统顺序扫描较多时，被多次顺序扫描的大表，如果有，应该考虑增加索引 select first 5 substr(t.tabname,0,20) tabname,substr(dbsname,0,10) dbname, nrows*rowsize*p.seqscans costs, substr(p.seq