NIOS数据库(informix)优化和备份策略方案指导.doc

亿阳信通服务支持中心 nios db(informix)数据库优化和备份策略方案指导 nios db(informix)数据库优化和备份策略方案指导亿阳信通服务支持中心2011年05月版权所有 本产品或文档受版权保护，其使用、复制、发行和反编译均受许可证限制。未经亿阳及其授权者事先的书面许可，不得以任何形式、任何手段复制本产品及其文档的任何部分。目录目录3第一章 前言41.1目的41.2内容41.3谁应该读这本书4第二章 nios数据库优化52.1 基本dbs优化和调整52.2 informix onconfig参数配置72.3 表结构及分片策略优化292.4 应用索引的优化30第三章 niosdb备份和容灾策略323.1.备份策略和容灾323.2 .高可用性323.3. 数据压缩32第四章 常用提高性能的注意事项和出错查询334.1 合理利用索引334.2 sql技巧334.3 view, pdq, stored procedure, and trigger344.4表被索处理344.5查询主键重复34第一章 前言1.1目的随着网优项目的推进，对数据粒度和范围需求造成数据量急剧膨胀。对原数据库构架不合理会造成难以适应生产的需要，对数据库进行格式优化就尤为重要，同样使用话务网管数据库的改造，根据现场情况，因地制宜的进行改造，会大大降低维护成本，提高生产效率。1.2内容针对省级网优系统服务程序的要求，本指导描述了对informix 11.5 以上版本的优化关键点，以及备份和容灾参考，保障生产需要：*数据库版本建议 *dbs空间划分改造的指导建议 *系统配置*onconfig文件参数的修改建议 *表结构及分片策略的建议*索引优化建议和参考*备份和容灾方案建议1.3谁应该读这本书本指导适合在现场有一定维护经验，对informix维护达到中级维护水平。并熟悉操作系统和有基本的数据库开发经验。第二章 nios数据库优化此章介绍informix 11.5优化指导内容：优化前准备n informix版本升级需要由于informix在技术升级和改造中，fc7版本以上相对问题比较少，建议informix升级到11.5fc7以上。移动统一采购了融海服务，建议局方要求数据库版本升级。并且研发都是在11.5之上的版本进行开发，有很多技术在9.40上需要改造，这样会耗费现场很多精力，并且在研发支持下才能进行改造。比较麻烦，并且无法保障结果。n 优化调整空间需求如果对表空间的重新调整，在不影响正常使用的情况下，需要增加现有apmdbs空间容量进行表结构的调整。2.1 基本dbs优化和调整 1plogdbs 10g（数据量小的省6g就可以） llogdbs 20g 每个log空间开销1g左右 tmpdbs 20g*6 dpmdbs 30g*8 （由于底层表数据保留日期比较短，建议一次性开到位，不在增加空间） apmdbs 50*13(如果大省可采用50g*17或80g*17（每次增加按相同比例增加，各现场应考虑现场实际存储的情况定） 对于原来数据分配10g左右一个的dbs，在空间增长过快时需要频繁增加chunk,带来问题是操作风险高，加错了可能造成数据库崩溃，并且在数据库管理上，会增加负荷，降低数据库效率。 2.在现网上进行调整命令 a)plogdbs(各现场可根据各现场实际进行修改，以下命令仅供参考) onspace c d plogdbs_n p /opt/informix/chunks/plogchk_n o 0 s 10240000 onmode uy #将数据库进入quiescent状态下，进行逻辑日志和物理日志的修改 onparams p s 100000 d rootdbs y #将plog移回到rootdbs上 onparams p s 10000000 d plodbs_n y onmode m #切回online状态 回收原空间b)llogdbs onspaces c d llogdbs_n p /opt/informix/chunks_n o 0 s 20480000repeat 20 onparams -a -d llogdbs_n -s 1000000将onconfig 的ontape设置成/dev/null将当前日志移动到新的dbs上ontape s l 0删除原日志回收原空间 c)tmpdbs onspaces c d t tpmdbs_n1 p /opt/informix/chunks/tmpchk_n1 o 0 s 20480000 onspaces c d t tpmdbs_n2 p /opt/informix/chunks/tmpchk_n2 o 0 s 20480000 onspaces c d t tpmdbs_n3 p /opt/informix/chunks/tmpchk_n3 o 0 s 20480000 onspaces c d t tpmdbs_n4 p /opt/informix/chunks/tmpchk_n4 o 0 s 20480000 onspaces c d t tpmdbs_n5 p /opt/informix/chunks/tmpchk_n5 o 0 s 20480000 onspaces c d t tpmdbs_n6 p /opt/informix/chunks/tmpchk_n6 o 0 s 20480000修改oncongfig 文件中dbspacetemp tmpdbs_n1,tmpdbs_n2,tmpdbs_n3,tmpdbs_n4,tmpdbs_n5,tmpdbs_n6重起数据库回收原空间d)dpmdbsselect tabname,nrows from systables where tabname like “tpd%”;可根据nrows的大小对分片控制文件中的表next size进行规划，具体方法在表结构及分片策略优化内容中进行描述。onspace c d dpmdbs_n1 p /opt/informix/chunks/dpmchk_n1 o 0 s 30960000onspace c d dpmdbs_n2 p /opt/informix/chunks/dpmchk_n2 o 0 s 30960000onspace c d dpmdbs_n3 p /opt/informix/chunks/dpmchk_n3 o 0 s 30960000onspace c d dpmdbs_n4 p /opt/informix/chunks/dpmchk_n4 o 0 s 30960000onspace c d dpmdbs_n5 p /opt/informix/chunks/dpmchk_n5 o 0 s 30960000onspace c d dpmdbs_n6 p /opt/informix/chunks/dpmchk_n6 o 0 s 30960000onspace c d dpmdbs_n7 p /opt/informix/chunks/dpmchk_n7 o 0 s 30960000onspace c d dpmdbs_n8 p /opt/informix/chunks/dpmchk_n8 o 0 s 30960000迁移底层表例如tpd_radio_bts为niosdb中的一张表，,修改tpd_radio_bts.xml中的分片空间为新的dbs,并修改表名为tpd_tadio_bts_new, tpd_radio_bts.sql文件中的表名也同时修改为tpd_radio_bts_new.perl make_script.pl -i tpd_radio_bts.sql -c tpd_radio_bts.xml -db informix -o frag_tab.sql -f insert_dict_data.sqldbaccess niosdb frag_tab.sqldbaccess niosdb insert_dict_data.sql运行分片滚动程序delete from tac_frag_manager where table_name=tpd_radio_bts; rename table tpd_radio_bts to tpd_radio_bts_old;rename table tpd_radio_bts_new to tpd_radio_bts ;update tac_frag_manager set table_name=tpd_radio_bts where table_name=tpd_radio_bts_new;insert into tpd_radio_bts where scan_start_time=迁移当天的0点；之前数据可通过dbload或分段方式导回tpd_radio_bts表。删除tpd_radio_bts_old表。所有表都迁移完后，oncheck peoncheck_pe.txt检查dpmdbs18没有任何表了，onspaces d dpmdbs1onspaces d dpmdbs2onspaces d dpmdbs3onspaces d dpmdbs4onspaces d dpmdbs5onspaces d dpmdbs6onspaces d dpmdbs7onspaces d dpmdbs8删除chunks下连接文件dpmchk18删除裸设备回收dpmdbs原空间。e)apmdbs操作方法同dpmdbs相同的处理办法，内容不在重复叙述。2.2 informix onconfig参数配置 与性能有关参数大概如下： multiprocessor1vpclasscpu,num=15,noagevp_memory_cache_kb0single_cpu_vp0cleaners32auto_aiovps1direct_io0locks8000000def_table_lockmoderowshmvirtsize819200shmadd819200btscannernum=5,threshold=50000,rangesize=-1,alice=6,compression=default off_recvry_threads50 bufferpoolsize=2k,buffers=3000000,lrus=256,lru_min_dirty=50,lru_max_dirty=60 /*针对hp和sun机器bufferpoolsize=4k,buffers=3000000,lrus=256,lru_min_dirty=50,lru_max_dirty=60 /* 针对ibm aix机器建立自动事物隔离级别，读取最后提交的事物，避免全表扫描uselastcommitted committed read需要建立procedure sysdbopencreate procedure informix.sysdbopen()set lock mode to wait 30;end procedure ;如果在建数据库dbs时调整了page的大小为8k那么bufferpoolsize=8k,据说选择大点的page,能够提高数据库效率，有条件本人实验后告诉大家效果。vpclasscpu,num=15,noage中的num为cpu核数-1,不是个数以下是陕西网优的参数，供大家参考，不建议对不熟悉的的参数进行修改，informix本身的机制有些是有冲突的，当开启过大fork进程数过多，会造成系统崩溃，不建议在现网频繁调试，如特殊需要开启最好征询ibm或融海。# licensed material - property of ibm# restricted materials of ibm# ibm informix dynamic server# copyright ibm corporation 1996, 2010. all rights reserved.# title: onconfig.std# description: ibm informix dynamic server configuration parameters# important: $informixdir now resolves to the environment# variable informixdir. replace the value of the informixdir # environment variable only if the path you want is not under # $informixdir.# for additional information on the parameters:# /infocenter/idshelp/v115/index.jsp# root dbspace configuration parameters# rootname - the root dbspace name to contain reserved pages and# internal tracking tables.# rootpath - the path for the device containing the root dbspace# rootoffset - the offset, in kb, of the root dbspace into the # device. the offset is required for some raw devices. # rootsize - the size of the root dbspace, in kb. the value of # 200000 allows for a default user space of about # 100 mb and the default system space requirements.# mirror - enable (1) or disable (0) mirroring# mirrorpath - the path for the device containing the mirrored # root dbspace# mirroroffset - the offset, in kb, into the mirrored device # warning: always verify rootpath before performing# disk initialization (oninit -i or -iy) to# avoid disk corruption of another instance#rootname rootdbsrootpath /opt/informix1150/chunks/rootchkrootoffset 0rootsize 2000000mirror 0mirrorpathmirroroffset 0# physical log configuration parameters# physfile - the size, in kb, of the physical log on disk.# if rto_server_restart is enabled, the # suggested formula for the size of phsyfile # (up to about 1 gb) is:# physfile = size of buffers * 1.1# plog_overflow_path - the directory for extra physical log files# if the physical log overflows during recovery# or long transaction rollback# physbuff - the size of the physical log buffer, in kb#physfile 59999000 plog_overflow_path $informixdir/tmpphysbuff 512# logical log configuration parameters# logfiles - the number of logical log files# logsize - the size of each logical log, in kb# dynamic_logs - the type of dynamic log allocation.# acceptable values are:# 2 automatic. ids adds a new logical log to the# root dbspace when necessary.# 1 manual. ids notifies the dba to add new logical# logs when necessary.# 0 disabled# logbuff - the size of the logical log buffer, in kb#logfiles 57 logsize 100000dynamic_logs 2logbuff 512# long transaction configuration parameters# if ids cannot roll back a long transaction, the server hangs# until more disk space is available.# ltxhwm - the percentage of the logical logs that can be# filled before a transaction is determined to be a# long transaction and is rolled back# ltxehwm - the percentage of the logical logs that have been# filled before the server suspends all other# transactions so that the long transaction being # rolled back has exclusive use of the logs# when dynamic logging is on, you can set higher values for# ltxhwm and ltxehwm because the server can add new logical logs# during long transaction rollback. set lower values to limit the # number of new logical logs added.# if dynamic logging is off, set ltxhwm and ltxehwm to# lower values, such as 50 and 60 or lower, to prevent long # transaction rollback from hanging the server due to lack of # logical log space.# when using enterprise replication, set ltxehwm to at least 30%# higher than ltxhwm to minimize log overruns.#ltxhwm 70ltxehwm 80# server message file configuration parameters# msgpath - the path of the ids message log file# console - the path of the ids console message file#msgpath /opt/informix1150/online.logconsole /dev/console# tblspace configuration parameters# tbltblfirst - the first extent size, in kb, for the tblspace# tblspace. must be in multiples of the page size.# tbltblnext - the next extent size, in kb, for the tblspace# tblspace. must be in multiples of the page size.# the default setting for both is 0, which allows ids to manage # extent sizes automatically.# tblspace_stats - enables (1) or disables (0) ids to maintain # tblspace statistics#tbltblfirst 0tbltblnext 0tblspace_stats 1# temporary dbspace and sbspace configuration parameters# dbspacetemp - the list of dbspaces used to store temporary# tables and other objects. specify a colon# separated list of dbspaces that exist when the# server is started. if no dbspaces are specified, # or if all specified dbspaces are not valid, # temporary files are created in the /tmp directory# instead.# sbspacetemp - the list of sbspaces used to store temporary # tables for smart large objects. if no sbspace# is specified, temporary files are created in# a standard sbspace.#dbspacetemp tmpdbs1,tmpdbs2,tmpdbs3,tmpdbs4sbspacetemp# dbspace and sbspace configuration parameters# sbspacename - the default sbspace name where smart large objects# are stored if no sbspace is specified during# smart large object creation. some datablade# modules store smart large objects in this # location.# syssbspacename - the default sbspace for system statistics # collection. otherwise, ids stores statistics # in the sysdistrib system catalog table.# ondbspacedown - specifies how ids behaves when it encounters a# dbspace that is offline. acceptable values # are:# 0 continue# 1 stop# 2 wait for dba action#sbspacenamesyssbspacenameondbspacedown 2# system configuration parameters# servernum - the unique id for the ids instance. acceptable # values are 0 through 255, inclusive.# dbservername - the name of the default database server# dbserveraliases - the list of up to 32 alternative dbservernames, # separated by commas#servernum 2dbservername niosserver2dbserveraliases niosserver2a# network configuration parameters# nettype - the configuration of poll threads# for a specific protocol. the# format is:# nettype ,# ,# ,(net|cpu)# you can include multiple nettype# entries for multiple protocols.# listen_timeout - the number of seconds that ids# waits for a connection# max_incomplete_connections - the maximum number of incomplete# connections before ids logs a denial# of service (dos) error# fastpoll - enables (1) or disables (0) fast # polling of your network, if your # operating system supports it.#nettype tlitcp,2,100,netlisten_timeout 60max_incomplete_connections 1024fastpoll 1# cpu-related configuration parameters# multiprocessor - specifies whether the computer has multiple# cpus. acceptable values are: 0 (single# processor), 1 (multiple processors or# multi-core chips)# vpclass cpu - configures the cpu vps. the format is:#vpclass cpu, num=,#,max=#,aff= | - |#( -/ ) #,noage#for example:#num=4,aff=(1-10/3) means assign 4 cpu vps to processors#1,4,7,10# vp_memory_cache_kb - specifies the amount of private memory # blocks of your cpu vp, in kb, that the # database server can access. # acceptable values are:# 0 (disable)# 800 through 40% of the value of shmtotal# single_cpu_vp - optimizes performance if ids runs with# only one cpu vp. acceptable values are:# 0 multiple cpu vps # any nonzero value (optimize for one cpu vp)#multiprocessor 1vpclass cpu,num=15,noagevp_memory_cache_kb 10240 single_cpu_vp 0# aio and cleaner-related configuration parameters# vpclass aio - configures the aio vps. the format is:# vpclass aio,num=,max=,aff=,no