NIOS數(shù)據(jù)庫(informix)優(yōu)化和備份策略方案指導(dǎo)

1、 nios db(informix)數(shù)據(jù)庫優(yōu)化和備份策略方案指導(dǎo)億陽信通服務(wù)支持中心2011年05月版權(quán)所有 本產(chǎn)品或文檔受版權(quán)保護，其使用、復(fù)制、發(fā)行和反編譯均受許可證限制。未經(jīng)億陽及其授權(quán)者事先的書面許可，不得以任何形式、任何手段復(fù)制本產(chǎn)品及其文檔的任何部分。目錄目錄3第一章 前言41.1目的41.2內(nèi)容41.3誰應(yīng)該讀這本書4第二章 nios數(shù)據(jù)庫優(yōu)化52.1 基本dbs優(yōu)化和調(diào)整52.2 informix onconfig參數(shù)配置72.3 表結(jié)構(gòu)及分片策略優(yōu)化292.4 應(yīng)用索引的優(yōu)化30第三章 niosdb備份和容災(zāi)策略323.1.備份策略和容災(zāi)323.2 .高可用性323.3. 數(shù)

2、據(jù)壓縮32第四章 常用提高性能的注意事項和出錯查詢334.1 合理利用索引334.2 sql技巧334.3 view, pdq, stored procedure, and trigger344.4表被索處理344.5查詢主鍵重復(fù)34第一章 前言1.1目的隨著網(wǎng)優(yōu)項目的推進，對數(shù)據(jù)粒度和范圍需求造成數(shù)據(jù)量急劇膨脹。對原數(shù)據(jù)庫構(gòu)架不合理會造成難以適應(yīng)生產(chǎn)的需要，對數(shù)據(jù)庫進行格式優(yōu)化就尤為重要，同樣使用話務(wù)網(wǎng)管數(shù)據(jù)庫的改造，根據(jù)現(xiàn)場情況，因地制宜的進行改造，會大大降低維護成本，提高生產(chǎn)效率。1.2內(nèi)容針對省級網(wǎng)優(yōu)系統(tǒng)服務(wù)程序的要求，本指導(dǎo)描述了對informix 11.5 以上版本的優(yōu)化關(guān)鍵點，以

3、及備份和容災(zāi)參考，保障生產(chǎn)需要：*數(shù)據(jù)庫版本建議 *dbs空間劃分改造的指導(dǎo)建議 *系統(tǒng)配置*onconfig文件參數(shù)的修改建議 *表結(jié)構(gòu)及分片策略的建議*索引優(yōu)化建議和參考*備份和容災(zāi)方案建議1.3誰應(yīng)該讀這本書本指導(dǎo)適合在現(xiàn)場有一定維護經(jīng)驗，對informix維護達到中級維護水平。并熟悉操作系統(tǒng)和有基本的數(shù)據(jù)庫開發(fā)經(jīng)驗。第二章 nios數(shù)據(jù)庫優(yōu)化此章介紹informix 11.5優(yōu)化指導(dǎo)內(nèi)容：優(yōu)化前準備n informix版本升級需要由于informix在技術(shù)升級和改造中，fc7版本以上相對問題比較少，建議informix升級到11.5fc7以上。移動統(tǒng)一采購了融海服務(wù)，建議局方要求數(shù)據(jù)庫

4、版本升級。并且研發(fā)都是在11.5之上的版本進行開發(fā)，有很多技術(shù)在9.40上需要改造，這樣會耗費現(xiàn)場很多精力，并且在研發(fā)支持下才能進行改造。比較麻煩，并且無法保障結(jié)果。n 優(yōu)化調(diào)整空間需求如果對表空間的重新調(diào)整，在不影響正常使用的情況下，需要增加現(xiàn)有apmdbs空間容量進行表結(jié)構(gòu)的調(diào)整。2.1 基本dbs優(yōu)化和調(diào)整 1plogdbs 10g（數(shù)據(jù)量小的省6g就可以） llogdbs 20g 每個log空間開銷1g左右 tmpdbs 20g*6 dpmdbs 30g*8 （由于底層表數(shù)據(jù)保留日期比較短，建議一次性開到位，不在增加空間） apmdbs 50*13(如果大省可采用50g*17或80g*

5、17（每次增加按相同比例增加，各現(xiàn)場應(yīng)考慮現(xiàn)場實際存儲的情況定） 對于原來數(shù)據(jù)分配10g左右一個的dbs，在空間增長過快時需要頻繁增加chunk,帶來問題是操作風(fēng)險高，加錯了可能造成數(shù)據(jù)庫崩潰，并且在數(shù)據(jù)庫管理上，會增加負荷，降低數(shù)據(jù)庫效率。 2.在現(xiàn)網(wǎng)上進行調(diào)整命令 a)plogdbs(各現(xiàn)場可根據(jù)各現(xiàn)場實際進行修改，以下命令僅供參考) onspace c d plogdbs_n p /opt/informix/chunks/plogchk_n o 0 s 10240000 onmode uy #將數(shù)據(jù)庫進入quiescent狀態(tài)下，進行邏輯日志和物理日志的修改 onparams p s 1

6、00000 d rootdbs y #將plog移回到rootdbs上 onparams p s 10000000 d plodbs_n y onmode m #切回online狀態(tài) 回收原空間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設(shè)置成/dev/null將當前日志移動到新的dbs上ontape s l 0刪除原日志回收原空間 c)tmpdbs onspaces c

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

8、bs_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重起數(shù)據(jù)庫回收原空間d)dpmdbsselect tabname,nrows from systables where tabname like “tpd%”;可根據(jù)nrows

9、的大小對分片控制文件中的表next size進行規(guī)劃，具體方法在表結(jié)構(gòu)及分片策略優(yōu)化內(nèi)容中進行描述。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/chunk

10、s/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 309600

11、00遷移底層表例如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 in

12、sert_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 wh

13、ere scan_start_time=遷移當天的0點；之前數(shù)據(jù)可通過dbload或分段方式導(dǎo)回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刪

14、除裸設(shè)備回收dpmdbs原空間。e)apmdbs操作方法同dpmdbs相同的處理辦法，內(nèi)容不在重復(fù)敘述。2.2 informix onconfig參數(shù)配置 與性能有關(guān)參數(shù)大概如下： multiprocessor1vpclasscpu,num=15,noagevp_memory_cache_kb0single_cpu_vp0cleaners32auto_aiovps1direct_io0locks8000000def_table_lockmoderowshmvirtsize819200shmadd819200btscannernum=5,threshold=50000,rangesize=-1,

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

16、 sysdbopencreate procedure informix.sysdbopen()set lock mode to wait 30;end procedure ;如果在建數(shù)據(jù)庫dbs時調(diào)整了page的大小為8k那么bufferpoolsize=8k,據(jù)說選擇大點的page,能夠提高數(shù)據(jù)庫效率，有條件本人實驗后告訴大家效果。vpclasscpu,num=15,noage中的num為cpu核數(shù)-1,不是個數(shù)以下是陜西網(wǎng)優(yōu)的參數(shù)，供大家參考，不建議對不熟悉的的參數(shù)進行修改，informix本身的機制有些是有沖突的，當開啟過大fork進程數(shù)過多，會造成系統(tǒng)崩潰，不建議在現(xiàn)網(wǎng)頻繁調(diào)試，如特殊

17、需要開啟最好征詢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

18、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:# # root dbspace configuration parameters# rootname - the root dbspace name to contain reserved page

19、s 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 d

20、efault 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 befo

21、re 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 physica

22、l 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#

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

24、.# 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

25、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 perc

26、entage 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 lo

27、gs# 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.#

28、 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/informix115

29、0/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 defau

30、lt 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

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

32、 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 smar

33、t 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 cata

34、log 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. acceptab

35、le # 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

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

37、# 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# mul

38、tiprocessor - 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

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

40、ns 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=,noage # cleaners - the number of page cleaner thread