数据库性能优化

单击此处编辑母版标题样式,性能问题探讨,2009,年,9,月,架构,我们的系统部署方式虽然多种多样的，但是从整个系统架构来说，不外乎左边图所示的结构。,大纲,操作系统优化,网络优化,数据库优化,中间件优化,应用优化,操作系统优化,目前我公司应用系统部署到的操作系统有：,AIX,、,HP-UX,、,Solaris,、,Linux,、,Windows 2003,。操作系统引起问题的地方主要有两个方面：内核问题和资源问题。,操作系统优化,内核问题,内核问题主要是内核参数的设置方面。,这个参数主要是分为两部分：内存相关的和网络相关的。,比如：我们在安装,Oracle,时未按照官方说明，修改内存相关的内核参数，就会导致无法使用大的共享内存块。,再比如，一些网络参数会影响,weblogic,的吞吐量。,案例：适当调整,udp_sendspace,值,网络参数,udp_sendspace,是,UDP,的发送缓存，默认值为,8K,，为减少,I/O,异常,需调整为,32k,。,操作系统优化,案例：,适录调整,nofiles,值,在东莞和南海都遇到了以下错误：,这个错误主要就是因为应用系统打开了太多的文件句柄。因为在,Unix,下，默认一个用户只能打开,2000,个文件句柄。一旦出现这个错误，,weblogic,将无法对外提供响应。,这个可以通过增加资源限制中的,nofiles,的值来解决。,资源问题,Unix,和,Linux,对每一个用户都有一个资源限制。这些限制可能包括能创建的文件大小、能同时打开的文件数量、能同时启动的进程数、能使用的最大内存等。,UNIX,环境中可以使用,ulimit,查看。,大纲,操作系统优化,网络优化,数据库优化,中间件优化,应用优化,网络优化,网络的问题主要是两个方面：响应时间和吞吐量。,网络优化,响应时间,这里的响应时间主要是查找和连接网络的时间。响应时间过长的常见原因是,DNS,的解析造成的。,案例：,DNS,解析造成服务启动慢,海南,EIP,在项目实施中，遇到服务启动很慢的问题，一个服务的启动时间需要,20,多分钟。后来发现系统走了,DNS,解析，没有走本地解析。修改为本地解析后，启动时间缩短为,4,分钟左右。,网络优化,吞吐量,吞吐量主要是指网络的传输速度。这里只讨论网络设备的设置问题。,案例：交换机传输模式影响网速,由于惠州供电局交换机传输模式是“半双工自适应”，造成网速很慢，表现为我们的系统响应很慢，后改成“全双工”模式，系统响应速度明显提高，输电部、计量部、变电部登录系统时间，及不同设备界面切换时间响应在,2,秒以内。,大纲,操作系统优化,网络优化,数据库优化,中间件优化,应用优化,数据库优化,目前我公司系统使用的数据库主要是,Oracle,。,Oracle,优化主要从,5,个方面来考虑：软件版本、内存、,CPU,、,IO,、数据库对象争用和,SQL,。,数据库优化,软件版本,软件版本可以从两个方面考虑，与操作系统响应的版本、与版本相关的补丁。,如果版本与操作系统不一致，即便能跑起来，也会产生系统问题。,另外，有些版本本身有,bug,，这些,bug,可能会造成性能问题，所以需要打上补丁。一般,2.4,以上的版本被视为稳定版本。,案例：,64,位机器上安装,32,位,oracle,的性能问题,惠州供电局原来的数据库服务器,cpu,是,64,位，但是为了图方便，安装了,32,位,windows 2003,和,32,位的,oracle 10g,，造成只能使用,1.7g,左右的内存，对于,8g,内存的机器来说，很浪费。后来按照,oracle,给出的扩大内存的方法进行调整，依然无效。原因就是该机器,cpu,是,64,位的。,后来改装,64,位,windows 2003,和,64,位,oracle 10g,，性能提高了几倍。,数据库优化,内存优化,一般,Oracle,建议将操作系统物理内存的,70%,分配给它。,比如，物理内存是,8G,，则分配给,Oracle,的内存就是,5.6G,。,Oracle,的内存主要分两块：,SGA,和,PGA,。按照,70%,的物理内存来划分，则是：,SGA,为,60%,，,PGA,为,10%,。,案例：内存设置太小造成性能问题,有人反映，他的系统很慢，感觉主要是数据库慢。连上去看他的数据库，发现,SGA,只有,300M,，,PGA,只有几十,M,。按照,70%,的比例进行调整后，发现数据库性能明显提高，相应的系统吞吐量也大幅提高。,数据库优化,CPU,优化,这里把,Oracle,中的,Processes,归为,CPU,方面，其实也不准确。主要是考虑到它们和,CPU,的利用率有关。,Processes,参数表明允许同时运行的最大服务器进程数。在独占式连接模式下，一个,Session,就启动一个,Process,，你也可以认为建立一个,JDBC,连接就启动一个,Process,。,案例：,Processes,设置太小造成性能问题,如果,Weblogic,中,JDBC,连接池需要建立的连接数大于,Processes,的值时，则会出现连接失败而等待。这时候增大,Processes,的值就会消除这种等待而提高性能。,数据库优化,IO,IO,主要是两个方面：每个用户使用自己单独的临时表空间、尽量关闭数据表空间的自动扩展功能。,案例：每个用户使用自己单独的临时表空间,如果没有给用户设置单独的临时表空间，那么用户就会使用系统临时表空间，从而引起资源竞争。上次东莞供电局邀请第三方做性能诊断时，看到我们的用户没有建立单独的临时表空间。对于这种常识性的错误，被别人揪出来会让人怀疑我们的专业性的。,案例：尽量关闭数据表空间的自动扩展功能,在建表空间时就应该估算到表空间的大小，至少是未来一段时间的空间大小。而不要使用自动扩展，因为自动扩展在实时系统上也是很影响性能的。,数据库优化,数据库对象争用,数据库对象的争用，主要是指表、索引、序列等的争用。,争用这个意思也很好理解，一个对象被引用的多了自然就存在争用，争用就会产生等待，等待就会影响性能。,案例：表被关联的外键太多,如果表被关联的外健太多，当多个关联表同时发生数据变更时，就可能产生等待。所以在设计上，是不是非要坚持范式设计，也是需要我们自己取舍一下。,案例：表上的索引太多,如果表上的索引很多，那么在更新表的数据时，这些索引都需要被更新。所以对于没有使用过的索引可以删除。可以通过,dba_indexes,视图来查询哪些索引没有使用过。,数据库优化,数据库对象争用,案例：实时表的数据太多,原则上来说，实时表的数据应该保持在一个比较小的规模。随着数据的增长，应该考虑历史表和实时表并存。但是很多时候，我们为了查询的方便，所有数据保存在一个表中。造成效率的低下。,这种情况下，可以采取的措施有：,分表,：根据时间（比如：年、月或日为单位），把历史数据分成多个表；,分区,：根据时间或其他条件字段，对该表分区；,绑定到内存,：将整个表绑定到内存，对数据的检索会有一定的提高，但是对于内存要求太高。,数据库优化,数据库对象争用,案例：合理地使用分区能极大提高查询效率,深圳供电局生产系统中的缺陷模块，有一个视图生产系统中的缺陷模块，有一个视图,V_DEFECT_FLOWCONTROL,，这个查询主要是针对流程跟踪表,WFT_FLOWCONTROL,进行查询的。测试时，该表约有,195,万条记录，缺陷的记录约有,17,万条。在对流程跟踪表,WFT_FLOWCONTROL,建立分区前，这个视图的查询时间为,13.29,秒；对流程跟踪表的缺陷的数据建立分区后，查询时间为,02.43,秒，查询效率提高了约,5.5,倍。将缺陷的数据导入另外一个表，对该表建立相同的视图进行查询，时间为,02.26,秒。对比可知，建立合适的分区与分表的效率是比较接近的。,数据库优化,数据库对象争用,案例：避免字段太多的大表,字段太多的表和,JAVA,中的大类相似，阅读困难，更主要的是会影响性能。每次,Insert,、,Update,操作针对部分字段，而我们要操作全部字段。,案例：避免,Blob,、,Clob,字段与其他类型的字段放在一张表中,对于,EJB,的实体,bean,来说，每次读写都要操作这些大字段，很影响性能，并且操作时，加锁时间也长，影响其他用户操作。,案例：定长字符串应该使用,char,类型字段,定长的,char,类型字段比可变的,varchar2,有更好的性能,数据库优化,SQL,SQL,语句对性能的影响是显而易见的。,案例：使用绑定变量,绑定变量可以减少我们相同,SQL,语句的解析时间，从而提高执行效率。但是通过东莞的那次性能诊断，还是看到系统中有不少地方没有使用绑定变量。,案例：合理的使用索引,索引对于在大表中检索少量数据，其效率是非常高的。但是很多时候我们对索引的使用不正确而造成效率低下。主要有以下几个方面：,没有建立索引,：数据量小的时候无所谓，大了就看到效率低下了；,索引使用不上,：建了和查询条件不匹配的索引，根本用不上。要知道，数据库不是有索引就一定会用，而且对于一张表来说，一次查询只能使用一个索引；,索引统计信息没有更新,：还有些情况下，由于索引的统计信息没有更新，导致数据库选择不使用索引。这时候就需要更新索引状态。,Oracle 10g,以后默认自动更新。,数据库优化,SQL,SQL,语句对性能的影响是显而易见的。,案例：正确的使用临时表,正确的使用临时表也会大幅提高系统的性能。因为临时表是和会话绑定的，一个会话一个临时表，所以它不存在锁的问题，而且临时表的数据都是在内存中。有时候当查询很复杂的时候，我们可以把查询分解，把一下中间过程数据放到临时表中，然后对临时表查询，这样会大大提高性能。,案例：合理的使用,hint,Hint,主要用来告诉数据库按照我们设定的执行计划来抽取数据。比如，尽管索引的统计信息没有更新，我们也可以通过,Hint,来使用索引。,但是如果数据量变少了，可能使用索引的效率还不如全表扫描，但是你使用了,Hint,，它还是会走索引。,所以说，,Hint,是一把双刃剑，需要谨慎使用。,案例：合理的使用视图的索引,视图使用索引，实际上是使用视图中原始表的索引。但是如果在视图定义中对原始表的索引字段定义了别名，则不会用到原始表的索引。这一点要注意。,大纲,操作系统优化,网络优化,数据库优化,中间件优化,应用优化,中间件优化,目前我司系统使用的中间件主要是,Weblogic,。中间件优化包括内存设置和参数调整。,中间件优化,Weblogic,建域模式,Weblogic,的建域模式分为开发模式和产品模式，两者有些参数的设置是不同的。,案例：,Weblogic,产品模式提升性能,我在进行南海,EIP,测试时，使用产品模式的性能要比开发模式的性能高。,中间件优化,Weblogic,集群,如果应用因为负载过重而导致性能问题，应该考虑集群,Weblogic,日志级别设置要适当,生成日志会影响性能，尽可能少的生成日志有助于提升性能。,中间件优化,Weblogic,内存设置,Weblogic,的内存调整就是调整,Java,虚拟机的内存。,32,位系统上,JVM,的可配置的最大内存在,1.5G,左右。,64,位则不受此限制。通常情况在设置普通堆的大小时，为了避免扩展带来的性能问题，建议普通堆的最小值和最大值相同。,但是一个非常重要的例外是,IBM,的,JVM,则要求两个不能相同，否则会出现垃圾回收方面的性能问题。,案例：设置,IBM,的,JVM,普通堆的最小值和最大值不同,海南局的,EIP,是基于,IBM,的,JVM,，开始的时候基于以前的习惯将普通堆的最小值和最大值设置相同,(-Xms4096m Xmx4096m),，后来根据网上同行的意见设置为,(-Xms1024m Xmx4096m),。,中间件优化,Weblogic,参数调整,案例：合理调整,Execute Queue and Threads Count,值,执行队列可以理解为一个排队的任务群。每当客户端发出一