Informix健康检查13

©2010IBMCorporation2020年2月24日星期一Informix数据库管理员快速指南IBMInformix中国开发中心InformixEnablementTeam谭永贻技术经理InformationManagement–Informix©2010IBMCorporation2大纲Informix快速健康检查定期执行的数据库管理任务(task)运行时的监控性能优化让Informix数据库管理员的从第一天就发挥生产力InformationManagement–Informix©2010IBMCorporation3Informix快速健康检查CPU–监控CPU的空闲值内存–查看总的可用内存–查看分配给IDS的内存–检查IDS的内存参数I/O–监测系统I/O带宽–查看各个磁盘上的I/O吞吐量是否均衡网络–查看与“IDS的网络连接”相关的信息online.log文件–查看错误和警告信息InformationManagement–Informix©2010IBMCorporation4CPUA．topTasks:209total,1running,207sleeping,0stopped,1zombieCpu(s):0.3%us,0.3%sy,0.0%ni,50.0%id,49.2%wa,0.0%hi,0.2%si,0.0%stB．sar510010:37:06AMCPU%user%nice%system%iowait%steal%idle10:37:11AMall0.700.000.4049.200.0049.7010:37:16AMall0.500.000.3049.450.0049.7510:37:21AMall0.500.000.5049.300.0049.7010:37:26AMall0.500.000.4049.350.0049.75分析：（1）普通情况下CPU的空闲率是否低于20％（2）峰值时CPU的空闲率是否低于2%当（1）或（2）的回答是肯定时，添加更多的CPUInformationManagement–Informix©2010IBMCorporation5内存A．topCpu(s):0.5%us,0.2%sy,0.0%ni,49.9%id,49.4%wa,0.0%hi,0.0%si,0.0%stMem:4044192ktotal,4016092kused,28100kfree,32344kbuffersSwap:8193140ktotal,138236kused,8054904kfree,3544312kcachedB．onstat-gsegidkeyaddrsizeovhdclassblkusedblkfree12943367525648014400000089534054410925608R21858721297613652564802795dd0003343974403920416V1703564605…………………………………………………………………V……………….Total:--1229737984--23562264607分析：总的空闲内存有多少？分配给IDS的内存有多少？IDS是否有4个以上的虚拟内存段？了解IDS的内存参数–BUFFERPOOL–SHMVIRTSIZE–SHMADDInformationManagement–Informix©2010IBMCorporation6I/O（1）A．iostat5100avg-cpu:%user%nice%system%iowait%steal%idle14.290.000.296.380.0079.04Device:tpsBlk_read/sBlk_wrtn/sBlk_readBlk_wrtnsda25.24386.78293.22131991652100065298sda10.000.010.00186614sda224.76380.97287.7313001048298190344B．SysmasterSQLforI/Oselectd.namedbspace,fname[1,125]chunk_name,sum(pagesread)diskreads,sum(pageswritten)diskwrites,sum(pagesread)+sum(pageswritten)disk_rwesfromsysmaster:syschkioc,sysmaster:syschunksk,sysmaster:sysdbspacesdwhered.dbsnum=k.dbsnumandk.chknum=c.chunknum--#c.chknumgroupby1,2orderby5desc;dbspacechunk_namediskreadsdiskwritesdisk_rwesdbs11/opt/dbschk/dbs114752323230123234759demodbs/opt/dbschk/demodbchk2493984342191323498llogdbs/opt/dbschk/llogch661115685011156916rootdbs/opt/dbschk/online_root375131000147514InformationManagement–Informix©2010IBMCorporation7I/O（2）C．onstat-Daddresschunk/dbsoffsetpageRdpageWrpathnameb2d481c011047523232301/opt/dbschk/dbs11b417dce822024939843421/opt/dbschk/demodbchkb41790283306611156850/opt/dbschk/llogchb4179218440375132738/opt/dbschk/online_rootD．onstat-giofAIOglobalfiles:gfdpathnamebytesreadpagereadsbyteswritepagewritesio/s3dbs119728004754757975244823232301916.64demodbchk51056642493201593262089843421916.65llogch135168662284922880011156850916.66online_root768266243751356074242738916.6确定读(Read)操作最多的Chunk确定写(Write)操作最多的ChunkInformationManagement–Informix©2010IBMCorporation8I/O（3）问题：系统I/O带宽是否达到Informix的要求?I/O流量只发生在少数特定的磁盘?是否有不可靠的（例如异常缓慢的）磁盘?分析：观测系统的I/O吞吐量和每个磁盘的I/O吞吐量考虑使用更好的存储设备在不同的dbspace之间移动数据表，从而达到更平衡的I/O对数据表进行分片将一些AttachedIndex改为DetachedIndex在磁盘毁损前，替换不可靠的磁盘InformationManagement–Informix©2010IBMCorporation9网络A．onstat-gntu|grepsqlexec|wc-l323B．onstat-gntu#netscbconnectsreadwriteq-freeq-limitsq-exceedalloc/max6/1252221129571148196/10135/100/010/10C.onstat-gntdClientTypeCallsAcceptedRejectedReadWritesqlexecyes52121235934766366分析：DBA必须为IDS配置足够多的网络连接。Rejected的数目较大意味着IDS没有足够多的网络连接。建议：当Rejected的数目较大时，修改NETTYPE参数，增大IDS的网络连接数–NETTYPEsoctcp，10,350，CPU–NETTYPEconnection_type，poll_threads，c_per_t，vp_class–poll_threads不能超过NUMCPUVPS。当c_per_t超过350，建议将vp_class设为NETInformationManagement–Informix©2010IBMCorporation10online.logA．grepErroronline.log10:05:43SCHAPI:Error-23197Databaselocaleinformationmismatch.B．grepThreadonline.log13:41:16Who:Session(654,prpsvr@hljpicc,213218,70000035702aff8)Thread(805,sqlexec,700000357393c98,4)File:rsdebug.cLine:106713:41:16Results:Possibleinconsistenciesin'prpalldb:piccprp.prprepay'13:41:16Action:Run'oncheck-cD6292799'C.grepAssertFailedonline.log00:57:53AssertFailed:Unexpectedvirtualprocessortermination,pid=22,exit=0x900:57:53Who:Session(1122,demodb@demo_no,6238,721214359)Thread(62340,sqlexec,3845e36,1)分析Error-23197：可使用“finderr-23197”命令查看详细信息oncheck-cD：硬盘上的数据出现问题，需使用oncheck命令对硬盘上的数据进行检查AssertFailed：查看af.xxx文件，联络IBM技术支持InformationManagement–Informix©2010IBMCorporation11大纲Informix快速健康检查定期执行的数据库管理任务(task)运行时的监控性能优化让Informix数据库管理员的从第一天就发挥生产力InformationManagement–Informix©2010IBMCorporation12定期执行的数据库管理任务(task)dbspace–检查rootdbspace–监控每个dbspace的空闲空间–计算每个dbspace的数据增长速度tempdbspace–应该设置多少个tempdbspace–在系统繁忙时监控tempdbspace的空闲空间逻辑日志和物理日志–监控逻辑日志和物理日志的使用情况–检查逻辑日志缓冲区和物理日志缓冲区的配置情况更新统计数据（UpdateStatistics）–根据需要更新某些数据表的统计数据InformationManagement–Informix©2010IBMCorporation13dbspaces—建立在rootdbs上的数据表A．SysmasterSQLselectdistinctt.dbsnamedatabase,d.namedbspace,t.tabnamefromsysmaster:sysdbstabd,sysmaster:syschunksc,sysmaster:sysextentstwheret.chunk=c.chknumandc.dbsnum=d.dbsnumandt.dbsnamenotlike'sys%'andt.dbsname!='onpload'andt.tabnamenotlike'sys%'andd.name='rootdbs';databasedbspacetabnamebank18030rootdbscus