数据库物理设计经验谈

　　概述 我们无论使用哪种数据库，无论怎样设计数据库，我想都会遵从一个原则：数据安全性和性能高效这两个主要方面，但是关于这两个方面的话题太多，在这里就不一一陈述，我只是从数据库物理分布设计方面和大家一起简单的探讨一下。因为数据库良好的物理分布设计也是对数据安全性和性能高效影响比较大, 就象我们在建大楼之前一定要先打好地基一样。 現实中我们在应用各种不同数据库的时候，往往会忽略数据库的物理布局，只有在数据库性能遇到问题的时候才去考虑，但这是得不偿失的，这样一来不仅会导致与设计相关的问题出现，而且会影响性能的调整效果，所以我们在创建数据库之前先进行规划数据库的物理布局也是很必要的，这也符合人们常说的”磨刀不误砍柴工”的道理。下面我就以Oracle为例从优化操作系统、磁盘布局优化和配置、数据库初始化参数的选择、设置和管理内存、设置和管理CPU、设置和管理表空间、设置和管理回滚段、设置和管理联机重做日志、设置和管理归档重做日志、设置和管理控制文件等几个方面作以陈述。 一. 优化操作系统 为了获得最佳的服务器性能, 对操作系统的优化也是很必要的, 因为操作系统性能问题通常会涉及到进程管理、内存管理、调度等，所以用户需要确保有足够的I/O带宽、CPU的处理能力、交换空间来尽可能的降低系统时间。如果应用程序在缓冲区出现过多的”忙”等待, 那么系统调用的进程将会增加, 虽然可以通过优化SQL语句等方法来降低调用的数目，但是这也是治病不治根的。用户可以启动Oracle的初始化参数timed_statistics来增加系统调用的数目, 反之如果关闭此参数,那么系统调用的数目也会减少。操作系统的缓存和Oracle自己的缓存管理是不相冲突的, 虽然它能消耗一定的资源, 但是它对性能还是有一定好处的, 因为一般所有数据库的I/O需要通过系统文件缓存来访问文件存储器。 Oracle的操作可能会用到许多的进程(有的系统叫线程), 所以用户应该确保所有Oracle的进程、后台进程、用户进程具有相同的优先级, 否则就会产生恶化的现象, 导致高优先级的进程等待低优先级的进程处理完毕释放出CPU资源后再处理, 更不能将Oracle的后台进程绑定到CPU中, 这样一来也会导致被绑定的进程被CPU资源饿死。 比较好的是有些操作系统提供有操作系统资源管理器(Operating System Resource Manager), 通过它可以对系统资源访问划分优先级来降低峰值负载模式的影响，来实现多种管理策略和方法，控制用户资源的访问，限制用户资源的可消耗量。 二．磁盘布局优化和配置 在大多数产品数据库应用中，数据库文件一般都放在磁盘上，因此磁盘的良好使用和布局也是很重要的。磁盘布局的目标是：磁盘性能是不能阻碍实现数据库性能，数据库磁盘必须专用于数据库文件，否则非数据库将会影响到该数据库,且这种影响是不可预测的; 系统硬件和镜像必须满足恢复和性能的要求，数据文件大小和I/O不能超过磁盘的大小和I/O，数据库一定是可以恢复的，必须使后台进程之间的竞争最小化。在规划硬盘配置时也要注意：首先所用的磁盘容量，有时用多个容量小的磁盘比用一个大的磁盘效果更好，因为可以进行更高级的并行I/O操作; 其次磁盘的速度，如反应时间和寻道时间都将影响I/O的性能, 可以考虑使用合适的文件系统作为数据文件; 再者使用合适的RAID。 RAID(Redundant Arrays of Inexpensive Disks)廉价冗余阵列可以改善数据的可靠性，而I/O的性能又取决于RAID配置的方式：RAID1可以提供比较好的可靠性和较快的读取速度，但写的代价比较大，所以不适合频繁写的应用；RAID0 1在原RAID1的基础上读取的速度更快，所以这也是大家常会选择的方式；RAID5可以提供比较好的可靠性，有顺序的读操作比较适合这种方式，但性能会受到影响，对于写操作频繁的应用也不适合这种。对于该选择那种方式不能一概而论，要根据具体的情况而定。 有些应用软件先天性受到磁盘的I/O限制, 所以在设计的时候应尽量使Oracle的性能不受I/O的限制, 所以在设计一个I/O系统时要考虑以下的数据库需要: 存储磁盘的最小字节; 可用性, 如24X7, 9X5; 性能如I/O的输出和响应时间。决定Oracle文件的I/O统计信息可以来查询下列: 物理读数量(V$filestat.phyrds)、物理写数量(V$filestat.phywrites)、平均时间, I/O=物理读 物理写。而I/O的平均数量=(物理读 物理写)/共用秒数), 估计这个数据对于新系统是有用的, 可以查询出新应用程序的I/O需求与系统的I/O能力是否匹配以便及时调整。 三．创建数据库初始化参数的选择 管理数据库的第一阶段就是初始化数据库的创建，尽管可以在数据库创建好以后再来调整性能，但是有些参数是不能修改的或很难修改，比如： Db_block_size、Db_name、Db_domain、Compatible、Nls_language、 Nls_characterset、Nls_nchar_characterset。 Db_block_size参数决定Oracle数据库块的大小，一般可以选择的范围是2K、4K、8K、16K、32K，使用下一个较大值数据库块大小的效果一般可以集中查询中性能提高50%。但是按常规来说对于一般服务器不提倡把这个值设的很大，小型机除外，因为这样一来数据库块中将会有更多的行，在数据库维护期间发生块级竞争的可能性比较大，避免这种竞争的办法是在表级和索引级增大Freelists、maxtrans和initrans 的设置值，通常Freelists设置为大于4会带来更多的好处。 Db_name该参数指定一个数据库标识符，一般在Create Database中指定的名称，改参数是可选的（在Oracle9i实时应用集群时是必选的，多个实例有相同的参数值），但是建议在Create Database之前设置它，如果不指定则要出现在Startup或Alter Database mount命令中。 Db_domain该参数指定全局数据库名的扩展部分，在Oracle9i实时应用集群时是必选的，多个实例有相同的参数值。 Compatible该参数指定Oracle服务器维护版本的兼容性，保证与早期的版本向下兼容的时候允许用户使用新的版本，在Oracle9i实时应用集群时是必选的，多个实例有相同的参数值。 Nls_language和Nls_characterset及Nls_nchar_characterset三个参数是数据库的字符集参数，在数据库创建完成后一般也不能改变或很难改变，所以在创建数据库的时候要先设置好。 四．设置和管理内存 Oracle使用共享内存来管理其内存和文件结构，Oracle常使用的内存结构如下： 系统全局区（System Global Area，SGA），SGA随着不同的环境而不同，没有一种普通的最佳方案，我们在设置它直前要先考虑以下的几个方面：物理内存多大；操作系统是那种及占多大的内存，数据库系统是文件系统还是裸设备；数据库运行的模式。SGA包括：Fixed size、Variable size、Database Buffers、Redo Buffers。SGA占有物理内存的比例没有严格的规定，只能遵从一般的规则：SGA占据物理内存的40%--60%左右。如果通过直观的公式化来表达则为：OS使用内存 SGA 并发进程数*（Sort_area_size Hash_area_size 2M）<0.7RAM,这个公式也只是参考，不必拘于此，实际情况可以自由发挥。初始化参数文件中的一些参数对SGA的大小有决定性的影响。参数Db_block_Buffers（SGA中存储区高速缓存的缓冲区数目），参数Shared_pool_size（分配给共享SQL区的字节数），是SGA大小的主要影响者。Database Buffers 参数是SGA大小和数据库性能的最重要的决定因素。该值较高，可以提高系统的命中率，减少I/O。每个缓冲区的大小等于参数Db_block_size的大小。Oracle数据库块以字节表示大小。Oracle SGA区共享池部分由库高速缓存、字典高速缓存及其他一些用户和服务器会话信息组成，共享池是最大的消耗成分。调整SGA区各个结构的大小，可以极大地提高系统的性能。 数据块缓冲缓存区（Data block buffers cache），Data buffers在8i中是Db_block_buffers*Db_block_size，9i中用Db_cache_size来代替这个参数。在内存的配置中把别的参数设置完成后，应该把能给的都给Data buffers。Oracle 在运行期间向数据库高速缓存读写数据，高速缓存命中表示信息已在内存中，高速缓存失败意味着Oracle必需进行磁盘I/O。保持高速缓存失败率最小的关键是确保高速缓存的大小。Oracle8i中初始化参数Db_block_buffers控制数据库缓冲区高速缓存的大小。可通过查询V$sysstat 命中率，以确定是否应当增加Db_block_buffers的值。 　　SELECT name,value FROM V$sysstat 　　WHERE name in (’dbblock gets’,’consistent gets’,’physical reads’); 通过查询结果命中率=1-physical reads/(dbblock gets consistent gets) 如果命中率<0.6～0.7，则应增大Db_block_buffers。 字典缓存区（Dictionary CACHE），数据字典缓存区的大小由数据库内部管理，大小由参数Shared_pool_size来设置。数据字典高速缓存包括了有关数据库的结构、用户、实体信息等。数据字典的命中率对系统有很大的影响。命中率的计算中，getmisses 表示失败次数，gets表示成功次数。查询V$ROWCACHE表： 　　SELECT (1-(SUM(getmisses)/(SUM(gets) SUM(getmisses))))*100 　　FROM V$rowcache; 如果该值>90%，说明命中率合适。否则，应增大共享池的大小。 重做日志缓冲区（Read log buffer）,下面将有陈述，在此就不做说明。 SQL共享池（Shared pool size），该共享池包括包括执行计划及针对数据库执行SQL语句的语法分析用的，在第二次运行相同的SQL语句时可用SQL中的语法分析来加快执行速度。如果它太小，语句会连续不断地再装入到库缓存区，从而影响性能。可以通过Alter system命令来修改此参数，9I以后的版本可以动态地修改其大小。 大池（Large pool size），是一个可选的内存区。如果选择可对数据库象备份/恢复这些大的操作提高性能。如果不选择此参数，则系统会使用共享池。 JAVA池（Java pool size），由其名字而言可知，是为满足JAVA命令语法分析的需求。在UNIX系统中如果区组的大小为4MB，则默认大小应该为24M，如果区组大小为 16MB，则默认大小为32M。如果数据库没有使用JAVA，则保持在10M—20M足够。 多缓冲区池（Multiple buffer pools），可以使用多缓冲区池把大数据集与应用的剩余部分分开，以减少它们争夺缓存区内相同资源的可能性，创建时需要在初始化参数中设定其大小。 程序全局区（Program global area，PGA）是Oracle的一个私有的内存区，9i以后的版本中，如果Workarea_size_policy=auto，则所有的会话共用一块内存，该内存在参数Pga_aggregate_target设置，它的一个好的初始设置是：对于一个OLTP系统 Pga_aggregate_target=（totalL_mem*80%）*20%；对于一个DSS系统Pga_aggregate_target= （total_mem*80%）*50%。这里的total_mem是物理内存。在调整Pga_aggregate_target参数时，下面的几个动态视图会有帮助的：V$sysstat和V$sesstat；V$sql_workarea_active；V$pgastat；V$ sql_workarea； V$process。 五．设置和管理CPU 在设置和安装数据库的过程中，基本不用对CPU做什么配置的，系统会自动默认的，但是在管理过程中我们可以利用操作系统监控工具来监控CPU的状况。例如在UNIX系统中，可以运行sar–u的工具来检查整个系统使用CPU的水平。其统计信息包括：用户时间、系统时间、空闲时间、I/O等待时间。在正常工作负载的情况下，如果空闲时间和I/O等待时间接近于0或少于5%，那就表示CPU的使用存在问题。 对于Windows系统可以通过性能监视器（Performance monitor）来检查CPU的使用状况可以提供以下信息：处理器时间、用户时间、特权时间、中断时间、DPC时间。 如果CPU的使用存在问题，则可以通过以下的方式来解决：优化系统和数据库；增加硬件的能力；对CPU资源分配进行划分优先级，Oracle数据库资源管理器（Database Resource Manager）负责在用户和应用程序之间分配和管理CPU资源。 六．设置和管理表空间 数据库文件之间的I/O竞争是数据库之大忌, 所以对数据库规划之前要先对数据文件的I/O进行初步的评估, 通常情况下, 应用的产品数据库表所在的表空间会很活跃, 索引表空间和数据字典之类的表空间也很活跃的, 对于事物比较频繁的应用中, 重做表空间也很活跃的, 所以对不同类型的数据库其数据文件的I/O竞争也会略有不同的, 但是基本上还是遵从以下的原则比较好: 应用的表和索引通常应该被分配或分区到多个表空间中, 以降低单个数据文件的I/O, 最好把每一种功能相同的区域对象建立单独的表空间; 没有理由把除数据字典表和系统回退段外的其他东西放到系统表空间中, 要把能移出系统表空间的对象都移出; 索引段不应该和相关表放在同一表空间中, 因为他们在数据管理和查询时会产生很多的并发I/O; 临时表空间是用以存储大量的排序, 所以其它的应用对象是不能放在临时表空间。 以上讲的是数据库文件分布的原理, 原理归原理, 事实应用中, 我们还是以经验来设置分布比较好些, 当然在没有经验之前还是参考原理以致于不会走弯路。 数据库和表空间可以是一对多的关系，表空间和数据文件也可以是一对多的关系，数据文件和数据对象也可以是一对多的关系。当创建一个数据对象（如表或索引）时，可以通过默认值或特殊命令将其赋予一个表空间，这样就会在该表空间中创建一个段（Segment）来存储与该对象有关的数据。一个段由一些称作区间（Extent，一组连续的Oracle块）的区段组成，一但现有的区段不能存储数据时，这个段就要获得另一个区间来支持将数据插入到对象中。因此这个段所使用的空间由它的参数决定的，这些参数可以在创建时指定，也可以在以后更改。如在Create table，Create index，Create cluster，Create rollback segment命令中没有指定存储参数，则数据库会默认它存储所在的表空间的参数，这些参数有initial，next，pctincrease， maxextents，minextents等。在创建后不能修改initial和minextents值，每个表空间的存储参数默认值可以在 Dba_tablespaces视图中查询出来。 磁盘I/O是系统性能的瓶颈，解决好磁盘I/O，可明显提高性能。通过查询V$filestat可以知道每个物理文件的使用频率（phyrds表示每个数据文件读的次数，phywrts表示每个数据文件写的次数） 　　SELECT name,phyrds,phywrts FROM V$datafile df,V$filestat fs 　　WHERE df.file# =fs.file#; 对于使用频率较高的物理文件,可以采用以下策略: 将I/O尽可能平均分配在尽可能多的磁盘上；为表和索引建立不同的表空间；将数据文件与重做日志文件分离在不同的磁盘上；减少不经Oracle server的磁盘I/O。 如果大家没有经验的情况下不小心把数据文件规划的不恰当, 以致于产生了大量的I/O竟争现象, 那么就要根据上面的原则重新调整数据文件的分布, 以平衡数据文件之间的I/O竟争, 具体如何移动数据文件, 各种数据库的方法不尽相同, 但是基本的原理还是相同的,下面是针对Oracle8i如何移动数据文件的两种方法的举例(9i略有不同)： 第一种方法﹕(Alter database) 关闭数据库-移动数据库文件-装载并改名-启动 1> svrmgrl 2> connect internal 3> shutdown 4> exit 5> mv /u/product/oradata/foxmold/user01.dbf /db3/oradata 6> svrmgrl 7> connect internal 8> startup mount foxmold 9> alter database rename file ‘/u/product/oradata/foxmold/user01.dbf’ to ‘/db3/oradata/user01.dbf’ 10> alter database open 第二种方法﹕(Alter tablespace) 关闭数据库-移动数据库文件-装载并改名-启动 1> svrmgrl 2> connect internal 3> shutdown 4> exit 5> mv /u/product/oradata/foxmold/user01.dbf /db3/oradata 6> svrmgrl 7> connect internal 10> alter database rename file ‘/u/product/oradata/foxmold/user01.dbf’ to ‘/db3/oradata/user01.dbf’ 8> alter database open 上面的foxmold代表当前的database name。 七．设置和管理回滚段 回滚段一般可以处理任意大小的事物，所以也就需要大小不同的回滚段。回滚段的大小是通过创建回滚段时指定存储子句来设置，但一般会遵从下面原则： OLTP事物特征有许多并发的事物，每个可能只修改少量的数据，可以建立10KB到20KB大小的回滚段，每个有2到4的范围；对于时间很长的查询为了维护读一致性有大量的回滚信息，所以需要较大的回滚段，建立回滚段的大小最好为最大表的10%左右（大多数查询只影响到表约10%的数据量）。设置回滚段的大小可以通过Create rollback segment和Alter rollback segment语句来实现。一般情况下，initial=next ，设置Optimal参数来节约空间的使用 ，不要设置maxextents为unlimited，回滚段应创建在一个特定的回滚段表空间内 。 回滚段的目标容量可以用存储参数Optimal来定义，它指定回滚段要缩小到的尺寸。如果发现回滚段由于Optimal的缘故持续地收缩，那么很可能是回滚段设置不恰当，这种可以通过动态性视图V$rollstat来确定是否有问题，如： SELECT substr(name,1,40) name,extents,resize,aveactive,aveshrink, extends,shrink FROM V$rollname rn,V$rollstat rs WHERE rn.usn=rs.usn; 结果如下： name extents resize aveactive aveshrint extents shrinks ----- ------- ------ ------- -------- -------- -------- system 4 202876 0 0 0 0 csirsl 2 202876 55192 0 0 0 如果平均大小接近于Optimal，则Optimal正确，如果extents和shrinks高，则必须增加Optimal值。在设计Optimal值时如果有长时间查询和运行的事物，则应该把Optimal设置大些。 使用回滚段可以改善系统性能，减少竞争，回滚段的多少应该由数据库中的并发事物决定，太多的事物使用一个回滚段时会发生竞争，查看动态性能表V$WAITSTAT可以查看回滚段上是否有竞争： SELECT class,count FROM V$waitstat WHERE class in(‘undo header’,’undo block’,’system undo header’,’system undo block’); 结果如下： CLASS COUNT ------------------------------- --------------- system undo header 0 system undo block 0 undo header 0 undo block 0 然后将这些值和数据请求的总数相比较。数据请求的总数等于V$sysstat中的db buffer gets和consistent gets之和，查询语句为： SELECT sum(value) ‘Data Requests’ FROM V$systat where name in(‘db block gets’,’consistent gets’); 结果如下： Data Requests --------------------------- 5105 如果任何一个class/sum(value)>10%,那么考虑增加回退段。回退段的数量一般按如下规律设定： 　　用户数　　　　　　　　　 回退段个数 n<16　　　　　 　　　　　　4 16<n<32　　　　　　　　　　8 32<=n　　　　　　　　　　　n/4 但不超过50 　　和回滚段有关的参数还有: transactions指出并发事物的数量;transcative_per_rollback_segment指出每个回滚段的事物数量。 使用set transaction语句将事物分配到适当大小的回滚段命令如下： set transcation use rollback segment RBS13； 把当前的事物分配到回滚段RBS13。 八．设置和管理联机重做日志 重做日志的大小也能影响性能，因为数据库的写入和归档取决于重做日志的大小，通常情况下，更大的重做日志文件可以提供好一些的性能，小的能增加检查点的活动和降低频率。 不可能为一个重做日志文件提供特定大小的建议，重做日志文件在几百兆字节到几GB字节都被认为是合理的，根据系统产生的联机重做数量决定日志文件的大小，一般情况下应保持在约20分钟交换日志文件一次。 如果发生重做日志缓冲区竞争，对数据库的性能影响也将是很大的。为了减少重做日志缓冲区竞争，我们可以通过查询V$sysstat表判定redo log 文件缓冲区是否足够。 　　SELECT name,value FROM V$sysstat WHERE name=’redo log space request‘; 　　此处value的值应接近于0，否则，应增大初始化参数文件的Log_buffers的值 重做日志文件被创建后大小不能被改变，但是可以增加新的、更大的文件，并且原来的文件能被删除。具体的实现方法如下： 1．假设现有三个日志组，每个组内有一个成员，每个成员的大小为1MB，现在想把此三个日志组的成员大小都改为20MB。 2．创建新的日志群组 alter database add logfile group4(‘d:\oradb\redo04.log’) size 2048k; alter database add logfile group5(‘d:\oradb\redo05.log’) size 2048k; 3. 切换当前日志到新的日志组 alter system switch logfile； alter system switch logfile； 4．删除旧的日志 alter database drop logfile group 1； alter database drop logfile group 2； alter database drop logfile group 3； 5．到操作系统下删除原旧的日志文件组1，2，3中的文件。 6．重建日志组1，2，3 alter database add logfile group 1 ('d:\oradb\redo01_1.log') size 20M; alter database add logfile group 2 ('d:\oradb\redo02_1.log') size 20M; alter database add logfile group 3 ('d:\oradb\redo03_1.log') size 20M; 7．切换日志组 alter system switch logfile； alter system switch logfile； alter system switch logfile； 8．删除中间过渡的日志组4，5 alter database drop logfile group 4； alter database drop logfile group 5； 9．到操作系统下删除过渡日志文件组4，5中的文件。 10．备份当前最新的控制文件 SQL>connect internal SQL>alter database backup controlfile to trace resetlogs; 联机重做日志文件也是可以移动的，具体的方法是：首先关闭数据库，移动联机重做日志文件，然后安装数据库，使用alter database命令通知数据库联机重做日志文件的新位置。然后就可以用新位置上的日志文件打开实例。 九．设置和管理归档重做日志 当Oracle以archivelog模式运行时,数据库在每个联机重做日志文件写满后,对它进行拷贝,通常是写入磁盘,也可以写入别的设备,但这需要人为的干预的。arch后台执行归档功能，如果有大量频繁的事物的时候，会产生重做日志文件磁盘方面的竞争，避免这种竞争的方式是将联机重做日志文件分布到多个磁盘上。为了提高归档的性能，可以创建具有多个成员的联机重做日志文件组，但是必须考虑到每个设备的I/O。 归档重做日志文件不应与system、rbs、data、temp、indexes表空间等存储在同一个设备中，更不能与任何的联机重做日志文件存储在同一个设备中，以免发生磁盘的竞争。 归档重做日志文件备份之后是可以删除或移走的,否则会占据比较大的空间影响硬盘使用和降低系统的性能。 十．设置和管理控制文件 控制文件的位置在实例初始化参数文件中指定的，若要移动控制文件，必须先关闭数据库实例，移动控制文件，编辑初始化参数文件，然后重新启动该实例。 下面就以Oracle8i为例说明如何移动控制文件: OS为Linux,Database为Oracle8i。 1. 查询当前数据库的控制文件的位置 SELECT * FROM v$controlfile; 2. 把控制文件/u/oradata/foxmold/control01.ctl 移到/db4/oradata/目录下。 3. svrmgrl 4. connect internal 5. shutdown 6. exit 7. cp /u/oradata/foxmold/control01.ctl /db4/oradata/control01.ctl 8. chmod 660 /db4/oradata/control01.ctl 9. initsid.ora control_files=… 10. startup mount foxmold 上面的foxmold代表当前的database name。 十一．总结 以上是针对Oracle数据库来对数据库物理设计作以简单陈述, 针对各种不同的数据库可能会略有不同, 但是整体思想还是一致的。 以上写的难免有不当之处, 欢迎大家批评指正!