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

1、 亿阳信通服务支持中心 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第三章 NI

2、OSDB备份和容灾策略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内容针对省级网优

3、系统服务程序的要求，本指导描述了对INFORMIX 11.5 以上版本的优化关键点，以及备份和容灾参考，保障生产需要：*数据库版本建议 *DBS空间划分改造的指导建议 *系统配置*onconfig文件参数的修改建议 *表结构及分片策略的建议*索引优化建议和参考*备份和容灾方案建议1.3谁应该读这本书本指导适合在现场有一定维护经验，对INFORMIX维护达到中级维护水平。并熟悉操作系统和有基本的数据库开发经验。第二章 NIOS数据库优化此章介绍Informix 11.5优化指导内容：优化前准备n INFORMIX版本升级需要由于INFORMIX在技术升级和改造中，FC7版本以上相对问题比较少，建

4、议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 （由于底层表数据保留日期比较短，建议一次性

5、开到位，不在增加空间） 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 #将数据库进

6、入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上

7、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/t

8、mpchk_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 s

9、ystables 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 30960000

10、onspace 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

11、/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.sqldba

12、ccess 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

13、_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 dpmd

14、bs7onspaces d dpmdbs8删除chunks下连接文件dpmchk18删除裸设备回收dpmdbs原空间。e)APMDBS操作方法同DPMDBS相同的处理办法，内容不在重复叙述。2.2 Informix onconfig参数配置 与性能有关参数大概如下： MULTIPROCESSOR1VPCLASScpu,num=15,noageVP_MEMORY_CACHE_KB0SINGLE_CPU_VP0CLEANERS32AUTO_AIOVPS1DIRECT_IO0LOCKS8000000DEF_TABLE_LOCKMODErowSHMVIRTSIZE819200SHMADD819200B

15、TSCANNERnum=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机器建立自动事物隔离级别，读取最后提交的事物，避免全表扫描U

16、SELASTCOMMITTED 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本身的机制有些

17、是有冲突的，当开启过大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 Parameter

18、s# 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/in

19、dex.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

20、 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 d

21、bspace# 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 0MI

22、RRORPATHMIRROROFFSET 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 f

23、or 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 lo

24、g 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# LOGBU

25、FF - 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# f

26、illed 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 l

27、ogging 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 l

28、ower, 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

29、 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 - T

30、he 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 0TBL

31、SPACE_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 dbspace

32、s 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,tmpdbs4SBS

33、PACETEMP# 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 sbs

34、pace 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#SBSPACENAMESYSSBSPACENAMEO

35、NDBSPACEDOWN 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#SERVERNU

36、M 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 o

37、f 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

38、,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.

39、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

40、:# 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