INFORMIX数据库培训.ppt

INFORMIX数据库培训,2005-04-01,培训主要内容,数据库安装数据库日常维护数据优化问题讨论,1）数据库安装-安装准备,root用户配置工作informix用户配置工作,1.1root用户配置工作,1.1.1设置系统内核参数：在/etc/system文件中加入：set shmsys:shminfo_shmmax=4294967295/此参数根据当时现场的硬件环境决定set shmsys:shminfo_shmmin=1set shmsys:shminfo_shmmni=1024set semsys:seminfo_semmni=100set semsys:seminfo_semmsl=200set semsys:seminfo_semopm=100set semsys:seminfo_semvmx=32767set msgsys:msginfo_msgmnb=4096set msgsys:msginfo_msgmni=50set msgsys:msginfo_msgssz=8set msgsys:msginfo_msgtql=40set msgsys:msginfo_msgseg=1024set shmsys:shminfo_shmseg=1024set semsys:seminfo_semaem=16384set semsys:seminfo_semmap=1026set semsys:seminfo_semmns=16384set semsys:seminfo_semmnu=4096set semsys:seminfo_semume=256set msgsys:msginfo_msgmap=1026set msgsys:msginfo_msgmax=4096参数说明：SHMMAX：最大共享内存段尺寸（字节），一般为物理内存的0.5SHMMIN：最小共享内存段尺寸（字节），一般设置为1SHMMNI：系统范围最大共享内存段数量SHMSEG：每进程最大共享内存段数量SEMMNI：信号灯标识符的最小数量SEMMNS：系统范围的最大信号灯数量SEMMSL：每套信号灯最小信号灯数量SEMMAP：信号灯映射里的记录数量SEMVMX：信号灯的最大值修改完/etc/system文件后，需要重新启动系统。#sync#reboot,1.1root用户配置工作,1.1.2创建用户组：在Solaris系统上，使用admintool创建组和用户，也可以使用命令行的方式创建；以root方式登录1、创建用户组informix groupadd-g gid group 说明：gid 组的ID号(不能与现有的组ID号重复)group 组名,1.1 root用户配置工作,1.1.3增加用户informix（隶属于informix组）useradd-u uid|-g group|-d dir|-s shell|-c comment|-m-k skel_dir login 说明：u 指定用户ID号 g 制定所在组 d 指定用户目录 s 制定用户SHELL c 用户的注释 m 建立用户目录 login 用户的登录名 例如：#useradd-u 1001-g informix-d/opt/informix-s/bin/csh-m Informix,1.1 root用户配置工作,1.1.4设置informix的口令#passwd Informix 1.1.5以informix用户登录;#su Informix注：上面命令执行后在/opt/informix中生成的环境文件是local.cshrc改名csh的环境文件%cd/opt/informix%mv local.cshrc.cshrc,1.2informix用户配置工作,1.2.1创建informix产品安装目录#mkdir/opt/informix#chown R Informix:Informix/opt/informix,1.2informix用户配置工作,1.2.2设置环境变量：设置PATH环境变量：（修改.cshrc文件）set path=（/usr/local/perl5/bin/bin/usr/bin/usr/sbin/usr/ucb/etc/opt/informix/opt/informix/bin/opt/informix/etc.）设置INFORMIX的环境变量：（修改.cshrc文件）setenv INFORMIXSERVER wnmsserver1setenv INFORMIXDIR/opt/informixsetenv ONCONFIG onconfig.npm,2)数据库安装-安装与配置,2.1安装Informix Online(以9.4为例）将ids.9.40.tar解压到/opt/informix目录下%cd/opt/Informix%tar xvf ids.9.40.tar以informix用户登录，并在/opt/informix目录下运行Informix服务器安装脚本：%./installserver安装脚本将完成Informix Dynamic Server的安装，注意在安装过程中需要按照安装程序提示输入产品序列号和对应密码。(以下是ids2000的序列号和密码)Enter your serial number(for example,INF#X999999)AAC#J696667Enter your serial number KEY(uppercase letters only)DLIJJP执行完成按系统提示执行一个SH文件；,2)数据库安装-安装与配置,2.1INFORMIX系统环境变量在root 用户下，把如下内容加入/etc/services中。sqlexec1 8001/tcp#for wnms DB serversqlexec2 8002/tcp#for wnms DB server说明8001端口是给数据库本身进行数据交互使用,交互过程使用的共享内存的方式进行的,速度是最快的;而8002是给网络用户的,通常使用的TCP/IP协议进行数据交互的;,2)数据库安装-安装与配置,2.3数据库的运行环境使用informix用户登录配置/opt/informix/etc/sqlhosts文件，修改DB服务器名称为指定服务器名称，增加wnmsserver onipcshm 主机名（或主机IP）8001 wnmsserver1 ontlitcp 主机名（或主机IP）8002注：wnmsserver1为定义在.cshrc中的INFORMIXSERVER，ontlitcp为tcp/ip方式，hostname为定义在/etc/hosts中的主机名，此服务器名是网络中的所有用户所使用的；而wnmsserver 是为本机通过共享内存访问而设置的。,2)数据库安装-安装与配置,2.4准备数据库服务器硬盘空间针对各省不同的情况，硬盘划分也有所不同，分为两种情况：1、裸设备：对于磁盘阵列：可以做成2G大小的逻辑单元，然后建立链接2、文件系统：创建相应的文件,2)数据库安装-安装与配置,2.5建立ROOTDBS首先创建root dbspace裸设备：（逻辑文件和裸设备建立连接）操作步骤如下：%cd/opt/informix%mkdir chunks%cd/opt/informix/chunks%ln-s/dev/rdsk/c1t11d0s0 rootchk%chmod 660*,2)数据库安装-安装与配置,2.6配置onconfig.npm文件%cd/opt/Informix/etc%cp onconfig.std onconfig.nmos修改onconfig文件中的参数：#Root Dbspace ConfigurationROOTPATH/opt/informix/chunks/rootchunk#Path for device containing root dbspaceROOTSIZE 2000000#Size of root dbspace(Kbytes)#DiagnosticsMSGPATH/opt/informix/online.log#System message log file pathALARMPROGRAM/opt/informix/etc/log_full.sh#Alarm program pathSYSALARMPROGRAM/opt/informix/etc/evidence.sh#System Alarm program path#System Archive Tape DeviceTAPEDEV/dev/null#Tape device path#Log Archive Tape DeviceLTAPEDEV/dev/null#Log tape device path#System ConfigurationSERVERNUM 100#Unique id corresponding to a Dynamic Server instanceDBSERVERNAME nmosserver#Name of default database serverDBSERVERALIASES nmosserver1#List of alternate dbservernamesNETTYPE ipcshm,1,100,CPU NETTYPE tlitcp,1,100,NET MULTIPROCESSOR 1#0 for single-processor,1 for multi-processorNUMCPUVPS 9#Number of user(cpu)vps#Shared Memory ParametersLOCKS 2000000#Maximum number of locksBUFFERS 450000#Maximum number of shared buffers(跟物理日志的空间大小相等)NUMAIOVPS#Number of IO vpsPHYSBUFF 64#Physical log buffer size(Kbytes)LOGBUFF 64#Logical log buffer size(Kbytes)LOGSMAX 30#Maximum number of logical log filesSHMVIRTSIZE 600000#initial virtual shared memory segment sizeSHMADD 50000#Size of new shared memory segments(Kbytes),2)数据库安装-安装与配置,2.7初始化数据库%oninit vi（-i：参数是初始化的含义，只有在第一次运行的时候使用）2.8开创数据库空间%cat/dev/null/opt/informix/chknpm/npmchk%cat/dev/null/opt/informix/chknpm/idxchk修改文件属性%chmod 660*加入chunks%onspaces-c-d npmdbs-p/opt/informix/chknpm/npmchk-o 0-s 2000000%onspaces-c-d idxdbs-p/opt/informix/chknpm/idxchk-o 0-s 2000000onspaces c-t-d tmpdbs1-p/opt/informix/chunks/tmpchk1-o 0-s 2000000 运行onstat d 查看数据库空间是否已经建好。,2)数据库安装-安装与配置,2.9加入logical Log在dbs建立好后，要将rootdbs中的逻辑日志移出%onstat l 查看逻辑日志号%onparams d l-y（删除逻辑日志）%onmode uy（将数据库进入quiescent状态下，进行逻辑日志和物理日志的修改）%repeat 19 onparams-a-d llogdbs-s 1000002.10加入physical log%onparams-a-d llogdbs s 100000%onparams p s 1999000 d plogdbs y%onmode m（将数据库模式转换到online状态）然后修改onconfig文件PHYSDBS plogdbs#Location(dbspace)of physical logPHYSFILE 1999000#Physical log file size(Kbytes),2)数据库安装-安装与配置,2.11重新启动数据库%oninit kuy（停止数据库）%oninit v（重新启动数据库2.12创建数据库用dbaccess进入管理界面，选择database，然后再选择create，输入数据库的名字;选择新的数据库的dbspace（cmdbs）;然后再选择log，将数据库处于Buffered_log模式。建数据库的日志方式：No Logging：不能进行事务处理。Buffered Log：共享缓存满即刷新写入磁盘。Unbuffered Log：当一个交易完成时即刷新写入磁盘。,2)数据库安装-安装与配置,2.13对用户赋予权限dbaccess sysmaster执行grant connect to nrmuser;grant connect to npmuser;grant connect to nmosuser;,数据库系统维护,简要系统维护系统检查参数设置,主要环境变量和配置文件,环境变量：INFORMIXDIR/opt/informixINFORMIXSERVERnmserver2LD_LIBRARY_PATH$INFORMIXDIR/libONCONFIGonconfig服务器表文件$INFORMIXDIR/etc/sqlhostsnmserveronipcshm10.0.0.18001nmserver2ontlitcp10.0.0.18002参数文件 etc/onconfig,数据库启停(1),简 单 系 统 维 护,数据库启停(1),1.onmode-k2.oninit(-v)3.onmode m4.onmode s(u)5.oninit-s6.onmode k(y),Online,Offline,Quiescent,1,2,3,4,5,6,数据库启停(2),简 单 系 统 维 护,数据库启停(2),oninitoninit vlog文件：$INFORMIXDIR/online.log失败常见原因：Shm分配错误数据库设备错误环境变量或配置文件错误处理临时表空间错误,状态检查,简 单 系 统 维 护,状态检查,onstat 一般：空间：d 锁：-k日志：-l用户：-u事务：-xIO:-g iov-g iofSQL:-g sql长事务检查。,物理检查,简 单 系 统 维 护,物理检查,oncheck 数据库：cc索引：cI 数据：-cD,结构脚本,简 单 系 统 维 护,结构脚本,dbschema从数据库中导出数据库对象的脚本，如：dbschema d-t dbschema d-f dbaccess运行脚本：dbaccess dbaccess-!SQL Statement!,数据库的维护-常用命令列表,数据库服务器：oninit数据操纵：dbaccess结构导出：dbschema状态检查：onstat数据库状态改变：onmode数据库参数：onmonitorinformix查错：finderr数据库空间操作：onspaces,数据库维护-数据库状态查看(1),onstat Informix Dynamic Server Version 7.31.UC2A-On-Line-Up 5 days 02:19:30-480320 Kbytesonstat g sqlSessSQLCurrentIsoLockSQLErrISAMErrF.E.VersIdStmttypeDatabaseLvlMode15891SELECTcookdb2DRNot Wait009.2315888SELECTcookdb2DRNot Wait009.23onstat g sql(ses)进一步查看某一进程的详细信息onstat u查看进程占用锁的个数以及此进程对数据的读写量,数据库维护-数据库状态查看(2),onstat k查看锁的信息onstat l查看物理和逻辑日志状态onstat p重点查看：Reads%cached应尽量保证在95%以上，Writes%cached保证在85%以上（不绝对）。onstat g iof查看各chunk文件的读写量和读写率,数据库维护-数据库状态查看(2),onstat dDbspacesaddressnumberflagsnchunksfchunkflagsownername253c61501111Ninformixrootdbs253c71002200121NTinformixtpmdbs1253c7700101105NinformixtmndbsChunksaddresschk/dbsoffsetsizefreebpagesflagspathname253c62101/10500000498659PO-/inf_disk/rootchk253c63e02/20250001505PO-/inf_disk/tmpchk1.chk253c6d8013/10010000007PO-/inf_disk/tmnchk2.chk253c6e6014/10010000002PO-/inf_disk/tmnchk3.chk,数据库物理检查,oncheck-cc数据库目录结构检查：oncheck cc oncheck cI数据库索引结构检查：oncheck cI:oncheck cD数据库数据检查：oncheck cD:oncheck ceextent空间检查：oncheck-ceoncheck p生成检查的报告而不进行修复,操作系统检查,df-k查看各文件系统的使用情况ps-ef查看操作系统正在运行的进程情况/usr/ucb/ps-auwxvmstat查看操作系统的内存使用情况iostat查看操作系统的磁盘使用情况iostat xnP,SA&DBA的维护工作,检查系统用户的环境设置，包括：INFORMIXSERVER，INFORMIXDIR环境变量正确设置$INFORMIXDIR/bin目录放入PATH环境变量中$ODBCHOME/lib、$INFORMIXDIR/lib等系统库路径加入到LD_LIBRARY_PATH环境变量中检查并保证crontab中的配置的内容正常执行定期清除/tmp目录下的内容定期对数据经常插入、删除的表进行索引重建定期运行oncheck对全库进行检查，及时发现问题定期进行数据库全库备份（一周）制定更详细的备份计划,数据库优化,1)查询语句（SELECT）的优化 1.1合理使用索引：where子句中变量顺序应与索引字键顺序相同。1.2将最具有限制性的条件放在前面，大值在前，小值在后。1.3避免采用MATCHES和LIKE通配符匹配查询 1.4避免非开始的子串 例如语句：SELECT FROM customer WHERE zipcode2，3“24”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。1.5避免相关子查询 例如：将下面的语句 select bdh,bf from TabA where item IN(select item form TabB where TabB.num=50)改为：select bdh,bf from TabA,TabB where TabA.item=TabB.item AND TabB.num=50 1.6避免或简化排序 索引中不包括一个或几个待排序的字段；group by或order by子句中字段的次序与索引的次序不一样；排序的字段来自不同的表。1.7消除对大型表行数据的顺序存取SELECT FROM orders WHERE(cust_num=126 AND order_num1001)OR order_num=1008 虽然在cust_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：SELECT FROM orders WHERE cust_num=126 AND order_num1001 UNION SELECT FROM orders WHERE order_num=1008,数据库优化,1.8对于大数据量的求和应避免使用单一的sum命令处理，可采用group by方式与其结合，有时其效率可提高几倍甚至百倍。1.9避免会引起磁盘读写的rowid操作。在where子句中或select语句中，用rowid要产生磁盘读写，是一个物理过程，会影响性能。1.10使用临时表加速查询,数据库优化,2)应用程序的优化利用set explain on语句来分析数据库查找策略。当发现某一部分INFORMIX语句运行特别慢又找不到原因时，可在程序中的查询语句之前加入“set explain on”语句，当程序运行时，在程序运行的当前目录下产生一个“sqexplain.out”文件，记录了INFORMIX数据库服务器采用何种优化策略来查找数据库。在该文件中可以发现查找中有无使用索引条件，估计的查找代价等信息。（2）、数据库在做dbimport后应运行update statistics语句。当数据库系统用dbimport实用程序完成数据库装载后，应运行update statistics数据库语句保证系统表中统计信息准确，否则将影响数据库优化器的策略和系统运行性能。例如：select from test where bdh matches“PC2002”test表在bdh字段上建立了索引，但在数据库运行update statistics前，数据库优化查找策略是按顺序查找而不是按索引查找，严重影响了查找速度。（3）、经常插入和删除的大表应定期运行update statistics high语句。对经常插入和删除的大表应定期运行update statistics语句保证系统表中统计信息准确，保证数据库优化器作出正确的优化策略提高系统运行性能。（4）、对大文件采用load命令装载入库前可先去掉原有的日志方式和去掉索引，等load装载完成后再重建索引和日志，能大幅提高装载效率，避免出错产生。去掉原有的日志方式：ontape S N dbname 删除索引：delete index indexname 用load 装入数据：load from filename insert into tabname 重建索引：create index to indexname on tabname(colA,colB,)重建日志：ontape C B dbname,数据库优化,3)索引策略的优化与原则 3.1避免高度重复率字段建立索引 3.2对同一表不要建立过多的索引 3.3建索引的字段的大小尽量小，复合索引尽量少用。3.4Where条件子句经常用到的字段上加索引。3.5在频繁进行排序或分组（即进行group by或order by操作）的字段上建立索引。3.6限制在频繁update的表上建索引的数目3.7对大批量update操作（如load数据）,首先drop index,再update；然后再建索引,能提高性能。3.8数据量小的表是否建索引影响不大，一般不要对记录数小于500的小表建立索引，因为从使用索引得到的速度不能抵消在表上打开和检索索引文件所需的时间。3.9建primary key,数据库优化,4)采用表分片（fragmentation）技术 4.1对于联机事务处理系统（OLTP），应分片索引，减少会话竞争。4.2对于决策支持系统（DSS），应增加分片表，但不分片索引，分离索引，并把它们放在分离的dbspace中。4.3DSS查询顺序读表，使用轮转法分片（Round-Robin）模式。4.4如果大多请求只存取表中的部分数据，建立基于表达式分片（Expression-Based）。4.5保持分片表达式的简单性。4.6调整分片表达式，减少测试。如：order_num=1 AND order_num=1 IN dbspace，效率会更高。4.7避免需要数据类型转换的表达式。4.8不对改变频繁的字段进行分片。4.9不要分片所有表，识别出存取最频繁的关键表。4.10不要分片小表。,数据库优化,5)磁盘空间的整理(指临时表的删除,表空间的回收等)6)数据库一致性检查a.以informix登录 b.将数据库状态置为off_line onmodeky 用onstat检查数据库状态为off_line c.将数据库状态置为单用户模式 onmodes 用onstat检查数据库状态为quiesent d.检查数据库保留页状态 oncheckcr1/tmp/oncheck.cr21 e.检查数据库目录页一致性 oncheckcc1/tmp/oncheck.cc21 f.检查数据库数据的一致性 oncheckcDworkdb1/tmp/oncheck.cd21,数据库优化,g.检查数据库索引的一致性 oncheckcIworkdb1/tmp/oncheck.ci21 h.检查/tmp下oncheck.cr,oncheck.cc,oncheck.cd,oncheck.ci文件，查看有无错误信息，如没有，则数据库状态正常，反之亦然。i.将数据库状态置为online onmodem 用onstat检查数据库状态为online,结束,谢谢大家,2023年2月14日,